Every now and then a new story of corporate embarrassment, or worse, caused by spreadsheet errors hits the business headlines. Sometimes the mistakes are on a scale that can only be described as monumental, like Fannie Mae's $1 billion-plus underestimate of total stockholder equity in 2003, the result of errors in a spreadsheet used in the implementation of a new accounting standard. Or the cut-and-paste snafu in the same year that caused Canadian power company TransAlta to spend $24 million more than it intended to on hedging contracts. Both of those horror stories pale, though, next to the $2.6 billion error uncovered at Fidelity Investments in 1994 when a tax accountant omitted a minus sign while keying information from the fund's financial records into a separate spreadsheet, turning a $1.3 billion loss into a $1.3 billion gain.
Chilling though the stories may be, finance leaders tend to shrug them off, believing "it could never happen here." But a slew of academic studies over the last 10 years showing that upwards of 86 percent of spreadsheets contain errors suggests that confidence may be misplaced. And the errors are by no means always trivial, according to Kenneth R. Baker, professor in the Tuck School of Business at Dartmouth College. In a 2007 paper, Baker and colleagues Stephen G. Powell and Barry Lawson examined 25 spreadsheets provided by two consulting companies, a financial services firm, a manufacturing company, and an educational institute. They found 381 potential errors, 117 of which were confirmed as errors by the developers of the spreadsheets. About 60 percent of the confirmed errors had a quantitative impact on the spreadsheets' results; the largest was $100 million.
Interestingly, many of the spreadsheet developers were not particularly surprised or dismayed by the errors that the research turned up. "We were prepared for the fact that there were large economic implications in some cases," says Baker, "but we were also a bit surprised to find that sometimes when we discovered errors, the people whose spreadsheets these were dismissed our findings for one reason or another." Sometimes the reaction was that the result was "close enough."
Maybe so, but still ... a note of complacency here? More reason to think so comes from a recent Deloitte online poll of some 3,000 finance pros. When asked how their company handles spreadsheet risk, only about 42 percent of respondents said it's part of a periodic risk assessment. And more than 17 percent answered "don't know/not applicable," a result that surprised Michael Juergens, principal with Deloitte & Touche LLP. "Spreadsheets are so widely understood in terms of functionality -- and everybody's got a spreadsheet story about something bad that happened -- that it seemed to me that people would be very aware of the problem," he says. "You would think they'd be having discussions about what to do about it."
Sarbanes-Oxley was a strident wake-up call, but one that didn't give companies enough time to approach spreadsheet risk management systematically. "They may have touched on it a little bit, but they didn't know how to handle it, so they stepped away," says Sarah Adams, director with Deloitte & Touche LLP and leader of the firm's national IT internal audit practice.
But now companies are starting to circle back to the problem, armed with a deeper understanding of what's needed to fix it and an expanded range of software tools for the job.
The approaches to spreadsheet risk management proposed by audit firms, risk consultants, and solutions vendors differ in the details, but the following broad outline is generally discernible:
1. Identify and inventory your target spreadsheet population. First you have to find the files you intend to review. Commercial software packages or internally developed applications can scan your organization's servers to identify all spreadsheets and the date they were last modified. That can be a useful starting point, but you'll still need to do some legwork to separate the wheat of mission-critical spreadsheets from the chaff of Christmas card lists and office sweepstakes files. Consulting and internal audit firm Protiviti recommends talking to the owners of key business processes, starting with processes that are most dependent on spreadsheets and those that have had instances of losses or errors in the past. Look for spreadsheets that, if deleted, would take a long time to recreate or couldn't be recreated at all, as well as documents that might severely impact the organization if they turned out to be inaccurate.
Compile a list of spreadsheets that support significant financial processes together with summary information that you'll need to make assessment decisions, such as the spreadsheet's owner, its purpose, the dollar volume it calculates, and its degree of confidentiality.
2. Gauge each spreadsheet's risk level. Next you need to rank your spreadsheets based on their potential for error and level of criticality. "The likelihood of risk increases almost geometrically as the complexity of the spreadsheet grows," notes Edward Hill, managing director with Protiviti. Complexity will depend on factors such as the spreadsheet's size, the number and complexity of its formulas, and the volume of linkage to other spreadsheets. Criticality depends on the operational quantities or dollar amounts the spreadsheet calculates. Focus efforts on the most complex and critical files, but bear in mind that even simple spreadsheets can contain errors, and since they often receive less testing than complex spreadsheets, the mistakes can be significant.
3. Establish each spreadsheet's integrity. The goal at this stage is to establish a point in time at which the spreadsheet is functioning in accordance with management's intentions, according to Deloitte. Identify the input data and check it against the source. Are the formulas the right ones for the spreadsheet's purpose, and are they entered correctly? Do they contain errors, such as hard-coding of numbers or incorrect references to other cells? The process of establishing this base line is where most project teams will spend the bulk of their time, according to Deloitte.
4. Review and remediate controls. Next, determine the appropriate level of control for each spreadsheet based on its risk profile. Are the controls that are already in place effective? Do they need to be supplemented with additional preventive or detective measures? (see 7 Controls To Protect Spreadsheet Baselines). At this point, the project team may want to review the organization's policies and procedures for spreadsheet use and recommend improvements, if necessary. And, of course, the controls will need to be tested to ensure that they operate effectively.
"The best practice for internal audit is that they do a risk assessment on a yearly basis," says Adams. "They need to understand the main risks that are affecting the organization, and those can be operational, financial, or IT related, so spreadsheets need to be part of that risk assessment. Oftentimes they are not."
Much of the discovery and analysis work in a spreadsheet risk management initiative seems perfectly suited to a software solution, and indeed a growing number of vendors are offering such packages, according to an overview of the market published in March by Gartner Inc. The sector is still in its infancy, though, and only a few hundred companies have purchased one of these products, which can run around $50,000 for departmental use and up to $500,000 on an enterprise basis.
Chicago-based wireless giant U.S. Cellular Corp. took the plunge only after a long period of due diligence, according to Don Krause, manager, financial control systems, and senior financial analyst Susan Abern, who jointly spearheaded the initiative. The project kicked off with a vendor analysis in April 2005 and finally went live in the fourth quarter of 2007 with a product from Prodiance. U.S. Cellular simultaneously implemented Microsoft SharePoint as the document management and workflow component of the solution.
Krause found the tool especially useful in analyzing and validating the key spreadsheets. "It tells you where you've got hidden cells, where you've got hidden columns and rows or embedded fixed numerals in your formulas," he reports. "It gives you all sorts of clues which can help you redesign your spreadsheets for minimum risk and to maximize productivity."
But the software's biggest contribution was in the controls area, says Krause. "The most important thing that we've used the system for is to get control of the spreadsheets through version control and access control. Those are very important because as soon as you identify what those key spreadsheets are those things can get completely out of control." The system's ability to provide comparisons of different versions of spreadsheets "has been a huge benefit to our control owners in homing in on various formula changes and key auditing items they need to identify," adds Abern.
Not that the software eliminated all of the work; far from it, Krause and Abern report. The initial discovery phase took a lot of what Krause calls "gumshoe work" to identify the key spreadsheets and their owners. Abern stresses the need for a comprehensive training program to ensure that spreadsheet owners understand the new system and the requirements of a more controlled spreadsheet environment.
Any spreadsheet risk management initiative is a serious commitment of time and resources, but the payoff goes beyond reducing the chances of headline-worthy error or fraud. It's an opportunity to increase the confidence of users in the data that pour through these ubiquitous but poorly controlled applications.
| Does your company factor spreadsheet risk into your overall risk reporting and decision-making process? | |
|---|---|
| Spreadsheets are considered as a part of my company’s periodic risk assessment | 41.9% |
| Spreadsheets are not considered as a part of my company’s periodic risk assessment | 29.3% |
| My company does not formally assess risks on a periodic basis | 11.3% |
| Don’t know/not applicable | 17.5% |
| Source: Deloitte & Touche LL | |
| Which option best describes the level your company utilizes spreadsheets to support business processes or financial reporting? | |
|---|---|
| Heavy: We rely on spreadsheets for critical portions of the business | 70.1% |
| Limited: We have some use of spreadsheets, but significant spreadsheet errors could not impact the business | 23.3% |
| None. We do not use spreadsheets | 0.3% |
| Don't know/not applicable | 6.3% | Source: Deloitte & Touche LLP |
| Does your company evaluate and protect spreadsheets that are important to the business? | |
|---|---|
| We employ specialized techniques to control and manage spreadsheets, and periodically evaluate these controls | 33.9% |
| We look at the spreadsheets, but have little or no specific processes to confirm that they are functioning in accordance with management's intentions | 42.7% |
| We recognize that there are spreadsheets, but do not undertake special measures to protect and test them | 14.6% |
| I don't know enough about how we are using spreadsheets to conclude which method would be most appropriate for us | 19.7% |
| Don't know/not applicable | 8.8% | Source: Deloitte & Touche LLP |
Goal: Integrity
Disposition: Detective
Control: Versioning should be employed in all spreadsheet changes. Changes to a spreadsheet should include some form of unique identifier that can be used by parties to differentiate versions of the spreadsheet.
Goal: Integrity
Disposition: Detective
Control: All changes to a spreadsheet are reviewed and approved. Someone other than the party making the change should perform this. The review process should guide the reviewer to confirm that the changes are functioning in accordance with management's intentions, and the integrity of the spreadsheet's formulas, data, and results have not been compromised.
Goal: Integrity
Disposition: Preventive or Detective
Control: The validity of spreadsheet inputs should be ascertained. Whether input data is manually keyed or imported, steps should be taken to confirm input data being imported into the spreadsheet is complete and accurate.
Goal: Availability
Disposition: Preventive
Control: Spreadsheets should reside on file servers. The primary copies of critical spreadsheets should not reside on portable or end-user computers.
Goal: Availability
Disposition: Preventive
Control: Spreadsheet files are backed up to external media. The frequency of the file backups should be sufficient to support business data recovery needs.
Goal: Integrity
Disposition: Preventive
Control: Spreadsheet files should be protected with some form of access control. Users without a business need to open the spreadsheet should be prevented from doing so. This can be done by restricting access to the file itself, or the folder in which the spreadsheet is stored.
Goal: Integrity
Disposition: Preventive
Control: Non-input-related spreadsheet fields are password protected. All fields, such as formulas, that do not need to be edited by the end user, but are necessary for the accurate employment of the spreadsheet, should be password protected to prevent unauthorized changes.
Source: Deloitte & Touche LLP