Dr Raymond R. Panko is a Professor of IT Management and Shidler Fellow at the Shidler College of Business, University of Hawaii. Dr Panko’s research focus lies in spreadsheet errors and governance and on human errors – research topics highly relevant in this day and age when we depend on computers and spreadsheets, to perform our daily tasks.
Dr Panko’s has been published in a number of journals, including the Journal of Organizational and End User Computing, European Journal of Information Systems, Information Systems Control Journal Online, Decision Support Systems, IS Audit and Control Journal and various others.
One of the many interesting articles you can find on Dr Panko’s website is “What We Know About Spreadsheet Errors”, published in the Journal of End User Computing Special issue on Scaling Up End User Development in 1998, but revised in 2008.
Panko’s list of different spreadsheet errors
In his article, “What We Know About Spreadsheet Errors”, Dr Panko explains that even though most people tend to regard spreadsheet errors as mistyping numbers and signs or not linking to the correct cell, his research indicates that there are different types of errors. He has identified three types of quantitative errors, which yield incorrect results in spreadsheets:
- Mechanical errors – simple mistakes (mistyped numbers, incorrect cell references in formulae)
- Logic errors – mistakes in reasoning (leading to wrong formulae)
- Omission errors – mistakes due to leaving something out
Logical errors and omission errors are much harder to detect than mechanical errors which means they pose a greater danger for the developer.
Spreadsheet development practices and policies
Panko writes that despite the ever-increasing use of spreadsheets in business and their importance, there is a lack of systematic processes in development, inspection and testing – which are necessary in detecting errors. Surveys of spreadsheet developers have identified these key problems:
- Development processes tends to be very informal
- Systematic and code inspection is rarely used, and does not appear to be part of the spreadsheet development "culture"
- Data is not tested rigorously enough; for example, testing was rarely done with out-of-bounds data
- Spreadsheets were built without prior design or planning, resulting in spreadsheets with poor design and layout
Panko suggests that a possible reason for the lack of rigour in spreadsheet development could be due to the lack of spreadsheet development policies in organisations. Another problem that plagues spreadsheet development is the well-known phenomenon of “over-confidence” in our own spreadsheets.
Which controls were being used in spreadsheet models?
Based on surveys and other information, Panko indicates that the following controls are currently used in spreadsheet models:
- Verification of the model’s logic (most widespread control)
- Participation in formal training
- Documentation, although most spreadsheets either had very little documentation or no documentation at all
- Formal audits and formal reviews (among the least-used controls!)
What are some implications and recommendations for spreadsheet development?
Based on Panko’s research, here are some recommendations:
- Spreadsheet developers should begin with an adequate preliminary design to provide some guidance
- Spreadsheets should be documented (currently rare in practice)
- Rigorous testing is necessary to dramatically decrease error rates (a task that is difficult to do and often ignored, as relatively few organisations have imposed requirements for comprehensive peer review and data testing)
- In the event that an existing spreadsheet has been used and changed constantly by different managers and stakeholders, it may be a good idea to rebuild it from a fresh template – this process helps to realign the design of the spreadsheet with the most current requirements and goals of the organisation, and can often identify major issues in the reconciliation process
Thus, it is necessary for organisations to adopt a more disciplined approach to spreadsheet development and testing, in order to ensure that there is validity and correctness in the models that are ultimately used as a basis for corporate decisions.
It is interesting and also rather frightening to note that according to Panko‘s research, even though spreadsheet developers indicated that controls should have been used, in many cases they were not used at all. Furthermore, formal audits and model reviews (the most effective types of controls) were actually the least used!
For more information on Panko’s research
Take a look at Dr Panko’s Spreasdheet Research website as well as his website about Human Error.
If you would like some assistance with model reviews or how to structure the development of your next financial model, let us know, this is what we focus on and are happy to help out.
Post new comment