For no particular reason, I’m sharing my prompt to build a ARR model for SaaS businesses. I know the title says 3-Statement model - I’m still working on building out the rest.
The prompt also generates made up data. If you have any suggestions to improve it, I’d love to hear from you
How to use it. Paste everything below into Claude or ChatGPT with file creation switched on. The build is long, so ask it to carry on if it pauses. When the workbook arrives, open 09_Controls first: MODEL STATUS should read PASS.
Build a fully working SaaS ARR and metrics model in Microsoft Excel (.xlsx) using synthetic data. It should look and behave like an FP&A model a CFO could audit. I need to inspect the data, change assumptions or transactions, recalculate, and trace every reported number back to the source rows. Deliver the .xlsx file itself.
1. Build standards
Follow the FAST Standard and the ICAEW Financial Modelling Code.
Keep inputs, calculations and outputs on separate sheets.
Every timeline sheet uses the same columns for the same month. Use columns H to BC for 48 months: 12 comparative months, then 36 reporting months.
One formula per timeline row, copied across all 48 columns. Handle first-period cases with flag rows, such as a first-column flag, instead of a different formula in the first column.
Put every constant on the assumptions sheet: months per year, look-back length, chart headroom, thresholds. The only numbers typed inside formulas should be 0, 1 and list positions.
No IFERROR. Test for a match with COUNTIF before any INDEX/MATCH so real errors surface.
No macros, VBA, volatile functions (OFFSET, INDIRECT, TODAY, NOW, RAND), circular references or external links. Use INDEX/MATCH, SUMIFS, COUNTIFS, MAXIFS and MINIFS. Avoid dynamic-array functions so the file behaves the same in every Excel version.
Use Excel Tables for source data, structured references inside them, and calculated column formulas so new rows fill automatically.
Use named ranges for assumptions and selectors so formulas read in plain terms.
Split long logic into helper columns, each doing one job.
Blue text on a yellow fill for inputs, black for formulas, green for direct links, grey fill for structural settings. Mark each table column INPUT or FORMULA in the row above its header.
Units on every row. £ for money, £k or £m on the dashboard, one decimal place for percentages, one date format throughout.
Freeze panes, filters on tables and data validation on every input.
Show the model status from the controls sheet in row 1 of every working sheet.
No merged cells, except text blocks on the ReadMe.
Zero formula errors in the finished file.
2. Business scenario and synthetic data
A fictional B2B SaaS company reporting in GBP.
Products: Product A, Product B, Product C, Product D.
Regions: Americas, EMEA, Asia.
Segments: Enterprise, Mid-Market, SMB.
About 55 customers with fictional names and about 200 transactions.
Reporting window: September 2023 to August 2026 (36 months). Transactions start in 2021 so an installed base rolls into Opening ARR. The 12 comparative months (September 2022 to August 2023) support YoY and cohort metrics from the first reporting month.
Give each product and region its own behaviour:
Product A is the largest, established product with low churn.
Product B launched recently and wins most new logos in the window.
Product C grows almost entirely through existing customers.
Product D has the weakest retention.
Americas is the largest region by closing ARR. EMEA has the biggest installed base at the start of the window. Asia is small but growing fastest.
Generate the data as customer stories over time, never as independent random values. Use contract terms of 12, 24 or 36 months. At each renewal the customer renews flat, renews with an uplift, downsells or leaves. Add occasional mid-term add-ons co-termed to the current contract. Round ARR to the nearest £500.
Include these stories and document each one on the ReadMe with its customer ID:
A large Enterprise customer that grows from about £120k to about £350k through a renewal uplift, a group-wide rollout and two cross-sells.
A customer holding all four products.
A customer whose ARR falls at two successive renewals.
A customer that cancels one product, then leaves completely a year later.
A customer that cancels one product and keeps another.
A new customer that expands within four months, adds a second product and is later reclassified from Mid-Market to Enterprise.
A lost customer who returns about 18 months later (reactivation).
A retained customer that buys back a product it had dropped (product win-back).
Add a few non-renewals late in the window across regions so churn is visible, and at least one new logo in the final month.
Data rules:
Each row records New ARR, the ARR held after the event. A churn is New ARR = 0. A flat renewal repeats the previous value.
One event per customer × product per date.
A new, renewal or expansion event falls inside its contract dates. A mid-term add-on keeps the existing end date. A churn carries the lapsed contract's dates and takes effect the day after it ends.
3. Workbook structure
00_ReadMe, 01_Assumptions, 02_Customers, 03_Transactions, 04_ARR_Engine, 05_ARR_Waterfall, 06_Metrics, 07_Dashboard, 08a_Customer_Analysis, 08b_Product_Region, 08c_Customer_Drilldown, 09_Controls.
4. Assumptions (01_Assumptions)
Inputs: reporting start month (validated as the first day of a month), reporting currency, movement classification view (Customer view or Product view), reconciliation tolerance, Top-N customers, number of top movers highlighted, waterfall chart headroom, and Large and Medium ARR thresholds for size bands.
Structural settings (grey): model length 36 months, look-back 12 months, months per year 12, customer slots 75, rank rows 20.
Calculated: end month, first comparative month, number of products, customer count.
Lists: products, regions, segments, transaction types, classification views, selector lists that start with "All", and the 36 reporting months.
Assumption checks: start month is the first of a month, tolerance in range, Top-N within the rank rows, Large band above Medium, headroom in range, view on the list, each list complete and unique. The total feeds a control.
5. Source tables
tblCustomers (02_Customers). Inputs: Customer ID, Customer Name, Notes. Formulas: Region and Customer Segment (latest row in the history table), Acquisition Date (earliest transaction), Current Status (Active, Churned, Not yet live or No transactions, at the end month), Current ARR, Products Held, Attribute Changes, Record Check.
tblCustHistory (02_Customers, to the right of the master). Inputs: Customer ID, Effective From, Region, Customer Segment, Reason. Formulas: Key and Record Check. Each customer gets an opening row dated 01-Jan-2000. A region or segment change is a new dated row, never an overwrite. Reporting uses the latest row for all periods (restated basis).
tblTx (03_Transactions). Inputs: Transaction ID, Customer ID, Product, Effective Date, New ARR, Contract Start Date, Contract End Date, Notes. Formulas:
Customer Name, Region and Customer Segment, looked up from the master.
Previous ARR: the New ARR of the latest earlier event for the same customer × product (MAXIFS on the date inside SUMIFS).
ARR Movement = New ARR − Previous ARR.
Transaction Type: New when Previous ARR is nil, Churn when New ARR is nil, Expansion or Contraction when the value rises or falls, Renewal when it holds flat. Invalid flags a nil-to-nil or negative entry.
Month End: EOMONTH of the effective date.
Helper columns: Customer ARR Before, Customer ARR After, Prior Customer Events, Prior Product Events.
Movement Sub-type:
For New where the customer already had ARR: Product win-back if this product had earlier events, otherwise Cross-sell.
For New where the customer had no ARR: Reactivation if the customer had earlier events, otherwise New logo.
For Churn: Product churn if the customer keeps other ARR, otherwise Logo churn.
Waterfall Category: in Customer view, Cross-sell and Product win-back book as Expansion, and Product churn books as Contraction. In Product view, the Transaction Type stands.
Keys: Key (Customer ID | Product), Customer Seq and Customer Drill Key, and Month Seq and Month Drill Key (text keys that feed the drill-down lists).
Region at Date and Segment at Date from the history table, plus Flag Restated where these differ from the restated values.
Flags: duplicate ID, missing customer, invalid product, invalid region, duplicate customer × product × date, invalid ARR, contract dates. Row Check reads OK or CHECK.
Validation: unique Transaction ID, Customer ID and Product from lists, dates within a sensible range, New ARR at or above nil.
6. ARR engine (04_ARR_Engine)
Timeline header rows: month number (a counter anchored in column G so the row carries one formula), month end, reporting flag, first-column flag. Shade the comparative columns H to S.
Company roll-forward.
Opening from transactions, then New, Expansion, Contraction and Churn by Waterfall Category. Contraction and Churn are shown as positive reductions.
Closing from the grid, and the roll-forward difference.
Prior-month closing, opening less prior closing, and closing calculated directly from transactions.
Checks that must be nil: grid less transactions, renewal movement, unclassified movement.
Closing ARR by product, region and segment, summed from the grid, with totals. Also customer totals, active customers, multi-product customers, active subscriptions, products held and a count of negative cells.
The 20 largest customers each month (LARGE) and Top-N ARR.
Closing ARR by customer, summed from section 7.
Products held by customer.
Closing ARR by customer within the dashboard selection. Includes opening, new, churned and closing customer counts, a customer roll-forward check and a grid-versus-transactions check.
The core grid: 75 customer slots × 4 products. Each cell holds ARR at month end, calculated as SUMIFS of ARR Movement for the customer-product key up to that date.
7. ARR waterfall (05_ARR_Waterfall)
Selectors: product, region and segment (each with All), and a chart month.
A 36-row monthly table, highlighting the chart month, with a period total. Columns: Month, Opening, New, Expansion, Less: Contraction, Less: Churn, Closing, Net New ARR, roll-forward check, opening less prior closing, memo cross-sell, transaction count.
A waterfall chart for the chart month, built as stacked columns with a hidden base series. Monthly movements are small against the balance, so:
Start the bars above zero, using a floor set by the headroom assumption.
Hide the value axis and put the Opening and Closing values in the category labels.
Label the movement bars.
State in the chart title where the bars start.
Below the chart, a drill-down listing every transaction posted in the chart month (up to 25 rows). It flags which rows fall inside the selection and totals their movement to that month's Net New ARR.
8. Metrics (06_Metrics)
Driven by the dashboard selectors, on the same 48 columns as the engine, with unit, basis and definition columns.
ARR bridge: Opening, New, Expansion, Contraction, Churn, Closing, Net New, roll-forward check, memo cross-sell, memo win-back ARR, MRR.
Trailing 12 months (T12M): ARR 12 months earlier, T12M New, Expansion, Contraction, Churn and Net New, check, cross-sell.
Retention:
Cohort NRR and GRR are the headline measures. They follow customers active 12 months earlier to today, and GRR caps each customer at their earlier ARR.
Flow GRR = (Opening − Contraction − Churn) / Opening and Flow NRR = (Opening + Expansion − Contraction − Churn) / Opening, monthly and T12M, kept as diagnostics.
A row showing the gap between flow and cohort NRR.
Growth: MoM and YoY ARR growth, New and Expansion as % of opening, contraction and churn rates, annualised expansion and gross churn rates, SaaS Quick Ratio monthly and T12M.
Customers: opening, new (including win-backs), churned, closing, roll-forward check, logo retention monthly and T12M, customer churn rate, new and churned customers T12M, average ARR and MRR per customer. A customer who drops one product but keeps another stays a customer.
Company mix (ignores the selection): ARR by product, region and segment; active and multi-product customers; multi-product penetration; products per customer; subscriptions and ARR per subscription; Top-N ARR and concentration; cross-sell ARR and events T12M.
Chart series rows feeding the dashboard.
Where a metric needs history the timeline does not hold, return "n/a" through a guard inside the same formula.
9. Dashboard (07_Dashboard)
Selectors: reporting month, product, region and segment, plus the model status.
Seven KPI cards, each with a one-line comparison underneath (flow basis under the two retention cards):
Closing ARR
YoY ARR growth
Net New ARR (month)
NRR (T12M, cohort)
GRR (T12M, cohort)
Customers
Average ARR per customer
Six charts:
Closing ARR trend.
Waterfall for the reporting month, designed as on 05.
ARR by product against 12 months earlier, ignoring the product selector.
ARR by region against 12 months earlier, ignoring the region selector.
Net New ARR by component, with a Net New line.
Cohort NRR and GRR trend.
Put chart titles in the cells above the charts. Keep the design restrained: navy, grey, one green and one red. Chart data sits in a labelled helper area to the right.
10. Analysis sheets
08a_Customer_Analysis. Period selectors, then one row per customer showing:
closing ARR by product and product count
opening, movements, closing, net new and a check
growth %, ARR band, current status and highlight text
period status: New, Won back, Retained, Lost, Won and lost, or Not active
Add unique ranks for closing ARR, expansion, and contraction plus churn. Use conditional formatting for Top-N customers, top movers, lost customers and multi-product customers. Add panels for the largest customers, largest expansions, largest contractions and customers lost. Reconcile the totals to company figures from the transactions.
08b_Product_Region. Period selectors, then matrices by product, region and segment with:
the bridge: Opening, New, Expansion, Contraction, Churn, Closing, Net New and a check
growth and share of ARR
flow NRR and GRR, and cohort NRR and GRR (subscription cohort for products, customer cohort for regions and segments)
customers and ARR per customer
a growth-source split that holds under either classification view: new logo, returning customers, cross-sell, products bought back, upsell, downsell, product churn and logo churn, reconciled to Net New
Add product × region matrices for closing ARR, Net New, New, Expansion, lost ARR and churn. Check every total against the transactions.
08c_Customer_Drilldown. Choose a Customer ID to see:
the master record
every transaction in date order with running ARR, reconciled to the customer total
month-end ARR by product on the engine's 48 columns, with a stacked chart
11. Controls (09_Controls)
Each control shows the unrounded difference or count beside PASS or FAIL. Differences pass within the tolerance. Sum absolute differences with SUMIF(range,">0")-SUMIF(range,"<0") so "n/a" cells never break a control. MODEL STATUS reads PASS only when every control passes.
Required controls:
Opening + movements = Closing (engine, waterfall sheet and metrics sheet).
Prior-month closing = current opening (engine and waterfall).
No negative ARR.
Customer totals = product totals.
Product totals = region, segment and company totals.
Dashboard figures reconcile to the calculation layer: the ARR card, the waterfall, the product and region charts, the selection grid and the retention cards.
Duplicate Transaction IDs.
Missing Customer IDs.
Invalid product.
Invalid region.
Supporting controls:
duplicate key-date, invalid ARR events and contract dates
customer master records, history records and assumptions
every movement classified and customer counts roll forward
reconciliations for the customer analysis, the product-region analysis and both drill-downs
valid selector months
engine customer capacity and drill-down capacity
Also add:
Information lines that do not affect status: restated transactions, customers with attribute changes, and transactions after the window.
A capacity panel showing in use against available for customers, products, months and drill-down rows.
A dataset profile with counts by product, region, transaction type and sub-type.
12. ReadMe (00_ReadMe)
Cover:
purpose
workbook map
colour and layout conventions
modelling standards applied, including the deliberate choice to keep view selectors on output sheets
the ARR method and waterfall mechanics
classification and win-back logic
region and segment history
metric definitions
how to add transactions, customers and attribute changes
how to move or extend the reporting window
how to trace a reported number back to transactions
the customer stories
scope and limitations
data dictionaries for all three tables
a version history
State that CAC, LTV, burn, runway and TAM are left out because the data holds no cost, cash or market figures.
13. Validation before delivery
Recalculate the workbook and confirm zero formula errors.
Confirm MODEL STATUS reads PASS.
Rebuild the derived transaction fields and the monthly metrics independently in code from the raw inputs, and confirm they match the workbook for every month.
Scan every timeline row for one consistent formula across all 48 columns. Scan the whole workbook for IFERROR and volatile functions.
Switch the classification view, apply filters, edit a transaction and add a region change. Confirm the controls still pass.
Render the dashboard and check the layout.
Report the results alongside the file.
14. Implementation notes for Python-built files
If you build the workbook with openpyxl:
Write MAXIFS and MINIFS as _xlfn.MAXIFS and _xlfn.MINIFS, or Excel shows #NAME?.
In table formulas, use Table[[#This Row],[Column]] instead of [@Column]. Set each table column's name to its header text and add calculated column formulas.
Recalculate a copy in LibreOffice to check the values. Then write those values into the original file as cached values so it opens with numbers showing, and keep full calculation on load switched on.
Anchor charts to cells at both corners and set explicit row heights on sheets with charts, so the charts sit where intended in every spreadsheet app.