1 / 17

Spreadsheet Engineering

My Point of View. I used to teach management science to MBAsI now teach spreadsheet modeling (with optimization and simulation)Half our students take an elective course in modeling (including Optimization and Simulation)Many use it intensively during their summer jobs and early in their careers.

irina
Download Presentation

Spreadsheet Engineering

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Spreadsheet Engineering Stephen G. Powell Tuck School of Business Dartmouth College INFORMS Teaching of Management Science Workshop July 29, 2005

    2. My Point of View I used to teach management science to MBAs I now teach spreadsheet modeling (with optimization and simulation) Half our students take an elective course in modeling (including Optimization and Simulation) Many use it intensively during their summer jobs and early in their careers

    3. Question for Participants Do you teach with spreadsheets?

    4. Question for Participants Do you teach: management science using spreadsheets OR spreadsheet modeling including management science?

    5. What do we know about spreadsheet use? Bugs are rampant End-users are overconfident The end-user design process is inefficient Missed opportunities abound for finding business insights

    6. Spreadsheet State-of-the-Art “Overall, these studies show that many spreadsheets are large, complex, important, and affect many people. Yet development tends to be quite informal, and even trivial controls such as cell protection are not used in most cases. In programming, code inspection and data testing are needed to reduce error rates after a module is developed. Yet code inspection is very infrequent, and while data testing is done, it lacks such rigors as use of out-of-bounds data. In general, end-user development in spreadsheeting seems to resemble programming practice in the 1950s and 1960s.” -Ray Panko

    7. Spreadsheet Engineering: A Structured Approach Design Build Test Analyze

    8. Goals of Spreadsheet Engineering Efficiency Build it once Build it right Effectiveness Solve the right problem Generate useful business insights

    9. Designing a Spreadsheet Rule 1: Plan Rule 2: Modularize Rule 3: Start small and prototype Rule 4: Parameterize Rule 5: Design for use Rule 6: Keep it simple Rule 7: Design for understanding Rule 8: Document important data and assumptions

    10. Building a Spreadsheet Rule 1: Follow a plan Rule 2: Build one module at a time Rule 3: Predict the outcome of each formula Rule 4: Copy and Paste formulas carefully Rule 5: Use relative and absolute addressing to simplify copying Rule 6: Use the Function Wizard to ensure correct syntax Rule 7: Use Range Names to make formulas easy to read

    11. Testing a Spreadsheet Rule 1: Check that numerical results look plausible Use a calculator Test extreme cases Make rough estimates Rule 2: Check that formulas are correct Check formulas individually Display individual cell references Display all formulas (Control ~) Use auditing tools Rule 3: Test that model performance is plausible Rule 4: Have a friend test it out

    12. Analyzing with a Spreadsheet Base case What-if analysis Data Sensitivity Tornado Chart Breakeven analysis Goal Seek What’s best? (optimization) Solver Sensitivity What risks? (simulation) Crystal Ball Sensitivity

    13. Concluding Remarks Spreadsheets are the “killer application” of the personal computer As a consequence, most of the world’s modeling is done in Excel If we want to influence modeling, we need to teach spreadsheet modeling If we want to encourage modelers to use management science, we need to teach them management science in spreadsheets If we want spreadsheet modelers to be effective we need to teach them spreadsheet engineering Spreadsheet engineering provides a logical framework within which to introduce management science to end-users

    14. Appendix: Web Sources on Spreadsheet Errors and Best Practices Ray Panko on spreadsheeet errors: http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm John Raffensberger on spreadsheet style: http://www.mang.canterbury.ac.nk/people.jraffen/spreadsheets/index.html Patrick O’Beirne on spreadsheet testing: http://www.sysmod.com

    15. Appendix: Web Sources on Spreadsheet Errors and Best Practices - Continued European Spreadsheet Risks Interest Group: http://www.gre.ac.uk/~cd02/EUSPRIG/projects.htm PWC Spreadsheet Modeling Best Practice: http://www.pwcglobal.com/uk/eng/about/svcs/bd/pub.html#2 Mailbarrow’s 52 ways to prevent spreadsheet problems: http://www.mailbarrow.com

    16. Appendix: Web Source for the Tuck Sensitivity Toolkit http://mba.tuck.dartmouth.edu/toolkit/

    17. Appendix: Web Source Spreadsheet Professional http://www.spreadsheetinnovations.com

More Related