Why do we need to use best practices in financial modelling?
If automated software is used to create valuations, not only can these be restrictive but the formulas within may not be understood and the cognitive link between the input figures and the output lost. If we create our own spreadsheets, every step is clear and a much deeper understanding is gained. By using best practices within our spreadsheets, the models become clearer, more shareable and easier to understand and to explain.
If a model is purely for your personal use, you need not worry too much, you know where you are coming from and where you want to get, you understand your process and have no need to explain it to anyone else. If you are creating the model to share or for somebody else, then it needs to be user friendly, efficient, and address the users needs. Your model needs to have a logical and easy to read layout, that is both informative and attractive.
Time periods should be displayed horizontally and inputs, outputs, and workings should be separate and clear, to avoid confusion and to make troubleshooting easier. However, if possible, inputs and outputs should be on the same sheet so the impact of any changes made are clear to see.
The 5 golden rules
- Keep formulae simple
- More complex formulas can be difficult to alter or troubleshoot
- The actual rule is up to the modeller, it can be a certain length or a certain number of functions
- Trade-off between needing a much larger workbook with many tables and simple formulas or more complex formulas in a visually simple spreadsheet
- Use intermediate calculations
- Breaks down more complex formula to make them simpler and easier to follow
- Never use hardcoded values in formulas
- These are fixed values embedded in formula
- For example, imagine you have formulas that have used a 1.5% interest rate
- If the interest rate changes or you need to see what would happen if it did, you would need to find and alter every occurrence of the fixed interest rate
- If you have a variable interest rate in another cell, and the formulas refer to this cell, you just have to change the interest rate once, this is much simpler and less prone to errors.
- Only use ONE formula per line
- Encourages consistency
- Enables copying from left to right without need for change
- Do NOT use array formulas
- A formula that can perform multiple calculations on one or more items in an array.
- These will result in a ‘#VALUE’ problem, this coudl be overcome by using CTRL + Shift + Enter to insert curly brackets but you would need to remeber to do this every time you click the formula and may result in errors.
- Best NEVER to use the array formula and create a simpler and more stable formula.
I have come up with the following pneumonic to help us remember the five golden rules:
H – hardcoded values to be avoided
O – One formula per line
U – use intermediate calculations
S – Simple is best
E – errors can be avoided by NEVER using the array formula
Learn more about excel formulas, functions and more on a Cambridge Finance financial modelling course: