Towards a Theory of Spreadsheet Accuracy: An Empirical Study

Abstract

Electronic spreadsheets have made a major contribution to financial analysis and problem solving. Although professionals base many decisions on the analysis of a spreadsheet model, literature documents the data quality problems that often occur, i.e. underlying formulas and resulting numbers are frequently wrong. A growing body of evidence, gathered from students in academia as well as working professionals in business settings, indicates that these errors in spreadsheets are a pervasive problem. In addition, numerous published articles describe techniques to increase spreadsheet accuracy, but no aggregation of the topics and no model explaining this phenomenon exist.

The research described here develops a theory and model of spreadsheet accuracy and then attempts to verify the propositions in a laboratory experiment. Numerous practitioner articles suggest techniques to move spreadsheets into a more structured development process, which implies an increase in spreadsheet accuracy. However, advances in our understanding of spreadsheet accuracy have been limited due to a lack of theory explaining this phenomenon.

This study tests various propositions of the proposed theory. Four constructs were developed from the theory to test it. The four constructs are planning and design organization, formula complexity, testing and debugging assessment, and spreadsheet accuracy. From these four constructs three aids were designed to test the relationship between the four constructs. Each of the three aids developed was designed to increase spreadsheet accuracy by addressing a single proposition in the model.

The lab experiment conducted required the participants to create a reusable spreadsheet model. The developed model and theory in this paper appear to represent the spreadsheet accuracy phenomenon. The three aids developed did increase spreadsheet data quality as measured by the number of errors in the spreadsheets. In addition, the formula complexity participants created spreadsheets that contained significantly fewer constants in formulas, and the testing and debugging participants corrected a significant number of errors after using the aid.