In our financial modelling courses, we discuss what makes a great financial model in Excel and here are the top 10 ‘Golden Rules’ that I find will help you to achieve that.
- Don’t hard code values, instead use input tables
- Don’t use CTRL + Shift + Enter command
- Use intermediate calculations to help simplify your formulas
- Don’t use more than 4 (four) “IF” functions per formula
- Use data validation
- Build error checks
- Create one formula per row (or column)
- Protect your workbook
- Avoid circular references
- Keep it simple and elegant
1) Don’t hard code values, instead use input tables
Hard-coding means typing numbers or text directly into your formulas. For example, if you type inflation rates (say 2%) directly into your formula, that is wrong because if you want to change the assumption for inflation to, say 1%, then you will need to go through all the cells in your spreadsheet that have 2% and manually change it to 1%. It is a huge risk you will miss a cell and your analysis will be wrong.
If you use an input table instead, you can just change the inflation rate in one cell and all formulas should follow through. Magic!
2) Don’t use CTRL + Shift + Enter command
This command tends to be used when you make ‘array’ functions that will force Excel to repeat certain calculations. For example, instead of using the OR function and repeating the base cell (in this case A1) multiple times as in:
you can use the CTRL + Shift + Enter (CSE) command and make the formula as in:
=OR(A1=C1:C3) + CSE
Although this command may simplify the “OR”, “AND” and “SUM” functions, for example, these formulas are extremely unstable and every time you click in a cell containing a CSE command, you will get the error message #VALUE throughout your spreadsheet if you don’t press CSE again. It’s a pain.
So NEVER use it!
Instead, you should use newer and more robust Excel functions such as SUMIFS and SUMPRODUCT, which will give you a much more reliable and easy to use financial model.
3) Use intermediate calculations to help simplify your formulas
If you see formulas such as:
you will know that either you or the person who created the formula went a bit overboard. Some people say that formulas should not be longer than the length of your thumb, but I think you could be a bit more complex than that.
So, to balance this out, you just need to mix ‘Golden Rule 3’ with ‘Golden Rule 10’ (remember to use ‘Golden Rule 4’ too) and you will achieve perfection in financial modelling.
4) Don’t use more than 4 (four) “IF” functions per formula
This follows through from ‘Golden Rule 3’ of trying to avoid long formulas.
My rule of thumb is that if you see more than 4 “IF” functions in a formula, that means that the formula isn’t fit for purpose. To solve this problem, you will need to break down the formulas into intermediate calculations, so chances of making a mistake is hugely reduced.
5) Use data validation
Data validation is a function that you will find under “Data” Tools group in Excel. You can set parameters for input, either as a range or as a list. For example, you don’t want the growth rate to be greater than 5%, then set the validation data to be less than 5%. In this case, if the user types in a value greater than 5%, then he will get an error message and will not be allowed to enter such data.
6) Build error checks
Error checks are small calculations that will help you visualise your mistakes. For example, say you have a debt facility of £500,000. If in your financial model the sum of all drawdowns is more or less than £500,000, you know you made a mistake. In this case, you can create an error check such as:
=Debt – SUM(All drawdowns)
If this value is different than 0 (Zero), then you can CONDITIONAL FORMAT it to be highlighted in RED, for example.
7) Create one formula per row (or column)
This really is what separates out a spreadsheet from a financial model. Using one formula per row or column means that you will need to create an initial formula and copy down or copy across. The results will automatically change based on the input cells and will work under different scenarios.
For example, you entered an initial rent in A10 as £100,000 but there is a review in 2 years’ time to £105,000. You cannot create one formula for the first year and then a different formula for the second year onwards. Your formula in year 1 should be the same as in year 2, 3, 4 to infinity.
8) Protect your workbook
I know Excel passwords are a joke and you can crack into any ‘password protected’ spreadsheets with a simple macro. But the point is that the average Excel user won’t know how to a) find the macro, b) use the macro. So if you are sending your financial models to your colleagues and you don’t want them to change your formulas but the inputs only, protect the cells that have formulas and add a password to it. Both of you will feel safer in this environment!
9) Avoid circular references
In property, sometimes circular references are part of our business. For example, if your construction cost is £1 million and your interest is 5% p.a. Your debt/equity split is 50% and you want to find out the British Pound figure for debt and equity. This is a circular reference calculation. Why? Because 5% of £500k is £25k so now your total project cost is £1,025,000. 50% thereof is £512.5k and your interest cost is now £25.63k, which makes your new total project cost to be £1,025,630.
To solve this problem, you have two alternatives: avoid circular references altogether using a fixed Loan-to-Value (LTV) figure or keep the circular reference and enable iterative calculation.
If keeping circular reference, make sure you don’t use it willy-nilly, but only when it makes economic sense, please!
10) Keep it simple, and elegant!
What I say in my classes, I will repeat here: the world is shallow, so appearance is everything! If you are creating your own financial models, make sure it looks simple and elegant (no 33 tabs, please!), because even the most complex of financial models can look organised, and elegant. Use colour codes, input / output tables, sensitivity and scenario analyses; all beautifully.
For more information, get in touch!