"The use of spreadsheets in business is a little like Christmas for children. They are too excited to get on with the game to read or think about the `rules` which are generally boring.... There is often little control over end user developments in spreadsheets with little if any standardization in development processes by users in different departments, little risk analysis and a general assumption that models, on which important decisions are made, are accurate."- David Finch, Head of Internal Audit- Superdrug plc
Spreadsheets must rank as one of the most prevalent tools in business today. Finance, engineering, service provision! You name it and there is generally some form of spreadsheet use, often in a decision making or reporting context.
Yet research has shown that a significant percentage of spreadsheets contain errors. The table below indicates that the quantum of spreadsheets with errors can be alarming.
Researcher and criteria | # Spreadsheets Audited | % with Errors |
At least a 5% error | ||
Coopers & Lybrand | 23 | 91% |
Major Errors exist | ||
KPMG | 22 | 91% |
Serious Errors exist | ||
Davies & Ikin | 19 | 21% |
Errors requiring extra tax payments | ||
Butler | 273 | 11% |
Butler 2000 | 7 | 86% |
Other studies | ||
Cragg & King | 20 | 25% |
Hicks | 1 | 100% |
Lukasic | 2 | 100% |
This research, together with other information available on our web site (www.AuditExcel.co.za), indicates that the probability of errors in any given spreadsheet is high. This supports the `gut feel` of frequent spreadsheet users that errors are easy to make and difficult to find. However, the probability of errors only becomes significant if the potential impact will affect an organization in a meaningful way.
Discovering the impact of errors in spreadsheets is difficult, as most organisations do not openly disclose spreadsheet errors. At present the majority of published `war stories` come from North America. For example in June 2003, Trans Alta, a listed Canadian company disclosed a loss of $24 million as "Trans Alta`s computer spreadsheet contained mismatched bids for transmission congestion contracts".
What is clear is that the potential impact of spreadsheet errors can range from a direct loss of money, to regulatory problems (CGT valuation spreadsheets beware!), to a loss of reputation. Much like Russian roulette you never know if or when it is going to hit you.
Given the research and war stories, it is not surprising that the majority of South African organizations approached are aware of the risks posed by spreadsheets. What is not as well known are the methods to address these risks. It seems that reliance is placed on the developer to get it right. In extreme cases the external auditors are called in to review the model at the very end of the decision lifecycle. Often these measures result in a manual review, with little if any use of the automated tools available.
Some of these automated tools already sit on your computers. There has been a steady increase in the review and auditing functionality of MS Excel. `Must know` functions for spreadsheet users include the Auditing Toolbar and the Go To Special commands. XP users have additional functions built in. For users of MS Excel, our web site contains some practical advice on inbuilt Excel tools.
However, the inbuilt tools do not facilitate a formal process to address the risks. An independently developed tool like Spreadsheet Professional is required. Features of this tool include visual methods of identifying and documenting areas of high risk in a spreadsheet. This tool aids in efficiently identifying errors before a decision is made.
From a potential impact perspective it is highly recommended that organizations understand what is driving their business and if spreadsheets play any significant part in the success of the organization. Is there a formal method to classify the `importance` of the models? Who in the organisation is capable of addressing these risks? And in some cases you should be asking yourself why it is that such important tasks are being performed by `scratchpad applications` developed in a spreadsheet package. Make no mistake, sometimes the problem is not an error in the spreadsheet but why a spreadsheet was used at all!
We believe that as the complexity of decision making increases and speed becomes a bigger issue, spreadsheet packages will become even more prevalent in the business world. We expect that in the near future a number of South African businesses will get a rude awakening to the risks posed by uncontrolled spreadsheet use.
The effects of human error in spreadsheets will never be eliminated. However the tools and processes do exist to reduce these risks.
Share
Editorial contacts