Financial Modelling Best Practices: The Conventions That Make a Model Auditable

Separate inputs, calculations and outputs

The single most important structural decision is that every hardcoded number lives in one place, every calculation references those inputs, and every output is a presentation of calculated values with no arithmetic of its own. Break this and everything downstream gets harder: you cannot sensitise a model whose assumptions are scattered across forty tabs, and you cannot audit one where the output page quietly recalculates something.

In practice that means a dedicated assumptions tab (or a clearly delimited assumptions block at the top of each schedule), calculation tabs that contain formulas and nothing else, and output tabs that are pure formatting over links. If you find yourself typing a number into a formula, you have just created a value nobody will ever find again.

The test: can you change the revenue growth assumption in exactly one cell and watch it flow through to the returns? If the answer requires editing more than one cell, the model is not driver-based, whatever the assumptions tab claims.

• One assumptions tab, or one clearly labelled assumptions block per schedule. Never both patterns in the same model.
• No hardcoded numbers inside formula cells. Ctrl+` toggles formula view and makes violations obvious at a glance.
• Output tabs contain links and formatting only. If an output cell does arithmetic, that arithmetic belongs upstream.

Colour coding is an audit tool, not decoration

The convention is near-universal across banks and funds, which is precisely what makes it valuable: blue for hardcoded inputs, black for formulas that reference the same sheet, green for links to another sheet within the workbook, red for links to an external workbook. A reviewer who knows the convention can assess the structural integrity of a tab in about five seconds without reading a single formula.

The most useful of these is red. An external link is a dependency on a file that may move, may be a different version, or may not travel with the model when it goes to a counterparty. Flagging them in red means nobody discovers the broken reference at the worst moment.

This matters more than it sounds. Colour coding is not a stylistic preference — it is the fastest structural audit available, and its absence is itself a signal about how the model was built.

Consistent rows, consistent formulas

A formula should be identical across every period in a row. If Q3 does something Q1 and Q2 do not, either that is a deliberate exception that needs flagging, or it is an error — and in the models we see reviewed, it is overwhelmingly the second. Inconsistent formulas across a row are the single most common source of quiet, undetected model error, because nothing about the output looks wrong.

Build one column correctly, then drag. If dragging breaks something, the fix is to restructure so it does not, rather than to hand-patch the exception. Anchoring with $ references, keeping assumptions on their own rows, and never mixing hardcodes into a projection row all serve this.

The corollary is that time should run in one direction, in the same columns, on every tab. A model where Year 1 sits in column F on one tab and column D on another cannot be cross-checked by eye, and eye-checking is how most errors actually get caught.

Build in error checks that fail loudly

Every integrated model should carry a check row that a reviewer can see without hunting: does the balance sheet balance, does the cash flow statement tie to the change in the cash balance, does the debt schedule close at the same figure the balance sheet reports, do the sources equal the uses.

A check cell should return zero when correct and something visually obvious when not. Conditional formatting that turns the cell red on any non-zero value is the standard implementation. Aggregating every check onto a single summary cell at the top of the model means one glance tells you whether anything in the workbook is broken.

A balance sheet that does not balance is not a formatting problem. It means a link is broken or the accounting treatment is wrong, and every downstream output — valuation, returns, credit metrics — is suspect until it is resolved. Models get submitted in this state more often than anyone admits.

• Balance check: total assets less total liabilities and equity, to zero tolerance.
• Cash tie: closing cash on the cash flow statement equals the balance sheet cash line.
• Debt schedule tie: closing balances agree with the balance sheet debt lines.
• Sources and uses: total sources equals total uses in any transaction model.
• One master check cell aggregating all of the above, visible on the first tab.

Handle circularity deliberately

Interest expense depends on the debt balance, the debt balance depends on cash flow, cash flow depends on interest expense. That circularity is real and unavoidable in any model with a revolver or a cash sweep. The question is whether you have controlled it or it has controlled you.

The two defensible approaches: enable iterative calculation and accept the circular reference, with a clearly labelled toggle that lets a user break it; or avoid the circularity by calculating interest on the opening balance rather than the average. The second is less precise and far more robust, which is why it is common in models built to be handed over.

What is not defensible is an uncontrolled circular reference with no toggle. One accidental keystroke turns the entire model into a field of zeros or #VALUE errors, and recovering it usually means going back to the last saved version.

The anti-patterns that get models rejected

Reviewers develop a fast heuristic for model quality, and it keys off structure rather than sophistication. A simple model built cleanly earns more trust than an elaborate one that cannot be navigated — because the reviewer can verify the first and has to take the second on faith.

• Hardcoded plugs. A number typed in to make something balance is a lie the model will tell every future reader.
• "Final_FINAL_v2" naming. Use [Project]_[ModelType]_[Version]_[YYYYMMDD] and keep a changelog tab.
• Merged cells in a calculation area. They break dragging, sorting and referencing, and they are almost never necessary.
• Hidden rows and columns holding live logic. Anything load-bearing should be visible or grouped, never hidden.
• Nested IF statements more than two deep. Break the logic into labelled intermediate rows a reviewer can follow.
• Sensitivity tables that reference the wrong output cell. Always stress-test one corner of the table manually against the model.

Model for the handover, not the build

The habit that separates practitioners from people who have only built models for themselves is assuming the model will outlive their involvement with it. It will go to a lender, be picked up by a colleague, be revisited in eighteen months by someone who has never seen it, or be interrogated by a counterparty whose job is to find the weak assumption.

That assumption changes small decisions everywhere: you label the units on every row, you write a one-line note on any assumption that would puzzle a stranger, you keep a source column next to market inputs, and you build the output page so it answers the question before anyone opens a calculation tab.

None of this is sophisticated. All of it is what "bank-grade" actually means in practice — not more complex modelling, but modelling that survives contact with other people.