If you analyze data, chances are you use spreadsheets in some capacity. And with good reason: spreadsheets are easy to use for simple data analysis and calculations, and they allow you to visualize simple tabular data via charts and graphs.
However, spreadsheets have limitations that many business users are not aware of or unwilling to admit. Relying on them for sophisticated data analysis and business decision-support can be inefficient at best. At worst, you can expose your company to significant risk.
Even small spreadsheets can contain thousands of links, formulas, and cell references, creating many of opportunities for error. Even in simple spreadsheet calculations, a cell reference that is off by even one row or column returns significantly different results than what you want.
In this article, we’ll take a look at 5 pitfalls of using spreadsheets for business analytics. We’ll also examine how business intelligence (BI) is a more reliable method for business decision support.
Pitfall #1: Erroneous Calculations
Spreadsheets are risky because they typically are updated manually, and error-checking is difficult. A perfectly crafted calculation can easily become broken in the course of adding or updating data. An error in one cell can snowball throughout your data. Don’t believe spreadsheet errors represent a significant risk? They are blamed for:
- A $6 billion derivatives trading loss at JPMorgan Chase. Investigators found that the model that underpinned the hedging strategy operated through a series of manually completed spreadsheets that should have been automated but never were.
- Erroneous findings by prominent economists Kenneth Rogoff and Carmen Reinhart, who used their spreadsheet analysis to advocate fiscal austerity measures for recession-plagued economies. Sadly, European policy makers had embraced the erroneous findings, which arguably prolonged the European recession.
No business can afford to base important business decisions upon something that can so easily be rife with mistakes.
Pitfall #2: Big Security Risk
Spreadsheets are frequently distributed via email. Unfortunately, both spreadsheets and email suffer from weak access-control capabilities, so you could easily expose unsecure data to both employees and non-employees. Did your spreadsheet with the comp plans for your sales team just get forwarded to your competition? Even password-protected spreadsheets can easily be compromised by free or inexpensive third-party tools (just Google “how to crack a spreadsheet password”).
Another security hazard is that with spreadsheets, anyone can change the data and there’s no built-in audit trail. If someone edits a formula or data, there’s no recording of who made the edit and why. There’s also no archive or roll-back capability.
Pitfall #3: Not Intuitive for Your Average Business User
Most business users can use simple functions such as “sort” and “sum,” but find it difficult to create a complex formula. For advanced analysis, general business users must often lean on the “power users” within their organizations to get the job done, which wastes time for everyone.
It’s also complex to update data. Users must manually recreate transformations and debug any calculations each time the data is refreshed. Merging data from different sources is difficult, time-consuming and filled with the potential for errors.
Pitfall #4: Lots of Arguing, Not Enough Decision-Making
Here’s a common scenario that plays out in spreadsheet-centric organizations every day:
The sales, marketing, and operations directors meet with finance to review quarterly expenses shortly before the close of the quarter. What ensues is a lengthy debate about the veracity of the finance director’s spreadsheet numbers:
- That T&E line item looks too high to the sales director. Which sales reps have submitted expense report reimbursement requests and for how much? This analysis does not match the sales director’s own analysis.
- Marketing expenses look low to the marketing director. Do expenses for an upcoming tradeshow hit this quarter or next quarter?
- The operations director thinks overtime salary looks more like last quarter’s numbers than this quarter’s numbers. Was the spreadsheet updated properly?
This is no recipe for timely decision-making. Many organizations spend more time forcing everyone to crunch their own versions of the numbers and then arguing over the reconciliation – instead of constructively developing strategies to maximize profit and correct problems.
Pitfall #5: Can’t Handle Big Data
As they grow in size, spreadsheets become slow and unwieldy, resulting in lost productivity as users wait for queries to complete. Links and formulas commonly break, and ensuring formula validity becomes more and more problematic, if not impossible. Spreadsheets are designed to handle data across two dimensions; analyzing more than this involves dealing with pivot tables or manually creating views of additional dimensions, which is fragile, time consuming and error-prone.
In addition, reports that must be updated daily (or more frequently) present significant problems when maintained as spreadsheets. Users must manually manipulate them to append the latest data and delete or archive the oldest data. Formulas must then be copied and validated to transform the raw data columns into the final data elements that are displayed in the report.
Business intelligence as a solution
While spreadsheets are useful for simple ad hoc calculations, when they are used for purposes more appropriately addressed with a BI tool, they can expose your company to reputational and financial risk, lost productivity and significantly slower time-to-insight as information workers struggle with the inherent limitations of spreadsheet-based reporting and analysis.
So how do you transition to true BI? Here are 5 steps:
Step 1: Understand and embrace spreadsheet users within your organization.
This may seem counter-intuitive, but understanding your spreadsheet users is the key to delivering superior analytic tools that solve real-world business problems. Conduct an audit within your organization to figure out which groups are regularly using spreadsheets for analysis, what data they are crunching, how they are presenting the data, how frequently they do this, and what business decisions are being made based on this data.
Step 2: Immediately address any urgent concerns you discover.
If you determine during your audit that serious risk exists, take immediate action to mitigate that risk.
Step 3: Educate users about the perils and shortcomings of spreadsheets.
Many business users are unaware of the security threats, liabilities, inefficiencies and error-prone nature of spreadsheets. They are equally unaware of the advantages of BI as an alternative. Taking away their trusty spreadsheets without explaining why or explaining that you plan to give them superior analytical tools will be counter-productive.
Step 4: Select a BI solution that works well with spreadsheets.
Again, this may seem counter-intuitive. But organizational acceptance of a BI solution will be easier if you learn to work with spreadsheets rather than completely ban them.
Spreadsheets can be an important input data source for BI. Some data just doesn’t reside anywhere else. For example, many sales organizations have their sales quotas and bonus schedules in spreadsheets and nowhere else.
Your BI reports and dashboards probably will also need to have export capabilities to spreadsheets and/or PDF. It is simply the easiest way to enable users to print, share, and present data to others.
Step 5: Introduce BI where spreadsheets are most prevalent.
Heavy spreadsheet use is a good indicator that BI can be adopted successfully in that group. It indicates that people are hungry for analytics for business decision support, and they are most likely to embrace more efficient and reliable tools. Phased implementation at the departmental level can be a cost-effective way to introduce BI into an organization, quickly establish success, and build a case for deeper penetration.
By migrating from spreadsheets to true BI, organizations are able to:
- Reliably and consistently integrate significant data volumes from disparate sources
- Deliver information to end users accurately, quickly and securely
- Enable users to get at the right numbers quickly, lessen liability and risk posed by error-prone, unsecured spreadsheets
- Increase opportunities to identify cost savings, efficiencies and new business opportunities