Article Preview
TopIntroduction
The prevalence of errors in organizational spreadsheets that can lead to disastrous consequences is well documented, yet few organizations have formal policies for quality control in spreadsheets deployed for end-users (Caulkins, Morrison & Wiedemann, 2007; Gandel, 2013; Lawson, Baker, Powell & Foster-Johnson, 2008; Panko, 1998; Panko & Ordway, 2005; http://www.eusprig.org/stories.htm). As organizations seek to define appropriate development guidelines and more formal policies, they need to control for errors that occur during the development and review of the model as well as during the later phases of the spreadsheet life cycle. It is, therefore, important to understand the various types of errors that can exist in a spreadsheet throughout its life cycle so that appropriate controls can be created before the spreadsheet is released for widespread use.
The literature on spreadsheet errors classifies errors into two general categories: quantitative and qualitative (e.g., Beaman, Waldmann, & Krueger, 2005; Kreie, Cronin, Pendley & Renwick, 2000; Kruck, 2005; Panko & Aurigemma, 2010; Powell, Baker, & Lawson, 2008, 2009; Teo & Tan, 1999). Quantitative errors result in immediate incorrect numerical values or logic in the current version of the spreadsheet. Quantitative errors created during the development phase usually occur as the result of an omission of part of the problem, erroneous logic or incorrect spreadsheet mechanics (Panko & Halverson, 1996). During the operational usage phase of a spreadsheet, quantitative errors can result from accidental deletion, alteration of formulas or from incorrect inputs provided by end users. While quantitative errors can occur at any phase of the spreadsheet life cycle, research lab studies have focused primarily on evaluating techniques to minimize or detect quantitative errors during the development phase (Galletta, Hartzel, Johnson, Joseph & Rustagi, 1996; O’Donnell, 2001; Panko & Halverson, 1997).
Qualitative errors, also sometimes referred to as dormant, or stealth, or latent errors do not result in immediate incorrect numerical values. They are the results of risky or poor design practices and are considered just as damaging to an organization’s productivity from a usability perspective as quantitative errors. Their presence increases the likelihood of an eventual quantitative error or misinterpretation occurring once the model is placed into operational use. They also make it more difficult to debug models with quantitative errors. In interviews with forty-five senior managers from various industry sectors, five of the seven most commonly mentioned types of spreadsheet errors reported were associated with potential qualitative errors, including inaccurate data (76%), errors inherited from reuse of spreadsheets (49%), misinterpretation of outputs or reports (27%), broken links (22%), and copy/paste errors (22%) (Caulkins et al., 2007). For example, while templates can enable sharing of domain expertise as well as spreadsheet skills, some of the interviewees felt “reused spreadsheets were difficult to control, since updating worksheets can introduce more errors” (Caulkins et al., 2007, p.7). Similarly, even when the spreadsheet was correct, several interviewees cited instances where the output had been misinterpreted by a person who did not correctly understand the model’s assumptions or limitations, resulting in a bad decision.