“Hmm, these numbers don’t add up…”
Sound familiar? We’ve probably all said this at one time or another, and it can be a frustrating experience.
In this article, we’ll explain the pitfalls of financial modelling in Excel, and give you a few survival tips to help you get accurate and reliable results from your models.
Microsoft Excel is a very flexible, powerful and ubiquitous tool for the purpose of financial analysis, however, astute users understand the dangers of a poorly built financial model.
A well built financial model or piece of analysis is an invaluable tool in decision making and financial management, but all too often a wrong calculation can have disastrous consequences. We often see businesses relying far too heavily on Excel models. It’s completely understandable – after all, Excel can do practically anything you ask it to (except make your morning coffee!).
So, what’s the solution? It is extremely difficult to ensure an error-free model, but by employing certain strategies, you can most definitely reduce their number and impact.
Please note: We’re not suggesting ditching the ubiquitous Excel – on the contrary, we think it’s great. We’re simply suggesting that we proceed carefully and use it with caution.
Check your work
The worst kind of error is one that you are not aware of! Constantly checking and performing rough checks on the results of the formulas as you build will minimise the chance of an error. Every time you hit the enter key (and you MUST use the enter key – don’t just click somewhere else or you really will get an error!) check the result. Is the number what you expected? Does it look sensible? Before you add anything else to the formula, make sure the first part is right. Please see Appendix 1 for an example.
A lot of modelling is trial and error and it’s OK to make mistakes – just make sure you pick up mistakes before someone else does! The only thing worse than having your boss pick up a mistake is no-one picking them up at all. By employing a sense-checking methodology you are more likely to let errors slip through the cracks.
Check it again!
One method of checking is to enter zeros into all the input cells, and make sure the output is zero. Perform a sensitivity test – if you add one unit, is the output increased by the value of one unit? Use formula auditing to see which cells are dependent on which. Please see Appendix 2 to find out how.
Have someone else check your work
It’s good to have your model reviewed or audited by someone else as they will bring a fresh perspective. Sometimes you’ve looked at your model for so long you just can’t see the blinding error in front of you. Working in pairs is sometimes very effective.
Document your assumptions
For those of you who have attended one of my courses, this will be a familiar theme! A model is only as good as the accuracy of the assumptions. The term “garbage in, garbage out” has never been more relevant than in the context of financial modelling.
Important decisions are made based on the outputs of the model, and it is absolutely critical to list the assumptions that have gone into the model. Whilst a good model can aid your business or decision-making process significantly, it’s important to remember that models are only as good as the data they contain, and the answer they produce should not necessarily be taken at face value. When presented with a model, the savvy manager will query all the assumptions, and the way it’s built.
Document your methodology
Using a flow-chart format, document the inflows and outflows of your data within the model. This helps to check your logic, but is also useful in explaining the methodology of your model to other people.
Please see Appendix 3 for an example.
Use models in the right context
I see some managers treating models as though they are able to produce the answer to all their business decisions, and solve all their business problems. Someone who has had some experience in building models will realise that they must be treated with caution. Models should be used as one tool in the decision-making process, rather than the definitive solution.
Sensitivities and scenario analysis
Do lots and lots of sensitivities and scenario analysis. If the absolute worst happens, what happens to my bottom line? How sensitive is my model to changes in key assumptions? This will help to test the logic and robustness of your model, as well as the soundness of the business, product or project the model is representing. At the minimum, you should do at least a best case, base case and a worst case.
In-Built Error Checks
Basic reconciliations can be built within a model, so that you can see at a glance if your formula is calculating correctly. For example, check that the sum of each section of a report adds to the total. This can be done by a simple IF statement. Please see Appendix 4 to find out how.
This strategy is most appropriate for capturing errors a subsequent user has made – it is less likely to highlight a model building error.
I can’t guarantee you an error-free model, but I do guarantee that if you employ the good modelling practices as shown above, your modelling errors will be greatly reduced!
Danielle Stein Fairhurst is an MBA-qualified financial modelling specialist. With over twelve years’ experience as a financial analyst, she helps her clients create meaningful financial models in the form of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.
Please contact us if you’d like to learn more about helping you achieve your financial modelling goals.
Appendix 1 – Check Your Work
Let’s look at an example of using sense-checking methodology.
This is a pretty common one. Let’s say you are adding up these totals and accidentally double-count by including the sub-totals in your Grand Total. If you look at the sub-totals you can see that the grand total should be around $120k. Instead of doing this in your head, you could highlight the two sub-totals using the control key and the total will appear at the bottom right hand side of the screen as shown by the red box below:
Appendix 2 – Check It Again!
Clicking in the cell or hitting the F2 button will show the dependents of a formula. This is most useful if the source cells are nearby and on the same sheet.
If the cells are on another sheet, you may find formula auditing to be more useful.
Select the formula. On the “Formulas” tab, in the “Formula Auditing” group, click on “Trace Precedents”. In prior versions of Excel, in the menu bar, click on Tools – Formula Auditing – Trace Precedents.
This will draw blue arrows from the current cell to all of the cells on the sheet that are used in calculating the cell.
Double-clicking on the blue will take you to the preceding cells.
Another trick is to immediately click on “Trace Precedents” again. This will draw blue arrows to all of the precedents of the precedent cells.
To remove arrows, click on “Remove Arrows”. Saving will also clear the arrows. If you don’t remove the arrows they will print.
Trace dependents will show you what cells depend on the selected cell.
This is particularly useful if you are considering deleting a cell and don’t want to mess up the model!
Appendix 3 – Document Your Methodology
In an example model, you can calculate the customer acquisition using the following methodology. If you start with the total population, apply the number of pet owner and then a takeup rate, you get the number of acquired customers. This methodology can be documented as follows:
Appendix 4 –
In-Built Error Checks
If the sum of each individual item does not equal the grand total, the cell will return the word “error”, otherwise it will show a zero. There are many variations of this formula, and I’m sure you can come up with of many of them. Many modellers prefer to show the word “OK” if the numbers are right, and “Check” if not.
