Assignment 1 for DFMW [Hetu Patel]

Published on: **Mar 4, 2016**

- 1. Prevalence of Spreadsheet Errors<br />MBA 236G-01B: Designing Financial Models that Work<br />Hetu Patel<br />5th April, 2010<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />1<br />
- 2. People tend to underestimate prevalence of errors in spreadsheets<br />Like in driving, a large majority of all people rate themselves as above average when it comes to spreadsheet skills<br />Brown and Gould (Brown & Gould, 1987) gave three spreadsheet development tasks to nine highly experienced spreadsheet developers, all made at least one error, and 63% of the spreadsheets overall had errors.<br />Yet, the experts were “very confident” about the correctness of the spreadsheet<br />People are reported to have more confidence in large, well formatted spreadsheets, than smaller spreadsheets (regardless of formatting).<br />However, in reality, larger spreadsheets are more prone to error<br />To err is human… but, overconfidence just prevents from taking steps to mitigate risk.<br />One source of this overconfidence is lack of testing and thorough review.<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />2<br />
- 3. Summary of results from separate spreadsheet studies<br /> Summary compiled by Professor Ray Panko, at the University of Hawaii,<br /><ul><li>These numbers seem high, but a conservative estimate of cell error rates is 1%. Assuming this is true for unique formulas, spreadsheets containing from 150 to 350 unique formulae, we find that the probability of an individual spreadsheet containing an error is between 78% and 97%.</li></ul>Evidence from field audits of spreadsheets reveal disturbing error rates<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />3<br />
- 4. Types of Spreadsheet Errors<br />Spreadsheet = Data + Formulas<br /> Therefore, errors in spreadsheet are due to errors in data or errors in formulas…<br />Quantitative Errors (makes the current spreadsheet incorrect)<br />Mechanical errors arise from typing or pointing errors.<br />Logic errors arise from choosing the wrong function or creating the wrong formula.<br />Omission errors arise from misinterpretation of the situation to be modeled.<br />Qualitative Errors (poses threat to make future versions incorrect)<br />Hard-coding numbers into formulas<br />Long formulas<br />Poor physical layout<br />Lack of sufficient documentation.<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />4<br />
- 5. Proper actions can be taken to mitigate spreadsheet error risk<br />Awareness of common errors<br />Acknowledging errors is first step to fix them<br />Training <br />Spreadsheet development is so dispersed and very few do spreadsheet development as their main task. Hence, imparting proper training to all becomes essential.<br />Adopting Best Practices<br />Best practices must be ingrained and come naturally<br />Adequate testing<br />Adopt proper testing methods and allocate sufficient time for testing spreadsheet models<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />5<br />
- 6. Examples of Some costly spreadsheet errors…<br />Forecast sales of $55 million for planned new computer product. Forgot planned price discount on key components to one supplier. Forecast was $8 million over. It was caught in time to prevent damage.<br />End users shared diskettes containing old data. In one case, a person ordered 30,000 units of a $4 part; but the plan had changed and the company only needed 1,500.<br />A Midwestern firm's estimated taxes were $5,000 off from a correct paper and pencil value. The spreadsheet had an incorrect formula for assessing salvage value.<br />Two spreadsheets with 15,000 cells were used to project the market for CAD equipment. Numbers were rounded off to whole dollars. But the inflation multiplier, which should have been 1.06, became 1. Without inflation, the market was underestimated by $36 million.<br />References<br />Business Week "How Personal Computers Can Trip Up Executives," (2861) September 24, 1984, pp. 94-102 passim.<br />http://www.louisepryor.com/showTopic.do?topic=31<br />http://www.iacis.org/iis/2005_iis/PDFs/Benham_Giullian.pdf<br />http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Errors.pdf<br />http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm<br />Hetu Patel<br />Prevalence of Spreadsheet Errors<br />6<br />