Do you really understand the level of spreadsheet risk in your business?

Spreadsheets are amazing tools that enable all sorts of analysis to be undertaken that would otherwise be difficult and time consuming to construct; what has always exited me is being able to build financial models and run scenarios (often complex ones) to support commercial decision making and business planning.

Although I am stating the obvious, It is not just me that has recognised the strength and potential of spreadsheets; spreadsheets are everywhere and in every business (pretty much). What has become apparent, however, is that many business place undue trust in the integrity of the analysis that spreadsheets produce.

As spreadsheet users have become more proficient (most accountants, for instance, dream about offset, index, match and indirect Excel formulae), their spreadsheets have become more complex. What they lack, however, is control. Essentially, you can be a free spirit in a spreadsheet. They were never designed to be enterprise-level applications, but the growing use of complex and user-defined functions, lengthy macros and links to other spreadsheets and systems has led to the development of highly complicated applications. In contrast to most other applications of this nature which support critical decision making, spreadsheets rarely are designed and developed by expert users or with controls in mind (even though many of the builders are accountants who were taught about controls early on in their training contracts).

Without appropriate controls in place, it can be dangerous to base critical business decisions upon spreadsheets. However, most businesses make critical decisions based on spreadsheet analysis, yet research shows that >90% of spreadsheets contain errors (Panko: What We Know About Spreadsheet Errors, May 2008).

 

ERROR RATES

Although spreadsheet programs are used primarily for small "scratchpad" applications, they are also used to develop many large applications including financial models. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists. These error rates, although troubling, are in line with those in programming and other human cognitive domains.

In programming, strict development disciplines exist to eliminate most errors. However, surveys of spreadsheet developers indicate that spreadsheet creation, in contrast, is informal, and few organisations have comprehensive policies for spreadsheet development (Panko: What We Know About Spreadsheet Errors, May 2008).

 

LACK OF AWARENESS OF SPREADSHEET RISK

Informed business decisions require accurate portrayal of options, impact, returns, risks and sensitivities. Many business leaders believe that they are making informed decisions having undertaken what they believe to be the right analysis. However, if such analysis is executed badly and hence flawed it can lead to bad and costly decision making; these same business leaders often don’t understand the level of risk that may be lurking within what they consider to be good financial appraisals.

Reasons for error, whilst not exhaustive, include:

·       Inexperienced users attempting to build complex systems

o   Poor design including lack of separation of inputs, workings and outputs

o   Volume, complexity and inconsistent use of formulae including hardcoding of numbers and/or assumptions

o   Multiple linkages to internal and external workbooks

·       Overconfidence (and ignorance or inexperience) of builder

·       Failure to test and review and audit

·       Failure to document

 

THE COST OF FAILED SPREADSHEETS

The increased regulation and compliance that now impacts spreadsheet control is not surprising given that the past few years have seen numerous multimillion-pound errors and frauds attributed to the use of spreadsheets. The financial impact can be many millions of pounds and the damage to a company’s reputation can be even worse. Some frequently quoted examples include:

·       ‘A cut-and-paste error cost TransAlta $24 million when it underbid an electricity-supply contract.’ (Source: The Register)

·       ‘Falsely-linked spreadsheets permitted fraud totalling $700 million at Allied Irish Bank/Allfirst.’ (Source: EuSpRIG)

·       ‘Kodak’s SEC 10-K filing reported a material weakness in its internal controls surrounding the preparation and review of spreadsheets that include new or changed formulas.’ (Source: Compliance Week)

SPREADSHEET RISK IS INCREASING

The level of spreadsheet risk is increasing. As spreadsheets become more complex, they are more prone to error (for the reasons noted above regarding error rate). As users are perceived to become more IT-literate, more, and potentially erroneous, spreadsheets are being used to support critical business processes. A combination of these two factors is significantly increasing the overall risk profile for many organisations.

Spreadsheet risk has always been important but there are indications it is becoming more significant. The UK’s HM Customs & Excise, in its ‘Methodology for the audit of spreadsheet models’ (2001), said that “the complexity and functionality of spreadsheets has reached levels of sophistication that few could have imagined even five years ago. The consequent threat posed to businesses by such powerful ‘end-user’ applications, mainly in the hands of untrained users, is immense”. This observation has continued to hold true in the years since its publication.