(c) Hong Kong Institute of Certified Public Accountants. Contact HKICPA for permission to reproduce this article., Business Management, Business Skills, Information Technology

HOW BUSINESSES CAN AVOID SPREADSHEET PITFALLS

By George W. Russell

Several recent high-profile mistakes have highlighted the dangers of putting too much faith in spreadsheets. Software experts tell George W. Russell how CPAs can avoid the pitfalls that can create a major misstatement out of a simple error.

When it comes to mathemat­ics, it has been said that the precision of an answer is only as good as the precision of the least precise number in the calculation.

Recently, a number of high-profile individuals and institutions appear to have struggled with this concept, leading to some embarrassing public errors.

The issue of “spreadsheet risk” came to the fore in April, when a widely hailed study on the effective­ness of national austerity programmes, Growth in a Time of Debt, by Harvard University economics pro­fessors Kenneth Rogoff and Carmen Reinhart, was found to be based in part on a flawed Microsoft Excel spreadsheet.

More recently, The Economist, the London busi­ness weekly, had to correct a misstatement about Co­lombia’s vulnerability to a freeze in capital inflows, which was due to another spreadsheet error. The ini­tial report in September sparked angry rebukes from the South American country’s government.

While these errors did not result in financial loss, bad spreadsheets can be massively expensive. In May, a report by JPMorgan Chase suggested the London Whale affair – in which the bank lost at least US$5 bil­lion trading credit default swaps – might have been exacerbated by an employee copying incorrect figures into Excel, distorting the firm’s risk models.

Experts say these types of errors can be caused by mistaken assumptions. “A spreadsheet is meant to be a flexible analytical tool for analysts to study scenarios and trends,” says Patrick Rozario, director and head of risk advisory services at BDO and chair of the Hong Kong Institute of CPAs’ Best Corporate Governance Disclosure Awards organizing commit­tee. “It is not meant to be a tool for controlling data needed to make key decisions.”

There is little hard data about the extent of spreadsheet risk. Ray Panko, a professor of information technology management at the University of Hawaii, who has researched spreadsheet governance since the 1980s, has estimated that about 90 percent of spreadsheets contain at least one mistake. A recent study by KPMG in the United States put the figure at between 70 and 80 percent.

A survey undertaken last year for the Institute of Chartered Accountants in England and Wales indicated that 90 percent of its members claimed their firms had no spreadsheet risk policies. “People are now starting to understand the size and scale of the problem,” Scott Bolderson, IT consulting director at Protiviti, which conducted the survey, said at the time of its release.

Spreading the word

The spreadsheet has been around since the early 1960s, but commercial programs became more widely available from 1980 with the launch of Super­Calc for personal computers with the CP/M operating systems. Accounting firms quickly adopted the time-saving programs.

It was the introduction in the U.S. of the popular programs Lotus 1-2-3 for MS-DOS in 1983 and Mi­crosoft Excel for the Macintosh in 1985 that really changed the global face of accounting.

The 1990s brought a rash of high-profile spreadsheet error cases, including a 1994 incident at Fidelity Investments in which an accountant treated a US$1.3 billion capital loss by its flagship Magel­lan Fund as a gain of the same amount. From 1993, flawed spreadsheet archiving degraded the risk model of the Jamaican local banking sector, leading to its ultimate collapse in 1997.

In 1996, the Australian government reported that the federal budget for that year contained 170 spreadsheet errors, including one of A$1.2 billion, while in 1999, spreadsheet error prompted the International Energy Agency to overstate inventory by 300 million barrels of oil, affecting global supplies.

Accountants, say IT experts, should be aware of the potential pitfalls associated with data entry. One of the issues often studied is dependence on spread­sheets without understanding their underlying assumptions, according to Craig Rode, director of partner management at Autodesk, the engineering and design software company based in Seattle.

“There is a type of software that simulates the behaviour of mechanical systems through mathematics called finite element analysis,” explains Rode, who is a long-time student of spreadsheet risk, drawing a parallel. “Engineers use this all the time.

“In order to get the software to make predictions, you have to input boundary conditions,” Rode adds. “These [boundary conditions] have to be numeric values [but software] doesn’t have the ability to detect bogus answers. If I enter 10kg when I meant to enter 100kg, nothing in the software says, ‘Wait, this doesn’t make any sense!’ It just gives an answer.”

Myriad mistakes

According to Myles Arnott, director of Clar­ity Consultancy Services in Bournemouth, England, which trains accountants in the use of Excel and other tools, spreadsheet errors tend to fall into three main categories: structural errors, such as improperly written formulas; data errors, such as incorrect inputs of figures; and administrative errors, such as lack of password protection or improper sharing. “Another issue is users not understanding that hiding rows, columns and sheets does not protect the information,” he adds.

Other potential problems arise from incompatible software products forced to integrate. “These are the so-called interface errors from the import or export of data with other systems,” notes Christos Tsolakis, a risk assurance partner with Pricewater­houseCoopers in Nicosia, Cyprus. Tsolakis and his team carried out a study in 2009 that found errors in 95 percent of the Excel spreadsheets examined.

Alex Bell, a forensic accountant and executive director of the KordaMentha ac­counting firm in Sydney, says that in his experience the most common spreadsheet errors are simple ones. “Users miss cells in a range so the total doesn’t add correctly or they copy individual values instead of the formula and so the cells fail to update with subsequent changes.”

Other issues include entering incorrect or invalid formulas and failing to update external links. Richard Blaustein, president of Analytic Solutions in Philadelphia, a consultancy that addresses spreadsheet risk for accountants, points out another common error: creating inconsistent formulas across adjacent columns. “This was the root cause of the Reinhart and Rogoff blunder,” he notes, referring to the Harvard professors.

Meanwhile, more sophisticated programs have exacerbated spreadsheet problems. “More data translate into more analysis and more spreadsheet formulas – and formulas of greater complexity – which ultimately often leads to higher occurrence of errors,” says Blaustein.

New technologies adopted by accounting firms have raised the potential risk too. “Data access has become more pervasive and voluminous through improved networking, including [through] cloud [computing],” says Ashley Clarke, chief operating officer of FlexSystems, the Hong Kong-based finance and accounting software maker.

Convenient communications also pres­ents risks, Clarke adds, because there are more potential sources for input data. “Having easier access to regional and global data from more applications increases volumes and increases risk.”

Rode at Autodesk says that many spread­sheet users simply accept that a spreadsheet is correct because it has been produced on professional software. “Spreadsheet creation is really a sort of low-level program­ming,” he points out. “In a lot of cases, the fact that the answer’s been calculated… is taken as evidence that it’s factual.”

Practical solutions

Blaustein says technology now exists to greatly reduce spreadsheet error rate. His company, for example, markets The Audi­nator, which automatically audits a selected Excel spreadsheet using proprietary algo­rithms. “However, the issue is that so few peo­ple are either aware of the technology or are willing to add the extra minimal effort to the process of spreadsheet modelling,” he says.

Rozario at BDO says there are practical ways to reduce the risk of spreadsheet errors and manipulation, such as the use of pass­words for accessing spreadsheets. “Cells – in particular those with formulas – are pro­tected from changes.”

Despite the plethora of potential techni­cal minefields, Bell, says the best safeguards are non-technical. “Policies within the orga­nization should promote transparency and consistency when undertaking spreadsheet or financial modelling analysis,” he says.

Spreadsheet risk companies suggest that such policies include all changes and revi­sions to key spreadsheets being logged by the person making the change and periodi­cally reviewed by management.

In many organizations, say experts, a change in mindset among spreadsheet users might be needed. “Users need to understand the risk introduced to a process by the use of spreadsheets and follow a best practice approach to mitigate this risk,” says Arnott at Clarity. “Such cultural changes need to be driven by senior management and supported by policies, training and audit.”

Blaustein recommends educating staff about best practices in spreadsheet model­ling. “From structural design, to implemen­tation and authorship of calculation formu­las, to the intuitive organizational layout of data input sections, the better versed a spreadsheet modeller is in these areas, the lower the risk of spreadsheet error.”

Rozario notes that spreadsheet error can, in some cases, be only one of several factors that lead to disaster. “The JPMorgan Chase case, which involved huge trading losses, was due to deficient accounting con­trols that included spreadsheet miscalcula­tions,” he says.

One issue that helped cause large valua­tion errors, and use of subjective valuation techniques, he adds, was the absence of a closed computer system – that is, he says, [a software program] “not vulnerable to unauthorized changes, that employs an ap­propriate valuation method and that has been tested thoroughly.” A spreadsheet, Rozario adds, is not the right tool for that purpose.

Whether through misuse or lack of edu­cation, Rode at Autodesk says most spread­sheet errors will remain an issue of human input rather than a problem caused by tech­nology. “I don’t consider it a technology is­sue. I think it’s a social issue,” he says. “We need to treat tools as tools, not as oracles.”

This article was originally published in the November 2013 issue of A Plus.

[stextbox id=”info” caption=”BEST PRACTICE CAN HELP AVOID ERRORS”]

One consequence of the threat of spreadsheet risk is the emergence of specialists devoted to mitigating the problem. Many offer courses in spreadsheet risk management and best practice modelling and provide training programmes to give users the skills to build better spreadsheets.

Clarity Consultancy Services, based in Bournemouth, England, specializes in helping companies to manage spreadsheet risk. “We have a four-stage approach to spreadsheet risk management: identify, evaluate, resolve and control,” says director Myles Arnott.

Some consultancies say they can adapt their training to suit a particular sector or industry. “We custom tailor the training seminars and interactive coursework to most directly align with the industry and functional areas of the participants,” says Richard Blaustein, president of Analytic Solutions in Philadelphia.

Consultants offer a few basic rules to help ensure spreadsheet integrity:

  • Education: Make sure key employees are well-versed in Excel or other com­mercial spreadsheet software.
  • Upgrades: Ensure each user has the latest version of the spreadsheet and is aware of any improvements or change.
  • Templates: Ensure users have guidance for effective template design, instruc­tions are followed and templates are populated with applicable data.
  • Version control: Users can often easily edit or revise formulas, data and values in a spreadsheet. Ensure master versions of each key spreadsheet are identified as such.
  • Backup: An exact copy of each master key spreadsheet should be saved to protect against loss. A third party should be able to load the spreadsheet and independently review all processes and calculations.
  • Validation: Oversight is required every time a critical computation or formula is changed in the spreadsheet. Any changed functions should be tested for accuracy.
  • Change management: Finding changes in complex spreadsheets can be difficult. Comparison software can be used to view the original, modified and redlined document simultaneously, and accept or reject the changes to create a new ver­sion of the spreadsheet.
  • Workbook auditing: Formulas and logic used in critical spreadsheets should be audited periodically.

Many large accounting firms now offer spreadsheet risk services, including:

  • Determination of whether calculations are internally consistent and mathemati­cally correct.
  • Evaluation of existing controls against a checklist.
  • Identification of the needed controls – change control, version control, access control, logic inspection, security – to mitigate the risks.
  • Comparison reviews of changes to updated versions of models.
  • Development of an action plan for each control gap identified.

Individual CPAs wishing to hone their Microsoft Excel skills can utilize a number of the software maker’s own learning tools, such as taking the Office specialist exam.

One popular free tool is the ExcelisFun YouTube channel created by Mike Girvin, an instructor at Highline Community College in Seattle. Another resource from the United States is MrExcel.com.

[/stextbox]