Excel for modelling — the cardinal rules

A model is a physical object as much as a logical one. Before it is right or wrong it is a bench: a place where the inputs, the working, and the results each have their spot, and where a stranger can find their way around without a guided tour. This module is about that bench — the layout, the colours, the reference habits, and the one rule whose violation causes more silent errors than every other mistake combined. None of it is finance. All of it is what separates a model a firm will trust with a decision from a spreadsheet that quietly lies the first time an assumption moves.

Three zones: inputs, calculations, outputs

Module 1 named the three layers; here you build them into the sheet. Inputs are the assumptions you are allowed to change — Sokoni's 10% revenue growth, its 40% gross margin, its 30% tax rate — and they live together, ideally on their own tab or in a single clearly-bordered block, so a decision-maker can change anything without hunting for it. Calculations are the engine: the income statement, balance sheet, cash flow, and the schedules that feed them, all formulas, none of them touched by hand. Outputs are the few numbers a reader actually acts on — the valuation, the cash position, the sensitivity grid. Keep the three physically apart. The test is simple: you should be able to hand someone the inputs block, let them change every number in it, and trust that every consequence flows through correctly without them ever editing a formula. The moment running a scenario means overwriting a calculation, the model is already broken.

The colour convention, and why it is not decoration

Every professional model speaks the same colour language, and it exists so that a reviewer can tell, at a glance and without reading a single formula, what each cell is. Blue is an input — a hardcoded number you are allowed to change. Black is a formula — a calculation you must not overwrite. Green is a link that pulls a value from another sheet — the same 'do not touch' status as black, but flagged so you know the real source lives elsewhere. (Many desks add red for links to a separate workbook, and that is the one colour you want to see rarely, because external links break.) The colours are an audit trail. When you open a stranger's model and see a black cell where a blue one should be, you have found a hardcoded assumption masquerading as a calculation — the exact thing that makes a model untrustworthy — before you have read a word of the logic.

Anchoring, named ranges, and the no-hardcode rule

A reference like C5 is relative: copy the formula one column right and it becomes D5, one row down and it becomes C6. That is exactly what you want for a value that should move with the formula — last year's revenue — and exactly what you do not want for a fixed input like the tax rate, which must always point at the same cell. The dollar sign locks it: $C$5 stays put however far you drag, and the F4 key cycles a reference through the four lock states ($C$5, C$5, $C5, C5) so you never type the dollars by hand. Anchoring is what lets you write one formula and copy it across five years and down forty rows without a single reference drifting. Named ranges are the readable alternative: define C5 as Gross_margin and your formula reads =Revenue*(1-Gross_margin) instead of =E9*(1-$C$5). Use them for the handful of assumptions referenced everywhere; do not name every cell, or you trade one kind of opacity for another. And the rule beneath all of it: never bury a number inside a formula. =EBT*0.3 hides the tax rate where no one can find or change it; =EBT*$C$6, pointing at a labelled blue cell, exposes it. If a number could ever change, it is an input, and it belongs in the blue block — not welded into the maths.

The cardinal rule: one formula, all the way across the row

Here is the single most important habit in this course. A row in a model is one idea applied across time: 'revenue grows at the assumption rate', repeated for Year 1 through Year 5. So the formula in the first forecast cell is built once, correctly, with its inputs anchored — and then copied, unchanged, all the way across the row. Every cell in that row must contain the identical formula. The commonest silent error in all of modelling is the lone hand-edited cell: someone decides Year 3 should be a little higher, types a number or tweaks the formula in that one cell, and moves on. It throws no error. It turns nothing red. It simply sits there breaking the chain, quietly producing a wrong answer that traces back to nothing — and because every later year compounds off it, the damage runs to the end of the model. The defence is a habit: after building a row, click across it and watch the formula bar, or press Ctrl+` (Show Formulas) and read the row like a sentence — every cell should say the same thing. If a year genuinely needs a different assumption, that difference belongs in a labelled input row of its own, never as a silent edit inside a calculation.

• Anchor with F4 as you type — decide for every reference whether it should drift on copy (relative) or stay put (absolute) before you move on.
• Build a row once, then copy it across unchanged — never hand-edit a single cell in the middle of a row.
• Never type a number inside a formula; point at a labelled blue input instead.
• Colour as you go: blue for inputs, black for formulas, green for cross-sheet links.
• Navigate with Ctrl+arrow to jump across blocks and Ctrl+Shift+arrow to select them — constantly reaching for the mouse is the slow tell of a beginner.
• Freeze panes so the year headers and row labels stay on screen as you scroll a wide model.
• Press Ctrl+` to reveal every formula on a tab and scan a row for consistency before you trust it.
• Wrap IFERROR only around errors you expect and understand — never as a blanket.

Laying out the Sokoni skeleton

Now build the frame you will fill for the rest of the course. Give Sokoni one tab per statement — Assumptions, Income Statement, Balance Sheet, Cash Flow — plus a Schedules tab for the working-capital, PP&E-and-depreciation, and debt-and-interest workings, and a DCF tab at the end. On the Assumptions tab, lay the blue input block: revenue growth 10%, gross margin 40%, opex 20% of revenue, depreciation 10% of opening net PP&E, capex 12% of revenue, receivable days 30, inventory days 60, payable days 45, tax 30%, interest 12% on opening debt, debt repayment 50,000 a year, dividend payout 20% — and the opening balances, cash 100,000, net PP&E 500,000, debt 300,000, share capital 200,000. Every one of those is blue, labelled, and referenced by everything downstream. Across the top of each statement, run the year headers: a Year 0 base column holding the opening position, then Years 1 to 5. Put a spacer column between the labels and the numbers so the sheet breathes, and freeze panes at the corner so the headers and labels never scroll away. This is the exact skeleton behind LeadAfrik's downloadable 3-Statement + DCF model at /templates/3-statement-financial-model; build your own from a blank sheet so you understand every cell, then open the finished file to check yourself.