Model architecture and the rules that prevent errors

Most modelling errors are structural, not arithmetic. They come from models where nobody — including the author, three weeks later — can tell which numbers are assumptions and which are results.

The rules

Separate inputs, calculations and outputs. Ideally on separate sheets, at minimum in visually distinct blocks. Every hardcoded number lives in the input section. If you find a number typed into a formula, it is a defect, not a shortcut.

One formula per row. A row should contain the same formula in every period column. The moment one column differs, you have created a trap that will catch someone — usually you.

Colour convention, without exception. Blue for inputs, black for formulas, green for links to other sheets. This is not decoration. It is how a reviewer knows in one glance where the judgement lives.

Build the check row first. Before anything else, build a row that confirms assets equal liabilities plus equity. Put it where you cannot miss it. A model without a live balance check is a model you cannot trust.

No circular references until you need one, and when you do, make it explicit and switchable. Interest on average debt creates a circularity: interest affects net income, which affects cash, which affects debt, which affects interest. Handle it with a documented iterative calculation and a switch to break it, not by hoping.

The build order

Revenue → costs → working capital → fixed assets and depreciation → debt schedule → then link into the three statements and cash.

Revenue first because everything scales off it. Debt last because it depends on the cash the rest of the model produces.

The test that matters

Hand the model to someone else with no explanation. If they cannot find your assumptions in sixty seconds, or cannot change revenue growth and watch it flow correctly through all three statements, the model is not finished — regardless of whether the numbers are right.