A model becomes presentable when a stranger can find every number somebody chose, as distinct from the ones the model derives. The assumption sheet holds those chosen numbers and nothing else, on one page.
The Cascade Instruments model (synthetic, US, $ million) runs on 24 inputs. Seven are reported actuals taken from the last filing. Five are policy or contractual, meaning the amortisation schedule, the interest rates on the term loan and the revolver, the minimum cash balance and the statutory tax rate. Twelve are analyst estimates, which is 50.0% of the sheet. Show that split as well as the inputs, because it tells a reader straight away how much of the output is judgement.
The sheet has four columns: the input, its value, its type and its source. A reader scanning the type column learns in five seconds where to push. A reader scanning the source column learns whether you looked anything up.
Advertisement
What is a hardcode, and what does it cost?
A black box is any number that a reader can't find on that sheet. The most common species is the hardcode buried inside a formula, and Cascade's model carries one on purpose so you can practise finding it. The second forecast year's tax line is typed as 21% of pre-tax income, while every other year runs at the 25% rate on the assumption sheet.
The damage is worth measuring, because the lesson is not the size of the error.
Pre-tax income in that year is $134.686 million. At 25% the tax is $33.672 million, and the hardcode gives $28.284 million. So tax is understated by $5.387 million and net income is overstated by the same amount, which is 5.33% of that year's net income. The knock-on into the third year is small: $0.162 million of extra net income, from interest earned on cash that shouldn't have been there. Closing cash ends $5.549 million too high. Value per share moves from $28.4316 to $28.5490, which is 0.41%.
An error that moves the answer by 0.41% barely touches the valuation. It does its damage to trust. A reader who clicks that cell during a walkthrough now believes there may be others, and has no way to bound how many. The rest of the meeting gets spent on cells, and the company hardly comes up.
Which integrity checks should a model pass?
Run three integrity checks before you build any slide, and put their results on the model output page as three lines of text.
The balance sheet balances in every year, with total assets less liabilities and equity equal to zero to twelve decimal places. The roll-forwards tie. Closing property, plant and equipment equals opening plus capital expenditure less depreciation, and closing equity equals opening plus net income, both to twelve decimals across all three forecast years. And the cash flow statement ties to the balance sheet: opening cash plus operating cash flow less capital expenditure less debt amortisation plus the revolver movement equals closing cash, again to twelve decimals.
Those three lines are the cheapest credibility in the deck. They cost one page slot, and they answer the question a sceptical reader is actually asking: is the model internally consistent, before anyone argues about its assumptions?
How do you stop hardcodes getting in?
Preventing a hardcode is mechanical. Inputs in one colour and formulas in another, without exception. One formula-audit pass across every driver row that reads the formula bar and ignores the displayed value. A trace-precedents sweep on each of the five biggest value drivers. And a hand recomputation of one full year, done on paper. That last one is the only check that catches an error the spreadsheet is happy with.