The Problem with Spreadsheets for BI

All of us at times have extended the use of spreadsheets past their intended application ― frequently into the world of database management and sometimes into business intelligence. While spreadsheets can work in those types of environments, there are several possible pitfalls that can cause downstream disasters.

Don’t forget the JP Morgan fiasco during the financial crisis where the bank lost several billion dollars on trades due to errors in spreadsheet-based risk analysis models that had been developed internally! Or … what happens when a classic product or customer number with leading zeros gets formatted into scientific notation 1.654763 E+11 using a spreadsheet database.

Let’s look at some of the more concerning issues that could occur when using spreadsheets in place of specifically designed and centrally managed Business Intelligence (BI) tools.

• When spreadsheets morph into databases, the source data is out of control of a central repository and can be easily (and accidentally!) modified. That loss of control can be critical during an enterprise-wide decision making process.

• BI tools have a degree of control built in because they access data from a centrally control repository and useg calculations that are consistent from user-to-user through centrally developed and managed templates. A good example is the calculation of profit which could be done in several different ways in spreadsheets and the resulting decision made incorrectly. BI tools have pre-calculated values or templates that are “forced upon” the user community when calculating globally-used values.

• Inevitably, comparative and trend analysis become an important factor in the decision making process using time ranges or data type ranges. BI tools with their central data sourcing process always have the most recent data and the correct past data cleansed and blessed by IT and finance. Spreadsheets ― with their inherent flexibility and personal customization features ― invite errors and encourage the database morphing process. A industry study done by a respected university found that 88% of spreadsheets have errors in various forms.

• Additionally, BI tools provide the ability to drill anywhere into the approved and sometimes secure data sources since the tools are securely connected to the central system of record.

• As user models and queries get more and more complex, so does the support of their systems. Plus, if a user leaves the company, he or she takes the knowledge of the model with them. Many times the output of these spreadsheets is centrally used with little knowledge of the data sources or the underlying accuracy of the calculations. BI tools for the most part have central controls or system rules enforced on the use and application of the data. If this sounds restricting, it is for good reason.

The ease of use of spreadsheets creates an illusion that the results are correct if the formula does not produce an error; but errors usually involve syntax and have nothing to do with the proper application of the formula. Rarely are there any checks and balances. Important decisions are being made with erroneous information in many cases due to data traceability and accuracy.

While spreadsheets are wonderful for crunching numbers, I wouldn’t rely on them to drive critical business decisions.