Towards a Theory of Spreadsheet Accuracy: An Empirical Study

TOWARDS A THEORY OF SPREADSHEETACCURACY: AN EMPIRICAL STUDYS. E. Kruck(ABSTRACT) Electronic spreadsheets have made a major contribution to financial analysis andproblem solving. Although professionals base many decisions on the analysis of aspreadsheet model, literature documents the data quality problems that often occur, i.e.underlying formulas and resulting numbers are frequently wrong. A growing body ofevidence, gathered from students in academia as well as working professionals in businesssettings, indicates that these errors in spreadsheets are a pervasive problem. In addition,numerous published articles describe techniques to increase spreadsheet accuracy, but noaggregation of the topics and no model explaining this phenomenon exist. The research described here develops a theory and model of spreadsheet accuracyand then attempts to verify the propositions in a laboratory experiment. Numerouspractitioner articles suggest techniques to move spreadsheets into a more structureddevelopment process, which implies an increase in spreadsheet accuracy. However,advances in our understanding of spreadsheet accuracy have been limited due to a lack oftheory explaining this phenomenon. This study tests various propositions of the proposed theory. Four constructswere developed from the theory to test it. The four constructs are planning and designorganization, formula complexity, testing and debugging assessment, and spreadsheetaccuracy. From these four constructs three aids were designed to test the relationshipbetween the four constructs. Each of the three aids developed was designed to increasespreadsheet accuracy by addressing a single proposition in the model. The lab experiment conducted required the participants to create a reusablespreadsheet model. The developed model and theory in this paper appear to represent thespreadsheet accuracy phenomenon. The three aids developed did increase spreadsheetdata quality as measured by the number of errors in the spreadsheets. In addition, theformula complexity participants created spreadsheets that contained significantly fewerconstants in formulas, and the testing and debugging participants corrected a significantnumber of errors after using the aid.