Editor’s note: In this two-part series, guest author Michael Zimmel offers 13 financial modelling errors that finance professionals would do well to remember to avoid when using spreadsheets.
Projections about the spread of the Coronavirus and its long-term implications on the global economy are uncertain. In other words, it is a nightmare for those of us working in the area of FP&A (financial planning and analysis).
The current economic impacts are of unprecedented scale, and they push the limits of many existing financial models to the point that the accuracy and reliability of their outputs suffer.
A virus is not a suitable input variable for financial forecasts. Hence the revision of existing financial models must take into consideration multiple changes in business drivers – all with significant impact on the income statement, balance sheet and cash flows.
Financial models are built with some basic assumptions of input ranges in mind. For example, the relation of certain COGS items to revenues may hold true for a variation of revenues by, let’s say 20% up or down.
But what happens when revenues abruptly decline by over 80% or even 90%, for example in particularly hard-hit industries such as aviation? At the same time, the situation evolves quickly. As one of my colleagues said: “Another day, another sensitivity analysis. Another week, another scenario calculation.”
Particularly in times like these, every analyst hopes – more than ever – that their financial models are robust and free of substantial errors. The unfortunate hard truth is: Many models aren’t. But, if there’s a shadow, there must be light: Now is the time to retreat to your home office, block out all distractions and give your financial model a complete overhaul.
There are a few “Spreadsheet Errors”, “How To” and “Golden Rules” lists out there. And yet, I still encounter these errors again and again when reviewing financial models.
Hence, I have compiled what I would like to see as the ultimate list of financial modelling errors (and how to avoid them). Trust me, in my journey as a financial analyst I have previously made many of these mistakes myself.
Fatal financial modelling errors
“The reliability of a spreadsheet is essentially the accuracy of the data that it produces and is compromised by the errors found in approximately 94% of spreadsheets.” (Ruth McKeever)
Over the years, I have built and reviewed dozens of financial models. As a result, I gained detailed knowledge of how they work. My exposure to this field is quite extensive. In my early career, I helped develop a financial modelling support tool at EY. I also teach financial modelling to MBA students and financial practitioners.
The process of building a financial model in Excel is a complex one. It can be frustrating if the model continues throwing errors. Especially when you spend much time setting up the formulas and conditions.
To avoid typical mistakes, a good financial analyst follows certain procedures and discipline. It is important to understand possible errors you are likely to make. This article will explain major errors made with financial modelling and how to avoid them.
What is a financial model?
“All models are wrong, but some are useful.” (George Box)
A financial model is an abstract presentation of a financial problem. You can use the model to solve business and economic problems.
For example, you could create and present a 5-year financial plan to equity investors. Or a 12-months budget to a board of directors. A short-term cash model for a bank to secure an overdraft extension is a financial model as well.
The way you create and present the model is important. It could mean the difference between a signed deal and no deal.
It is difficult to overestimate the importance of correct financial models. In 2005, the FSA even stated that “Spreadsheets are integral to the function and operation of the global financial system”.
Given the importance of financial models, understanding the potential errors is critical. You must understand the common mistakes people commit and how to overcome them.
When using Excel to develop a financial model, adhere to guidelines minimizing spreadsheet risks. Also, your goal in creating financial models should be to achieve consistency in the team or company.
With this in mind the financial modelling mistakes to avoid if you’re a pro are:
1. Failure to plan
When creating a financial model, most tend to overlook its impact. They fail to think through how the model will work. They don’t think what its users, applications, and scope will be.
Others do not consider the kind of information and reports needed to develop the model. Failure to make considerations can over-complicate a model.
Possible issues include providing unneeded information or missing out on critical components.
To paint a mental picture of how this works, think about building a house without planning. If you do not have a plan beforehand, you will likely encounter unforeseen problems. The entire project may end up being a failure.
Similarly, your financial model will be disconnected from the operations of the business if you spend inadequate time preparing and developing it.
To avoid committing this error:
- Identify the financial problem the model will solve
- Identify the end-users of the model and how they should use the model
- Identify the needed inputs, processes, and outputs of the model
- Keep inputs in one place for quicker and easier access during the development stage
- Plan your model structure adequately
You will have to constantly monitor whether the underlying assumptions of your plan continue to hold true throughout the modelling process.
As my friend Vincenzo Melpignano rightfully points out, situations change, and hence, the model-building analyst should always work together closely with the business resp. client during the entire project.
2. Lack of a logical structure
Most financial models have several sheets, and you must logically organize them. Haphazardly arranged sheets can be difficult for the users to navigate between. They make it difficult determining which part you wrote in one sheet and which you presented in another.
To overcome this weakness, identify the factors affecting the model and elaborate on different sheets.
Present the content in a way that users can transition between sheets with ease. Make sure the flow within each sheet is natural, from left to right and from top to the bottom.
Your model should have three basic components:
Assumptions → Calculations → Output
Following a logical structure has several advantages. First, it offers a consistent and reliable architecture. This allows users to determine which areas they should work in and which the computer works in.
Second, it mitigates errors by storing all assumptions in one place. This way, it is less likely to forget to remove old assumptions in earlier analyses.
3. Building overly complex models
An informed model is good for your business since it provides more information. But the more assumptions you make, the less realistic your decisions are. It is difficult to assume anything far into the future.
Making accurate assumptions of dozens of different factors takes longer and may not yield desired results. In many cases using ten to fifteen key assumptions gives more accurate results.
When building a financial model, reduce implicit assumptions to a minimum. You may build some implicit assumptions into the model.
But explicit ones will require input fields and formulas for more flexibility. Your goal should be to find a balance between the model’s flexibility and complexity.
4. Excessive use of complex formulas
Long, nested formulas are difficult and time-consuming to follow through. A good financial model should be easy to follow and interpret.
The rule of thumb is that most formulas should not exceed half the length of the formula bar.
The longer a formula is, the more likely you will make a mistake. To avoid this, simplify and reduce the number of steps in a formula.
Choose simple and doable steps to check and pinpoint any errors present. Excel’s functionality provides many ways of simplifying financial models.
The complexity of a formula starts with how it looks. If your reader perceives the formula to be complex, the model is complex.
First impressions count. The first 30 seconds are enough for a reader to give positive or negative feedback for the model.
Resist the temptation of showing off your skills by creating complicated formulas when using Excel. Strive to make it simple for your readers.
If it is impossible to create a simple formula, look for ways to break it down into multiple cells.
When using Excel, you should:
- Use flags
- Avoid nested IFs
- Simplify IF statements using alternative functions
- Consider logics like MAX, MIN, OR, AND
- Consider functions such as HLOOKUP, VLOOKUP, INDEX, OFFSET (the latter with care!)
5. Formula errors
Yes, that’s a thing! Errors related to formulas are the easiest to make. They may also be the hardest to find. For example, it is easy to miss a piece of data when preparing the formula.
This could be because the formula did not copy across right. Or it could be that a certain range of information is incomplete.
Whichever the reason, formula errors contribute to the inaccuracy of your financial model. Because of this, you must be careful when writing or changing a formula in an existing financial model.
Take extra care when inserting columns or rows near the edge of ranges or where the references in the cell are fixed.
6. Using hard values instead of formulas
This is a classic, yet repeated error. The general rule is to never hard code. If you must, make sure you insert a comment against the cell with an explanation and where the number has come from.
The biggest problem with hard values is that they make the formula more complex. You may remember the value when creating the model but forget something about it when you come back.
According to financial modelling expert Karl Gezer, there are many pitfalls of hard coding inputs. Besides a reader finding it challenging to follow the model’s logic, the assumptions become hidden.
The task becomes even harder if the document is printed or otherwise presented. Keeping inputs visible in the margins, or even better as a separate Inputs/Variables worksheet, enhances the flow of reasoning throughout the model.
The temptation to save time by hard coding formulas demonstrates a lack of coding discipline, something which is fundamental to reliability and transparency.
It is also the rookie mistake of inexperienced analysts. You may not know where the data is coming from, and it may be impossible to see the financial impact of changes in the assumption.
The only time you should use hard values is when dealing with underlying inputs and assumptions that drive elements of the model, such as costs and revenues.
It is good practice to clearly and consistently formatting these cells as ‘Inputs’. Doing so will immediately increase a reader’s confidence in the reliability of the data.
Editor’s note: We will follow-up with the remaining seven things to avoid when it comes to financial modelling in the conclusion to follow.
This article first appeared on LinkedIn. Click here.
To read the second part of the series, click here.
