Spreadsheets have become a pervasive villain in the financial world - they are guilty of huge error rates and cataclysmic mistakes, and are a major source of heartburn and heartache when it comes to audit and compliance concerns.Nonetheless, Excel is ubiquitous and is arguably central to the financial operations of many companies. There is no other tool that has contributed so enormously to productivity in the arena of business performance management.
So is Excel villain or hero? This article sorts out both sides of the argument, and proposes some pragmatic approaches to mitigating risk and maximizing return on Excel utilization in BPM applications.
The downside
Perform an Internet search on "Excel Hell" or "spreadsheet errors" and you'll quickly be reaching for the Maalox. Statistics stating that 90 percent of spreadsheets contain some type of error, and anecdotal stories citing errors costing millions of dollars - and in several cases even billions - are the pervasive theme. The advent of Sarbanes-Oxley legislation has put a heightened focus on the role of spreadsheets in the financial processes of public companies in terms of potential for error, fraud and lack of control.
Here's a list of some of the biggest problems:
* Spreadsheets are subject to errors in input, formula specification and import/export definition.
* Spreadsheets are defined by end users who have neither the technical training nor the discipline applied in other programming practices.
* Minimal attention is paid to security, version control, testing, documentation and auditability.
* The rampant replication of spreadsheets opens a mammoth potential for inconsistency.
* The ability of an end user to embed an inscrutable macro into a critical spreadsheet further complicates testing and maintenance.
* Because spreadsheets are static documents, dynamic changes in the underlying data cause spreadsheets to go "stale" or become outdated.
The upside
Can 65 million Excel users be wrong? It is impossible to walk into any finance department and not find Excel running on every desktop.
The most common explanations for Excel's widespread success are its ease of use, flexibility, analytic power, universal acceptance and low cost. And the number of qualified staff who can effectively use the tool is beyond compare.
Excel has also been effective at addressing a broad spectrum of corporate financial needs, and that success is an extremely powerful argument for its continued use.
Clearly, an effective strategy needs to consider the trade-offs and approaches that can minimize the risks of Excel usage without cutting off the benefits.
The analysis
The strengths and weaknesses of Excel are of different relevance and weight, depending on the application that it is used for. For example, the risk/reward ratio of implementing a general ledger in Excel is far different than using an Excel template for an expense report.
We'll examine the arguments in the context of business performance management, which consists of the following:
* Business modeling;
* Planning/budgeting;
* Consolidation;
* Reporting - management and statutory;
* Analysis - regular and ad hoc;
* Presentation - graphics, dashboards and balanced scorecards; and,
* Feedback - problem and opportunity identification, action plans and communication.
Looking at these functions clarifies the nature of risks and weaknesses of the spreadsheet approach. They break down as follows:
* No distinction between data, logic and process. A single number used in a management report may appear in hundreds of spreadsheets. If its value (or a formula computing the value) changes, it needs to be changed everywhere.
* Lack of traditional IT discipline and expertise. Beware of the organization that does its consolidation on a spreadsheet named "Godzilla," written by an analyst who left last year. Without casting aspersions on financial spreadsheet creators everywhere, how many perform rigorous validity tests, audit checks, back-up procedures, write protection, etc.?
* Capacity. A budget or a report for a department done in Excel is great. Ten departments - not as great. As this number increases, Excel's limitations increase accordingly and are readily apparent.
* Flatland - a lack of multi-dimensionality. Financial data is "multi-dimensional" in character, as evidenced by the broad acceptance of OLAP tools. Read-only pivot tables are great for read-only access, but Excel does nothing to facilitate organizing and managing the data.
* Inadequate security model. Planning and modeling applications require strict control over who can change assumptions and logic. Reporting applications need to restrict distribution of sensitive information.
* Natural redundancy. The "elementary particle" of Excel is the workbook, whereas financial management deals in the realm of financial data elements. Version control on workbooks simply does not have the granularity to prevent incorrect or out-of-date values.
On the flip side, the characteristics that make it such an incredible productivity tool for financial management include:
* Speed and ease of specification. From building an ad hoc analysis, to a budget template, to a comprehensive financial model of a complex business, Excel has few, if any, equals.
* Coalescing data from multiple sources. While Excel is not an ETL tool, by parsing files, writing import queries, cutting and pasting, and re-keying from hardcopy reports, it is possible to merge and analyze data from a number of sources.
* Simplicity of scenario analysis. This is the upside of the unification of data and logic - you can change any formula or value, and immediately see the impact.
* Universal usability. Excel's functionality is not only broad and deep, but it is layered so that a user with any level of expertise can easily access the features needed to accomplish their task. Satisfaction breeds continued use, and a huge pool of fans.
* Presentation, presentation, presentation. From the standpoint of high-quality formatted reports and charts, Excel has no equal. Like it or not, pretty counts, and finance organizations across the world are benefiting.
Budgeting and planning has received a great deal of attention from software providers. Initially, they claimed to offer the antidote for the nightmare of managing spreadsheet data without an underlying database. But once managers proved that they could support their plans with specific logic, vendor focus turned to depth and quality of Excel integration.
The reality is that the consistency, security and ease of consolidation that multi-dimensional databases bring to the budgeting party are huge benefits. Yet users do not want to lose the familiarity, comfort and versatility of both design and entry that Excel provides.
Statutory and management reporting have different trade-offs. In both cases, the "one version of the truth" advantage that a database supplies is critical. Security, auditability and consistent definition of computed values are essential for reports targeted at external parties. The virtually unlimited formatting and graphical capabilities of Excel are highly prized for both management and statutory reports.
Ad hoc analyses benefit from the freedom and power Excel provides the user to specify, manipulate, compute and present. However, easy access to consistent, up-to-date and organized source data is necessary to ensure that the analysis is built on solid ground.
Each component of BPM functionality can be analyzed in this way when considering the role Excel should play in addressing the need. The accompanying table (see box, this page) summarizes the impact of these positive and negative attributes in the form of an Excel "report card."
| AN EXCEL REPORT CARDBy BPM function | |||||||
| Negatives | |||||||
| Merged data & logic | No IT discipline | Not a database | Inadequate security | Redundancy | |||
| Business modeling | C | C | D | D | D | ||
| Planning/budgeting | D | D | D | F | F | ||
| Consolidation | F | D | F | D | C | ||
| Management reporting | F | D | D | C | D | ||
| Statutory reporting | D | F | F | F | F | ||
| Recurring analysis | B | D | D | C | C | ||
| Ad hoc analysis | B | B | C | C | D | ||
| Dissemination | B | D | D | D | D | ||
| Feedback | B | C | D | D | D | ||
| Positives | |||||||
| Ease of Specification | Multiple Sources | Scenario Analysis | Universal Usability | Presentation Quality | |||
| Business modeling | A | B | A | B | B | ||
| Planning/budgeting | B | B | B | A | B | ||
| Consolidation | C | A | C | C | B | ||
| Management reporting | B | B | C | A | A | ||
| Statutory reporting | C | B | n/a | B | A | ||
| Recurring analysis | A | B | B | B | A | ||
| Ad hoc analysis | A | A | A | A | A | ||
| Dissemination | C | C | C | A | A | ||
| Feedback | A | B | C | A | C | ||
The recommendation
The functions associated with BPM straddle two worlds - one is the highly structured, controlled and disciplined one of accounting and operational systems, and the other the free-wheeling, dynamic and analytical world of personal productivity and analytic tools. The following approach suggests a way to enjoy the best of both.
* Capitalize on Excel as a presentation layer.
* Use a multi-dimensionally capable database to manage shared, critical data and take advantage of its capacity, robustness and operational advantages, such as back-ups, logging, etc.
* Use a database and a shared computational engine to store common meta-data such as important formula definitions, roll-ups, access rules, etc.
* Build a secure wall around data and meta-data.
* Draw the line carefully between what an end user can freely build and distribute (analytic worksheet formulas and formatting) and what is administratively controlled (actual data, definition of key metrics).
* Make it easy for consistency to be applied and enforced.
* Facilitate the Excel process in areas such as common formatting templates, pivot table and multiple worksheet integration.
* Structure and manage spreadsheet chaos by using a centralized management system to store shared analyses and presentations.
* Leverage supporting infrastructure, such as e-mail, portals and common libraries, to facilitate sharing and communication.
With this approach you don't need to partition the world of Excel versus non-Excel - simply integrate it!
Max Kay is founder and chief executive of KCI Computing, a Torrance, Calif.-based provider of enterprise performance management and decision support software.
