I was recently talking with a friend who was giving a workshop to boards of directors on the risk within their businesses. This topic made me think of all the companies that I had worked with over the years and what was the biggest risk they faced but was not realized it within the organization. Upon reflection of all of 2 minutes, I came to the conclusion that that it was Excel!
Yes, it is Excel! Excel has taken over our corporate environments where it is used by finance, marketing, sales, HR, etc. to do everything from planning, budgeting, forecasting, pricing, reconciliation and analysis. While Excel is a wonderful tool, which undoubtedly made many of these tasks much easier, the problem is that anyone can use it and change a model without anyone else realizing it.
There are three areas of concern:
1. Incorrect models.
Many people within organizations develop Excel models to solve problems that they are currently facing. However, many of these models are built using the improper practices that I've highlighted in previous blogs. As a result of these practices, other users of the models are not always aware of the model's shortcomings but still rely the output to make large decisions.
In one case, the author of the model had linked all but two of the assumptions to the assumptions page thus when you changed the assumptions all but two of them change within the model. As a result, no one was aware that these two assumptions when "off-line" for the model and were making decisions based on incorrect results. While appearing to be an insignificant problem, the lack of connection of these assumptions led to some projects being undertaken because they had positive NPVs and good IRRs, but in reality were terrible projects with negative returns.
2. Hard Coded Models.
Many models are built using the bad practices I have mentioned before. While I have come across many of these, I cannot meet the example given by my friend Rob Brown in his blog post. To have an organization making decisions with an error in their spreadsheet of order of magnitude of $200 million that no one was aware of is scary. Do you have one of these in your company? Before you answer "No" are you sure!
3. "Fixed Models."
More worrying are those models where a relatively good model has been built, but then hard-coded change is made to prevent a #N/A or #DIV/0! result. Unfortunately, no one tells anyone about this change, and it left unnoticed for ages. As a consequence, massive errors can arise.
Recently I was working with a company whose budgeting model had some hard coded adjustments in formulas to adjust sales, compensation, and other budget items. When reviewing this model with the company's finance department, there was no explanation of why they made these changes. When asked how good the design was for budgeting, the answer I received was "Last year was a horrible surprise!" As a result, I wrote CEOs Beware: Problems with Financial Modeling. At last check, the company was still using the model!
Recently I was working with a Fortune 500 company that had a model to drive dashboards for the executive team. Somewhere the data had not all been supplied and so there was a #DIV/0! result. Rather than fix this, someone had changed some formulae to exclude the offending cells and their related data. However, when entering proper results into the offending cells the dashboard didn't update to reflect this. The dashboard continued to exclude these outcomes in the actual and historical averages. Who knows how many ad campaigns and how much marketing spend resulted to improve the results, when the results were probably already exceeded expectations.
So what to do?
Companies, departments, etc. should review all their models on a regular basis. This review involves someone checking all the calculations, cell references, and links to ensure that the model is performing as expected. A painful task, but surely better than to keep walking off the cliff hoping you can survive the landing.