1 / 18

Decision Making

Decision Making. The Art & Craft of Spreadsheet Modeling (adapted from a presentation by Professor Isken). Models. Simplified representation or abstraction of reality. Capture essence of system without unnecessary details Models tailored for specific types of problems

jmcclure
Download Presentation

Decision Making

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. Decision Making The Art & Craft of Spreadsheet Modeling (adapted from a presentation by Professor Isken)

  2. Models • Simplified representation or abstraction of reality. • Capture essence of system without unnecessary details • Models tailored for specific types of problems • Models help us understand the world • Prediction (What if?) • Optimization (What’s best?)

  3. Example • Enrollment in MIS 300 • Suppose there are three sections of MIS 300 • Each one starts off with 65 students • People drop the class at a constant rate • Where do we end up?

  4. A Few Types of Models • Physical model • crash test dummy • Detailed computer simulation model • flight trainer • discrete event model (e.g. SimCity) • Mathematical model • Regression • F=MA • Optimization model • Stochastic vs. deterministic

  5. Descriptive Model Describes a system in terms of parameters and variables If we change some input parameter, what will happen to our output performance measure? Prescriptive Model Suggests good or “optimal” solutions Also made up of parameters and variables Searches over many possible solutions to find “best” solution (in some sense) Descriptive vs. Prescriptive Models

  6. Why do we model for decision making? • Building model forces detailed examination and thought about a problem • structures our thinking • must articulate our assumptions, preconceived notions • Searching for general insights • form of relationship between key variables involved in decision • importance of various parameters on decisions • Looking for specific numeric answers to a decision making problem

  7. "All models are wrong; some are useful." - W. Edwards Deming Remember

  8. Example • How can we improve the MIS 300 enrollment model? • Is the drop rate constant? • What about adds? • How could we model the percentage of people attending class?

  9. A Simple Modeling Process Create model forces detailed examination and thought about a problem • structures our thinking • must articulate our assumptions, preconceived notions Validate model - Does it mimic reality well enough? Modify model Use the model to support decision making • Searching for general insights • Specific numeric answers

  10. Example • How could we validate the MIS 300 enrollment model?

  11. Exercising the ModelThings we might do • How do input and/or decision variable values affect outputs (sensitivity analysis)? • Find values of decision variables that minimize or maximize the outputs (optimization) • Create graphic representation of model parameter relationships (visualization)

  12. Basic Spreadsheet Modeling Concepts • Plan general structure and format of model • use influence diagrams for logical structure • blank spreadsheet like a “blank canvas” – plan the physical structure • Enter inputs (parameters) and decision variables • Develop relationships between them via formulas to the model outputs • Then we can “exercise the model” • use it to explore situation of interest • what if? • what’s best?

  13. A few spreadsheet design tips • Clear, logical layout of overall model • Separation of different model parts across multiple worksheets • Clear headings for different model sections and the inputs, outputs and decision variables • Use range names • Name your worksheet tabs

  14. A few more spreadsheet design tips • Use formatting • bold, italics, fonts, color, indenting, etc. • Use cell comments • Use text boxes for assumptions, lists, and other model annotations

  15. In-Class ModelingCaseFinding Break-Even Point • The Great Threads Company sells hand-knit sweaters. Great Threads is planning to print a brochure of its products and undertake a direct mail campaign. • The cost of printing the brochure is $20,000 plus $0.10 a catalog. The cost of mailing each catalog is $0.15. In addition, the company will include direct reply envelopes in it’s mailings. It incurs $0.20 in extra cost for each direct mail envelope that is used by a respondent. • The average size of a customer order is $40, and the company’s variable cost per order averages around 80% of the order’s value.

  16. Great Threads Case -- continued • The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: • How does a change in the response rate affect profit? • For what response rate does a company break even? • If the company estimates a response rate of 3%, should it proceed with the mailing? • How does the presence of uncertainty affect the usefulness of the model?

  17. GreatThreads.xls

  18. Basic Data Manipulation • Excel has numerous data manipulation and analysis tools • Data sorting • Data filtering • Pivot tables and pivot charts • Data tables

More Related