IT Model Validation and Spreadsheet Errors

1. IT Model Validation and Spreadsheet Errors Consultation for Broker’s Inc. La StreichMoor Inc.

3. Overview • Broker’s Inc. • IT Model Validation • Spreadsheet Horror Stories • Spreadsheet Errors • Interview with Martin Erwig • Application for Broker’s Inc. La StreichMoor Inc.

4. Brokers Inc. • Online Brokerage Service • Operates in all 50 States • Online Account Holders Make Up Majority of Business • Problems with Spreadsheet Related Errors • Constantly Updating Data and Formulas Within Database La StreichMoor Inc.

5. Brokers Inc. Questions • How can Model Validation Improve Our Use of Client Related Spreadsheets? • Pro’s and Con’s of Using Model Validation • Cost of Implementing Model Validation La StreichMoor Inc.

6. Model Validation • Validation is the process of determining the degree to which a model or simulation is an accurate representation of the real world from the perspective of the intended uses of the model or simulation. • Does it give an accurate output given the inputs? • Does it achieve the intended purpose of the model? • Is it both valid and credible?

7. Model Validation • Four Categories of Errors • Project Management Errors • Data and Data Model Errors • Logic Model Errors • Experimentation Errors • Human Error Factor (HEF) • Data entry errors • Applications for Broker’s Inc.

8. IT Model Validation (ITMV) • Represents Financial Reality Amongst Data • Does Model Perform Appropriately? • Purpose of Intended Use • Practical Validation • Establishing Credibility • Exploits Redundancies • Looks for Inconsistencies • Proving Correctness is Virtually Impossible La StreichMoor Inc.

9. ITMV Within Excel • How do you know if your numbers and formulas are correct? • Human Judgment was all we had before ITMV • Excel, much like queries will do as you say • Apples to Oranges • Reasoning about units • Helps exploit the redundancies in a spreadsheet La StreichMoor Inc.

10. Spreadsheet Errors In Brief Types of Errors Quantitative and Qualitative Mechanical Logical Omission Effective Steps for Accuracy Built-in auditors Server based application system “11 of 25 spreadsheets contained errors with non-zero impacts. Among the ten spreadsheets with non-zero impacts for which error size was reported, all 10 had an error that exceeded $100,000, 6 had errors exceeding $10 million, and 1 had an error exceeding $100 million. (Powell, Lawson, and Baker, 2007) Spreadsheet Errors La StreichMoor Inc.

11. Spreadsheet Precautions • Still Little Knowledge But Still We Can Take Precautions • Have a preliminary design in mind • Fairly formal planning could reduce problems significantly • Plans in writing to give guidance • Construction, assumption and cell protection • “Cleanroom Development” (checking formulas) • Entering formulas without understanding leads to subtle but important errors La StreichMoor Inc.

12. Oculus Visualizer Demo Purpose Benefits From This Program Analyzing and monitoring Comprehensive Is This A Solution? Results Oculus Excel Visualizer La StreichMoor Inc.

13. Oculus Excel Visualizer La StreichMoor Inc.

14. This Could Happen To You! • True stories on costly spreadsheet errors • Exam scores botched by mis-sorting • Understating the benefit of unbundling by $50M • Accounting error forces bank to $3b write-down La StreichMoor Inc.

15. Interview with Dr. Erwig • GoalDebug-Spreadsheet software • Erwig and Robin Abraham created in 2005 • Capitalizes on common errors in spreadsheets, and exploits them • Finds potential and reasonable options for solving spreadsheet problems La StreichMoor Inc.

16. Why take on Spreadsheet Errors? • Spreadsheets usage is prevalent • Estimated that 90% of all spreadsheets contain errors • Mistakes are costly • Fidelity's Magellan fund • “The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain on this separate spreadsheet. This meant that the dividend estimate spreadsheet was off by $2.6 billion....” La StreichMoor Inc.

17. Benefits of Erwig’s Program • Helps detect errors • Click on problem cell, and different potential solutions arise • Potential for saving thousands, millions, billions • 80% of the time, the correct solution is among the top 5 suggestions La StreichMoor Inc.

18. Recommendations for Brokers Inc. • Red Rover Software • Helpful due to the dynamisms of business • Change=Error • Stock market is always changing La StreichMoor Inc.

19. Cost of Implementation • Estimated that Red Rover would cost about $700.00 per P.C. • Comes with helpful tutorial • Comes with 2 sister programs: • Ugowego-provides any Excel user with the power of browser-like navigation inside a spreadsheet • Audit-  enables managers, spreadsheet modelers, and internal auditors to quickly verify spreadsheet integrity while providing a documented audit trail • Could push Broker’s Inc. to the edge of Brokerage field, with advanced accuracy La StreichMoor Inc.

20. Red Rover Cont. & Other Suggestions • Excellent cost advantage over time • Reducing the “HEF” or Human Error Factor by training • Workshops are now being worked into middle school curriculum to expect HEF • Younger generation could potentially be much more effective at reducing spreadsheet errors • Microsoft Excel Data Analysis and Business Modeling • Wayne L. Winston • Incorporating Incremental Validation and Impact Analysis Into Spreadsheet Maintenance: An Empirical Study • Krishna et. al.

21. Recap • ITMV and its benefits and importance to model verification • We learned the great potential for spreadsheet DISASTERS, and real life repercussions • GoalDebug, innovations at OSU • Potential solutions for Broker’s Inc. • Oculus-spreadsheet visualizer • Red Rover-ITMV software

22. The End… Questions?