Excel templates · SaaS & subscriptions
Subscription Revenue Forecast Template (Excel) — Free
The forecast that connects the two halves of a subscription business: what you spend to acquire customers, and what is left of them twelve months later. Change the budget or the churn rate and the whole curve moves.
The download
Free download
Download: Subscription revenue forecast
- New customers derived from spend ÷ CAC
- Churn applied to the opening base, not the closing one
- MRR, gross margin and gross profit
- What is left once marketing is paid for
No email, no sign-up. Formulas already wired up.
The sheet itself
- New customers derived from spend ÷ CAC
- Churn applied to the opening base, not the closing one
- MRR, gross margin and gross profit
- What is left once marketing is paid for
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Subscription revenue forecast | |||||||||||||
| 2 | Fill in the blue cells — every other cell is a formula and works itself out. foreqast.app | |||||||||||||
| 3 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||
| 4 | ||||||||||||||
| 5 | €4,000 | €4,000 | €5,000 | €5,000 | €6,000 | €6,000 | €7,000 | €6,000 | €7,000 | €8,000 | €10,000 | €9,000 | ||
| 6 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | ||
| 7 | 19 | 19 | 24 | 24 | 29 | 29 | 33 | 29 | 33 | 38 | 48 | 43 | ||
| 8 | ||||||||||||||
| 9 | 200 | 212 | 224 | 240 | 256 | 276 | 295 | 318 | 336 | 357 | 383 | 418 | ||
| 10 | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | 3.5 % | ||
| 11 | 7 | 7 | 8 | 8 | 9 | 10 | 10 | 11 | 12 | 12 | 13 | 15 | ||
| 12 | 212 | 224 | 240 | 256 | 276 | 295 | 318 | 336 | 357 | 383 | 418 | 446 | ||
| 13 | ||||||||||||||
| 14 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | ||
| 15 | €18,868 | €19,936 | €21,360 | €22,784 | €24,564 | €26,255 | €28,302 | €29,904 | €31,773 | €34,087 | €37,202 | €39,694 | ||
| 16 | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | 82.0 % | ||
| 17 | €15,472 | €16,348 | €17,515 | €18,683 | €20,142 | €21,529 | €23,208 | €24,521 | €26,054 | €27,951 | €30,506 | €32,549 | ||
| 18 | €11,472 | €12,348 | €12,515 | €13,683 | €14,142 | €15,529 | €16,208 | €18,521 | €19,054 | €19,951 | €20,506 | €23,549 | ||
The blue cells are yours to fill in. Every other cell is a formula and works itself out.
How to use it
- 1
Set CAC from what you actually paid last quarter
Total acquisition spend ÷ new customers, including the salaries of the people doing it. A CAC that only counts ad spend is roughly half the real number.
- 2
Do not let CAC stay flat as spend grows
Doubling the budget rarely doubles the customers. If you plan to grow spend by 2.5× over the year, raise CAC across the same period.
- 3
Apply churn to the opening base
Customers who join this month rarely churn this month. Applying the rate to the closing count double-counts your own new signups.
- 4
Read the last row, not the MRR row
MRR after marketing is what the business actually earns. Growing MRR with a shrinking bottom row is growth you are buying, not earning.
A worked example
2.5× the marketing budget across the year
- Customers, January
- 212
- Customers, December
- 423
- MRR, December
- €37,647
- Marketing spend, December
- €9,000
- After marketing, December
- €21,871
The base doubles and the contribution after marketing roughly triples — the model works. But at 3.5% monthly churn, the business loses about 15 customers a month by December against 43 gained: a third of the marketing budget is buying replacements. Cutting churn to 2% would be worth more than another €3,000 a month of spend.
The mistakes that break it
Holding CAC constant while scaling spend
It is the single assumption that makes almost every subscription forecast too optimistic, and it compounds every month.
Forecasting MRR without a churn row
Adding new customers to a base that never shrinks produces a curve no subscription business has ever had.
Ignoring the gross margin on the revenue
Hosting, support and payment fees take 15–25% of subscription revenue. Comparing gross MRR to CAC overstates the return every time.
Where the template stops
It cannot see when the money arrives
Annual plans are paid up front, monthly plans over a year, and marketing is paid before either. Same MRR, very different bank account.
12-month cash-flow planOne churn rate for every cohort
New customers churn far faster than customers of two years. A single rate makes a young business look stable and an old one look fragile.
Churn & cohort analysisIt is only as current as your last evening with it
Every number in here is typed in. The month you skip is the month the sheet quietly stops describing your business — and it never says so.
What comes after the spreadsheet
Losing a clientTake the biggest customer out and see what is left — in cash, per month, on the dates it actually happens.Calculators