Auditing and error-proofing

Every model you build will, sooner or later, be read by a stranger — a credit committee, an incoming analyst, an auditor, or a version of yourself six months from now who has forgotten every shortcut. The model is only worth what that stranger can trust, and they cannot trust your confidence; they can only trust the checks. This module is about those checks and the mindset behind them, which is simple and slightly uncomfortable: assume your model is wrong until the checks say otherwise. A model that has not been audited is not 'probably fine'; it is unexamined, and the research on spreadsheets is blunt about what that means — errors are the norm in large models, not the exception, and the ones that matter are the quiet ones that never throw an error message.

The checks that make a model trustworthy

The first and most important is the balance check you met in module 5: for every year, total assets minus total liabilities minus equity must equal zero. Build it as a live row — the difference for each year — and reduce that row to a single master flag at the top of the model that reads 'OK' only when every year is zero. The discipline is not to build the check and glance at it; it is to treat a non-zero balance check as a full stop. If Sokoni's balance sheet is out by even one shilling in year 4, you do not present year 4 and you do not 'come back to it' — you stop and find the shilling, because a balance sheet that does not balance is telling you a link is broken somewhere upstream, and everything the model says downstream of that break is fiction.

Two more structural checks sit alongside it. The cash check: cash on the balance sheet must never go negative unless you have deliberately modelled a revolving credit facility to fund the shortfall. A model that shows negative cash with no revolver is quietly assuming the company can borrow unlimited money at no cost — an overdraft from nowhere — and any valuation built on that is wrong. The fix is to model the revolver honestly, so it draws when cash would dip below a floor and repays when cash is ample, with interest on the balance. The sources-equals-uses check: whenever the business funds something — the capex programme, a debt repayment, a dividend — the sources of that cash (operating cash flow, new debt drawn, equity raised, opening cash) must exactly equal the uses. If they do not tie, a plug is hiding in the model, silently balancing the books with money that has no origin.

Identical formulas across a row — and the broken one

A well-built model has a simple, almost visual property: every formula in a row is the same formula, written once in the first forecast column and copied across. Revenue in year 3 is calculated exactly as revenue in year 2; the only thing that changes is which cells the relative references point at. This is what lets you understand a hundred-row model by reading five columns — once you know the pattern of a row, you know all of it. It also means the most common structural error announces itself: one cell in a row that does not match its neighbours. The tools to hunt for it are worth knowing cold. Excel's Show Formulas view (Ctrl + `) replaces every value with its formula so you can read a row like a sentence and see the odd word. FORMULATEXT in a helper row does the same for a specific cell, printing its formula as text next to a known-good one for side-by-side comparison. Go To Special > Row differences (Ctrl + \) selects, in one keystroke, every cell in a selection that differs from the pattern of its row — point it at the revenue row and any broken cell lights up. And the colour convention from module 1 does quiet work here: if inputs are blue and formulas are black, a stray blue cell in the middle of a calculated row is a hardcode caught by the eye alone.

The sister discipline is the no-hardcodes-in-formulas audit, and it traces straight back to module 1's one source of truth. Every number inside a formula should be a reference to a labelled input cell, never a constant typed inline. =C10*(1+$B$5) is correct, where $B$5 is a blue cell labelled 'Revenue growth'; =C10*1.12 is a landmine, because the 1.12 is an assumption nobody can see, nobody can change, and nobody will remember. To audit for them, read the formulas in Show Formulas and look for digits that are not part of a cell reference — a bare 1.12, a 0.3 tax rate, a 365 that should have been a named input. The tell that a hardcode is doing damage is the one from module 1: you change the assumption in its proper blue cell and some downstream number refuses to move, because the real number was welded into a formula three columns away. A model full of inline constants is not a model; it is a picture of one particular scenario that lies the moment you try to flex it.

Sign and sensible-range checks

Structural checks catch broken links; sign and range checks catch numbers that are individually wrong. A sign check asks whether each line points the way it should: costs and capex are outflows, depreciation reduces the asset base, a dividend is negative to cash, a tax line does not quietly turn into income. Build these as flags where you can — a small cell that turns red if operating cash flow is negative in a year the income statement shows a healthy profit, because that mismatch usually means a working-capital line has the wrong sign. A range check asks whether each number is sane: revenue growth inside a plausible band rather than 190% because a formula compounded twice, gross margin between zero and one, a cash conversion cycle in days not years, a terminal growth rate below the discount rate. None of these is sophisticated; all of them catch errors that have embarrassed senior people in real meetings. The value of automating them is that they check every year, every time you touch an input, without you having to remember to look.

Documentation, the change log, and the written review checklist

The last layer is not arithmetic; it is legibility. A trustworthy model documents itself: a notes or cover tab that states the purpose, the valuation date, the currency, and the colour convention; every material assumption sourced and dated, so a reader knows the 30% tax rate came from the Income Tax Act and the 15% WACC from the build in module 8, not from thin air. Alongside it sits a change log — a running list of date, who, what changed, and why — so the model's history is readable and you can answer 'why is this number different from last week' without archaeology. And before the model leaves the building, a senior runs a written review checklist: not from memory, but a printed list ticked item by item, because the discipline is in the ticking. Run that checklist over Sokoni and most of it passes cleanly — every year's balance check reads zero, no growth rate hides inside a formula, every row is one formula copied across, and every margin and ratio sits in a sensible band. One check does not pass, and that is the whole point of having it: the cash check fires, because Sokoni's cash goes negative from Year 2 (−30,270, then −87,851, −140,345, −187,695). That is not a defect to paper over — it is the check earning its place, telling you in numbers what module 4 warned in words, that a profitable company whose books balance to the cent can still be quietly running out of money. Capex at 12% of revenue, 50,000 of debt repaid a year, and a fifth of profit paid in dividends together outrun the cash operations throw off, and the negative line is the model asking for a revolver you would size and add before this ever financed a real decision. A senior signs off not on a model with no flags, but on one whose every flag they can explain — and this is a teaching model that deliberately leaves the flag flying so you can see it. That is what earns the model the right to be shown to someone whose decision depends on it.

• Structural: the balance check is zero in every year; cash never goes negative without a modelled revolver; sources equal uses on every financing and transaction line.
• Formulas: each row is one formula copied across, verified with Show Formulas or Go To Special > Row differences; no inline constants — every number is a labelled blue input or a reference to one.
• Signs and ranges: costs, capex, and dividends carry the right sign; growth rates, margins, and ratios sit inside sane bands; automated flags turn red when they do not.
• Links: every statement links to its schedules — working capital, debt and interest, capex and depreciation; trace-precedents confirms each link points at the intended row.
• Terminal and valuation: terminal growth is below WACC; terminal-year capex and working capital are in steady state; the DCF ties to the free cash flow the model actually produces.
• Documentation: purpose, date, currency, and colour legend stated; assumptions sourced and dated; a change log records what moved and why.
• The stranger test: could someone who has never seen this model change any blue input and trust every consequence to flow through correctly? If not, it is not finished.