Inconsistent data, incorrect analysis and a lack of regulatory compliance are just three of the drawbacks of using spreadsheets for business intelligence (BI), corporate performance management (CPM), and other analytic purposes in the enterprise. Still, what other choice do organizations have?
“The challenge for not using the spreadsheets is to find something server-based but as easy to use as spreadsheets,” said Andy Tran, a data architect at Boeing, where spreadsheet use is widespread. “It is practically impossible.”
Tran is right. Spreadsheets in the enterprise are here to stay. What organizations can do, however, is improve the way they manage spreadsheets to better ensure that the data they consume is timely and accurate, that the analysis which users conduct is logically sound and follows business rules, and that regulatory statutes are being followed.
“Critical spreadsheets need to be treated the same way business applications are,” said Philip Howard, research director for Bloor, a London-based IT analyst firm and consultancy. “You really want to have some formal, central management.”
But even before spreadsheets can be managed effectively, organizations must first undertake an inventory of the spreadsheets in use and identify which are “critical” to the business and which are not
Making such distinctions is not a cut-and-dried job, Howard said. But, loosely speaking, organizations should consider as mission-critical those spreadsheets that are used to make core business decisions or whose misuse could result in hefty fines for noncompliance with important regulations.
For example, financial firms that develop analytic trading models in spreadsheets or healthcare companies that house sensitive patient data in spreadsheets would probably consider them core to the business.
Step two, Howard said, is to run mission-critical spreadsheets through a series of tests to catch and fix broken formulas, blank cells, incorrect data and other errors. A number of inexpensive tools are on the market that can help companies do just this, he said.
Only then, Howard said, can organizations take on the difficult job of actually managing spreadsheets.
Spreadsheet management software: Buy or build?
With critical spreadsheets identified and errors corrected, organizations now have a decision to make: Should they purchase commercial software to design and automate spreadsheet management or build the capabilities internally themselves?
The answer depends largely on the number of spreadsheets and the amount of data an organization is attempting to manage. Smaller organizations may be able to handle the task internally.
In those cases, according to John Hagerty, an analyst with Stamford, Conn.-based Gartner, organizations should consider housing their mission-critical spreadsheets in a central repository where data connectors can be set up and business rules applied to ensure accurate, timely data.
From there, users can access the spreadsheets and associated data for their own desktop analysis.
“At least you have some comfort that the data [business users] retrieved has been vetted,” Hagerty said. “People need to stop going out and collecting data willy-nilly on the fly.”
At larger enterprises, which can have literally millions of spreadsheets littered throughout the organization, commercial spreadsheet management tools might be needed.
A number of spreadsheet management vendors are in the market, Howard said. Among them is ClusterSeven. The U.K.-based vendor specializes in software which ensures that workers are following predefined business rules when working in spreadsheets.
The software also identifies when a spreadsheet has become critical to business processes and migrates it to a central server to maintain data integrity when it reaches maturity, according to Ralph Baxter, ClusterSeven’s CEO. Spreadsheets reach maturity, Baxter said, when the dimensions in a spreadsheet become stable, with only the data itself changing on a regular basis.
The only catch is that users have to make their spreadsheets available to corporate networks so ClusterSeven’s software can access them.
Most companies yet to tackle spreadsheet management
At Dealer Services, an auto financing firm in Carmel, Ind., about half of the spreadsheets are either managed in a central repository or are fed data directly from a corporate data warehouse, according to CIO Chris Brady.
“The biggest risk [of not managing spreadsheets centrally] is that the data has been corrupted and there is no way to know,” Brady said in an email. “People make changes in formulas, sort and filter – and then forget what they have done.”
Dealer Services may be the exception rather than the rule, however, as Bloor’s Howard believes most organizations have yet to tackle spreadsheet management. He noted that the top four spreadsheet management vendors – ClusterSeven, Boston-based Cimcon, Pleasanton, Calif.-based Prodiance, and U.K.-based Finsbury Solutions -- have only about 500 customers combined.
That means there are still a lot of unmanaged spreadsheets floating around Fortune 500 companies. Most large organizations, for example, still roll up their monthly and yearly budgets in spreadsheets, eschewing dedicated CPM software, according to Robert Kugel, an analyst with Ventana Research.
“If you’ve recognized that you’ve got a problem, that’s the first step,” Howard said.
Unfortunately, for many, that realization doesn’t come until spreadsheet mismanagement has resulted in significant costs to the organization.
Dig deeper on Business intelligence best practices