Advertisement

Errors in spreadsheets can lead to Excel hell

Heading: 

Spreadsheet errors can be the cause of loss of lucrative contracts, fraud-related charges being brought against company executives, and massive losses in financial results, even resulting in a free fall in share price for those organisations that are listed on the stock exchange.

Staggeringly, over 90% of spreadsheets contain errors of some kind, according to research by Raymond Panko, a professor at the University of Hawaii.

David Finch, head of internal audit at Superdrug in the UK, says: "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, standardisation 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."

It will be difficult for companies that have financial data in a series of spreadsheets to comply with regulations such as the Sarbanes-Oxley Act, which requires companies to maintain a consistent audit trail, as it is difficult to maintain one version of the truth in spreadsheet-driven financials.

Adrian Miric, MD of Miricle Solutions, which supplies Spreadsheet Professional, an Excel auditing tool, mentions a local example of spreadsheet error with the country's first democratic elections. The final results were unreliable, due to bugs in spreadsheet macros and typographical errors made when moving data from fax to computer.

Miric says errors in spreadsheets are a nightmare when it comes to tenders, public-private partnerships (PPPs), and engineering contracts. "The reason is, if you make an error in a tender and it is to your company's advantage, you probably won't win the tender as the price will be too high. Alternatively, if the price is too low, the chance is you will win the tender, but will have to deliver the product and service at a fixed lower price than you anticipated."

Canadian company TransAlta took a $24 million charge last year after a bidding mistake caused by a cut-and-paste error in an Excel spreadsheet, according to a report in Computerworld. This is one of the most common sources of errors in spreadsheets, says Miric. This, along with the use of absolute and relative referencing, was possibly the reason behind TransAlta disclosing a loss of $24 million.

TransAlta's computer spreadsheet contained mismatched bids for transmission congestion contracts (TCCs). As a result, it bought more contracts at higher prices than intended. The actual value of TCCs was determined daily, based on changing supply, load and transmission conditions. TransAlta worked throughout the month to mitigate the financial impact from the error.

The National Australia Bank (NAB) wrote down the value of its US mortgage business HomeSide Lending by a massive AUS$3 billion, according to the Australian Broadcasting Corporation. It says: "A big chunk of the loss can be attributed to a basic accounting error." The "news triggered a free fall in the NAB's share price that knocked more than $6,5 billion off the bank's market value". In the US, a bidder lost a government tender to supply Internet connectivity to all of the state of Tennessee's schools.

The bid appeared cheaper until costs for the duration of the contract were computed. The bidder had made an error on a spreadsheet and the bid was actually higher than that of the competitor. "There are increasing instances such as these," says Miric. Because of this, conferences are now being dedicated to spreadsheet risk, with the next one being held in July in Austria.

Miric says projects such as the Gautrain and the 2010 soccer World Cup could be at risk, as the likelihood is high that a significant amount of money will be spent based on feasibility studies done in spreadsheets, which could contain errors. "Resource-intensive industries in South Africa, which are particularly capex-heavy, are also at risk, due to the likelihood of spreadsheet errors. "Excel is used in many areas where it shouldn't be, as it is too easy to make mistakes."

Miric says in some businesses, Excel is more important than the company's accounting system, as it is used as the basis for business decisions on whether or not to go into a venture, while the accounting system tells executives whether or not they made money from a venture. "People don't realise how important non-process software is," he says.

"The extent of company spend on Excel is on licences and minor training courses. They should rather spend more time acknowledging the risks associated with spreadsheet errors," he says.

According to Panko, "One reason why individual spreadsheet developers do not use rigorous development disciplines may be the fact that few organisations have policies on end user development in general, much less spreadsheet development.
"Galletta and Hufnagel surveyed 107 MIS executives using a mail questionnaire that focused broadly on policies for controlling end-user development. For restrictions on end-user development, 23% said they had rules and 58% said they had guidelines. Yet even for corporations with rules and guidelines, the respondents said that there was full compliance only 27% of the time.

"When asked about requirements for post-development audits, only 15% said they had rules, and another 34% said they had guidelines. The respondents said that where they had rules and guidelines for post-development audits, furthermore, there was full compliance only 10% of the time, and guidance was completely ignored 41% of the time."

Panko relates many similar horror stories in a research paper entitled "What we know about spreadsheet errors", that can be found at http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm
He concludes: "Overall, formal spreadsheet policies seem to be fairly rare, and their enforcement seems to be casual. While more respondents seem to believe that informal and typically unwritten guidelines exist, the effectiveness of such guidelines can only be left to conjecture."

The solution to fixing spreadsheet errors, says Miric, is to use a spreadsheet-auditing tool. "Your business could depend on it," he says.

More information can be found at www.auditexcel.co.za or from Adrian Miric, at Miricle Solutions on (011) 807-6718 or [email protected]

Details

Advertisement

Comments


Advertisement