Model architecture and error prevention
Financial modeling · Investor Analyst Blueprint
The majority of errors in financial models are structural rather than arithmetical. They arise in models in which neither a reviewer nor the original author can readily distinguish assumptions from calculated results.
Modelling standards
Separation of inputs, calculations and outputs. These should occupy separate worksheets or, at a minimum, clearly delineated sections. Every hard-coded value belongs in the input section. A constant entered directly into a formula is a modelling defect.
Consistency of formulae across periods. Each row should contain the same formula in every period column. A formula that differs in a single column is a common source of undetected error.
Formatting convention. Inputs are formatted in blue, formulae in black, and links to other worksheets in green. The convention allows a reviewer to identify at once where judgement has been applied.
Balance check. Before any other element is built, the model should include a check row confirming that total assets equal total liabilities plus shareholders' equity in every period. The check should be prominently displayed. A model without a functioning balance check cannot be relied upon.
Circular references. Circularity should be avoided unless required, and where required it should be explicit and controllable. Calculating interest on average debt balances creates a circular reference: interest expense affects net income, which affects cash, which affects debt, which in turn affects interest expense. The circularity should be resolved through documented iterative calculation with a switch that allows it to be disabled.
Sequence of construction
The recommended order is: revenue, operating costs, working capital, fixed assets and depreciation, the debt schedule, and finally the integration of the three statements and the cash balance.
Revenue is built first because most other line items are derived from it. The debt schedule is built last because it depends on the cash generated by the rest of the model.
Review standard
A model is complete when a reviewer without prior explanation can locate every assumption within one minute, and can change the revenue growth assumption and observe the effect flow correctly through all three statements. A model that fails this test is incomplete, irrespective of the accuracy of its outputs.
Work it through
1 exercise · nothing is recordedReview questions
-
A hard-coded constant is found within a formula. How should it be classified?
Every assumption belongs in the input section. A constant embedded in a formula cannot be identified by a reviewer, or reliably by the author at a later date, and is a common source of outputs that cannot be defended.
-
Which element of the model should be constructed first?
The balance check should be in place before any element capable of producing an error is built, and should be prominently displayed. A model without a functioning balance check cannot be relied upon, however sound its logic appears.
Work this through
Construct the framework of the model for the company under analysis: an input worksheet, a calculation worksheet, an output worksheet and a functioning balance check.
Do not populate the model at this stage. Establish the structure, the formatting convention and the balance check, and then ask a colleague to identify the location of the assumptions without assistance.
You are previewing this Blueprint
Exercises like the one above run through every module — calculators you can push, checks that explain the reasoning, and tasks that make you apply it. The rest of them, the workshops, your progress and your notes are included with Analyst membership.