Title: Reducing Spreadsheet Risk with FormulaDataSleuth
ArXiv ID: 0807.2997
Date: 2008-07-21
Authors: Researchers from original ArXiv paper
📝 Abstract
A new MS Excel application has been developed which seeks to reduce the risks associated with the development, operation and auditing of Excel spreadsheets. FormulaDataSleuth provides a means of checking spreadsheet formulas and data as they are developed or used, enabling the users to identify actual or potential errors quickly and thereby halt their propagation. In this paper, we will describe, with examples, how the application works and how it can be applied to reduce the risks associated with Excel spreadsheets.
💡 Deep Analysis
Deep Dive into Reducing Spreadsheet Risk with FormulaDataSleuth.
A new MS Excel application has been developed which seeks to reduce the risks associated with the development, operation and auditing of Excel spreadsheets. FormulaDataSleuth provides a means of checking spreadsheet formulas and data as they are developed or used, enabling the users to identify actual or potential errors quickly and thereby halt their propagation. In this paper, we will describe, with examples, how the application works and how it can be applied to reduce the risks associated with Excel spreadsheets.
A new MS Excel application has been developed which seeks to reduce the risks associated with
the development, operation and auditing of Excel spreadsheets. FormulaDataSleuth® provides a
means of checking spreadsheet formulas and data as they are developed or used, enabling the
users to identify actual or potential errors quickly and thereby halt their propagation. In this
paper, we will describe, with examples, how the application works and how it can be applied to
reduce the risks associated with Excel spreadsheets.
INTRODUCTION.
MS Excel, like many powerful and flexible computer applications is prone to “misuse”
and error to which even experienced users are not immune. It has been estimated that
94% of spreadsheets have at least one error in them [Panko, 2005]. There are many cases
of spreadsheet error opening up business and other organisations to serious financial risk
[EuSpRIG, 2007] and [Croll, 2005] or worse [Croll, 2006]. Reductions in risk can be
brought about by improved development practice [O’Beirne, 2005] and [Read & Batson,
1999] and training. Many Excel users are perhaps not fully aware of the vulnerabilities of
the application and are thus likely to remain unaware of the need for training. The role of
the spreadsheet auditor will grow as companies become increasingly concerned about
their vulnerability to spreadsheet error. Developers working with rapidly changing
requirements have a need for an application to assist and aid their work to prevent errors
at source.
In this paper, FormulaDataSleuth® will be described. This is a new application produced
to reduce the risks associated with the development, auditing and operation of MS Excel
spreadsheets. Some of Excel’s vulnerabilities will be described, including its methods of
auditing, formula analysis and error detection. An overview of the major features of
FormulaDataSleuth®, including those central to the reduction of spreadsheet risk, will
then be presented. The principles of operation will be described including the
WatchFormula sheet, which plays a pivotal role in the operation of the application. A
simple example from telecommunications systems cost analysis will then be presented.
2. EXCEL VULNERABILITIES.
Excel has some features to help developers and auditors avoid or detect errors. However,
in practice, these can be somewhat “hit and miss”. Excel’s security and protection
features tend to be unused when spreadsheets are continually being changed. The “green
triangles” that warn of formula irregularities do not work in every situation. For example,
a range vulnerability occurs when adding rows, so that on inserting rows above a cell
with a column summation of formulas, no green warning triangle appears to show that the
range is missing the new row. The order in which cells are inserted and formulas are
Reference
Formula
Flags: Change Detected, Error Found, Final Result.
Area Elements
Reference Formula
Cell/range references
Functions
Constants
Area Properties:
Location, size,
watched or not
watched, formula or
data.
filled can also give rise to errors that are not flagged by a green triangle. The formula
display feature, which places the formulas in the cells (as opposed to calculated values),
can present the auditor with a daunting “spot the error” task in all but the simplest
spreadsheets.
Structural issues can arise from the many different ways cells are referenced (relative,
absolute, mixed or Name). Spreadsheet development and restructuring can be
compromised by vulnerable reference dollaring causing errors in “copy and paste” and
fill operations. The problems grow with the number of sheets and workbooks and inter-
workbook links are a significant additional source of errors.
3. OVERVIEW OF FormulaDataSleuth®.
FormulaDataSleuth® is an Excel application in the form of a workbook that “watches” a
target Excel spreadsheet, workbook or group of workbooks. Information from contiguous
areas of formulas and data (a single cell is a special case of an area) is recorded. For
formula areas (data areas are treated slightly differently), this information falls into one of
three categories as shown in figure 1:
Area Elements - information about the elements of the formulas of those areas e.g. cell
references,
Area Properties - information relating to the properties of the areas e.g. location,
Flags e.g. Error Found.
Figure 1. FormulaDataSleuth® Elements and Properties of Contiguous Formula Areas.
Formula Area Elements are the components and terms of the formula. Note that Area
Properties include whether the area is watched or not. Only watched areas are recorded,
other areas are “Not Watched” by
