Research and Best Practice
There is a very extensive research base on the risks of using spreadsheets within business see [Panko, 2000] [Panko & Ordway, 2005] [Powell, Baker & Lawson, 2007]. Much of the research has been coordinated and progressed by EuSpRIG [Chadwick, 2003]. Further significant work improving the end user approach to software has been undertaken by the EUSES consortium [EUSES, 2009].
The main known risks of spreadsheets include:
a) Human Error – To err is human, hence the majority (>90%) of spreadsheets contain errors. Because spreadsheets are rarely tested [Panko, 2006] [Pryor, 2004] these errors remain. Recent research has shown that about 50% of spreadsheet
models used operationally in large businesses have material defects [Powell, Baker, Lawson, 2007] [Croll, 2008]. Approximately 50% of executives recently surveyed had encountered spreadsheet related problems up to and including staff dismissal [Caulkins, Morrison & Weideman, 2007].
b) Fraud – Because of the ease with which program code and data is mixed, spreadsheets are the perfect environment for perpetrating fraud [Mittermeir, Clermont, Hodnigg, 2005]. The $600m fraud perpetrated by John Rusnak at AIB/Allfirst was spreadsheet related [Butler, 2002]. Other spreadsheet related frauds have occurred and have been notified to the regulator, but have not been reported.
c) Overconfidence – Because spreadsheet users do not go looking for errors, they don’t find any or many. Spreadsheet users are therefore overconfident in their use of spreadsheets [Panko, 2003].
d) Interpretation – Translation of a business problem into the spreadsheet domain can “…lead to a position where decision makers may act in the belief that decisions can be made with confidence on the output from the spreadsheet despite evidence to the contrary” [Banks & Monday, 2002].
e) Archiving – “The case of failed Jamaican commercial banks demonstrates how poor archiving can lead to weaknesses in spreadsheet control that contribute to operational risk” [Lemieux, 2005].
It has recently been suggested that there may be a further series of systemic risks posed by spreadsheets including (but not limited to!) Assumptions, Opacity, Reification and Enterprise Interoperability.
In a continuation of this section, we will cover further basic research.
Guidelines from the Institute of Chartered Accountants in England & Wales (ICAEW)
- How to review a spreadsheet
- Spreadsheet Competency Framework (2018, currently in review)
- Twenty principles of good spreadsheet practice
- Financial Modelling, Issue 63; 2nd edition, a practical guide to financial modelling in a corporate finance transaction context. (Members)
According to Professor Tom Grossman, author of the popular EuSpRIG paper “Spreadsheet Engineering: a Research Framework”, spreadsheet best practices are “Situation Dependent”, a view that is supported widely within the practitioner community. Guidance on spreadsheet best practice is therefore gradually emerging, as it depends upon what you are doing. In a recent paper, Tom Grossman and Ozgur Ozluk describe three Spreadsheet Engineering Methodologies. They find it credible that each of the methodologies enhances productivity, accuracy and maintainability for large financial planning models.
A constantly updated bibliography of spreadsheet research and list of articles on Excel best practice is maintained at https://www.i-nth.com/about/whats-new.
Here are some more documents to get you started on the subject of best practice.
Is this spreadsheet a tax evader? by Ray Butler (HMRC) to HICS33 in 2000.
How do you know your spreadsheet is right? More than fifty Principles, Techniques and Practice of Spreadsheet Style by Philip L. Bewig July 28, 2005. With references.
Spreadsheet Modelling Best Practice (1.4MB pdf) kindly donated by Louise Bartlett of IBM Business Consulting Services. Written by Nick Read and Jonathan Batson when Business Dynamics were part of PriceWaterhouseCoopers (PWC), published 1999 by the Institute of Chartered Accountants in England and Wales (ICAEW).
In a “Software Management” article (Jan 2001) “Software Defect Reduction Top 10 List” (121K PDF) Barry Boehm and Victor Basili predict “The ranks of ‘sorcerers apprentice’ user-programmers will also swell rapidly, giving many who have little training or expertise in how to avoid or detect high-risk defects tremendous power to create high-risk defects.”
However, following Tom Grossman’s observation that best practice is situation dependant, David Colver questions with qualified examples that there may be no such thing as good practice. Acknowledging both points of view, Angus Dunn sets out a set of Required and Desirable spreadsheet practices, with a further set of practices to be Avoided.
Professor Ray Panko, University of Hawaii, gave some comprehensive guidance on testing spreadsheets at the EuSpRIG 2006 conference. Ray’s paper “Recommended Practices for Spreadsheet Testing” is available here.
Dr Louise Pryor gave a succinct outline of the principles of testing spreadsheets in her paper “When, Why and How to test spreadsheets”. In her follow up paper, she describes why merely establishing correctness of a spreadsheet may not be enough. Grenville Croll describes how a large accounting firm reviews spreadsheet models for accuracy. Harmen Ettema and colleagues from Price Waterhouse Coopers describes another approach – building a shadow model – to determine accuracy in important spreadsheet calculations. Simon Murphy describes his commercially based spreadsheet review process.
Dr John Raffensperger of the University of Canterbury, Christchurch, New Zealand, gave a memorable presentation at the EuSpRIG 2001 conference in Amsterdam on “New Guidelines for Spreadsheets”.
Bill Bekenn and Ray Hooper draw upon the Toyota automotive manufacturing methodology to describe a set of techniques “Poka Yoke” – Japanese for “Mistake Proofing” – to reduce the possibility of making mistakes in the first place.
Pat O’Beirne draws from the long history of database development to outline a methodology for making small but important improvements to spreadsheets without altering their fundamental design. Refactoring is a change made to the internal structure of software to make it easier to understand and cheaper to modify without changing its observable behaviour.
Ray Payette describes how to use some of Excel’s built in features to document spreadsheets, and gives copious examples in his paper “Documenting Spreadsheets”.
Dr Louise Pryor gives a brief characterisation of the purposes and forms of documentation in and of spreadsheets. Jocelyn Paine describes how to document spreadsheets with pseudo-code using an exercise with cash-flow and loans by way of example.
A number of vendors including Clusterseven, EASA, Prodiance, Qtier Rapor, Resolver Systems, Ringtail XL, Risk Integrated, Spectrum Global and Xenomorph describe their various approaches to delivering safe and effective spreadsheet based systems.
Guidance from Microsoft
Microsoft have attended EuSpRIG on two occasions and have provided useful guidance on the development of better quality models. Firstly through a 2006 paper from Brandon Weber entitled “Strategies for Addressing Spreadsheet Compliance Challenges” and secondly in a 2008 paper directed at Accounting and Finance Professionals, Mbwana Alliy and Patty Brown focus on a number of key issues.
Spreadsheet Control Projects
Andy McGeady and Joseph McGouran described a large project they had been involved with at Allied Irish Bank (AIB) to control the legions of spreadsheets in use within the Capital Markets division. They offer clear and prescient advice to others who would seek to embark upon such a project. Jamie Chambers and Mark Hamill describe a similar project at an unnamed European Investment Bank to control the use of end user computing tools for business critical applications.
Sebastian Dewhurst describes how he has used his company’s technology to transform critical spreadsheets into Web based applications at Zurich Financial.
Thomas Lemon and Ewen Ferguson describe their service based approach. They have worked with a number of organisations, ranging in size up to huge multi-nationals, to help them build appropriate spreadsheet governance frameworks, including the design and implementation of policies, minimum design standards, control processes, training and awareness programmes and the consideration and implementation of spreadsheet management tools.
Ben Rittweger and Eoin Langan examine in the context of financial reporting, the controls that organisations have in place to manage spreadsheet risk and errors. Their recent findings support previous findings of Panko, that errors occur frequently in spreadsheets and that there is little or unenforced controls employed, however this research finds that attitudes are changing with regard to spreadsheet risk.
Patrick O’Beirne’s presentation Controlling Spreadsheets, Motivation & Methodology to the ISACA EuroCACS11 conference explains to auditors the need for good practice in spreadsheet development and control, and certification standards, how to conduct risk assessments and audit/control scoping, formulate questions to ask, apply spreadsheet management processes and understand the use of software tools for spreadsheet audit efficiency.
Controlled Development using Spreadsheets
Spreadsheets can be safely used to build complex applications. The approach to this must be careful and controlled, incorporating the wide ranging aspects of a proper software development methodology. Susan Allen describes such a project that has gone live at Lloyds TSB bank in 2009.
There is an emerging body of best practices for teaching spreadsheet use in scholars and students. Francoise Tort outlines some curriculum design principles. She suggests key principles for spreadsheet teaching and illustrates them with teaching guidelines.
Simon Thorne describes a new MSc module in End User Computing Risk Management. This pulls together much of the research and practitioner experience in Spreadsheet Risk Management to arm the next generation of finance spreadsheet champions with the relevant knowledge, techniques and critical perspective in an emerging discipline.
Almost ten years ago, David Chadwick, a EuSpRIG founder, and Rodney Sue outlined a teaching approach which had been devised to help students to reduce their likelihood of making common errors during spreadsheet development.