Modeling Spreadsheet Audit: A Rigorous Approach to Automatic Visualization

Abstract Computations in spreadsheets are hard to grasp and consequently many errors remain unnoticed. The problem with the hidden errors lies in the invisibility of the structure of calculations. As a result, auditing and visualization tools are required to make spreadsheets easier to comprehend and to make errors easier to detect. This paper presents a theoretical model of spreadsheets and a technique to describe spreadsheet auditing tools. These are then employed to describe and compare various tools. Moreover, two new visualization mechanisms are introduced. The spreadsheet model reflects not only current spreadsheet systems but also the way people actually use spreadsheets. Theoretically, it is impossible to check the correctness of a spreadsheet without a formal definition of its computations, but our hope is to find visualizations that point out parts of spreadsheets that contain anomalies, i.e. potential locations of errors. The model helps us to understand how such anomalies can be defined.

[1]  Henry C. Lucas,et al.  Spreadsheet analysis and design , 1989, CACM.

[2]  Jorma Sajaniemi,et al.  Extracting implicit tree structures in spreadsheet calculation , 1991 .

[3]  S. Ditlea,et al.  Spreadsheets can be hazardous to your health , 1987 .

[4]  John D. Gould,et al.  An experimental study of people creating spreadsheets , 1987, TOIS.

[5]  Bay-Wei Chang,et al.  Fluid visualization of spreadsheet structures , 1998, Proceedings. 1998 IEEE Symposium on Visual Languages (Cat. No.98TB100254).

[6]  Jorma Sajaniemi,et al.  An empirical analysis of spreadsheet calculation , 1988, Softw. Pract. Exp..

[7]  D. Nardi,et al.  Automatic generation of explanations for spreadsheet applications , 1994, Proceedings of the Tenth Conference on Artificial Intelligence for Applications.

[8]  J.Steve Davis.  Tools for spreadsheet auditing , 1996, Int. J. Hum. Comput. Stud..

[9]  David G. Hendry,et al.  CogMap: a Visual Description Language for Spreadsheets , 1993, J. Vis. Lang. Comput..

[10]  Henry C. Lucas,et al.  Toward a logical/physical theory of spreadsheet modeling , 1992, TOIS.

[11]  Jorma Sajaniemi,et al.  Transforming verbal descriptions into mathematical formulas in spreadsheet calculation , 1994, Int. J. Hum. Comput. Stud..

[12]  David F. McAllister,et al.  Discrete mathematics in computer science , 1977 .

[13]  Laks V. S. Lakshmanan,et al.  On querying spreadsheets , 1998, Proceedings 14th International Conference on Data Engineering.

[14]  Jorma Sajaniemi,et al.  Structured spreadsheet calculation , 1988, [Proceedings] 1988 IEEE Workshop on Languages for Automation@m_Symbiotic and Intelligent Robotics.