Four layers, kept separate
Almost every unusable model fails the same way: assumptions, calculations and presentation are mixed together, so nobody can find what drives what. The fix is architectural and costs nothing if you do it first.
A fourth layer sits alongside: outputs and checks — the summary a reader sees, and the integrity tests that tell you when the model has broken. The discipline is simple. If you are typing a number into a formula, you are creating a bug that will be invisible in six months.
Blue for inputs, black for formulas, green for links to other sheets. It is a fifty-year-old convention and it survives because it works: anyone opening the model can see instantly which cells they are allowed to change.
Revenue is built, not assumed
A growth rate is not a model. It cannot be tested, and when the number misses you learn nothing about why. Build revenue from a volume, a rate and a value — three things you can observe monthly and act on.
| Business | Volume | Rate | Value |
|---|---|---|---|
| Subscription | New sign-ups | Retention / churn | Monthly price |
| Retail | Traffic or sessions | Conversion | Average basket |
| Professional services | Billable capacity | Utilisation | Hourly rate |
| Practice | Patient visits | Case acceptance | Revenue per visit |
| Manufacturing | Capacity in units | Utilisation | Price per unit |
| E-commerce | Sessions | Conversion | Average order value |
The formulas that carry the weight
| Metric | Formula | Why it matters |
|---|---|---|
| Revenue | Volume × Rate × Value | Every miss is attributable to one of three things |
| Contribution margin | (Price − Variable cost) ÷ Price | What each incremental sale actually contributes |
| Break-even units | Fixed costs ÷ Contribution per unit | The volume below which nothing works |
| Customer lifetime value | ARPU × Gross margin ÷ Churn rate | The ceiling on what acquisition may cost |
| Payback period | CAC ÷ (ARPU × Gross margin) | Months until an acquired customer repays |
| Cash conversion cycle | DIO + DSO − DPO | Days of working capital growth consumes |
Lifetime value divides by churn, so small errors compound violently. Moving monthly churn from 3% to 4% cuts implied lifetime by a quarter and changes what you can afford to spend acquiring a customer. If one input deserves real data rather than an estimate, it is this one.
Costs move in three different ways
Modelling every cost as a percentage of revenue produces a business that is always profitable at scale. Nothing real behaves that way, and the error only shows up in the scenarios you built the model to test.
- Variable — materials, merchant fees, direct labour where hours genuinely flex. Model per unit or as a percentage of revenue.
- Step — a supervisor at fifteen staff, a second van, another machine. Flat, then a jump, then flat again. Model with a threshold formula, not a percentage.
- Fixed — rent, insurance, core salaries, software. They do not care what you sold.
Headcount deserves its own schedule with role, start month, base salary and a loading for payroll taxes, benefits and workers' compensation. The loaded cost is commonly 25–40% above base, so a model using base salary understates its largest expense line by roughly a third.
| Cost type | Formula shape | Common error |
|---|---|---|
| Variable | Revenue × rate | Applying it to costs that are actually fixed |
| Step | CEILING(volume ÷ threshold) × cost | Modelled as a percentage, so it never steps |
| Fixed | Constant, with inflation | Forgetting inflation over a five-year model |
| Headcount | Base × (1 + loading) | Base salary only, understating by a third |
The three statements must connect
A model that forecasts profit without tracking cash will happily show a business running out of money and never mention it. That is the standard failure mode for anything growing quickly, and linking the statements is the only cure.
| From | To | Link |
|---|---|---|
| P&L | Balance sheet | Net income increases retained earnings |
| P&L | Cash flow | Net income is the starting line |
| Balance sheet | Cash flow | Every working capital change is a cash movement |
| Cash flow | Balance sheet | Ending cash becomes the cash balance |
Balance sheet balances — assets less liabilities less equity equals zero, every period. Cash ties — ending cash on the cash flow equals cash on the balance sheet. No circularity — interest calculated on average debt creates a loop; break it with a switch or an opening-balance convention. Leave all three visible at the top of the model, not buried on a hidden tab.
A model without integrity checks is not a model. It is a set of numbers that happen to be arranged in a grid.
Scenarios and sensitivity
Vary the two or three assumptions that genuinely drive the outcome. Varying everything produces noise; varying one thing at a time tells you where the model is fragile.
Find the drivers before you build cases
Move each input by ten percent and record the effect on the output you care about. Most models have two or three inputs that dominate and a dozen that barely register. A tornado chart of those sensitivities is worth more than a third scenario.
| Case | What it is | Who reads it |
|---|---|---|
| Base | Most likely, not most hopeful | You, monthly, against actuals |
| Upside | Growth funded and delivered | Investors |
| Downside | Revenue misses, costs do not | Lenders — this is the one they underwrite |
If your upside scenario has more cash in the bank than your base case, the statements are not properly linked. Growth consumes working capital — receivables and inventory are funded before they are collected. An upside case that is richer in every dimension is a model that has not connected its balance sheet.
A model is only useful if you reopen it
The value is not in the forecast. It is in the variance — the monthly comparison between what you expected and what happened, and the revision that follows. A model written once and filed taught you nothing.
- Monthly actuals beside forecast, same rows, same order, with a variance column.
- Explain variances above a threshold, not every line. Five percent or a set dollar amount.
- Revise the assumptions, not the history. If churn is running at 4%, change the input rather than adding a fudge.
- Keep a version dated, so you can see what you believed six months ago and how wrong it was.
- Model monthly for two years, then annually. Annual periods hide seasonality and cannot show the month you run out of cash.
A simple model compared against actuals every month beats a sophisticated one written once. The point was never predicting the future. It was learning how the business actually behaves, and that only happens when you check.