Financial Modelling in Microsoft Excel/Building for safety

The more you work with spreadsheets, the more you will see how inventive people can be in making mistakes, or in misunderstanding what was intended. You have to assume that if it can be read the wrong way, someone will read it wrong.

The guidelines below are all aimed at making it harder to make mistakes.

Inputs and Data

Separate inputs & assumptions from calculations (this is crucial)
Label & colour input cells clearly
Protect & constrain inputs with data validation or user controls
If you are importing data, keep it separate from the calculations
Give important variables range names, and use them in formulae
Treat constants (e.g. tax rates) as (unchangeable) inputs – never hard code them into formulae as numbers
Clearly explain anything that may be unfamiliar or confusing

Formulae & Calculations

Protect calculations – on a separate sheet, or protect the sheet
Flow calculations from left to right, top to bottom, to echo Excel's calculation sequence, and also the way people will "read" the spreadsheet
Highlight any cells with inconsistent formulae (i.e. different to neighbours) – preferably don’t do this at all because these anomalies can be very hard to find and may be overwritten if someone else makes a change and copies it down a whole column!
Use colours carefully – keep bright colours for warnings
Use a consistent layout & design between sheets
Use consistent magnitudes/time intervals (e.g. months or years)
Show sources (references, links to source documents, extracts)
Include anomaly checks and self-tests (e.g. cross-totals) so that the model checks itself as much as possible
Don’t be clever! e.g. don’t use obscure functions that no-one else understands
Don’t create incredibly long formulae – rather use more columns
Don’t distract or confuse the checker

Results

Set results out clearly and unambiguously
State all key assumptions, especially in printable results
Put date (and version number if any) on printouts