JOnline: Controlling Spreadsheets 

 
Download Article

Until recently, information systems audit and control professionals paid relatively little attention to spreadsheets. However, a number of regulatory compliance requirements—especially those raised by the US Sarbanes–Oxley Act—have focused new attention on the use of spreadsheets in critical corporate functions. Large, manually managed webs of spreadsheets, each with thousands of cells, are often used in critical businesses functions, and there are errors in 1 percent to 5 percent of all formula cells unless extensive testing is performed (which is rare). Material errors are frequent, and few spreadsheet webs have good fraud deterrence controls. Overall, it is difficult to understand how any company that uses spreadsheets heavily or even moderately in financial reporting can believe that it does not have more than a remote likelihood of having material errors.

Discovering the Dark Matter of IT

Sarbanes–Oxley compliance came as a shock to corporations. First, Sarbanes-Oxley affected nearly every major firm. Earlier compliance crises, such as the Health Insurance Portability and Accountability Act (HIPAA), the Gramm-Leach-Bliley Act (GBLA), and Title 21 Code of Federal Regulations (CFR) Part 11 on electronic records and signatures, had affected only a few industry sectors.

Second, the compliance standards for Sarbanes-Oxley were extremely strict. If a firm had even a single control deficiency during the reporting period that had more than a remote chance of causing a material reporting error, the firm and its auditor could not assess the firm’s financial reporting control system as having been effectively controlled during the reporting period. Five percent is the normal threshold for an error to be considered material.1 This is a very small margin for error.

Failing an audit of effectiveness can be very costly. Research firm Glass, Lewis & Company analyzed 899 cases in which firms reported material weaknesses through the early part of 2005.2 They found that companies experienced an average stock price drop of 4 percent after the announcement. In turn, Dutch research firm ARC Morgan found in 2004 that in more than 60 percent of all cases, the chief financial officer (CFO) was replaced within three months after a company reported material control deficiencies.3

Third, when corporations looked at their financial reporting systems in detail, what they saw were spreadsheets—a lot of spreadsheets. Several large-scale surveys were conducted in the US and internationally in response to Sarbanes-Oxley.4 All confirmed the universal importance of spreadsheets and the complexity of spreadsheet systems. Firms frequently had 200 or more spreadsheets operating as a manually controlled web. Even firms that used financial reporting packages such as Hyperion often still used many spreadsheets, and such "auxiliary" spreadsheets typically are used in end-of-period consolidations and other high-risk analyses.5

Although this widespread use of spreadsheet webs in critical corporate processes surprised many people, it should not have. Earlier, when the pharmaceuticals industry had to contend with the 21 CFR Part 11 rules, it learned that spreadsheets were widely used in the development and testing of new drugs. The widespread use of spreadsheets has also been seen in taxation, the management of intellectual property assets and many other areas.

This widespread use of spreadsheets in critical processes generally has come as a surprise to IT. Corporate IT departments traditionally have focused on large central systems for accounting, payroll, billing, e-mail and other critical corporate functions. For end-user computing (EUC), in contrast, most corporations have help desks, but this is a purely tactical response to a strategic issue. Although it was obvious in the early 1980s that EUC would soon be larger than traditional IT,6 corporate IT departments dismissed EUC as not being a strategic issue and have continued to do so.

Overall, EUC, especially spreadsheeting, is the dark matter of IT. The corporate IT department has ignored it, and no other part of the firm has picked up this strategic slack. Surveys have shown that few corporations have strategic policies for the development of spreadsheet applications, and spreadsheet development usually is highly informal and bears little resemblance to careful software development.7 Control over spreadsheeting is left to individual departments and even to individual employees.

The Need for Error Controls

Two areas have an obvious need for spreadsheet controls: accuracy controls and fraud controls. Although Sarbanes–Oxley was created in response to fraud incidents, the Act has strong requirements for correctness in financial reporting, and innocent error is just as bad as fraud.

In software development, programmers know that they consistently make errors in 1 percent to 5 percent of all code statements, although they usually express this as 10 to 50 faults per 1,000 lines of code.8 Error rates are expressed as percentages in this article because this is the norm in human error research.9

There is nothing remarkable about a human error rate of 1 percent to 5 percent. Human error rates have been measured in many types of human activity, and except for the most trivial actions, error rates almost always exceed 1 percent of all cognitive actions.10 Furthermore, total error rates do not vary dramatically among individuals. Even between experts and novices, total error rates usually vary only by a factor of two or three.11

How high are error rates in spreadsheet development? Since the 1980s, many laboratory studies have looked at errors made during spreadsheet development.12 Collectively, these studies have involved more than 1,000 subjects. Every study, without exception, found formula error rates (FERs) of at least 1 percent—including studies that used the Wall Task, which was specifically designed to be extremely simple and free from the need for domain knowledge.

Many people are skeptical of laboratory experiments, despite the fact that several spreadsheet development experiments used experienced spreadsheet developers. More convincing to most people are field studies that conducted comprehensive testing on operational spreadsheets used in real organizations. Figure 1 shows the results from field studies conducted since 1995.13 Although these studies typically used less than optimal error detection methods, they still illustrate the fact that errors are not simply artifacts of experimental conditions.

Figure 1

Note that these studies found errors in nearly every spreadsheet they examined. The studies in Figure 1 found errors in 94 percent of these spreadsheets, despite the fact that several studies reported only serious spreadsheet errors. More importantly, the studies that provided formula error rates collectively found errors in 5.2 percent of all formulas. As noted previously, this error rate is absolutely expected from research on human errors. Given that important spreadsheets tend to be very large, typically with thousands of root (not copied) formulas, this formula error rate means that most spreadsheets have dozens or even hundreds of errors.

The issues, then, are not whether a large spreadsheet contains errors but rather how many errors the spreadsheet contains and how serious these errors are. In the Coopers & Lybrand study in Figure 1, errors of at least 5 percent were found in 91 percent of all spreadsheets with more than 150 rows. As noted earlier, 5 percent is a common measure of error materiality. In addition, in the KPMG study shown in the figure, 91 percent of the spreadsheets had errors serious enough to affect decisions. In other words, errors are not only very common, but also frequently serious. It is difficult to understand how any company that uses spreadsheets in financial reporting can believe that it does not have a more than remote likelihood of having material errors.

In addition, with the first rounds of Sarbanes-Oxley reporting in, concerns over spreadsheet accuracy have proven to be not merely conjectural. Although the first round of Sarbanes-Oxley audits typically focused only on glaring weaknesses, RSM McGladrey reported that "numerous" companies had already cited deficiencies because of spreadsheets.14 Weaknesses included both operational control deficiencies and actual errors. In future rounds of Sarbanes- Oxley audits, now that the most glaring weaknesses have been addressed, firms and their auditors are likely to take a much closer look at their spreadsheets.

Spreadsheet Error Controls

Software developers have long faced the issue of errors. Although not all firms follow the full set of controls recommended in Control Objectives for Information and related Technology (COBIT) and other control frameworks, careful development practices in software development are common. In particular, programmers have long acknowledged the need for comprehensive testing, and they spend 30 percent to 40 percent of their total development time in testing.15

In contrast, as noted previously, although spreadsheet error rates are very similar to software development error rates, spreadsheet developers rarely engage in disciplined development practices. In particular, comprehensive testing or logic inspection after development is uncommon.16 Worse yet, when spreadsheet developers use the term "testing," they tend to mean such superficial approaches as "eyeballing" a few parts of the spreadsheet. As figure 2 shows, examination (including testing, logic inspection and other methods) that covers all formulas is comparatively rare, even in spreadsheets of material importance.

Figure 2

Many authors have proposed best practice guidelines for spreadsheet development.17 These guidelines generally are based on the traditional systems development life cycle or modifications such as iterative development or agile development.18

In large measure, these guidelines agree about what should be done:

  • There should be a firm understanding of requirements so that risks can be assessed.
  • There should be design before development.
  • Development should be modular.
  • There should be testing.
  • There should be documentation and user training.

Use should be controlled through such technical methods as protecting cell contents and by operational procedures.

However, there are several areas of disagreement among the guidelines. The most pronounced differences come in proposals for how to organize modules. Some guidelines advocate separating the module into three sections: input, processing and output. Unfortunately, formulas in the processing section must refer to distant cells; this may increase errors and will make the processing section more difficult to read (a problem for code inspection). Other guidelines argue that the spreadsheet should flow left to right, top to bottom, like a document.19 In this area and in other areas, research is needed to decide which recommendations are correct. Companies should not have to implement recommendations that have not been proven by research to be safe and effective.

The biggest weakness in these recommendations, however, is in their guidance on testing or code inspection. Most fundamentally, recommendations give very little guidance in this critical area that consumes 30 percent to 40 percent of the development process in software development—a cognitive area that has very similar error-making and error-correction rates.

Some guidelines recommend only the use of static testing programs, such as Spreadsheet Professional. In addition, spreadsheet development and testing are typically portrayed as separate phases rather than interwoven activities, as they are in software development. More serious, recommendations for testing (which involves inputting test cases and observing if the result is correct) seem to be extremely naive in terms of their recommendations for picking test cases and finding an oracle to let the tester know when the test is successful.20 It may be that code inspection methodologies will prove to be better at improving the accuracy of spreadsheets; certainly, spreadsheet developers have proven their ability to use code inspection to detect errors at about the same rate as professional programmers.21

Regarding ongoing use, one unanimous recommendation is to protect spreadsheets to prevent hardwiring—a user typing a number into a formula cell. Although a hardwired spreadsheet may be correct for that user, it will give the wrong answer for later users who have different data. Dent reported that one mining firm in Australia found that 30 percent of its spreadsheets contained hardwiring.22 Rabbit Photo, in turn, found that even when the company protected spreadsheets, users found ways to unprotect these spreadsheets to hardwire them deliberately.23 The psychology of persistent hardwiring behavior needs to be understood, and ways to enforce protection need to be found that are both easy enough to use to be acceptable and strong enough to guard against deliberate hardwiring. One possibility is to use a spreadsheet compiler to completely lock the formulas (and increase processing speed). This was the solution that Rabbit Photo chose.

Spreadsheet webs also need strong operational controls. Many processes in these webs are manual, and this creates opportunities for errors. In addition, spreadsheet users need to be trained in how to input data if they did not create the spreadsheet. A US $2.4 billion Fidelity reporting error in 1994 was due to a user entering the wrong sign in a single data value to indicate a loss.24

What is perhaps most obvious is that data input quality needs to be assured. Operational controls have not been studied to any significant extent for spreadsheets.

Fraud Risks and Controls

Sarbanes–Oxley was created in response to several major corporate financial reporting frauds. Unsupervised development and manual operations create a rich opportunity for frauds of many types. In a famous example, currency trader John Rusnak used spreadsheet manipulation to engineer a US $600 million fraud at Allfirst Bank, where he was employed.25 This fraud lasted several years and was uncovered only when a new manager insisted on the enforcement of long-ignored operational controls.

Several types of operational controls are most likely necessary to reduce fraud risk. In development, there could be separate development, testing and operations groups. At each stage of hand-offs between the groups, the group handing off the spreadsheet would have no more access to its code. This practice would slow development and require the creation of separate testing groups. Given the domain knowledge necessary to inspect or test a spreadsheet, it is not clear who should be in a dedicated testing group.

There also need to be operational controls to deter and detect fraud. Reconciliations based on results from multiple sources, the separation of duties, clear accountability, training and strong management oversight are among the operational controls that corporations need to create. Exceptions are inevitable, so there should be strongly controlled exceptionhandling procedures. Managers need to be knowledgeable in the spreadsheet’s content domain to manage developers and users effectively. Documentation, the traditional bane of developers, takes on strong importance in compliance regulation, which requires extremely strong and complete documentation of tools and processes. These are only a few examples of operational fraud controls.

Figure 3For strong fraud control, several companies (CIMCON, Wimmer Systems, etc.) offer vault security, which figure 3 illustrates. The vault server should have extremely strong security hardening and extremely strict access control methods that use strong authentication, strict but flexible permissions authorization, and strong auditing (including logging and tools to read logs efficiently and effectively). In addition, communication between the vault server and the client PC should be protected by IPsec or at least SSL/TLS transmission security.

On the client PC, the presence of up-to-date antimalware software and other protections should be assured. In fact, in a Windows environment, group policy objects (GPOs) should be used to strongly lock down the client PC. In addition, the vault security system should put coding in the spreadsheet to log all user activity for later auditing. Hashes should be used to ensure that hardwiring and other modifications of the spreadsheet’s labels or logic did not occur, and also that all numerical input values were really input as numbers (this is easier than putting validation types and ranges on each number cell).

The fact that Sarbanes–Oxley compliance often uses webs of manually operated spreadsheets creates many opportunities for misbehavior. Secure workflow technology is needed to ensure correct operations that require the use of multiple spreadsheets, and this workflow technology must have strong auditing and other security features.

Conclusion

In 2005, the results from the first full year of Sarbanes–Oxley reporting became available. In that first year, there was a broad feeling that auditing firms played softball a little, focusing on the worst problems in financial reporting, such as inaccurate tax-related data from foreign subsidiaries. Spreadsheets were largely ignored.26

In future years, however, regulators are likely to become more concerned about spreadsheets. If this occurs, there could be a sudden phase change in compliance expectations. Spreadsheets could quickly become the most important compliance concern of corporations, and controlling them could make the cost of compliance efforts today seem tiny.

What should firms do today to begin to address spreadsheets in light of Sarbanes–Oxley? First, they need to examine the many spreadsheets they currently use in financial reporting. How many are there, how big are they, what risks do they create, and what manual processes are involved in using them? Then firms need to understand the controls they currently impose on spreadsheets for financial reporting, and they need to determine whether these controls are enforced.

Second, they need to examine their spreadsheets. They should develop good inspection protocols, test at least 25 spreadsheets and record their findings. Most likely, they will decide that they need to develop much stronger controls in general. Then the real work begins.

Third, firms need to begin exploring vault security systems and other types of fraud control. Given the importance of fraud in the genesis of Sarbanes-Oxley, a strong security system will be needed if apparent weaknesses in spreadsheet fraud controls continue to be important under closer examination.

In many ways, developing controls for spreadsheets will involve the same types of controls long used in traditional IT—a well-managed systems development process, AAA (authentication, authorization and auditing) access controls and operational controls. Broadly speaking, new dogs need to be taught old tricks. However, whether traditional IT controls can be applied directly to spreadsheet development or must be modified is uncertain. For example, traditional testing may not work very well, and code inspection methods may have to be used instead. Just as drug companies cannot make unsubstantiated claims about the effectiveness and safety of their medicines, it is necessary to require any candidate control practices to be proven to be safe and effective through research before they are accepted.

Reference

Howard, Philip; "Managing Spreadsheets," Bloor Research white paper, UK, April 2005

Endnotes

1 Vorhies, J.B.; "The New Importance of Materiality," Journal of Accountancy (online), May 2005, www.aicpa.org/pubs/jofa/may2005/vorhies.htm

2 Durfee, Don; "Spreadsheet Hell?", CFOAsia.com, July/August 2004, www.cfoasia.com/archives/200409-07.htm

3 Ibid.

4 Panko, R.R.; "Sarbanes–Oxley: What About All the Spreadsheets?", Proceedings of EuSpRIG 2005, University of Greenwich, UK, July 2005, www.eusprig.org and http://panko.cba.hawaii.edu/SSR/Mypapers/ Panko%20EUSPRIG%202005.doc

5 Roger Debreceny, personal communication, 2005. The author validated this insight at a national meeting for accountants in discussions with a dozen financial accountants heavily involved in the management of Sarbanes–Oxley compliance.

6 Benjamin, Robert I.; "Information Technology in the 1990s: A Long Range Planning Scenario," MIS Quarterly, June 1982, p. 11-31

7 Op. cit., Panko

8 Ibid.

9 Reason, J.; Human Error, Cambridge University Press, UK, 1990

10 Panko, R.R.; Human Error web site, www.cba.hawaii.edu/panko/papers/ss/humanerr.htm, 2006

11 Ibid.

12 Panko, R.R.; Spreadsheet Research web site, www.cba.hawaii.edu/panko/ssr/, 2006

13 Ibid.

14 Kelly, Matt; "Spreadsheet Blues: Few Controls Yield Many Weaknesses," Compliance Week, 23 August, 2005, www.complianceweek.com

15 Jones, T.C.; Estimating Software Costs, McGraw-Hill, USA, 1998. Kimberland, K.; "Microsoft’s Pilot of TSP Yields Dramatic Results," news@sei, 2, Software Engineering Institute, USA, 2004, www.sei.cmu.edu/news-at-sei/

16 Op. cit., Panko, Spreadsheet Research web site

17 American Institute of Certified Public Accountants, Assisting Clients in Developing Policies and Procedures for Electronic Spreadsheet Applications, Consulting Services Practice AID 93-6, 1993. Panko, Raymond R.; End User Computing: Management, Applications and Technology, John Wiley & Sons, USA, 1988. PricewaterhouseCoopers, "The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley," July 2004, www.pwcglobal.com/extweb/service.nsf/8b9d788097dff3c9852565e00073c0ba/ cd287e403c0aeb7185256f08007f8caa/ $FILE/PwCwpSpreadsheet404Sarbox.pdf. Reed, Nick; Jonathan Bateson; Spreadsheet Modeling Best Practice, Institute of Chartered Accountants in England and Wales, UK, 1999. Richardson, Ronnie; Professional’s Guide to Robust Spreadsheets, Manning Publications, 1996. Thommes, M.C.; Proper Spreadsheet Design, Boyd & Fraser Publishing Company, USA, 1992

18 O’Bierne, Patrick; Agile Spreadsheet Development web site, 3 January 2005, www.sysmod.com/agile. Op. cit., Panko, 1988. Taylor, M.J.; E.P. Moynihan; A.T. Wood- Harper; "End-User Computing and Information Systems Methodologies," Information Systems Journal, vol. 8, no. 1, 1998, p. 85-96

19 Raffensperger, John F.; "The New Guidelines for Writing Spreadsheets," 15 July 2003, www.mang.canterbury.ac.nz/ people/jfraffen/spreadsheets/index.html

20 Panko, R.R.; "Recommended Practices for Spreadsheet Testing," Proceedings of EuSpRIG 2006, University of Greenwich, UK, 2006

21 Ibid.

22 Dent, A.; personal communication with the author via e-mail, 2 April 1995

23 Bryant, Gayle; "Rabbit Leaves Spreadsheet Behind It," Business Review Weekly, 16 November 1988, www.brw.com.au/content/161198/brw59.htm

24 Savitz, E.J.; "Magellan Loses Its Compass," Barron’s, vol. 84, no. 50, 12 December 1994

25 United States Department of Justice, United States of America v. John M. Rusnak, SMS/SD/USAO #2002R02005, 2002, www.usdoj.gov/dag/cftf/chargingdocs/allfirst.pdf

26 PricewaterhouseCoopers, "The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley," July 2004, www.pwcglobal.com/extweb/service.nsf/ 8b9d788097dff3c9852565e00073c0ba/ cd287e403c0aeb7185256f08007f8caa/$FILE/PwCwpSpreadsheet404Sarbox.pdf

Raymond R. Panko, Ph.D.
is a professor of IT management in the College of Business Administration at the University of Hawaii (USA). He teaches IT security and end-user computing courses. He also wrote the first textbook on end-user computing, which Wiley published in 1988. Prentice-Hall published his security textbook, Corporate Computer and Network Security, in 2004. He is currently writing the second edition of that book. He has been conducting research on spreadsheet errors and fraud since 1993 and maintains web sites on spreadsheet errors (http://panko.cba.hawaii.edu/ssr) and human error (http://panko.cba.hawaii.edu/HumanErr). He is a member of ISACA, the Information Systems Security Association (ISSA), the Association for Computing Machinery (ACM) and the Association for Information Systems (AIS).


Information Systems Control Journal, formerly the IS Audit & Control Journal, is published by the ISACA. Membership in the association, a voluntary organization of persons interested in information systems (IS) auditing, control and security, entitles one to receive an annual subscription to the Information Systems Control Journal.

Opinions expressed in the Information Systems Control Journal represent the views of the authors and advertisers. They may differ from policies and official statements of the Information Systems Audit and Control Association and/or the IT Governance Institute® and their committees, and from opinions endorsed by authors' employers, or the editors of this Journal. Information Systems Control Journal does not attest to the originality of authors' content.

Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, Mass. 01970, to photocopy articles owned by the Information Systems Audit and Control Association Inc., for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.