Imagine you’re working on an exciting new project. You’ve provided a financial model that’s being used for a project or key function of your business. It looks fantastic. People are fired up; money is being spent. But weeks or months into the project, the numbers suddenly aren’t adding up. Why is the project so far over budget?
On review, you suddenly realize there has been an error in your original calculations. Yikes! Your credibility and confidence in your work are being questioned, leading to some uncomfortable moments during meetings (not to mention, concern over your future at the company).
The European Spreadsheets Risks Interest Group (EuSpRiG) runs a well-established annual conference to present the latest research and findings on the subject. There are many well-documented cases of high-profile Excel model blunders. Some of these are documented by EuSpRiG. Given the reluctance of most businesses to talk about their mistakes, the number and frequency of the stories documented by EuSpRiG lead many to believe that errors in spreadsheets are a regular occurrence in most organizations.
What form can these errors take? Generally, modeling errors can be grouped into three broad categories: formula errors, assumptions or input errors, and logic errors.
Formula errors in your financial model
Formula errors are the easiest errors to make and relatively easy to spot, but they’re horribly embarrassing when they’re discovered. These kinds of “mechanical” errors are also the easiest to avoid by self-checking and correction.A common formula error is simply picking up the wrong cell in the formula — for example, linking to cell B98, which contains 6, instead of cell B97, which contains 0.6. This error initially might seem quite minor, but let’s say your initial investment was $100,000. Through your modeling, you work out that there is 60 percent profit margin, but due to this error, you predict $600,000 profit instead of $60,000. Oops!
Assumptions or input errors in your financial model
Your model’s formulas may be calculating perfectly, but assumptions in financial models are a textbook case of “garbage in, garbage out.” If the assumptions you’ve used as inputs are incorrect, the model will also be incorrect.When it comes to input errors there are two main types to consider:
- Data input: Data input errors can easily occur if you’re updating operating costs, for example, on a week-to-week basis. If these costs aren’t linked correctly or refreshed regularly, you can get an incomplete or inaccurate picture of the process. Sometimes linking this information to a separate, automatically generated file and using some of the new Modern Excel tools such as Get & Transform (formerly called Power Query) can automate and expedite this process. Also, be sure to confirm who is responsible for updating the spreadsheet and make sure any changes to the process or update schedule don’t affect your model.
- User input: User input errors occur more frequently when you’re less familiar with the product or project you’re modeling. For example, when it comes to the salary costs of a program, you may factor in the benefits that an employee will receive and assume it will be 5 percent of her salary, which is a fairly standard across-the-board assumption. However, because you’re new to the organization, you may fail to take into account other factors that affect the employee’s benefits, such as an increase in the cost of delivering the dental and medical program that the company prides itself on. Suddenly, this drives the cost to 12.5 percent of salary, completely blowing out all the staff costs you’ve so carefully calculated.
If you’re making assumptions, you need to record them, consider them, and lay them out carefully in your model. It’s also a good idea to confirm these inputs with the key stakeholders.
The old saying “Too many cooks spoil the broth” most certainly applies to building a financial model. Unless you have a strict, collaborative set of standards that will ensure that the model is laid out and assumptions are entered consistently, you’ll achieve the best result by having only one modeler working to build the model.When it comes to using the model, however, anyone should be able to use a well-built model. If you’re worried about people messing up your calculations or entering inputs incorrectly, make sure your instructions and documentation explain how to use the model. Also, apply data validations or cell protection to the model to restrict changes the user can make.
Logic errors in your financial model
Errors in the model’s logic are probably the most difficult to spot, because you can have the assumptions, inputs, and formulas all working perfectly, but the logic and methodology — the way the model is built — can still be incorrect. You must capture the logic and ensure that the builder’s approach is clear. Otherwise, if you lose the key person who built the model, confusion can ensue.For example, this model shows the percentage of the investor’s funds that have been loaned. The percentage has been calculated by dividing the investment balance (the current investment plus the new investment) by the portfolio balance (the current loans plus the new loans minus the loans expired).
Hold on a second. Do you calculate it based on the number of loans written or the cumulative balance? You can see how the way the ratio has been calculated can cause confusion for those not familiar with this model. It’s important to document how you’re calculating it. You might also easily confuse monthly amounts with cumulative balance; you can see that this is clearly documented in column B.