Spreadmarts Bad for Business

Info~Tech Research Group's Info~Tech Advisor (a subscription based newsletter - reproduced here with permission) has an article pointing out the dangers of using desktop spreadsheets for business decisions. It can lead to isolated, incomplete and inconsistent information and analysis. Spreadmarts Bad for Business Date: January 04, 2005

For many, Microsoft Excel is the application of choice for budgeting, forecasting, graphs, and financial reporting. Yet spreadsheets containing crucial information often exist in limbo, cut off from the enterprise view. Rein in these "spreadmarts" now, before they start causing serious problems for the organization.

What Is a Spreadmart?

Excel is the most popular decision support tool for business users worldwide. When spreadsheets containing valuable corporate data are duplicated uncontrollably, and then modified differently by different users, each file becomes a separate version of the "truth." Each one of these fractured versions of the truth is called a "spreadmart." Coined by Wayne Eckerson in 2002, spreadmart is a word meaning both spreadsheet and data mart. It's a suitable name for a growing crisis.

How It All Starts

Many knowledge workers utilize massive Excel spreadsheets in order to circumvent a business intelligence (BI) application that they perceive lacks functionality. Still others wish to gather data from disparate sources across the enterprise, a task for which they believe Excel is well suited.

However, problems occur when knowledge workers - whose sole purpose and training is to analyze data - begin collecting, integrating, and defining that data, duties that they are often not trained to do. The result is vital data, captured on individual desktops, that exists outside the corporate information architecture, without any centrally-controlled metrics to define it or give it context. Particular spreadmart issues include the following:

Users create their own methods to analyze the data, creating a fractured view of the enterprise that is very likely inaccurate, leading to poor decision-making. Not all users are Excel experts, and could make errors in calculations that end up skewing or corrupting the entire file. Business meetings go awry when several managers or executives show up with their own spreadsheets and cause confusion over whose data is "right."

Excel Nightmare: A True Story

A large U.S. healthcare provider was outsourcing its data processing operation, and the contract was worth over $500 million. The consulting group hired to manage the competitive process used a spreadsheet to evaluate and rank the competitors in order to select a shortlist of three companies. The three vendors were informed of the selection, and those not on the shortlist were dismissed from the process.

An error in the spreadsheet was soon discovered. This error changed the ranking to exchange one of the shortlisted vendors for one that had been dismissed. Being a government acquisition, this negated the entire four-month process, which then had to be started all over again from scratch.

Killing Spreadmarts Is Worth It

The average ROI for a spreadmart consolidation project is $3.34 million, with an average payback period of 2.1 years (source: TDWI, 2004). In addition, one company managed to save 600 man-hours per year by instituting a forecasting and integrated analysis system based on an Excel/BI hybrid (source: Computer Business Review, 2004).

Action Plan

Tackling corporate culture is a major challenge that won't be won easily, but when IT is responsible for crucial data, action must be taken.

1. If you don't currently have a BI solution, consider getting one. If spreadmarts are truly rampant within your organization, then they are likely generating enough data (and users analyzing the data) to justify a BI software purchase. Look for a BI solution that utilizes Excel as its client. At least this way, executives can still use their beloved spreadsheets while IT manages the server on which the common data store resides. This is a win-win situation for the enterprise, as all data, reports, and metrics will be accessed and used in a controlled manner, with everyone on the same page.

Ensure the BI server interfaces with the desktop via a Web service or Excel plug-in. Create controls that allow clients to interact with the server-based data, but not to alter it directly.

If less stringent control of the data is needed, the BI tool must allow for Excel query builders that let users create custom queries and reports (from multiple systems, if necessary).

Whatever the case, IT must remain in control of all metrics and data elements to ensure consistency.

2. If you already have a BI solution, find out why it's being circumvented. Interview users to establish a firm set of reasons as to why they are not using the BI system. Make it clear from the outset that this is not a witchhunt. More often than not, the BI software's interface is not user-friendly, so users turn back to Excel, which is familiar to them. Use the reasons you unearth to either develop a BI fix-it plan (if required), or to inform your BI buy-in strategy.

3. Strongly encourage usage of the BI solution. The trick here is to demonstrate the value and benefits of using BI instead of spreadmarts. While this won't be easy, use these BI selling points to get your message across:

Data quality: The data warehouse on which the BI solution is built assures data quality and cleanliness much better than Excel ever can.

Data abundance: The data warehouse is centralized, and can collect and integrate data from far more sources than a single user could.

Comparable functionality: BI tools have the same functionality as Excel, but without the inherent risk of creating custom formulas and calculations.

Additional functionality: BI tools also have functions well beyond Excel's, such as scheduling, reporting, and automated publishing.

IT support: The IT department will not and cannot support desktop spreadmarts, but the BI tool is maintained by IT, and IT will address any problems with it.

4. Establish executive support for red-flagging spreadmarts. Though it may be the executives themselves who are the cause of spreadmart profusion, they must be made aware that Excel alone is poorly equipped to deal with rapidly changing business processes and/or workflow in an increasingly complex computing environment.

5. Form a common set of definitions. Advise your company's executives to mandate that each business unit clearly define rules for common metrics and terms used under the BI umbrella, and to tolerate no deviance from them. This will help shift user habits away from spreadmarts to a more structured and centralized approach to data analysis and management.

Bottom Line

Using Excel has obvious business benefits that cannot be ignored. However, it's equally important to recognize Excel's limitations and the dangers it poses when left to run amok.