Controlling Excel Chaos With Business Intelligence

Business analysts develop Excel spreadsheets to assist with the day-to-day operational decisions their jobs demand. Pleased with the autonomy and sophisticated analysis that Excel supports, they share these innovations with colleagues who modify spreadsheet logic and manually tack on data from their own information silos. Over time, rogue spreadsheets with data from dubious sources is propogated throughout the organization. Executives find themselves making decisions based on untraceable, questionable data. The enterprise is at a loss to audit these numbers for themselves and for regulatory agencies.

Meanwhile, the IT division has complete, auditable, and backed-up operational system data that is untapped by the Excel user community. IT executives are frustrated by the rampant spread of unreliable information and their inability to leverage corporate data stores.

Information Builders has both push and pull strategies to serve up corporate data for use in Excel spreadsheets. These strategies buttress Excel's weaknesses as an enterprise business analysis tool while allowing users to continue to operate in their preferred analysis environment.

The Excel Challenge

Spreadsheet Chaos

Spreadsheets were the standard model for doing financial analysis long before enterprises automated accounting and finance functions. When Microsoft launched Excel, it mimicked the spreadsheet layout and made the business analysts' transition from paper to computer a seamless one. Moreover, the easy-to-use, point-and-click interface running on the Windows platform gave Excel a market-share victory over competitors like Lotus and VisiCalc.

Manual Labor Begets Errors

Excel was designed for the individual analyst working alone on a computer with a limited amount of data. The original Excel model had a business analyst typing a small subset of corporate data into a spreadsheet, cell by cell, and then manually manipulating that data to solve a business problem. But manual data entry is an expensive, time-consuming misuse of the business analyst's time and error prone as well. Cell-by-cell data entry leads to simple mechanical errors such as mistyping a number or pointing to the wrong cell. Graver errors can also occur when the analyst makes errors in formulas because of bad keystrokes or bad logic.

When data is available in an existing spreadsheet, it can be cut and pasted via the clipboard or merged via Excel functionality. This works best when the source and target ranges are formatted identically. If they are not identical, data and formulas can inadvertently be corrupted.

Alternatively, ranges of data from an external Excel workbook can be referenced in place. The effectiveness of this technique is dependent on the source file remaining unchanged. Maintenance to spreadsheets chained in this way can be horrendous when there is no centralized change management policy.

Ideally, data from corporate stores should be transferable directly to Excel spreadsheets, thereby insuring that all business analysts start their spreadsheet ventures with the one true version of enterprise data. But Microsoft's vision of interoperability has remained focused on Excel and other Microsoft desktop products. Outside connections must be established through ODBC or DDE connections. Beyond these options, one must transform data to the text-based CSV format and stage it on the Windows platform.

On the Spreadsheet Audit Trail

For many companies Excel has grown beyond a small-scale analysis tool. It is instead the main vehicle for financial analysis in mission-critical departments, pushing Excel functionality beyond its original intent. Excel has no built-in mechanism for separating the presentation, calculation, analysis, and storage layers of an application. The onus of solid spreadsheet design is still left to the spreadsheet developer. Of course, skill levels in Excel and in programming methodology vary widely among analysts. In non-IT departments untrained spreadsheet programmers can apply resource-intensive calculations at the wrong level, creating a performance bottleneck. Rigorous testing with sufficiently varied data samples is rarely done at the departmental level. These are just some examples of possible fallout from not applying IT methodology to the spreadsheet community.

Accurate and traceable financial information is critical to making sound business decisions that move the enterprise forward. Auditable information is required outside the organization as well. The 2002 Sarbanes-Oxley Act requires disclosure in annual reports of internal controls over financial reporting. Spreadsheet errors can lead to expensive and embarrassing problems. The European Spreadsheet Risk Interest Group analyzes and quantifies the cost of spreadsheet errors worldwide.