Recognizing the Dangers of Using Excel
Financial modelers, like anyone working extensively with Excel, are very aware of the inherent risks involved. According to a study by Ray Panko, who is a leading authority on spreadsheet practices, close to 90 percent of spreadsheets contain errors.
Some managers treat models as though they are able to produce the answer to all their business decisions and solve all their business problems. It’s frightening to see the blind faith that many managers have in their financial models.
After reading this book, you should have a good idea of the importance of financial modeling in businesses today. The reliance on Excel-based financial models is so entrenched within the culture of many organizations, and the practice of handing “legacy models” over to junior staff who don’t understand how the models work is a widespread practice. Models that have been used over and over for many years are passed on and reused. As a consultant, I’ve seen this time and again — the user doesn’t understand how the model works, but he’s “fairly confident” it’s giving him the correct results.
According to both PwC and KPMG (http://www.theregister.co.uk/2005/04/22/ managing_spreadsheet_fraud), more than 90 percent of corporate spreadsheets
contain material errors. Considering the importance of spreadsheets in business, this risk is not one to be taken lightly. The European Spreadsheet Risk Group (EuSpRIG) was set up in 1999 purely for the purpose of addressing issues of spreadsheet integrity. They research and report on spreadsheet horror stories, which contain the latest spreadsheet-related errors reported in the media and how they could have been avoided. The disastrous consequences of uncontrolled use of spreadsheets are always disturbing, and make for somewhat gruesome reading.
I’m always terrified when people say that they’re going to go ahead with a multimillion-dollar project “because of the results of the financial model.” It’s very easy to get a formula wrong, or for the input assumptions to be just a few basis points out, all of which may well have a material impact on the output. Tweaking the input assumptions by just a few dollars either way can have a huge impact on cash flow, profitability, and the downright viability of a project!
We know that both formula and logic errors are very easy to make and prevalent in corporate financial models. As a financial modeler, you should be vigilantly looking for errors as you build the model. For strategies for reducing error in your models, turn to Chapter 13.
Although the major dangers of using Excel relate to its susceptibility to errors, the related issues of capacity and lack of discipline also warrant a mention. In this section, I take a closer look at each of these issues.
Capacity
Prior to Excel 2007, the maximum number of rows that Excel could handle was 65,000. That may seem like a lot, especially if you’re just getting started with Excel, but it’s nowhere near enough. The average Excel user would regularly run out of rows and have to resort to using Microsoft Access or keeping data in mul- tiple workbooks to store the data. My, how things have changed!
From Excel 2007 onward, the number of rows was increased to over a million, which seemed like a big improvement at the time. In this age of big data, though, it’s still pretty easy to run out of rows, especially when you start running a few formulas down the column. Realistically, anything more than half a million rows becomes very slow using ordinary Excel.
I still classify the lack of capacity of Excel as a danger because, despite all the new capabilities of Modern Excel, many of them are still being developed and few peo- ple are using them to their full capacity yet. To deal with the size limitations of Excel when working with large amounts of data, people are still cutting the data into various chunks, importing and exporting from Access or other databases to avoid having to store data, and deleting archived data, all of which are dangerous practices because they’re prone to error and are unbelievably time-consuming.
Excel is a highly flexible tool. You can pretty much do anything in Excel, but it doesn’t mean that you should! One of the reasons I love it so much is the lack of boundaries or restrictions. Most software forces you to use it in a certain way, but Excel allows you to type anything into any cell.
Now, as wonderful as it is to be without boundaries, it’s also incredibly dangerous and somewhat alarming. You know just how much damage can be done with an incorrect financial model, and the fact that there are no checks and balances — except what you as the modeler put into it — is a terrifying prospect.
Many of the best practices of financial modeling, such as those laid out in Chapter 4, have been created for the purpose of contending with this lack of dis- cipline in financial models. Error checks, formatting, and rules about model lay- out, design, and structure are all designed to put some boundaries around a model, which, without them, becomes a dangerous tool in the wrong hands.
Errors
The possibility of error in a model is the number-one thing that keeps a financial modeler awake at night. As a modeler, you must have a healthy respect for spread- sheets and their susceptibility to error.
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 ques- tioned, leading to some uncomfortable moments during meetings (not to men- tion, 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 at www.eusprig.org/stories.htm. Given the reluctance of most businesses to talk about their mistakes, the number and fre- quency of the stories documented by EuSpRiG lead me 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
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 “mechan- ical” errors are also the easiest to avoid by self-checking and correction. Chap- ter 13 covers some techniques you should employ while building your model to reduce the possibility of formula errors.
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 invest- ment was $100,000. Through your modeling, you work out that there is 60 per- cent profit margin, but due to this error, you predict $600,000 profit instead of
$60,000. Oops!
Assumptions or input errors
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 fi 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 confi who is responsible for updating the spread- sheet and make sure any changes to the process or update schedule don’t aff 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 benefi 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 aff the employee’s benefi 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. (See Chapter 4 for more information about assumptions documentation.) 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 enter- ing 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
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, in Figure 2-4, the 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 port- folio balance (the current loans plus the new loans minus the loans expired).
FIGURE 2-4:
Calculating investment portfolio ratios.
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 docu- ment 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.
