Latest post: Opening fund operations to agents Read the post

Prompt to build a SaaS Forecasting Tool

Subscribers, MRR and ARR, billings and deferred revenue, with contract and billing cycles. Paste the prompt into Claude, ChatGPT or Gemini, answer its questions, and it builds the workbook with the same structure as my SaaS Forecasting Tool.

Free
No sign-in. 1,493 words, for Claude, ChatGPT or Gemini

Or get the finished model

SaaS Forecasting Tool

SaaS Forecasting Tool

Built and tested, for Excel and Google Sheets and in your browser. $20 suggested, pay what you want.

Get the model

How to use it

You need an AI that hands back a real spreadsheet file, not a description of one. Here’s where that lives in each:

  • Claude creates .xlsx files on every plan, free included, and it’s on by default. If it only describes the workbook, open Settings, then Capabilities, and turn on “Code execution and file creation” (called “Create and edit files” in newer versions). On a Team or Enterprise plan your admin controls it.
  • ChatGPT has no switch to find. Ask for the workbook as an .xlsx file and it gives you a download link. A paid plan copes better with a model this size.
  • Gemini builds working spreadsheets inside Google Sheets: open a blank sheet, click the Gemini icon in the side panel and paste the prompt there. That needs a Google AI Pro or Ultra plan or a Workspace plan with Gemini.

The prompt opens with a few questions about your business. Answer them, or tell it to use the defaults and see the model working before you put your own numbers in.

When it’s done, open the workbook and read the model checks before you trust a number. An AI that builds a model from a prompt still makes mistakes, and the checks are there to catch them. If you want a second pair of eyes on it, that’s what a working session is for.

The prompt

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.