Spreadsheet Development, Auditing, & Control Tools
Download
Report
Transcript Spreadsheet Development, Auditing, & Control Tools
Spreadsheet Development,
Auditing, & Control Tools
INFORMS 2004, Denver
Roger Grinde
Whittemore School of Business & Economics
University of New Hampshire
[email protected]
Context
Spreadsheet Errors Happen
Regulations
Spreadsheet Risk
VAT, FDA, SOX
Likelihood of Errors
Impact of Errors
Love them or hate them, spreadsheets exist!
“Business always moves too fast for traditional
systems to keep up. The gap is filled by the
ubiquitous Microsoft Office suite of desktop
programs, particularly Excel.” (Cluster Seven Ltd.)
Recent Errors (Made Public)
10/12/04. Business Council (125 US CEOs) revised
economic forecast.
From 0-2% growth, to 2.1-4.5% growth
“It was a computational error. The spreadsheet shifted so
the wrong numbers appeared in the wrong columns…It was
just one column on the spreadsheet.” “…has never happened
before in the survey’s history.”
8/12/04. City of Richmond, VA. Overstated sales
revenue from new shopping mall.
From Sept-Dec 2003, sales reported to be $80 million; actual
sales were $57 million.
“The city blamed its mistake primarily on an error in a
spreadsheet formula, which amplified a subtotal amount.” “It
was a clerical error.”
Confluence of Interests
Organizational “embarrassments”
Regulations
Spreadsheet Auditing & Control
Software
Consultant, Developer, and User
Community
Academic Research
Literature
Spreadsheet Error Research
Spreadsheet Auditing Research
Empirical studies. Detecting errors in small
spreadsheets, building spreadsheets, code
inspection, peer- and self-audit, classification of
spreadsheet errors
Field audits, spreadsheet risks, which models to
audit, development of auditing tools, comparison
of tools
Spreadsheet Software Research
Theoretical models of spreadsheet software, usercentered approach for functions, alternate
paradigms
Current Focus
Tools for related phases of spreadsheet
life cycle
Design
Development/Productivity
Auditing: Review & Testing
Management & Control
Types of Spreadsheet Users
(Jones, Blackwell, Burnett 2003)
Basic
Moderate
Managing & printing a list of information
Very simple formulae, e.g., =SUM(A1:A10)
Understand spreadsheet paradigm fairly thoroughly.
Mastered pre-requisites, tackle more ambitious and longlived applications
e.g., mastered absolute and relative cell references
Advanced
Understand Visual Basic and can write user-defined
functions and procedures
e.g., mastered array functions
Classification of Spreadsheet
Errors
References
Panko & Halvorson (1996); Chadwick, Knight, & Rajalingham
(2001), Rajalingham, Chadwick, & Knight (2002)
Software Errors
User Errors
Quantitative Errors
Mechanical Errors
Logical Errors
Omission Errors
Qualitative Errors
Structural Errors
Temporal Errors
Design Tools
Diagramming/Framing Tools
Possible Interface to Spreadsheet Model
Alternatives (among others)
Example: Influence
DiagramModulesInputs/OutputsInitial
Spreadsheet Layout
Visio
iGrafx FlowCharter
Built-In Office Tools
Currently, not much of a direct link to
spreadsheet model design.
Development Tools
“Productivity” Tools. Primary purpose is to
ease the development of a spreadsheet
model/application
Example Capabilities
Assistance in writing formulas, manipulating data,
type conversions, text handling.
Range name management
Sheet/workbook management
Enhanced selection & search tools
Conversion tools (e.g., export as JPG or EPS)
Number of available tools with a variety of
capabilities. Some fairly specialized.
Auditing Tools
Tools to help the modeler perform spreadsheet model
verification.
Example Capabilities
Review (“Code Inspection”)
Execution Testing
Workbook Summaries
Formula Analysis, Referencing Problems
Precedence/Dependence Analysis
Structure analysis, model design
Worksheet/book comparisons
Execution testing
Native Excel has a level of some of these tools.
Several “suites” available as well as a number of “focused” tools.
Management & Control Tools
Tools to assist in the management and
control of spreadsheet models within the firm
Example capabilities
Version management & control
Access and change control
Change logging
Security
Sarbanes-Oxley Act of 2002, Section 404
“The requirements under Section 404 of the
Sarbanes Oxley Act increase the focus on controls
related to the development and maintenance of
spreadsheets.” (PWC, July 2004).
Spreadsheet Tools
Sources
Reviewed literature for tools, categorized into one of
four categories, assessed feature set at high level
Tools found
Web searches
Lists of add-ins
Mentioned in papers
Design: none (with direct spreadsheet integration)
Development/Productivity: 10
Auditing: 10 “suites”, 11 “focused”
Control: 4
Overlap between categories exists
Features
Precedence & Dependence Browsing
Identify unique vs. (apparent) copied
formulas, identify inconsistent formulas
Data & referencing problems
Workbook summaries
Version control and comparison of
worksheets/workbooks.
Execution testing
Productivity aids
Multi-Model management
Feature Mapping: Auditing Tools
Product
Excel Smart Tools Auditor
ExChecker
Operis Analysis Kit
Rainbow Analyst
Precedence/De
pendence
ID Unique &
Copied
Formulas
Data &
Referencing
Problems
Workbook
Summaries
Version Control
/ Comparison
Execution
Testing
Productivity
Aids
Multi-Model
Management
Y
Y
Y
Y
N
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
N
N
N
N
N
N
Y
N
N
Y
N
N
Y
Y
Y
N
Y
Y
N
N
N
Y
Y
Y
Y
N
Y
N
Y
Y
Y
N
Y
Y
N
N
Y
N
N
N
N
N
N
N
N
N
Y
Y
Y
Y
Y
Y
Y
N
N
Y
N
N
N
N
N
N
N
Y
Y
Y
N
Y
Y
N
N
N
Y
N
N
N
N
Y
N
N
Y
Y
N
Y
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
Y
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
SpACE (HMCE)
Spreadsheet Detective
Spreadsheet Professional
The Excel Auditor
XLSior
XLSpell (with XDrill)
Code Tracer
DSLimited Tools - dsCopyFinder
DSLimited Tools - dsSummarizer
Excel Compare
Explode Add-In
Master Tools
Reference Browser
Spreadsheet xlNavigator
Synkronizer 8.0
Xdrill
XL Analyst
N
Y
N
N
N
Y
N
N
N
N
Y
Features: Development Tools
Product
ActiveData
AppsPro Utilities
ASAP Utilities
EAT Toolpack
FastTrack for Excel
Modeling ToolPack
Navigator Utilities
OZGrid Analysis Collection
Power Utility Pack
Spreadsheet Composer
Precedence/De
pendence
ID Unique &
Copied
Formulas
Data &
Referencing
Problems
Workbook
Summaries
Version Control
/ Comparison
Execution
Testing
Productivity
Aids
Multi-Model
Management
N
N
N
N
Y
Y
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
Y
N
N
N
N
N
N
N
Y
N
Y
N
N
N
N
N
N
N
Y
N
N
N
N
N
N
N
N
Y
N
N
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
N
N
N
N
N
N
N
N
N
Features: Control Tools
Product
CIMCON einfotree
ClusterSeven XiGence Suite
DaCS
Perpetual Workbook
Precedence/De
pendence
ID Unique &
Copied
Formulas
Data &
Referencing
Problems
Workbook
Summaries
Version Control
/ Comparison
Execution
Testing
Productivity
Aids
Multi-Model
Management
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
N
Y
Y
Y
Y
N
N
N
N
N
N
N
N
Y
Y
Y
Y
Illustrations: Using Galletta
Spreadsheet (1996)
Seeded with Errors
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
B
C
Assuming
Cash Budget
D
2%
Fall
Cash, beginning
Outflows - School costs
Living Costs
Inflows - Loans
Support from home
Cash, end
School (contractual): Tuition
Fees
School (other): books/supplies
Living (contractual)
Housing
Insurance
Living Costs (other)
Food
Entertainment
Transportation
Clothing
$1,000
$4,468
$4,172
$3,000
$6,000
$1,360
=======
$4,115
$53
$300
MONTHLY
$450
$53
$330
$150
$40
$21
E
F
G
inflation/semester
Spring
Summer
Overall
$1,360
$673
$4,474
$4,480
$13,422
$4,213
$4,485
$12,870
$3,000
$3,000
$9,000
$5,000
$6,000
$17,000
$673
$980
$3,013
=======
=======
=======
$4,115
$4,115
$12,345
$53
$53
$159
$306
$312
$918
MONTHS
4
4
$1,800
$212
$1,800
$212
$1,800
$212
$5,400
$636
4
4
4
4
$1,320
$600
$160
$80
$1,346
$612
$161
$82
$1,373
$624
$164
$84
$4,039
$1,836
$485
$250
Precedence & Dependence
Examples
Explode Add-In
Spreadsheet Detective
Ex Checker
P/D: Explode
Shown with
Explode AddIn
Text-based
precedence &
dependence
browsing.
P/D: Spreadsheet Detective
Precedent Reports (Galletta example from SS Detective)
The Spreadsheet Detective Precedent Report
Double click on a row to go to the corresponding cell, press F5 to return to this worksheet
Cell
Label
Formula
Depth
'Model Copy'!F8
`Summer`CashEnd
= F3`CashBegig - F4`Out-SchCos - E5`Spring`LiviCosts + F6`Infl-Loans# + F7`SupFroHom
+
'Model Copy'!F3
`Summer`CashBegig
= E8`Spring`CashEnd
++
'Model Copy'!E8
`Spring`CashEnd
= E3`CashBegig - E4`Out-SchCos - E5`LiviCosts + E6`Infl-Loans# + E7`SupFroHom
+++
'Model Copy'!F4
`Summer`Out-SchCos
= SUM(F10:F12`SchoTuitn:`SchBoo/su)
++
'Model Copy'!F12
`Summer`SchBoo/su
= ROUND(E12`Spring * (1 + $D$1`Assumng#) , 0)
+++
'Model Copy'!E5
`Spring`LiviCosts
= SUM(E14:E20`Housing:`Clothng)
++
'Model Copy'!E14
`Spring`Housing
= D14`Fall
+++
'Model Copy'!E17
`Spring`Food
= ROUND(D17`Fall * (1 + $D$1`Assumng#) , 0)
+++
'Model Copy'!E19
`Spring`Transon
= ROUND(D19`Fall + (1 + $D$1`Assumng#) , 0)
+++
'Model Copy'!E20
`Spring`Clothng
= ROUND(D20`Fall * (1 + $D$1`Assumng#) , 0)
+++
'Model Copy'!F7
`Summer`SupFroHom
= ROUND(1000 + F4`Out-SchCos + F5`LiviCosts - F3`CashBegig - F6`Infl-Loans# , - 3)
++
'Model Copy'!F4
`Summer`Out-SchCos
= SUM(F10:F12`SchoTuitn:`SchBoo/su)
+++
'Model Copy'!F5
`Summer`LiviCosts
= SUM(F12:F19`SchBoo/su:`Transon)
+++
'Model Copy'!F3
`Summer`CashBegig
= E8`Spring`CashEnd
+++
P/D: Ex Checker
Source/Copied Formula Analysis
Shown with Spreadsheet Detective Add-In
Identifies “unique” formulas, and effective copying operations
Can reduce amount of manual code inspection required.
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
B
C
Assuming
Cash Budget
D
2%
Fall
Cash, beginning
Outflows - School costs
Living Costs
Inflows - Loans
Support from home
Cash, end
School (contractual): Tuition
Fees
School (other): books/supplies
Living (contractual)
Housing
Insurance
Living Costs (other)
Food
Entertainment
Transportation
Clothing
$1,000
$4,468
$4,172
$3,000
$6,000
$1,360
=======
$4,115
$53
$300
MONTHLY
$450
$53
$330
$150
$40
$21
E
F
G
inflation/semester
Spring
Summer
Overall
$1,360
$673
$4,474
$4,480
$13,422
$4,213
$4,485
$12,870
$3,000
$3,000
$9,000
$5,000
$6,000
$17,000
$673
$980
$3,013
=======
=======
=======
$4,115
$4,115
$12,345
$53
$53
$159
$306
$312
$918
MONTHS
4
4
$1,800
$212
$1,800
$212
$1,800
$212
$5,400
$636
4
4
4
4
$1,320
$600
$160
$80
$1,346
$612
$161
$82
$1,373
$624
$164
$84
$4,039
$1,836
$485
$250
Formula Analysis
Formula Reports (Galletta example
using SS Detective)
Map and Complexity Measures (Galletta
example using Power Utility Pack)
Workbook/Worksheet Reports
Galletta Example using Power Utility
Pack
Galletta Example using XLAnalyst
Native Excel
Precedents/Dependents
Error Checking
Trace Error
Evaluate Formula
Watch Window
Track Changes
Observations
“Advanced” Excel features (e.g., Solver,
Pivot Tables, VBA) are not treated in
great detail by the auditing tools.
Lack of design tools that help
user/modeler to frame problem and
develop initial model structure.
Anticipate a stronger linkage between
auditing tools and control tools as
regulations become more widespread.
Questions
How effective are auditing tools in identifying errors?
How effective are auditing tools in helping user
identify errors?
How common are false positives and false negatives?
For which users can auditing tools be of benefit?
Which models should be audited? (Butler, 2000)
Can using these tools help to improve skills of
users/modelers?
How much structure can be imposed on the
user/modeler in the design stage (e.g., Lotus
Improv)?
Need for Research
Detailed survey of tools, capabilities
Comparison “Shootout”
Empirical Studies
Use of audit tools by end users
Do users find more errors? Are they faster?
Effect of larger models
Spreadsheet Engineering
Laboratory models
Real-world spreadsheets
Control tools can help track model development process
Tools to help improve a “basic” user’s modeling capabilities.
Spreadsheet software improvements and paradigm shifts.
Reverse Engineering a “spaghetti” spreadsheet
Use of tools in teaching
Auditing tools to assess student spreadsheets
Control tools to track development process
Student use of auditing tools
Thank You!
Questions?