Printer Friendly

Spreadsheet errors: are they undermining decision making in your organization? By prioritizing a set of important spreadsheets and developing review procedures, public managers can lower the risks of errors, and scaling the intensity of quality control efforts to the level of risk provides an economical approach to avoiding disastrous consequences.

Almost all public managers use spreadsheets to contemplate important decisions, yet audits routinely show that many if not most contain errors. Even conservative estimates find that almost a quarter of operational spreadsheets have errors, while a synthesis of recent research finds that undetected errors could afflict an alarming 91 percent of spreadsheets.

That sounds like a recipe for disaster, but there has been little systematic study of how spreadsheet errors affect decision making. Our recent research with executives and senior managers in the public, nonprofit, and private sectors suggests that this is a serious managerial problem that ought to receive more attention.

Local Government Example

We heard this story from one respondent:
 A countywide property reassessment forced local governments to adjust
 their revenue projection models and associated tax rates. With a final
 vote set for that evening's meeting, the chief financial officer of
 one local government entity suddenly gets a phone call at 5:30 p.m.
 A senior analyst has just found a critical mistake. The collection
 delinquency rate was erroneously pointing to a blank cell, which Excel
 interprets as a zero. That implicitly assumed 100 percent tax
 collection, so that a low tax rate would raise sufficient funds.
 Passing that rate would have created budget shortfalls in the millions
 of dollars.


Experiences like this are hardly unique. The European Spreadsheet Risks Interest Group, a consortium of researchers and practitioners dedicated to addressing spreadsheet risk, has tallied a sobering compendium of errors (http://www.eusprig.org). Even quality-conscious organizations with prestigious auditors suffer debilitating spreadsheet errors. Should you rely, as the senior analyst above told us later, on "dumb, blind luck?" Or, might it be time to review your organization's quality-control procedures for catching spreadsheet errors early, before they jeopardize decisions in your organization?

Understanding Spreadsheet Errors

Many of the qualities of spreadsheets that make them so useful also make them error-prone. The accessibility of spreadsheets encourages overconfidence. Because spreadsheet auditing is difficult, many developers never discern the presence of errors in their own spreadsheets. Some errors are easy to catch, but others are more elusive. We distinguish three broad types:

* Model errors, such as errors of omission. Model errors are especially difficult to catch. Simple examples include applying a uniform rate to a seasonal variable or omitting interrelationships, such as a city treating parking lot utilization as a fixed constant when exploring the budgetary implications of raising the municipal parking tax. A spreadsheet with model errors can be perfect mathematically but still be wildly out of touch with the real-world situation.

* Formula errors and wrong calculations. Formula errors can range from parentheses errors in a mathematical formula to a missed row in a summation. They arise in many ways--even by disappearing when formulas are pasted over with constants. Excel's built-in formulas, such as AVERAGE and NPV, are helpful shortcuts but run the risk of being misapplied. Amusingly, one analyst had an error in a logical flag she built to warn herself of other errors.

* Errors in data quality. Typical data entry errors include familiar "typos" caused by quick fingers, yet other sources of data corruption are just as consequential and much more difficult to detect: a bad query copied into a spreadsheet, a bad sort that destroys the integrity of a row, or a mismatch of units. For example, a health insurer reported that a change in prescription pill units triggered costly automatic reimbursements that were far too large. Since "garbage in" equals "garbage out," most organizations implement quality-control procedures for catching data entry errors in spreadsheets, including input controls, validation, protection, and cross-footing.

Spreadsheet quality guru Raymond Panko notes that rates of error creation in spreadsheets are roughly similar to comparable tasks: typing for data entry and software coding for formula design. Indeed, none of these error types is unique to spreadsheets per se. It matters less whether or not people create errors (they will) than how adroitly an organization finds and corrects them.

Many managers bet on a discerning eye, assuming that any undetected errors will have a negligible impact on their decision making. The good news is that organizations find most of the big errors before disaster strikes. The bad news is that quality-control procedures are often informal, catching gross errors by a cursory inspection but repeatedly missing less obvious ones. The cumulative costs of these small errors can be quite large when spreadsheets are reused.

Given how common spreadsheet errors are, why do so many organizations still stake their quality-control efforts on informal measures? Many managers mistakenly believe that implementing better quality control gobbles up staff time. However, good management up front usually saves time and money in the long run. Another problem is lack of understanding about how best to treat errors. One manager we spoke with tried to fix a spreadsheet bug by literally throwing out the computer and buying a new one. Luckily, many important practices, such as consistent peer review, do not require a PhD in computer science to implement. Awareness, motivation, and a minor investment are required to reduce errors substantially.

Ideal Quality Control: Software Engineering for Spreadsheets

The ideal quality control for spreadsheets would match standards for software engineering, where products have a distinct life cycle with phases such as design, development, and testing. In that sense, coding in spreadsheets is not so different from coding in C++ or Java. We describe next what quality control can look like in the best case and then in a resource-constrained world.

Specification

Unlike many spreadsheet developers, programmers rarely start typing code or formulas without an explicit discussion of program or "model" specifications. Specification is an important stage, ensuring that the users and developers have a shared and accurate understanding of the real-world "problem" at hand; the simplifications, abstractions, and limitations of the model that will be coded into the spreadsheet or computer program; and what functionality is expected of that spreadsheet or computer program (what types of output it will be expected to produce). A specification stage can also help identify errors of omission, which are notoriously difficult to detect.

Of course, one way of understanding the problem at hand is to build informal, exploratory prototypes, and these are easily done in a spreadsheet, yet too often these prototypes are directly converted into final code without pausing to specify exactly what that code is supposed to accomplish. Failing to enforce a precise and common understanding through a specification process can lead to spreadsheets that are flawed as a whole, even if every individual formula is technically correct in the narrow sense. We frequently see error-prone situations where young analysts, with little domain knowledge, build spreadsheets for executives, who don't really understand the spreadsheets. This can also be problematic when the spreadsheet model user and developer are the same person because that one person never has to articulate explicitly what decision the spreadsheet is supposed to inform.

Design and Development

Spreadsheet developers creating complex spreadsheets could use guidelines of the sort available to professional computer programmers. Guidelines for building spreadsheets help produce spreadsheets that are both reliable (meaning error-free) and auditable (meaning they can be understood and certified as being error-free).

While true standards for spreadsheet design are still being developed, some guidelines exist. For interface readability, common practices include designing from left to right and creating menus and indexes. Typically, there should be separate modules for data, calculations, and reports. Large modules should be broken into submodules. Numeric data should be entered only in the data module and then referenced, never hard-wired into a cell formula. Complex formulas should be broken into shorter, digestible pieces since our ability to detect errors decreases with formula length. Models given to others can be provided with a "control panel" or "dashboard" worksheet that contains only the key inputs and outputs.

Documentation

In software engineering, adequate documentation is a prerequisite for quality. All who have reflected seriously on the topic recommend the same for spreadsheet models. Undocumented spreadsheets can be as impenetrable as "spaghetti code." Fewer than half of practitioners we spoke with documented their spreadsheets. However, those that did had fewer concerns about major errors.

Recently, a federal agency's spreadsheet-based analysis, designed entirely by one senior analyst, was found to be reporting results with an error of several billion dollars. When the model was ultimately redone in SAS statistical software, almost none of the analysis could be verified due to poor documentation. Documentation precludes the possibility of having to discard spreadsheets understood by a sole analyst. In the software development field, this situation is humorously dubbed "job security," but information technology firms take the implications seriously enough to have well-developed policies for preventing it. Organizations of all types might benefit from analogous policies, ensuring that important spreadsheets are organizational, not individual, resources.

Testing and Validation

Typically in software development, one expects to spend twenty to thirty minutes testing software for every hour spent developing it. Relative to coding in other languages, spreadsheets are quick to generate, yet difficult to test. So the ratio of testing time to development time should be even greater for spreadsheets, yet often it is smaller. Indeed, contrary to expert advice, many organizations do not even think of testing or validation as distinct activities when it comes to creating spreadsheets.

Instead, the most common approach is an iterative process of "code, show, and then fix." As a result, executives catch many errors, not developers. One of our respondents described a case in which a spreadsheet with an error was shown to twelve board members; the error cost the unwitting reviewers a good chunk of their annual meeting time, and the analyst, his job. It is far cheaper to detect errors earlier rather than later, yet over half of our interviewees first identified errors after spreadsheets were operational.

Volumes of technical literature have been written about traditional software testing and validation. Far less is known about spreadsheet validation. Various review and testing methods have been suggested for spreadsheets. There are some software auditing tools one can buy, but no industry standard exists, and their performance in practice has not been independently evaluated.

Still, two recommendations are clear. First, institutionalizing spreadsheet review is a sensible step. Many experienced analysts recognize the benefits of review, but not enough organizations commit to formal review policies or budget time and resources to do it. Second, the reviews should be done by someone other than the spreadsheet developer. The specific review methods can be customized to the organization and type of spreadsheet, but the key is a fresh set of eyes.

Factors in Real Organizations

Applying the ideal level of quality control to all spreadsheets would be impossibly time-consuming. It could even be counterproductive inasmuch as one of the great virtues of spreadsheets is their ability to facilitate quick, flexible analysis.

Clearly, one should scale the intensity of quality control to the level of risks. If a spreadsheet is used to inform a single $1,000 decision, do not spend $10,000 checking it for errors, yet spending only $10,000 on quality control may be penny-wise and pound foolish if the spreadsheet forms the basis for a multimillion-dollar acquisition or lawsuit!

Besides simply the stakes of a decision, there are broadly two sets of factors that guide whether one should use the "Cadillac" or "Chevrolet" version of quality control: (1) characteristics of the spreadsheet itself, and (2) characteristics of the human and decision processes within which the spreadsheet is embedded. Spreadsheet experts have traditionally emphasized the former, but both are equally important.

Spreadsheet Characteristics Influence What Level of Quality Control Is Needed

Certain characteristics of a spreadsheet make it error-prone. A higher level of quality-control is warranted if the spreadsheet has multiple users, high frequency of use, or a long lifespan.

Complexity

Reviewers require more time to audit and understand a large, complex model. Spreadsheets with complex mathematical models, including calculations that spread over several worksheets, require more quality-control investment. Another factor to consider is the level of functionality, such as the use of macros or VBA script and spreadsheets that automatically couple with other software, such as an Access database.

Note dimensions of your model that are complex, so that a qualified reviewer can focus on these aspects first. When complex models are developed under time pressure, experts recommend prioritizing parameters and calculations with high leverage on the bottom line.

Use and Reuse

The more often a spreadsheet will be used, the more time and effort should be invested in ensuring its quality. A small error can aggregate into a nontrivial cost when the spreadsheet is reused. Another problem is spreadsheet mission creep. It is common for spreadsheets to grow like a country house with so many additions and extensions that the complete version is far larger and more complicated than what was originally envisioned. Each new cycle of use can introduce errors in adding incorrect new data, not updating parameters, inappropriately applying an old structure, or copying and pasting or otherwise accidentally corrupting formula integrity.

Number of Users

For multiple users, version problems are a frequent source of errors than can be avoided by version numbering with documentation of changes. A good maintenance structure can be simple or formal, with specified version control or an audit log.

Guidelines exist for quick assessment of the level of quality control needed. The New Zealand Treasury provides a good example (available at http://www.treasury.govt.nz/dice/reports/rev-spreadsheets.pdf).

Human and Decision Factors Influence What Level of Quality Control Is Needed

Obviously, spreadsheets warrant higher levels of quality control when there are clear, tangible costs or severe consequences to making the wrong decision. However, our research identified other characteristics of the decision context that should shape the level of investment in quality-control.

Credibility and Litigiousness

In a nonconfrontational environment, a $1 million error is 1,000 times more important than a $1,000 error. However, respondents described spreadsheet errors in partisan budget battles, lawsuits, and contentious negotiations with labor unions that were small in dollar terms but still had ramifications in terms of credibility essentially as grave as numerically larger errors. If an opponent can use a $1,000 error to discredit the reliability of all of your calculations and thus undermine your bargaining position, then the $1,000 error is also very important, but the $1,000 errors are much harder to detect by simple inspection of output or other traditional quality-control procedures.

Time Factors

Bureaucratic adherence to quality-control procedures should not preclude using spreadsheets to inform quick-turnaround decisions. If deadlines force a choice between an informally reviewed spreadsheet and none at all, the informally reviewed spreadsheet may be better than using intuition alone to make judgments about complex financial forecasts. However, if the timing of the decision cycle permits testing and review, by all means manage analysts' milestones and deadlines in a manner that permits review.

Decision Maker/Human Involvement

We observed that the less human participation there is in the decision loop, the greater the need for formal validation. Spreadsheets that generate automatic billings or check writing must get it right! Other spreadsheets just give background information for a final decision based primarily on human judgment. When there is a person in the loop who can detect gross errors, spreadsheet errors are less likely to lead directly to a decision error.

Invariably, some spreadsheets will continue to be inherently informal models used for intuition and experimentation. That is not bad. Spreadsheet expert Thomas Grossman emphasizes the importance of "scratch pad" applications in experimental learning, but cautions against letting these casual "artifacts" metamorphose into formal organizational assets. If the rough analysis turns out to be valuable, then perform a formal specification and manage the resulting spreadsheet appropriately.

Conclusion: What You Should Do

Spreadsheets are ubiquitous and frequently flawed. Spreadsheet errors can and do lead to costly bad decisions, yet few managers spend enough time thinking about spreadsheet errors or ways to mitigate them. Happily, a few targeted efforts can minimize serious risk to decisions without imposing unreasonable costs.

We have outlined "Cadillac" spreadsheet quality control, modeled on sound software engineering principles. We have suggested guidelines for when you need the Cadillac and when a more modest approach may be acceptable (and still much better than nothing). What, if any, action items are called for depends on the particulars of your organization. For most organizations, the following are sensible first steps for any spreadsheets that will be shared, reused, or inform high-stakes decisions:

* Raise awareness of the issue among your staff.

* Incorporate explicit design stages for important and complex spreadsheets models.

* Require independent peer review of all but the most informal of spreadsheets.

* Budget for testing and documentation activities.

* Have reviewers publicly sign off on their certification of the spreadsheet's quality.

* Enforce version numbering with documentation of changes.

* Disseminate written guidelines on quality control.

Larger organizations might also develop spreadsheet quality training programs, designate a point person or spreadsheet quality assurance, or enforce guidelines for spreadsheet design. However, even simple quality-control strategies have great potential to introduce a culture of accountability and accuracy for spreadsheet analysis.

REFERENCES

Boehm, Barry, and Victor R. Basili, "Software Defect Reduction Top 10, List." Computer 1 (2001): 135-137.

Grossman, Thomas A. "Spreadsheet Engineering: A Research Framework." European Spreadsheet Risks Interest Group 3rd Annual Symposium, Cardiff, July 2002. Available at http://www.usfca.edu/sobam/faculty/grossman_t.html.

List, Ian, "How to stop the spread." Australian CPA 2 (2002): 58-59.

Panko, Raymond, "What We Know About Spreadsheet Errors." Journal of End User Computing (Special Issue on Scaling Up End User Development) 2 (1998): 15-21. Available at http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm.

Whittaker, David. "Spreadsheet Errors and Techniques for Finding Them." Management Accounting 9 (1999): 50-51.

Jonathan P. Caulkins is Professor of Operations Research and Public Policy at Carnegie Mellon University's Heinz School of Public Policy and Management. Erica L. Morrison is a current master of science in public policy management (MSPPM) candidate at the Heinz School. Timothy Weidemann received his MSPPM from the Heinz School in 2004 and is currently in public-sector practice at Deloitte Consulting, LLP.
COPYRIGHT 2005 Bureaucrat, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2005 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ARTICLE
Author:Caulkins, Jonathan P.; Morrison, Erica L.; Weidemann, Timothy
Publication:The Public Manager
Geographic Code:1USA
Date:Mar 22, 2005
Words:3014
Previous Article:Best practices in communications: ten tips for improving communications and setting the stage for your success; With competing priorities and busy...
Next Article:ASPA'S National Capital Area Chapter holds annual awards ceremony.
Topics:

Terms of use | Copyright © 2017 Farlex, Inc. | Feedback | For webmasters