When Good Spreadsheets Go Bad
Ventana Research strongly advises companies to eliminate standalone spreadsheets
in all collaborative or repetitive enterprise-wide finance functions such as budgeting
and planning. We also advise companies to limit their use in cases where security,
control, and auditability are critical. The importance of these issues has increased
with the passage of the Sarbanes-Oxley Act and subsequent focus on establishing
and maintaining robust financial controls.
An alternative to standalone spreadsheets is what we call the Enterprise Spreadsheet. This software bolts a spreadsheet (usually Microsoft Excel) to a centralized computing system. It leverages all of the training and experience individuals have accumulated over the years, while eliminating the difficulty of rolling up information from a set of spreadsheets, ensuring the synchronization of information, and enforcing all forms of security around the information and process. For budgeting and planning, companies such as Applix, Cartesis, SRC Software, and OutlookSoft offer this kind of application approach.
View
We have written a great deal about the difficulties spreadsheets pose in the budgeting and planning process, and have been advocating their elimination for this purpose (see "Budgeting: Stop the Madness," 10/17/03). We have also pointed out that spreadsheets can be a significant point of failure in financial control systems (see "Budgeting: Stop the Madness," 9/19/03). As a rule, anytime spreadsheets are part of a repetitive business or analytic process companies must duplicate identically over time and across departments. Therefore, they must be part of a coordinated Enterprise Spreadsheet system. Ventana Research states emphatically that standalone spreadsheets (e.g., Microsoft Excel), when used without an enterprise application platform, are not a good solution for organizations.
On their own, spreadsheets are excellent as individual ad hoc analytical tools and for personal data storage. They are still the best alternative for doing exploratory and one-off analyses, or creating and maintaining models and stores of information for personal use, or creating unique or infrequently used reports. For these functions, more robust tools are not productive alternatives because of the amount of time and training required to use the software. The simplicity and low cost of spreadsheets relative to alternatives have led to their widespread adoption by individuals, and near universal use within finance organizations for a wide variety of tasks.
However, standalone spreadsheets are inappropriate whenever more than a handful of people have to share data, when the data must be synchronized between all users, or whenever employees have to execute processes consistently in a collaborative fashion. Spreadsheets, by themselves, cannot support these requirements. Without a centralized server, attempts to overcome spreadsheet shortcomings lead to convoluted, poorly documented and informally supported spreadsheet sets that are inconsistently maintained and prone to obsolescence, which provide inconsistent information and are overly time consuming.
Alternatively, when used as the "front-end" to a server-based analytic system, finance departments can overcome much of the above limitations to spreadsheets. These sorts of Enterprise Spreadsheet systems have the advantage of combining Excel-user familiarity with centrally managed data processing. Data processing and storage is consistent, protected and used appropriately, while data entry and analysis is distributed, accessible and familiar. Because the spreadsheet is bolted to a centrally maintained database management system on the back end, Enterprise Spreadsheets address the problems posed by standalone spreadsheets.
Ventana Research asserts that to be useful as part of enterprise applications, Enterprise Spreadsheets must imbue a spreadsheet front end with four additional key capabilities:
- Shared access to a data store of record
- Processing management
- Security and control
- Auditability
These are the minimum capabilities for Enterprise Spreadsheets compared to stand-alone spreadsheets. In evaluating competing applications that use an Enterprise Spreadsheet architecture, we recommend users compare the specific features and functions offered for business process and collaboration, and the alignment of the application's business logic to the company's processes.
Shared access to a data store of record (in practice almost always server based but potentially through a peer-to-peer network) ensures that information is available to all that need it, that the most up-to-date version is available, and that all users have a coordinated data view. This addresses the "islands of data" aspect of standalone spreadsheets, where information is locked up on individual hard drives and not always accessible to all users. It also allows for synchronization of data to ensure there is "a single version of the truth." When employees maintain files on individual hard drives, versions proliferate and give rise to "dueling spreadsheets." The data store of record also maintains the calculation engine(s) used in the process, ensuring calculation consistency across all users and over time. This limits the potential for errors and fraud that are points of vulnerability for standalone spreadsheets because each is its own calculation engine, and it may be difficult or impossible to verify the formulas are correct.
Processing management functionality includes aggregation (rolling up data quickly and reliably); disaggregation (drilling down to components); synchronization; and versioning (including saving data as complete sets as well as managing the versioning process). Security and control includes limiting access the system and data to valid users, as well as being able to restrict individual users' ability to view and/or modify data, formulas, and the like. Auditability enables administrators to determine which individuals accessed or changed information, formulas, etc.
For budgeting and planning applications, data management, security, and control are far more critical issues than auditability. Being able to track changes is important when companies use Enterprise Spreadsheets in the closing process or in treasury functions.
The most common use of Enterprise Spreadsheets today is for budgeting and planning. Some other budgeting and planning systems (e.g., Cognos Enterprise Planning, Clarity Systems, FRx) incorporate a great deal of a spreadsheet's look and feel and also allow users to import existing spreadsheet models, but do not use the spreadsheet itself as the user interface environment.
Assessment
Ventana Research advises companies to reassess how they use IT systems in order to improve core financial processes. Our research shows a majority of organizations use standalone spreadsheets for budgeting and planning. We also have found standalone spreadsheets are the root cause of many of the issues that prevent budgeting and planning from being a more effective performance management tool. Companies that are hesitant to eliminate spreadsheets entirely from their budgeting and planning process system have alternatives in software that uses Microsoft Excel as the front end, and can duplicate much of the look and feel and functionality in other systems that use the spreadsheet metaphor.
We also find that finance departments use spreadsheets extensively. Realistically, completely eliminating standalone spreadsheets from processes where security, audit and control are important is not going to be cost effective. However, companies should investigate alternative processes and software to limit their use of standalone spreadsheets as much as possible in these instances.
