Security and Sarbanes Oxley: What About the Spreadsheets?

Security and Sarbanes Oxley: What about the Spreadsheets?

Raymond R. Panko

Submitted to the ISSA Journal

January, 2006

January 2006

To be published in the ISSA Journal

Security and Sarbanes Oxley: What about the Spreadsheets?

RaymondR.Panko
University of Hawai`i

Do Spreadsheets Matter?

Sarbanes–Oxley came as a shock to corporations. It forced them to take a hard look at their corporate financial reporting systems. What they saw were spreadsheets—lots of them. Many firms had financial reporting systems that were manually operated webs of 200 or more spreadsheets. Even firms that use Hyperion or other financial reporting programs use many spreadsheets, and these spreadsheets are often used to make the most critical and risky decisions, such as end-of-period adjustments.[1] Surveys conducted by consulting firms in light of Sarbanes-Oxley uniformly found that a large majority of firms were using spreadsheets in their financial reporting.[2]

Spreadsheets raise two concerns for Sarbanes–Oxley: error and security. Both can create material control deficiencies. We will start with errors because research on spreadsheet errors has been conducted since the 1980s and is mature. Today there is overwhelming evidence that spreadsheets contain errors in about 1% to 5% of all formula cells, so that nearly all large spreadsheets have errors.[3] The error rate can be reduced greatly by aggressive (and expensive) testing, but such testing is rare.[4] At best, most firms “eyeball” their spreadsheets to look for odd patterns or to conduct spot checks on formulas.

Perhaps these errors are just small ones? Studies that have looked at the size of spreadsheet errors give little justification for such hope. Most directly, a 1997 study by the spreadsheet auditing department at Coopers & Lybrand in London found that 91% of the financial spreadsheets it audited that had more than 150 rows contained an error of at least 5%.[5] Fiver percent is a common benchmark for materiality.[6] Another spreadsheet consultancy in London, KPMG, found that 91% of the decision-making spreadsheets they audited contained an error large enough to affect the decision.[7] This is certainly bad news for Sarbanes–Oxley compliance.

In fact, in 2005, the first full year of SOX compliance, Macdonald[8] noted that several firms already have had to report material control deficiencies because of spreadsheets.RSM McGladrey, in turn, reported that “numerous” companies had already revealed material control deficiencies because of spreadsheets.[9] Yet in this first full year of the requirements, few companies looked at their spreadsheets closely, and auditing companies were similarly relaxed about spreadsheets.

Although error can prevent Sarbanes–Oxley compliance, the law was created primarily to prevent fraud. This is not an idle concern. At Allfirst Financial, John Rusnak was a currency trader.[10] By manipulating his spreadsheet, he was able to hide his trading losses for several years. When the company finally discovered his fraud, the damage had reached $691 million. In turn, H.M. Customs and Excise in the U.K. audits many spreadsheets that are submitted along with tax filings. They found several cases of deliberate fraud. In some cases, for instance, the perpetrator took a number in a column to be totaled, made it a label, and then right-justified it. This effectively removed the entry from the total.

Certainly, spreadsheet programs are the perfect Petrie dishes for fraud. Their development is rarely controlled, detailed examination is rare, and most of their actions are handled manually. Everything depends on trust in the developer and user, a situation that is the antithesis of fraud control thinking.

Perhaps CobiT will provide some guidance? In its third version, which was published in 2000, CobiT had exactly two paragraphs on end-user development. These paragraphs merely said to be careful. They did not even mention spreadsheets. Thefourth version of CobiT, published in late 2005, even omitted these two cautionary paragraphs. All references to end users refer to people who use central IT systems. So much for CobiT guidance.

Vault Systems

Although CobiT and IT security in general have largely ignored spreadsheets, compliance concerns make it impossible for corporations to continue their neglect. Fortunately, Sarbanes–Oxley is not the first law to affect large systems of spreadsheets. In 1997, the U.S. Food and Drug Administration released rules for the use of information technology in pharmaceuticals testing. Fraud was a special concern of the rules, which became known as 21 CFR 11. When pharmaceuticals companies looked at their testing and documentation approaches, they too found an enormous number of spreadsheets.

To support pharmaceuticals companies, several vendors built what we will call vault systems. A typical vault system is shown as Figure 1. The figure shows that the heart of the system is a hardened and protected vault server that stores the spreadsheets securely. The vault server maintains version control and secure backup (to reduce sabotage risks). It encrypts all files on the server to prevent intellectual property theft (many spreadsheets are highly confidential). It also requires strong authentication and authorization before someone can check out a spreadsheet to work on it.

Figure 1: Vault System

When spreadsheets are checked out for use, strong cryptographic controls are applied to the transmission to the client PC. Ideally, the PC should be checked for vulnerabilities before the file would be sent to it. In addition, there should be strong digital rights management (DRM) controls to prevent copying once the spreadsheet is on the client PC.

The spreadsheet itself is self-auditing. Every keystroke is recorded to a secure log file built into the spreadsheet. This log file is kept with the spreadsheet when it is checked back in. Of course, log files tend to be write-only memories unless log reading tools are available to dig through the log files. These log-reading tools should automatically scan for certain actions that tend to indicate fraud. For instance, if the user changes a formula (or attempts to change a formula), the log reading tool should bring this to the attention of the administrator as soon as the spreadsheet is checked back in. It is also possible to use computed canary values, such as a message digest or digital signature based on formula cells. This would be a quick way to check if any formulas have been changed.

In conjunction with vault systems, it is possible to use spreadsheet compilers, which absolutely prevent users from changing formulas. Of course, it would be important to examine files at check-in to be sure that the spreadsheet has not been copied, changed, and then recompiled. A message digest or digital signature would ensure this.

Controlling Development?

Normal spreadsheet development is completely uncontrolled, allowing the developer to use the spreadsheet to conduct or hide fraud. Rusnak, in fact, used his currency trading spreadsheet to do both.

One extreme reaction to this situation would be to use the traditional three-server approach used for secure software development. Developers would create spreadsheets on one server. These spreadsheets would then be moved to a testing server for examination by a separate testing group. Developers would have no access to the testing server. After testing, the spreadsheets would be moved to a production server, which should be a vault server. Neither developers nor testers would have access permissions on the production server.

Although this approach would give good control, it is rather draconian, and few organizations are likely to accept it willingly. Consequently, the three-server approach likely to be followed only if PCAOB creates requirements for very strong spreadsheet controls. Even then, the tendency for spreadsheets to change over time as use creates new insights would make change control after development a nightmare.

If the full three-server approach is not followed, testing alone can provide substantial benefits. This testing must go beyond auditing, which only focuses on “the most dangerous” formulas. It must be comprehensive cell-by-cell testing with excellent methodology. Certainly, comprehensive testing is necessary if spreadsheet errors are to be reduced by 80% to 90%.[11]

However, traditional testing is not enough. Companies will have to do paranoid testing, which focuses not on correctness alone but also upon potential for abuse. Chadwick and Sue[12]have argued that it is not enough to train people to build spreadsheets correctly. They must also be trained how to avoid building a spreadsheet incorrectly.

Operational Controls

As a consequence of the manual nature of spreadsheet operation, companies will also have to use operational controls. Paranoid testing, of course, is an operational control. It will be important to develop many more types of operational controls. In many cases, operational controls designed to reduce spreadsheet errors can, with modification, be used for fraud control. In other cases, entirely new controls are needed.

Most importantly, it is important to understand fraudulent thinking and to understand what tends to work in fraud control. Regarding the former, it is common consider the fraud triangle,[13] which Figure 2 illustrates. This framework expresses three things which must be considered to understand fraudulent behavior.

Figure 2: The Fraud Triangle

The first is incentives, such as financial theft or intellectual property theft. However, it is also likely to be covering up poor performance, such as Rusnak’s stock losses.

The second vertex on the triangle is opportunity. It is important to think of all of the ways in which a spreadsheet can be written or changed to support the fraud. It is then important to think of procedural controls that would reduce these opportunities.

The last vertex in the fraud triangle is the most subtle. It is the rationalization the attacker would use to justify the fraud to himself or herself. For instance, Rusnak probably rationalized his hiding of losses as a temporary expedient until he could “catch up.”

It is also important to learn how to detect frauds by looking at how fraudsare generally detected in organizations. One major study of internal financial fraud,[14] discovered that most frauds were detected by tips. This included 40% of all cases and 51% of the cases that involved owner or executive. In declining order of importance, detection also came from internal audits, accidents, internal controls, external audits, and the police. This is why the Sarbanes-Oxley requirement to maintain an independent auditing unit and to accept anonymous tips is such a good idea and why spreadsheets should be explicitly considered by such auditing units. Although the findings of this study suggest that internal controls are not highly effective (especially for owner or executive fraud), such controls may be extremely important as deterrents to fraud.

One obvious control is external reconciliations. Rusnak’s fraud should have been caught very early because independent confirmations of his fictitious trades were required by company policy. Unfortunately, Rusnak talked the unit that was supposed to do the confirmations out of doing them. He reportedly convinced them that because his fictitious offsetting trades supposedly expired before end of the day, there would be no need to confirm and record them. He may also have used his position in the corporation to bully the clerical confirmation staff into acquiescence.

Independence of data is also important. Rusnak was supposed to get external data on currency conversion rates. However, he simply used his own falsified conversion rates, typing them into the program. This is an obvious example of a situation where paranoid testing of the spreadsheet program could have revealed a lack of data independence.

The fact that many spreadsheet operations are manual suggests that separation of duties may be crucial. If the spreadsheet developer creates a fraudulent change in a spreadsheet, he or she may not be able to exploit if someone else handles manual operations. Similarly, mandatory vacations may make the maintenance of a manual fraud operation infeasible, and the rotation of duties can reduce prospects for collusion.

Documentation

One shock for every corporation is the degree to which everything must be documented, including software. Users have been loath to document the spreadsheets they develop to an even greater degree than programmers. Compliance regulations will not tolerate this. Simply documenting a firm’s existing spreadsheets can be a monumental task.

Conclusion

This paper has merely scratched the surface of security controls for spreadsheets. It has only looked at a single security threat, fraud.However, there could be many other motives for security attacks, including sabotage and espionage. Even fraud has multiple goals, including both financial theft and the hiding of poor performance. Certainly, this paper’s description of spreadsheet security controls is far from complete.

Of course, corporations have to ask whether they really need to spend large amounts of money on spreadsheet controls for either errors or security. Although spreadsheetshave been an issue in some Sarbanes–Oxley reports of material control deficiencies, spreadsheets certainly have not been the main problem for compliance during the first year of corporate and auditor reports.

However, there is a widespread feeling that auditing forms played softball during the first round of Sarbanes-Oxley findings, focusing only on the most severe deficiencies. This is not likely to continue. More fundamentally, given the large volume of data on spreadsheet error rates and materiality, it seems unlikely that the SEC or the PCAOB will continue to ignore spreadsheet vulnerabilities. Companies need to begin thinking about how they will control their spreadsheets if the SEC or PCAOB begin taking spreadsheets seriously.

Biography

Dr. Raymond R. Panko is a professor of IT management in the College of Business Administration of the University of Hawai`i. He teaches courses in IT security, and Prentice-Hall publishes his textbook, Corporate Computer and Network Security. He has been conducting research on spreadsheet errors and other spreadsheet matters since 1993. For more information on spreadsheet errors, visit his Spreadsheet Research website, http:/panko.cba.hawaii.edu/ssr/.

Page 1/11

[1] Debreceny, Roger (2005). Personal communication with the author. 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.

[2] Panko, R. R. (2005, July). “Sarbanes–Oxley: What About All the Spreadsheets?” Proceedings of EuSpRIG 2005, University of Greenwich, London, UK. (

[3] Panko, R. R. (2006). Spreadsheet Research (SSR) Website. ( Honolulu, HI: University of Hawai`i.

[4] Ibid.

[5] Freeman, David. How to Make Spreadsheets Error-Proof, from Journal of Accountancy, vol. 181 (5), pp. 75--77, May 1996.

[6]Vorhies, J. B. (2005, May). “The New Importance of Materiality.” Journal of Accountancy (online).

[7] KPMG Management Consulting (1998, July 30). “Supporting the Decision Maker—A Guide to the Value of Business Modeling,” press release.

[8] MacDonalds, Elizabeth. (2005, December 15). “Profit Pitfalls,” Forbes.com.

[9] Kelly, Matt (2005, August 23). “Spreadsheet Blues: Few Controls Yield Many Weaknesses,” Compliance Week.

[10]United States Department of Justice (2002). “United States of America v. John M. Rusnak.” SMS/SD/USAO #2002R02005.

[11] Panko, R. R. (2006). Spreadsheet Research (SSR) Website. ( Honolulu, HI: University of Hawai`i.

[12] Chadwick, D. and Sue, R. (2001, July). “Teaching Spreadsheet Development Using Peer Audit and Self-Audit Methods for Reducing Errors,” Proceedings of the 2001 EuSpRIG Conference.

[13] Accounting Standards Board (2002). Statement on Auditing Standards (SAS) 99: Consideration of Fraud in a Financial Statement Audit. American Institute of Certified Public Accountants, 1211 Avenue of the Americas, New York, NY10036-8775.

[14] Association of Certified Fraud Examiners. (2004). 2004 Report to the Nation on Occupational Fraud and Abuse, ACFE World Headquarters, World Headquarters, the Gregor Building, 716 West Avenue, Austin, TX, 78701-2737.