Guide
How to structure a financial model
A good model is boring: inputs in one place, calculations in a predictable order, outputs a reader can follow without opening every tab.
Structure a model in this order: assumptions, revenue build, cost build, P&L, cash flow, balance sheet, funding and debt, scenarios, outputs. Each sheet should only reference sheets before it.
Sheet order and purpose
| Sheet | Contains | Notes |
|---|---|---|
| 1. Assumptions | Every input, with units and basis | The only place numbers are typed |
| 2. Revenue | Volume × price by stream | Monthly, with seasonality index |
| 3. Costs | Fixed, variable, step costs | Staffing separated |
| 4. P&L | Revenue − costs = result | Monthly then annual |
| 5. Cash flow | Receipts and payments | Timing, not accrual |
| 6. Balance sheet | Assets, liabilities, equity | Checks and balances |
| 7. Funding | Drawdowns, repayments, interest | Debt schedule |
| 8. Scenarios | Switches and outputs | Base, downside, upside |
| 9. Summary | One-page output | For the reader |
Assumptions sheet
Each row: name, value, unit, basis, source, confidence.
Illustrative example — invented figures for a fictional business:
| Assumption | Value | Unit | Basis |
|---|---|---|---|
| Average price | 45 | SAR | Competitor benchmark |
| Daily transactions | 120 | count | Capacity estimate |
| Trading days | 26 | days/month | Operating calendar |
| COGS | 30% | % revenue | Supplier quotes |
| Rent | 8,000 | SAR/month | Lease quote |
| Staff | 5 | FTE | Operating plan |
Revenue build
Keep the calculation visible: transactions per day × trading days × average price × seasonality index.
For multiple streams, one block per stream, then a total. Do not merge streams into one growth rate; the reader cannot test it.
Cost build
Separate three behaviours:
- Fixed — rent, salaried staff, insurance, licences.
- Variable — materials, packaging, payment fees, delivery.
- Step — second shift, additional vehicle, new location.
Step costs are the ones that break models. Add a line that flags when a step is triggered.
Cash flow
Cash flow is not the P&L with a timing adjustment bolted on. Build it from receipts and payments:
| Row | Basis |
|---|---|
| Opening cash | Prior month closing |
| Cash receipts | Revenue adjusted for collection period |
| Supplier payments | Costs adjusted for payment terms |
| Payroll | Paid in month |
| Capex | Per capital schedule |
| Loan drawdown / repayment | Per debt schedule |
| Closing cash | Sum |
If closing cash is negative, the model is telling you the funding requirement. That is the model doing its job.
Balance sheet, briefly
A simple balance sheet should balance: assets (cash, receivables, inventory, fixed assets) = liabilities (payables, debt) + equity (paid-in capital, retained earnings).
If it does not balance, there is a timing error in the cash flow or a missing accrual. Do not plug the difference.
Scenarios
Use a single switch cell (1, 2, 3) that drives named changes:
| Scenario | Volume | Ramp | Price |
|---|---|---|---|
| Base | 100% | Plan | Plan |
| Downside | 80% | +3 months | Plan |
| Upside | 115% | −1 month | Plan |
Document what changes in each case. A scenario switch that nobody understands is worse than no scenario.
Presentation rules
- One currency, stated on every output.
- Round outputs to whole units; keep inputs precise.
- No hard-coded numbers in formulas.
- Colour-code inputs (blue) and formulas (black).
- A summary tab that fits on one page.
For a built model, see financial modelling. For the surrounding document, see business plan writing.
A worked mini-model
Illustrative example for a fictional business — invented figures.
| Line | Month 1 | Month 6 | Month 12 |
|---|---|---|---|
| Transactions per day | 40 | 95 | 120 |
| Trading days | 26 | 26 | 26 |
| Average price (SAR) | 45 | 45 | 45 |
| Revenue (SAR) | 46,800 | 111,150 | 140,400 |
| Cost of goods (30%) | 14,040 | 33,345 | 42,120 |
| Gross profit | 32,760 | 77,805 | 98,280 |
| Fixed costs | 62,000 | 62,000 | 68,000 |
| Net result | (29,240) | 15,805 | 30,280 |
The step from SAR 62,000 to SAR 68,000 in month twelve is a step cost — an additional staff member. Without that line, the model would overstate profit by SAR 6,000 a month from the point the hire is needed.
That is the whole discipline of modelling: every cost that changes with scale appears at the point it changes, not as a smooth annual average.
Naming and documentation conventions
- Prefix input cells with
in_and calculations withcalc_if the model will be maintained by someone else. - Name the scenario switch cell, so formulas read
=IF(scenario=2, 0.8, 1). - Put a date on the assumptions sheet.
- Record who changed what in a version log.
Those conventions take an hour to set up and save days later, particularly when a lender asks for a revised case two weeks before submission.
Structural mistakes that cost days
| Mistake | Consequence | Better approach |
|---|---|---|
| Mixing inputs and formulas on one sheet | Impossible to audit or update | Separate assumptions tab |
| One giant sheet for everything | Errors hide; reviewers give up | One purpose per sheet |
| Copy-pasting months instead of referencing | A change breaks the model | Build month one, drag with absolute references |
| Circular references | The model recalculates unpredictably | Use a separate interest calculation on opening balances |
| Hard-coded totals | They drift from the detail | Sum the detail rows |
| No version control | Nobody knows which file is current | Date-stamped file names and a change log |
A model that is easy to audit is also easier to defend. When a lender asks "what happens if rent rises by 10%", the answer should take one cell change, not a rebuild.