# Build a SaaS Forecasting model

Source: https://www.hemrock.com/saas-forecasting-tool/prompt

Paste everything below into Claude, ChatGPT or Gemini.

---

You are an experienced SaaS finance modeler. Build me a monthly SaaS revenue forecast in a spreadsheet. It forecasts subscribers from a growth rate and churn, handles contract and billing cycles that differ, and turns that into revenue, MRR and ARR, billings, deferred revenue and unit economics. It's a revenue model: no headcount, operating expenses or financial statements.

## Before you build

Ask me these questions first, in one message, and wait for my answers. If I say "use the defaults", use the defaults below.

1. What's your average revenue per subscriber per month, and your gross margin?
2. How many new subscribers do you add a month now, and how fast is that growing? Do you have existing subscribers?
3. How long are your contracts, and how often do you bill (monthly, annually upfront)?
4. What share of subscribers churn at each renewal? Do you know your retention by cohort month instead?
5. What does it cost to acquire a subscriber, and to renew one?

## Workbook structure

- **Get Started**: every input, and nothing else.
- **Forecast**: one column per month, with the timeline, subscriber flows, revenue, MRR and unit economics. A summary block by fiscal year sits at the top or the right.
- **Key Reports**: charts only, plotting rows from the Forecast.

## Inputs (Get Started)

| Input | Default |
| --- | --- |
| Company name | Company |
| Currency symbol | $ |
| First month of the forecast | Jan of next year |
| Months to forecast (12 to 120) | 48 |
| Fiscal year end month | December |
| Average revenue per subscriber, per month | $30 |
| Gross margin, % | 87.5% |
| Customer acquisition cost (CAC), per new subscriber | $25 |
| Retention cost, per renewed subscriber | $5 |
| Deferred revenue at the start | $0 |
| Subscribers at the start | 0 |
| New subscribers in the first month | 25 |
| Monthly growth in new subscribers, % | 10% |
| Monthly change in that growth rate, % of itself | −5% |
| Retention approach (dropdown: Overall churn, Cohort curve) | Overall churn |
| Churn per contract cycle, % | 5% |
| Reactivation rate, % of last month's churned subscribers | 0% |
| Contract cycle, months | 12 |
| Billing cycle, months | 12 |
| Months of customer lifetime counted in LTV | 60 |
| Cohort curve: share of a cohort still subscribed, by month since starting (one input per month, 120 months) | 100%, then 97% of the prior month |

## Calculations (Forecast)

Timeline rows: month number, period end date, fiscal year and quarter, all formulas from the first month and fiscal year end.

New subscribers. The growth rate in month 1 is the input; each later month it changes by the "change in growth rate" percent of itself (so 10% becomes 9.5%, then 9.03%). New subscribers in month 1 are the input; each later month is the prior month × (1 + that month's growth rate).

Overall churn approach:
- Subscribers come up for renewal one full contract cycle after they started or last renewed. Starting subscribers are spread evenly across the first contract cycle, so 1/12 of them come up each month for a 12-month contract.
- At renewal, the churn percent leave and the rest renew.
- Reactivated = last month's churned × the reactivation rate.
- Rows: subscribers at the beginning of the month, not up for renewal, new, renewed, churned, reactivated, subscribers at the end of the month (beginning + new + reactivated − churned), and subscribers up for billing (those reaching the end of a billing cycle, using the same logic as renewals but on the billing cycle).

Cohort curve approach (when selected):
- Active subscribers = the sum over every monthly cohort of that cohort's new subscribers × the curve value for its age in months.
- Renewed = cohort members reaching a multiple of the contract cycle. Churned = the drop in active subscribers from the curve. Reactivation is off in this mode.
- Starting subscribers keep following the overall churn logic.

Revenue and cash:
- Revenue = ending subscribers × revenue per subscriber.
- Marketing spend = new × CAC + renewed × retention cost + reactivated × CAC.
- Billings = billing cycle × revenue per subscriber × subscribers billed this month (every subscriber when the billing cycle is 1; otherwise new, renewed and reactivated subscribers plus those reaching a billing date mid-contract).
- Deferred revenue = prior month + billings − revenue, starting from the input.

MRR, as a waterfall that ties out each month: beginning MRR (last month's ending), existing MRR (not churned or renewed), new MRR, renewed MRR, churned MRR (negative), reactivated MRR, ending MRR. ARR = ending MRR × 12. Also show subscriber growth, gross MRR churn % and net MRR growth % by month.

Unit economics:
- Expected subscribed months within the LTV cap: under overall churn, the sum over months 0 to LTV months − 1 of (1 − churn) raised to the number of renewals passed (month ÷ contract cycle, rounded down), because churn lands at each renewal, not every month; under the cohort curve, the sum of the curve over the LTV months.
- LTV = expected months × revenue per subscriber × gross margin.
- LTV ÷ CAC, and CAC payback in months = CAC ÷ (revenue per subscriber × gross margin).

Fiscal-year summary: revenue, billings, marketing spend, new and churned subscribers, ending subscribers, ending MRR and ARR, and year-over-year growth, using SUMIFS on the fiscal year row (ending values take the last month of each year).

## Key Reports

Charts: ending subscribers with new and churned as columns; the MRR waterfall as stacked columns; revenue versus billings; deferred revenue; ARR by fiscal year.

## Model checks

TRUE or FALSE: subscribers never go negative; the subscriber roll-forward ties (beginning + new + reactivated − churned = ending) in every month; ending MRR equals ending subscribers × revenue per subscriber; deferred revenue never goes negative; the cohort curve starts at 100% and never rises.

## Optional: enterprise pipeline

If I tell you I sell larger contracts one at a time, add a "Pipeline" sheet instead of the subscriber flows: one row per prospective client with likelihood to close, total contract value, contract cycle, billing cycle, start date, and % expansion or churn at renewal. Calculate expected bookings (value × likelihood), billings on the billing cycle, revenue recognized evenly over each contract, and deferred revenue. Use one approach or the other, not both added together.

## How the workbook should look and behave

These are my house rules for spreadsheets. Follow all of them.

- Use Arial 12 everywhere, including the workbook's default style. Never Calibri.
- Inputs are blue text (#0000FF) on a light grey fill (#F2F2F2), so they read like form fields. Calculations are black text with no fill. Never use grey text anywhere.
- Every sheet leaves column A blank and narrow. The model starts in column B.
- Column B holds row labels. Column C is a narrow Notes column, about half the width of the label column, holding a short note on what the row is or where it comes from. Let notes clip rather than wrap. Values, or the first period of a timeline, start after the notes column.
- No cell comments. Notes go in the notes column.
- Turn gridlines off on every sheet. Freeze panes on the header rows only, never on columns.
- No borders at all, not even header underlines or subtotal rules. Blank rows and bold text carry the structure.
- Every calculated number is a live formula that traces back to an input. Never type a number into a calculation cell, and never mix typed values and formulas in the same row.
- Keep the number of sheets low. Sheets that share a timeline use the same columns, so a given column is the same month on every sheet.
- Format currency with thousands separators and no decimals, percentages with one decimal place, and dates as "mmm yyyy".
- Name rows in plain words a founder or investor would use, with units in the label where it helps ("Churn per contract cycle, %").

## When you're done

- Recalculate and confirm there are no errors (#REF!, #DIV/0!, #VALUE!, #N/A) and no circular references unless I asked for one.
- Show me the results of the model checks and the headline outputs.
- List every assumption you made that I didn't give you, and the three inputs I should change first.
- If you can't produce an .xlsx file, give me each sheet as a table with the exact label, input value or formula in every cell, so I can rebuild it in Excel or Google Sheets without guessing.
