Spreadsheets: Risks (Part 1)

Spreadsheets have stood the test of time because they continue to meet the analytical needs of organizations, especially for analyzing and reporting financial results and providing support for decision-making.

“…spreadsheets will always fill the void between what a business needs today and the formal installed systems…” Mel Glass et al

The use of spreadsheet has changed over time from simple record keeping to more complex analysis formats. Due to the very nature of spreadsheet, it is also prone to more risk than any other system in the organization.

“Average spreadsheets stay alive for five years and during their life span they are used by 12 different people.” Felienne Hermans

Spreadsheet played an important role in the $690m AIB Allfirst fraud. Allfirst “would not pay the $10K fee for the direct data feed from Reuters to the risk control section”, instead they got John Rusnak to download his exchange feeds into a spreadsheet. Rusnak then substituted links to his private manipulated spreadsheet, the total losses hidden by the fraud were almost $700m and Rusnak received exaggerated bonuses.

You can read more spreadsheet horror stories at http://www.eusprig.org/stories.htm and http://www.eusprig.org/horror-stories.htm.

Spreadsheet risks can be categorized in two category:

  • Unintentional errors – honest mistakes in spreadsheets
  • Fraud risks – created intentionally to deceive

Ruijter and Pjoter, defined seven categories of spreadsheet errors. They are:

  1. Reference errors – This category includes errors like wrong references to other spreadsheet cells or incorrect summation of values.
  2. Incorrect Formula error – Cells containing an incorrect formula according to financial principles. For example an incorrect formula for a discounted cash flow.
  3. Logical errors in Excel – This category includes incorrect application of a formula function. For example an IRR (Internal Rate of Return) function is used instead of an XIRR (Internal Rate of Return for a Series of Cash Flows) function.
  4. Interface errors – This category contains incorrect or incomplete references to external sources, other spreadsheets or Pivot tables that are not up to date.
  5. Input errors –  Typing errors and incorrect assumptions are included here.
  6. User related errors – This category contains the incorrect use of copied values and formulas (instead of correct references) or incorrect use of filters and sorting.
  7. Control environment errors – These kinds of errors are caused by a lack of controls within the spreadsheet. For example formulas that are accidentally overwritten by fixed numbers, unauthorized changes and the use of wrong versions.

Spreadsheet Fraud

What is spreadsheet fraud?
There is no generally accepted definition of spreadsheet fraud. Spreadsheets are simply a tool used to commit a fraud. Common characteristics of a spread sheet fraud are:

  • Falsification of inputs
  • Manipulation of formulas to alter output
  • Concealing relevant information

As per Ralph Baxter of ClusterSeven, Spreadsheet frauds can be categorized in five categories:

  1. Presentation Fraud – It is the most common and it involves modifying the way a spreadsheet is viewed. Sometimes whole lines of data is made invisible, or negative values are formatted to show as positive, images are embedded to show wrong values, etc.
  2. Data Fraud – This is where input data is replaced by false data values, for example excel spreadsheet links may be redirected to different data sources changing the spreadsheet outputs.
  3. Incremental Fraud – This is seen in organisations where bonuses are calculated on the value of a changing portfolio (e.g. trading). Over time the fraudster sequentially adds a small amount to cells hidden in the detail of the workbook. The incremental approach avoids sudden output changes that might generate suspicion. Over time the adjustments contribute a material difference, and result in the payment of the performance bonus. After that the increments are then removed also on a gradual basis. By the end of the process all evidence of the manipulation has been removed but the trader has retained their bonus.
  4. Burial Fraud – Here a fraudulent change is made to a key transaction in a list and the user then sorts the list using standard Excel spreadsheet sorting functions. With thousands of rows of data in the spreadsheet these type of changes are virtually impossible to locate manually.
  5. Function Fraud – This makes use of the extensible nature of Excel to create new functionality beyond standard cell-base formulas. It includes the fraudulent manipulation of macros or UDF (User Defined Functions) that are difficult for an average user to understand. In extreme circumstances this functionality may be located on hidden worksheets to avoid discovery.

Spreadsheets being end user computing tool, lacks the internal control as compared to other enterprise-level tools. And also there are no hard and fast rules for auditing spreadsheets.

Related Articles:
Spreadsheets: Auditing & Validating (Part 2)
Spreadsheets: Best Practices (Part 3)

Advertisements

7 thoughts on “Spreadsheets: Risks (Part 1)

  1. KnowledgeFreaks June 13, 2017 / 12:43 am

    Well summarised. I have seen many auditor working blindly on excel sheets with no idea about possible errors and/or frauds.

    Like

  2. ITauditSecurity June 16, 2017 / 5:48 pm

    Hi,
    Does ‘Interface errors ‘ address the failure to load all the data needed?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s