Make spreadsheets more powerful by enhancing data integrity and placing more control on the spreadsheet process.
by Jason Schemmel
Published 12/06/2005

Organizations use spreadsheets for many reasons:

1. Spreadsheets are easy to use and do not require extensive training. 
2. Information found in spreadsheets can be changed easily and quickly.
3. Extremely flexible, spreadsheets provide excellent formatting capabilities and do not require development or maintenance support from systems administrators.

The ability to perform complicated tasks--such as data integration, complex financial modeling, data sharing, and development of OLAP-type reports with pivot tables--point to the flexibility of using Microsoft Excel as a Business Intelligence (BI) solution. Add to this the idea of broad end-user adoption and you can begin to understand why Excel is being used today as a BI solution.

With the increasing exposure that Sarbanes-Oxley regulations have placed on the corporate financial world and the growing number of corporate bankruptcies or investigations, the controls around the spreadsheet have come to the forefront. Did inaccurate spreadsheets bring down Enron, MCI, or Arthur Andersen? Are spreadsheets the root cause of financial reporting problems in today's businesses?
Certain software vendors seem to think so! These vendors, many of which are concentrated in the BI space, have made the spreadsheet the scapegoat for all that ails an organization. Anti-spreadsheet propaganda seems to be the first line of many marketing brochures produced by these companies. But when you dig deeper, you will find that products produced by these companies will have such features as "Excel-like interfaces" or Excel export functions. Such features can be found in a many software vendors' products.

So why the contradiction? Is it really the spreadsheet that is the issue? The old adage of garbage-in garbage-out seems to hold true with any reporting or analysis tools. The problems could come from how the report was built, what parameters were used, or what database was pointed to. None of these issues (which these BI vendors already know exist) are resolved by simply eliminating the spreadsheet from the equation.

The Solution

"It isn't an inherent control weakness to use spreadsheets; it's how people use them," quoted a director of a major national accounting firm.

The truth is that spreadsheets are beloved by managers the world over, and nothing that we say will make any difference to that fact. Instead of trying to eliminate the spreadsheet, the appropriate answer is to make the spreadsheet more powerful by enhancing the integrity of the data itself and placing more control on the spreadsheet process.

The principal issue with the spreadsheet is the accuracy of the data. Have I entered the data correctly? Do I have an error in the formula? Have I created a maze of spreadsheet links that is easily broken? This issue is easily resolved by utilizing applications that will replace the manual keying of information with a process that refreshes the data automatically against the underlying data source itself. Dynamic formulas that automatically calculate amounts directly from your host system replace numbers that might be entered incorrectly by your personnel. These applications, which typically utilize your existing security settings, can provide a deeper analytical view of your business, often having drill-down capabilities that can go deep into the detailed data. Because the application is directly connected to the host system, you are always ensured of having the latest and greatest data.

Why not offer a user a more clear but detailed view of the business without creating complicated reports or queries and without having to beg and plead with the IT department to build it for you? And you can have all of that without a major investment of your time or money!

Built-in functions can also help you validate how accurate your spreadsheet is. Some spreadsheet applications, such as Microsoft Excel, have built-in tools individuals can use to make sure data and the formulas created to calculate data are accurate and logical. For example, data validation is one of the most powerful, but least used, features in Excel. Excel's formula auditing toolbar allows users to review invalid data and display relationships to determine a formula's source and dependence on a cell's value. Relationships are displayed with lines and dots that show either the cells used in a formula's calculations or the dependent cells that refer to the selected cell.

To reduce errors and help organizations maintain control when using spreadsheets in financial reports, auditors also recommend the following steps:

1. Utilize an auditing tool that can monitor and document the copying, revising, or deleting of key spreadsheet files.
2. Implement a spreadsheet-user inventory process that allows spreadsheets to be located when needed and provides their most recent version only.
3. Apply the spreadsheet application's built-in tools, protections, and password choices to prevent unauthorized or accidental changes.
4. Incorporate testing, documentation, and spreadsheet reviews and results as part of control definitions.

These and other features go beyond the built-in controls available in spreadsheets. By applying these recommendations, organizations can control spreadsheets for accuracy and minimize data mistakes. More important, controlling spreadsheets needs to be an integral part of the Sarbanes-Oxley compliance efforts. When used properly, these internal controls can help maximize spreadsheet accuracy and reliability.

It is clear that spreadsheets play, and will continue to play, a primary role in the analysis and reporting of business information. Instead of fighting it, let's accept the fact that the spreadsheet, with some additional applications or add-ons, offers the most flexible and cost-effective alternative to the expensive and complex world of Business Intelligence.

Jason Schemmel is Vice President of Research & Development for Global Software, Inc. in Raleigh, North Carolina. Reach him by email at jason.schemmel@glbsoft.com.