A financial model is a spreadsheet that turns assumptions about a business into forecast financial statements and a valuation. Models are used to value companies, price deals, set budgets, test debt capacity and support decisions. A model is only useful if others can follow and trust it.
One workbook, with tabs in this order:
Small models can put the three statements on one sheet, stacked vertically, with years in the same columns throughout.
| Element | Convention |
|---|---|
| Hard-coded inputs | Blue font |
| Formulas | Black font |
| Links to other sheets | Green font |
| Historical periods | Labelled A (actual); forecasts labelled E (estimate) |
| Negative numbers | Parentheses |
| Units | Stated once at the top (for example, $ millions) |
| Time | Flows left to right, one column per period, same column for the same year on every sheet |
SUM, IF, MIN, MAX, AVERAGE, INDEX and MATCH (or XLOOKUP), CHOOSE or OFFSET for scenario switches, NPV, XNPV, IRR, XIRR, and data tables for sensitivity analysis.
Decide the purpose, the level of detail, the number of historical years (three) and forecast years (five is standard), annual or quarterly periods, and which outputs are needed.
A well-built model flows one way: inputs feed calculations, calculations feed outputs, and nothing flows back. When an output cell feeds an input, or a calculation on the income statement reaches forward into a valuation tab and back again, errors become hard to trace. Draw the flow on paper before building: which tab feeds which.
Every input should say where it came from: a filing and page number, management guidance, or 'analyst assumption'. A notes column beside the inputs, a change log on the cover sheet and a short description of each tab make a model usable by someone else, and by its builder six months later.
Collect every check (balance sheet balances, cash ties out, sources equal uses, debt never negative) onto one line, and sum their absolute values in a single master check cell on the cover sheet. It should show zero. Format it to turn red when it does not, and look at it every time you change anything.
A forecast row reads =C10*(1+$C$4), copied across five years. The dollar signs fix the reference to C4, so every year uses the same growth rate. That is right if growth is one input for all years, and wrong if the model has a row of growth rates by year, where the formula should read =C10*(1+D4), with a relative reference that moves across. Most copy-across errors come from getting the dollar signs wrong in one direction or the other.
One analyst enters costs as negative numbers and computes EBIT as a sum; a colleague enters costs as positive and subtracts. When the two halves of the model are combined, a cost is subtracted twice, and margin jumps by the size of that cost. Pick one convention, write it on the cover sheet and keep it.
OFFSET and INDIRECT recalculate every time anything changes, slowing large models, and INDIRECT breaks silently when sheets are renamed. INDEX with MATCH, or XLOOKUP, does the same jobs more safely. Avoid hard-coded row numbers inside text formulas for the same reason.
Studies of spreadsheets used in business have found errors in most of them. In 2012, JPMorgan's 'London Whale' losses were made worse by a value-at-risk spreadsheet that divided by a sum instead of an average, understating risk. Formatting rules, checks and review exist because errors are normal, not exceptional.
Before sending a model: trace three key outputs back to inputs with the formula-auditing tools; switch scenarios and confirm the checks stay at zero; set a growth rate to an extreme value and confirm the results move in the expected direction; and have someone else trace one calculation from end to end.
Create a blank workbook with the eight-part layout, the colour conventions set as cell styles, year headers for three historical and five forecast years, and a check row on the balance sheet.
Workbook for this module: Three-statement model, Starter (.xlsx) · Solution (.xlsx). Build the yellow cells in the starter; open the solution only to check your work.
Educational content only; not investment, tax or legal advice. Companies, tickers and figures are illustrative examples, not recommendations. Rules, rates and limits change, so check current details with the regulator, exchange or tax authority before relying on them. Worked examples are not forecasts. Written by Elizabeta Dimoska. RiskStock is not a registered dealer or adviser.