Whilst not the most sexy topic for a blog article, this is probably the thing that I’m asked most about by clients. Because many modellers are self-taught, a lot of them don’t necessarily adhere to best practice. Or perhaps they do – but don’t realise why they are doing it! I thought it might be helpful to lay out my views on the topic, and as always, I’d welcome any reader input…
The principles of best practice outlined here are for the purpose of reducing errors, making a model easier to read, audit, update and use for its intended purpose. This list is by no means exhaustive, but outlines the most important principles in Financial Modelling. By following these key principles, your model is easier to navigate and check, and much more likely to be robust, accurate, reliable and error-free.
Document your assumptions
- Your model can be as well built as you like, but its validity is really reliant on the assumptions that go into it; i.e. garbage in, garbage out. The most beautifully structured model in the world is pretty useless if the assumptions that go into it are garbage!
- Assumptions documentation helps with validation & avoids misinterpretation.
- List assumptions on a separate page, clearly labelled. For a smaller model, you might decide to mix source data and assumptions together, or they could be separated in a large model.
- The more detail the better.
- Document source data and unique calculations as you go. Don’t leave it all to the end – and don’t rely on your memory!
Linking, not hard-coding
- Link as much as possible so that when the inputs change, the outputs also change.
- Use Named Ranges for more robust links to external files.
- No hard coding except for input variables unless referenced or source data is listed or it is otherwise blindingly obvious where you got the number from.
- By linking, you can trace source data back through the links, so making your model auditable, traceable and easy to validate.
- Never use a value within a formula. The only exceptions are those things which are “standard” or commonly accepted values which will not change, such as 24 hours in a day, 7 days in a week, or 60 minutes in an hour
- You may notice that many modellers put input variables in blue font. Whilst this is a bit old-fashioned, it’s still quite standard practice and it’s a kind of signal to your user or another modeller that this is a hard-coded input variable that can change (i.e. you are allowed to change this value!)
Only enter data once
- Never enter the same value twice; enter it in once as a source and always reference to that one cell.
- Never use a value within a formula eg. =IF(G$6>=$E7,136800,0) should have a link, not 136800 typed in.
- However, do not link source data to a cell which itself has been linked to the original source. Always go directly to the source data.
Use Consistent Formulas
- In a table or block of data, the same formula exists all the way across and down the entire block.
- Use Absolute / Mixed Referencing to achieve this.
- It saves time, and avoids error – and this practice is a keystone to good financial modelling technique. If you only pick up one modelling technique from this blog article – this is it!
- Avoid blank rows or columns in data blocks as this can cause problems with sorting, filters and pivot tables.If sheets are similar, use a template where possible and copy the same sheet. Then when you need to update it, group the sheets to make global changes.
- Try not to use macros to perform lengthy calculations or manipulations as this reduces transparency.
Format & Label Clearly
- Format cells appropriately. Use symbols for currency $€£¥, percentages % etc and commas for thousands – this makes your model easier to read and avoid mistakes and misinterpretations.
- Label your data clearly. It sounds simple, but mixing units (e.g. mixing apples and oranges) is a common source of error in financial modelling, and good formatting and labelling will avoid this.
- Include a dedicated units column (eg, column C) and make sure the units are entered into that column, eg, $’000, MWh, litres, headcount etc.
- If you decide to round figures into thousands, show this clearly at the top of the row with a descriptive heading such as “Revenue $’000” to avoid confusion and misinterpretation.
- Column and row headings should have unit or currency headings and only contain one type of unit or currency.
- When building multi-sheet models, columns should be used consistently. For example, if building a model for the period 2013 – 2020, use column F on each worksheet for 2013, column G for 2014, and so on.
- It is often a good idea to reserve a column (eg, Column D) for constants that apply to all years, months or days. For example, if growth rate is 5%, have that in column D, and then link all calculations to column D.
In general these best practice guidelines outlined above are really just common sense. Whilst some of them may seem tedious and overly prescriptive to the beginner, most of these points are for the purpose of reducing error and bringing increased robustness and clarity to the model. These practices are probably second nature to the experienced financial modeller who will most likely already follow these guidelines by instinct.
