The Dangers of Using Excel for Financial Modeling - dummies

The Dangers of Using Excel for Financial Modeling

By Danielle Stein Fairhurst

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.

Financial modeling is very important to 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, 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.

It’s always a little terrifying 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!

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.

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.

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 multiple 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.

The lack of capacity of Excel can still be considered as a danger because, despite all the new capabilities of Modern Excel, many of them are still being developed and few people 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.

Lack of discipline

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 have been created for the purpose of contending with this lack of discipline in financial models. Error checks, formatting, and rules about model layout, design, and structure are all designed to put some boundaries around a model, which, without them, becomes a dangerous tool in the wrong hands.