According to a recent survey, many users are still using Microsoft Excel spreadsheets to analyze and report on corporate data. These same users also spend much of their data-gathering time correcting out-of-date information in these Excel spreadsheets. So what are the real benefits of using Excel spreadsheets to analyze and report data? What additional benefits do business intelligence (BI) tools offer?
For quite a number of years, spreadsheet software packages are used by lots of business users in many industries as a means of simplifying their lives and providing a better understanding of business problems. However, with little or no control over the use cases, users often find themselves in high waters when it’s too late to correct errors within spreadsheets; or worse off, to not identify errors before critical business decisions have been made based on the spreadsheet data.
All of us at times have extended the use of spreadsheets past their intended application ― frequently into database management and sometimes into business intelligence. Although spreadsheets can work in such environments, there are several possible pitfalls that can cause downstream disasters.
A few years ago, JP Morgan lost several billion dollars on trades due to errors in spreadsheet-based risk analysis models that had been developed internally! TransAlta lost $24 million dollars in a spreadsheet error.
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 use calculations that are consistent from user-to-user through centrally developed and managed templates.
- 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.
While spreadsheets are wonderful for crunching numbers, I wouldn’t rely on them to drive critical business decisions.
