LESSON - The Managed Spreadsheet Environment
How to Preserve the Best and Most Useful Features of Spreadsheets
By Neil Raden, Founder of Hired Brains, on behalf of Actuate
It’s commonly assumed that spreadsheets became widely deployed in organizations because they were intuitive, easy to use, and filled a need that was not being met by other technologies. The truth is that spreadsheets exploded in popularity because they were used as an alternative to enterprisewide, IT-backed reporting, planning, and analysis applications.
Today, spreadsheets are used for complex multi-user application development, stretching to the breaking point their already razor-thin capacity for security, maintainability, scale, networking, and concurrent use. Because of changes in the regulatory environment, such as the Sarbanes-Oxley Act, these applications, no matter how well conceived and executed, can potentially expose organizations and their executives to fines and even criminal charges if they lack sufficient transparency.
As a prototyping tool, spreadsheets have no equal, but with constrained IT budgets (the norm over the past few years), an attractive prototype can become a permanent stand-in. The drawback is that the feasibility curve for spreadsheets hits the wall as the complexity increases, and the applications either fail precipitously or gobble up greatly underestimated maintenance costs to keep them functioning.
To make matters worse, a large part of these maintenance costs is the time of manager-level people. This not only increases costs in an obscured way, but also diverts the attention of critical people from their primary roles. The principal contributors to complexity are scale (number of users, amount of data), serial models (linked spreadsheets, dependent applications), external data such as customer lists or syndicated data, rapidly changing elements, assumptions, and organizational structure.
Today, spreadsheets are used for complexmulti-user application development, stretchingto the breaking point their already razor-thincapacity for security, maintainability, scale,networking, and concurrent use.
At the extreme, spreadsheet applications attempt to perform operations that are clearly best suited for enterprise tools, such as data cleansing and integration, and often employ programming with scripting or Visual Basic, which places them squarely in the realm of software engineering.
Not all spreadsheet applications suffer from dangerous deficiencies in security, scalability, error trapping, etc. There are many instances of clever, even brilliant, workarounds that stretch spreadsheet software far beyond its intended purpose. The problem is that, in most cases, the workarounds are not brilliant, and they generously substitute labor for good design.
The use of spreadsheets is a crucial part of virtually every organization’s information management process, and they contribute a great deal of value. Banning them or trying to dictate their use has proven to be counterproductive. How are spreadsheets used productively? What roles do they perform that are vital to an organization? Typically, spreadsheets are employed to fulfill roles in three primary activities:
- Gathering, creating, and managing data
- Creating models and calculations
- Disseminating information, principally as reports
Gathering, creating, and managing data represents an entire discipline within IT, so the thought of domain experts with no formal training in data management devising applications is worrisome to most IT managers. Models and calculations, on the other hand, are more or less alien to mainstream IT, which is more focused on infrastructure and transaction processing.
Nevertheless, a derived value shares equal weight with source data, and modeling processes must have a formal review and approval process, especially today. Only the last activity, disseminating information, is relatively risk free. The other two are the principal source of error and cost. A truly breakthrough solution would be one that preserves all of the features of current spreadsheets for personal use, but manages the use of spreadsheets for gathering data and creating models and calculations for shared applications.
There are alternatives to the Shadow IT conundrum. It is not necessary to throw away the baby with the bathwater. All that is needed is to unbundle the best parts of spreadsheet programs from the practices that lead to dysfunction, excessive costs, and risk in order to provide better-managed solutions that suit everyone’s goals.
This article originally appeared in the issue of .