Spreadsheet Modeling & Decision Analysis

1. Spreadsheet Modeling & Decision Analysis A Practical Introduction to Management Science 5th edition Cliff T. Ragsdale

2. Chapter 1 Introduction to Modeling & Problem Solving

3. Introduction • We face numerous decisions in life and professional settings. • We can use computers to analyze the potential outcomes of decision alternatives. • Spreadsheets are often the tool of choice for today’s problem-solvers.

4. What is Operations Research? • A field of study that uses computers, statistics, and mathematics to solve problems in a variety of settings. • Also known as: • Management Science • Decision science

5. Home Runs in Operations Research • Motorola • Procurement of goods and services account for 50% of its costs • Developed an Internet-based auction system for negotiations with suppliers • The system optimized multi-product, multi-vendor contract awards • Benefits: • $600 million in savings

6. Home Runs in Operations Research • Waste Management • Leading waste collection company in North America • 26,000 vehicles service 20 million residential & 2 million commercial customers • Developed vehicle routing optimization system • Benefits: • Eliminated 1,000 routes • Annual savings of $44 million

7. Home Runs in Operations Research • Hong Kong International Terminals • Busiest container terminal in the world • 122 yard cranes serve 125 ships per week • Thousands of trucks move containers in & out of storage yard • Used OR to optimize operational decisions involving trucks, cranes & storage locations • Benefits: • 35% reduction in container handling costs • 50% increase in throughput • 30% improvement in vessel turnaround time

8. Home Runs in Operations Research • John Deere Company • 2500 dealers sell lawn equipment & tractors with support of 5 warehouses • Each dealer stocks 100 products, creating 250,000 product-stocking locations • Demand is highly seasonal and erratic • Developed inventory system to optimize stocking levels over a 26-week horizon • Benefits: • $1 billion in reduced inventory • Improved customer-service levels

9. What is a “Computer Model”? • A set of mathematical relationships and logical assumptions implemented in a computer as an abstract representation of a real-world object of phenomenon. • Spreadsheets provide the most convenient way for business people to build computer models.

10. The Modeling Approach to Problem Solving • Everyone uses models to make decisions. • Types of models: • Mental (arranging furniture) • Visual (blueprints, road maps) • Physical/Scale (aerodynamics, buildings) • Mathematical (what we’ll be studying)

11. Characteristics of Models • Models are usually simplified versions of the things they represent • A valid model accurately represents the relevant characteristics of the object or decision being studied

12. Benefits of Modeling • Economy - It is often less costly to analyze decision problems using models. • Timeliness - Models often deliver needed information more quickly than their real-world counterparts. • Feasibility - Models can be used to do things that would be impossible. • Models give us insight & understanding that improves decision making.

13. Example of a Mathematical Model Profit = Revenue - Expenses or Profit = f(Revenue, Expenses) or Y = f(X1, X2)

14. AGeneric Mathematical Model Y = f(X1, X2,…,Xn) Where: Y = dependent (response) variable (aka bottom-line performance measure) Xi = independent (explanatory) variables (inputs having an impact on Y) f(.) = function defining the relationship between the Xi & Y

15. Mathematical Models & Spreadsheets • Most spreadsheet models are very similar to our generic mathematical model: Y = f(X1, X2,…,Xn) • Most spreadsheets have input cells (representing Xi) to which mathematical functions ( f(.)) are applied to compute a bottom-line performance measure (or Y).

16. Categories of Mathematical Models Model Independent OR/MS Category Form of f(.) Variables Techniques Prescriptive known, known or under LP, Networks, IP, well-defined decision maker’s CPM, EOQ, NLP, control GP, MOLP Predictive unknown, known or under Regression Analysis, ill-defined decision maker’s Time Series Analysis, control Discriminant Analysis Descriptive known, unknown or Simulation, PERT, well-defined uncertain Queueing, Inventory Models

17. The Problem Solving Process Formulate & Implement Model Identify Problem Analyze Model Test Results Implement Solution unsatisfactory results

18. The Psychology of Decision Making • Models can be used for technical aspects of decision problems. • Other aspects cannot be modeled easily, requiring intuition and judgment. • Caution: Human judgment and intuition is not always rational!

19. Anchoring Effects • Arise when trivial factors influence initial thinking about a problem. • Decision-makers usually under-adjust from their initial “anchor”. • Example: • What is 1x2x3x4x5x6x7x8 ? • Median answer 512 • What is 8x7x6x5x4x3x2x1 ? • Median answer 2,250 • 8! = 40,320

20. Framing Effects • Refers to how decision-makers view alternatives in a problem, often from a win-loss perspective. • The way a problem is framed often influences choices in irrational ways… • Suppose you’ve been given $1000 and must choose between: • A. Receive $500 more immediately • B. Flip a coin and receive $1000 more if heads occurs or $0 more if tails occurs

21. Framing Effects(Example) • Now suppose you’ve been given $2000 and must choose between: • A. Give back $500 immediately • B. Flip a coin and give back $0 if heads occurs or give back $1000 if tails occurs

22. Payoffs $1,500 Alternative A Initial state Heads (50%) $2,000 Alternative B (Flip coin) $1,000 Tails (50%) A Decision Tree for Both Examples

23. Good Decisions vs. Good Outcomes • Good decisions do not always lead to good outcomes... • A structured, modeling approach to decision making helps us make good decisions, but can’t guarantee good outcomes.

24. End of Chapter 1