Institute of Internal Auditors

1
Institute of Internal Auditors

• Spreadsheet Errors incidence, detecting,
  preventing
• 29 June 2007
• Andrea Gluyas
• Actuary
• PricewaterhouseCoopers Actuarial Services

PwC
2
Agenda/Contents

• Introduction
• Incidence/Incidents
• Detection
• Prevention
• Conclusions

3
Introduction

• Spreadsheets are used everywhere today
• Spreadsheets are a useful tool but
• Their flexibility is their downfall from a risk
  perspective
• They are often used as a stop gap measure but
  become business critical
• Its important to consider whether a spreadsheet
  is the appropriate tool and to review this
  decision periodically
• Should you be using a proprietary system?
• Might a database handle this better?

4
Incidence of Errors

• Errors are inevitable
• Actual wrong formulae
• Incorrect or outdated assumptions
• Poor transfer of data
• Bad practice is rife
• Poor design in the first place
• Spreadsheets often outgrow their original scope
• Model creators leave
• The spreadsheet becomes a black box
• Users do not understand the implications of
  changes they make

5
Incidents of Errors

• We commonly find
• Incorrect formulae
• Outdated direct links to other sheets
• Incorrectly linking workbooks
• Inappropriate copying/non-copying down or across
  of formulae
• Non-compliance with standards, product
  documentation or legislation
• Temporary workarounds/adjustments left in place

6
Incidents of Risk

• There are many risky practices in common usage
• Poor documentation of models and resultant key
  person risks
• Poor updateability/robustness of models
• Hard coded embedded parameters
• Interfaces between areas of responsibility
• Need for independent review of changes made
• Co-coordinating specialised input (eg tax)
• Inappropriate folder security
• Folder, workbook and cell security

7
Detecting Errors

• Tools and methods to help detect errors
• In-built cross checks and control totals
• Completion and control checklist
• Edit/Links to check workbook is linking to the
  correct source data
• Excel audit tools
• Proprietary spreadsheet audit tools (eg
  Spreadsheet Detective)
• Regular sign off of inputs/parameters by relevant
  areas
• Regular sign off of methodologies and compliance
  by relevant areas
• External review of model

8
Example of Excel In-built audit tools
Detecting Errors
9
Example of Proprietary Spreadsheet Audit Tool
Detecting Errors
10
Detecting Errors

• Tools and methods to help detect errors
• In-built cross checks and control totals
• Completion and control checklist
• Edit/Links to check workbook is linking to the
  correct source data
• Excel audit tools
• Proprietary spreadsheet audit tools (eg
  Spreadsheet Detective)
• Regular sign off of inputs/parameters by relevant
  areas
• Regular sign off of methodologies and compliance
  by relevant areas
• External review of model

11
Preventing Errors

• The key to preventing errors is good practice in
  model design and use
• Put a spreadsheet protocol into place in your
  organisation
• Think about whether a spreadsheet is the
  appropriate tool
• Keep it simple
• Document it well
• Test it thoroughly
• Insist on
• a change control process
• regular sign off of inputs/parameters by relevant
  areas
• regular sign off of methodologies and compliance
  by relevant areas or external advisors

12
Preventing Errors

• Your spreadsheet protocol might contain
  requirements for
• Consistency
• Updateability
• Reliability
• Understandability
• Presentability
• Documentation, including use of different colours
  for
• Hard coded data
• Linked data
• Warnings or unusual formulae
• Clear identification/separation of inputs and
  assumptions

13
Preventing Errors

• Getting the basics right and reducing the risk of
  errors
• A single formula in each row or column
• Appropriate use of separate sheets and workbooks
• Good use of macros to assist risk reduction, eg
  clearing or transferring data
• Documentation of data sources, model operators,
  date and time model run
• Use of look-up functions rather than direct
  references to ensure correct data is picked up
• Documentation, including
• Cell comments
• Written instructions in the front of the workbook
• Clear responsibilities for the accuracy of the
  model

14
Conclusion

• Spreadsheets are a valuable and irreplaceable
  business tool but
• Used badly, models often deteriorate
• Errors are introduced or evolve over time
• Knowledge of the model is lost
• Staying in control of your model is one of the
  greatest challenges for the model user and is
  essential to maintain the credibility and
  usefulness of the model over its lifespan.

15
Questions