Spreadsheet Development, Auditing, & Control Tools

1. Spreadsheet Development, Auditing, & Control Tools INFORMS 2004, Denver Roger Grinde Whittemore School of Business & Economics University of New Hampshire roger.grinde@unh.edu

2. Context • Spreadsheet Errors Happen • Regulations • VAT, FDA, SOX • Spreadsheet Risk • 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.)

3. 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.”

4. Confluence of Interests • Organizational “embarrassments” • Regulations • Spreadsheet Auditing & Control Software • Consultant, Developer, and User Community • Academic Research

5. Literature • Spreadsheet Error Research • Empirical studies. Detecting errors in small spreadsheets, building spreadsheets, code inspection, peer- and self-audit, classification of spreadsheet errors • Spreadsheet Auditing Research • Field audits, spreadsheet risks, which models to audit, development of auditing tools, comparison of tools • Spreadsheet Software Research • Theoretical models of spreadsheet software, user-centered approach for functions, alternate paradigms

6. Current Focus • Tools for related phases of spreadsheet life cycle • Design • Development/Productivity • Auditing: Review & Testing • Management & Control

7. Types of Spreadsheet Users (Jones, Blackwell, Burnett 2003) • Basic • Managing & printing a list of information • Very simple formulae, e.g., =SUM(A1:A10) • Moderate • Understand spreadsheet paradigm fairly thoroughly. • Mastered pre-requisites, tackle more ambitious and long-lived 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

8. 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

9. Design Tools • Diagramming/Framing Tools • Possible Interface to Spreadsheet Model • Example: Influence DiagramModulesInputs/OutputsInitial Spreadsheet Layout • Alternatives (among others) • Visio • iGrafx FlowCharter • Built-In Office Tools • Currently, not much of a direct link to spreadsheet model design.

10. 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.

11. Auditing Tools • Tools to help the modeler perform spreadsheet model verification. • Review (“Code Inspection”) • Execution Testing • Example Capabilities • 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.

12. 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).

13. Spreadsheet Tools • Sources • Web searches • Lists of add-ins • Mentioned in papers • Reviewed literature for tools, categorized into one of four categories, assessed feature set at high level • Tools found • Design: none (with direct spreadsheet integration) • Development/Productivity: 10 • Auditing: 10 “suites”, 11 “focused” • Control: 4 • Overlap between categories exists

14. 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

15. Feature Mapping: Auditing Tools

16. Features: Development Tools Features: Control Tools

17. Illustrations: Using Galletta Spreadsheet (1996) • Seeded with Errors

18. Precedence & Dependence • Examples • Explode Add-In • Spreadsheet Detective • Ex Checker

19. P/D: Explode • Shown with Explode Add-In • Text-based precedence & dependence browsing.

20. P/D: Spreadsheet Detective • Precedent Reports (Galletta example from SS Detective)

21. P/D: Ex Checker

22. 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.

23. Formula Analysis • Formula Reports (Galletta example using SS Detective) • Map and Complexity Measures (Galletta example using Power Utility Pack)

24. Workbook/Worksheet Reports • Galletta Example using Power Utility Pack • Galletta Example using XLAnalyst

25. Native Excel • Precedents/Dependents • Error Checking • Trace Error • Evaluate Formula • Watch Window • Track Changes

26. 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.

27. 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)?

28. Need for Research • Detailed survey of tools, capabilities • Comparison “Shootout” • Laboratory models • Real-world spreadsheets • Empirical Studies • Use of audit tools by end users • Do users find more errors? Are they faster? • Effect of larger models • Spreadsheet Engineering • 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

29. Thank You! • Questions?