This chapter describes two experiments that examined overconfidence in spreadsheet development. Overconfidence has been seen widely in spreadsheet development and could account for the rarity of testing by end-user spreadsheet developers. The first experiment studied a new way of measuring overconfidence. It demonstrated that overconfidence really is strong among spreadsheet developers. The second experiment attempted to reduce overconfidence by telling subjects in the treatment group the percentage of students who made errors on the task in the past. This warning did reduce overconfidence, and it reduced errors somewhat, although not enough to make spreadsheet development safe.
TopIntroduction
Spreadsheet development was one of the earliest end-user applications, along with word processing. Spreadsheet development continues to be among the most widely used computer applications in organizations (United States Bureau of the Census, 2003). Although many spreadsheets are small and simple throwaway calculations, surveys have shown that many spreadsheets are quite large (Cale, 1994; Cragg & King, 1993; Floyd, Walls, & Marr, 1995; Hall, 1996), complex (Hall, 1996), and very important to the firm (Chan & Storey, 1996; Gable, Yap, & Eng, 1991).
Unfortunately, there is growing evidence that inaccurate spreadsheets are commonplace. For instance, Table 1 shows that recent audits of 88 real-world spreadsheets have found errors in 94%; yet several studies only reported spreadsheets with serious errors. The implications of this ubiquity of errors are sobering.
Table 1. Studies of spreadsheet errors
| Study | Year | Number of Spreadsheets | Percent of Spreadsheets Containing at Least One Error | Formula Error Rate (FER): Percent of Cells Containing Errors |
| Field Audits | | | | |
| Hicks | 1995 | 1 | 100% | 1.2% |
| Coopers & Lybrand (c) | 1997 | 23 | 91% | |
| KPMG (b) | 1998 | 22 | 91% | |
| Lukasic | 1998 | 2 | 100% | 2.2%, 2.5% |
| Butler | 2000 | 7 | 86% | 0.4% |
| Clermont, Hanin, & Mittermeier (a) | 2002 | 3 | 100% | 1.3%, 6.7%, 0.1% |
| Lawrence & Lee | 2004 | 30 | 100% | Average of 6.9% |
| Total/Per Spreadsheet | | 88 | 94% | 5.2% |
| | | | |
| Development Experiments | | | | |
| Brown & Gould | 1987 | 27 | 63% | |
| Olson & Nilsen (f,g) | 1987-1988 | 14 | | 21% |
| Lerch (f,g) | 1988 | 21 | | 9.3% |
| Hassinen (g) | 1988 | 92 | 55% | 4.3% |
| Panko & Halverson | 1997 | 42 | 79% | 5.6% |
| Panko & Halverson | 1997 | 35 | 86% | 4.6% |
| Teo & Tan | 1997 | 168 | 42% | 2.1% |
| Panko & Sprague (i) | 1998 | 26 | 35% | 2.1% |
| Panko & Sprague (j) | 1998 | 17 | 24% | 1.1% |
| Janvrin & Morrison (h) | 2000 | 61 | | 6.6%-9.6% |
| Janvrin & Morrison (h) | 2000 | | | 8.4%-16.8% |
| Kreie (posttest) | 2000 | 73 | 42% | 2.5% |
(a) Computed on basis of all non-empty cells instead of on the basis of formula cells. (b) Only spreadsheets with major errors were counted. (c) A dependent variable value was off by at least 5%. (d) Only errors large enough to demand additional tax payments were counted. (e) Only serious errors were counted. (f) Counted errors even if they were corrected by the developer. (g) CER is based only on formula cells. (h) CER was based only on high-risk formula cells. (i) MBA students with little or no development experience. (j) MBA students with at least 250 hours of spreadsheet development experience.
Source: Panko (http://panko.cba.hawaii.edu/ssr/). References to studies are given at the Web site.