A Spreadsheet Auditing Tool Evaluated in an Industrial Context Book

A Spreadsheet Auditing Tool Evaluated in an Industrial Context Book
  • Page 1

    A Spreadsheet Auditing Tool Evaluated in an Industrial Context Markus Clermont, Christian Hanin, Roland Mittermeir Universität Klagenfurt Universitätsstraße 65- 67 A-9020 Klagenfurt Austria actionURI(mailto:mark@isys.uni-klu.ac.at):mark@isys.uni-klu.ac.at ABSTRACT Amongst the large num...

  • Page 2

    to arrangements where data-entry cells, cells immediately dependent on these data entries used for preparatory operations, and cells performing the final modelling or analysis are allotted to distinct, well identifiable locations (to avoid confusion we avoid the term “area” at this moment) or...

  • Page 3

    or more results of the spreadsheet irrespective of their “distance” to the erroneous cell. Worse, the effect of an error might show at a different place than the error itself, thus further increasing the complexity of identifying faults. There are techniques to reduce the complexity of sprea...

  • Page 4

    programs, like new tax-rates or new organizational structures, will force the spreadsheet users to maintain the spreadsheet. However, the lack of documentation makes it hard for spreadsheet authors to understand the effect of changing a single cell has on the rest of the spreadsheet. If the main...

  • Page 5

    Our technique identifies regular structures in the spreadsheet. These regular structures, so called logical equivalence classes, are sets of similar cells. These similar cells do not have to be neighbours, but we noticed that on large sheets • They are either neighbours on the layout, or • ...

  • Page 6

    The identified errors were coarsely categorized by their immediate impact into qualitative and quantitative errors (see [Teo, 2000]), and by their origin into the following categories (see [Ayalew, 2000]): • Constant instead of formula • Constant instead of reference • Reference to empty c...

  • Page 7

    correctness of the displayed values was checked. Special attention was put on wrong sums, wrong formatting and errors that were reported by Excel. After these routine checks in the value domain, the toolkit described in section 3.2 was applied. The so discovered irregularities where then discusse...

  • Page 8

    had severe negative effects on the company. What we found though was a very high number of qualitative errors with the potential to become quantitative errors in the next (or future) maintenance cycle(s). Thus, the numerical test that each workbook undergoes after each round of modifications bec...

  • Page 9

    In order to check the effectiveness of the auditing technique, we calculated the Copy-Equivalence to Formula ratio, i.e. the average size of each copy equivalence class. In the average, each copy-equivalence class contains 5.1 formulas. Thus, only every fifth formula cell of the spreadsheet had t...

  • Page 10

    discussed with the sheets’ authors, and documentation work had to be done. This gives an average inspection rate of 1208 cells per day. Compared to other approaches (see [Panko, 1997]) this is rather high. Hence, we claim that the approach is worthwhile to follow at least for those portions o...

  • Page 11

    8 CONCLUSION This paper presents an auditing toolkit for assessing the correctness of large spreadsheets. The tool helps to identify irregularities in the spatial distribution of similar formulas. An assessment in an industrial context proved to be quite encouraging. It helped to analyse 78 spre...

  • Page 12

    David Lorge Parnas (1994). Software Aging. In Proceedings of the 16th international conference on Software Engineering, Volume 16, pages 279–287. IEEE. Karen Rothermel, Curtis Cook, Margaret Burnett, Justin Schonfeld, T. Green, and Gregg Rothermel (2000). WYSIWYT Testing in the Spreadsheet Par...