Level 3CapstonesProject, 15 min
Capstone: a mini model from revenue to cash
The task
A six-month model for a growing subscription business. Inputs holds three scenarios and a switch in B1. Model turns revenue into profit, then profit into cash, because customers pay late and cash is what runs out. Every row is one formula.
- The scenario switchOn Inputs, in F3, pull the whole column of drivers for the scenario named in B1:
=XLOOKUP(B1, B2:D2, B3:D8). Everything downstream reads this Live column. - RevenueOn Model, in B2, grow the starting revenue by the monthly rate across six months:
=ARRAYFORMULA(Inputs!F3*(1+Inputs!F4)^SEQUENCE(1, 6, 0)). SEQUENCE(1, 6, 0) is the row 0, 1, 2, 3, 4, 5. - Gross profitIn B3, gross profit for all six months: revenue times the gross margin in Inputs!F5.
- Net incomeIn B4, net income: gross profit minus the monthly operating costs in Inputs!F6, for all six months.
- Cash collectedCustomers pay 60% in the month of sale and the rest the month after. In B5, cash collected each month: this month's revenue times Inputs!F7, plus last month's revenue times (1 - F7).
HSTACK(0, B2:F2)is last month's revenue, lined up under this month (nothing before month 1). - Net cash flowIn B6, net cash flow: cash collected minus everything paid out. Costs paid are revenue minus net income (cost of sales plus operating costs), so the row is
B5:G5 - (B2:G2 - B4:G4). - Ending cashIn B7, the cash balance at the end of each month: start from the opening cash in Inputs!F8 and add each month's net cash flow with
SCAN. - The number the CFO asks forIn B9, the lowest cash balance over the six months.
The data, 2 tabs
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Scenario | Base | ||||
| 2 | Driver | Base | Upside | Downside | Live | |
| 3 | Starting monthly revenue | $40,000 | $40,000 | $40,000 | ||
| 4 | Monthly growth | 6% | 10% | 2% | ||
| 5 | Gross margin | 62% | 65% | 58% | ||
| 6 | Monthly operating costs | $21,000 | $23,000 | $20,000 | ||
| 7 | Collected in month of sale | 60% | 70% | 50% | ||
| 8 | Opening cash | $50,000 | $50,000 | $50,000 |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | M1 | M2 | M3 | M4 | M5 | M6 | |
| 2 | Revenue | ||||||
| 3 | Gross profit | ||||||
| 4 | Net income | ||||||
| 5 | Cash collected | ||||||
| 6 | Net cash flow | ||||||
| 7 | Ending cash | ||||||
| 8 | |||||||
| 9 | Lowest cash | ||||||
| 10 | |||||||
| 11 | |||||||
| 12 |