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