Spreadsheet Modeling & Decision Analysis

1. Spreadsheet Modeling & Decision Analysis

2. Introduction to Decision Analysis • Models help managers gain insight and understanding, but they can’t make decisions. • Decision making often remains a difficult task due to: • Uncertainty regarding the future • Conflicting values or objectives • Consider the following example...

3. Deciding Between Job Offers • Company A • In a new industry that could boom or bust. • Low starting salary, but could increase rapidly. • Located near friends, family and favorite sports team. • Company B • Established firm with financial strength and commitment to employees. • Higher starting salary but slower advancement opportunity. • Distant location, offering few cultural or sporting activities. • Which job would you take?

4. Good Decisions vs. Good Outcomes • A structured approach to decision making can help us make good decisions, but can’t guarantee good outcomes. • Good decisions sometimes result in bad outcomes.

5. Characteristics of Decision Problems • Alternatives - different courses of action intended to solve a problem. • Work for company A • Work for company B • Reject both offers and keep looking • Criteria - factors that are important to the decision maker and influenced by the alternatives. • Salary • Career potential • Location • States of Nature - future events not under the decision makers control. • Company A grows • Company A goes bust • etc

6. An Example: Magnolia Inns • Hartsfield International Airport in Atlanta, Georgia, is one of the busiest airports in the world. • It has expanded many times to handle increasing air traffic. • Commercial development around the airport prevents it from building more runways to handle future air traffic. • Plans are being made to build another airport outside the city limits. • Two possible locations for the new airport have been identified, but a final decision will not be made for a year. • The Magnolia Inns hotel chain intends to build a new facility near the new airport once its site is determined. • Land values around both possible sites for the new airport are increasing as investors speculate that property values will increase greatly in the vicinity of the new airport. • See data in file Fig15-1.xls

7. The Decision Alternatives 1) Buy the parcel of land at location A. 2) Buy the parcel of land at location B. 3) Buy both parcels. 4) Buy nothing.

8. The Possible States of Nature 1) The new airport is built at location A. 2) The new airport is built at location B.

9. Constructing a Payoff Matrix See file Fig15-1.xls

10. Decision Rules • If the future state of nature (airport location) were known, it would be easy to make a decision. • Failing this, a variety of nonprobabilistic decision rules can be applied to this problem: • Maximax • Maximin • Minimax regret • No decision rule is always best and each has its own weaknesses.

11. State of Nature Decision 1 2 MAX A 30 -10000 30 B 29 29 29 The Maximax Decision Rule • Identify the maximum payoff for each alternative. • Choose the alternative with the largest maximum payoff. • See file Fig15-1.xls • Weakness • Consider the following payoff matrix <--maximum

12. Weakness • Consider the following payoff matrix State of Nature Decision 1 2 MIN A 1000 28 28 B 29 29 29 The Maximin Decision Rule • Identify the minimum payoff for each alternative. • Choose the alternative with the largest minimum payoff. • See file Fig15-1.xls <--maximum

13. Probabilistic Methods • At times, states of nature can be assigned probabilities that represent their likelihood of occurrence. • For decision problems that occur more than once, we can often estimate these probabilities from historical data. • Other decision problems (such as the Magnolia Inns problem) represent one-time decisions where historical data for estimating probabilities don’t exist. • In these cases, subjective probabilities are often assigned based on interviews with one or more domain experts. • Interviewing techniques exist for soliciting probability estimates that are reasonably accurate and free of the unconscious biases that may impact an expert’s opinions. • We will focus on techniques that can be used once appropriate probability estimates have been obtained.

14. Weakness • Consider the following payoff matrix State of Nature Decision 1 2 EMV A 15,000 -5,000 5,000 B 5,000 4,000 4,500 Probability 0.5 0.5 EMV Caution • The EMV rule should be used with caution in one-time decision problems. <--maximum

15. Land Purchase Decision Airport Location Payoff 13 A 31 Buy A 1 -18 6 -12 B A -8 4 Buy B 2 -12 23 B 11 0 5 A 35 Buy A&B 3 -30 29 -1 B A 0 0 Buy nothing 4 0 0 B 0 A Decision Tree for Magnolia Inns

16. Land Purchase Decision Airport Location Payoff 0.4 13 A 31 Buy A 1 EMV=-2 -18 6 -12 B 0.6 0.4 A -8 4 Buy B 2 -12 EMV=3.4 23 B 11 0.6 0 0.4 5 A 35 Buy A&B EMV=3.4 3 -30 EMV=1.4 29 -1 B 0.6 0.4 A 0 0 Buy nothing 4 EMV= 0 0 0 B 0 0.6 Rolling Back A Decision Tree

17. Using TreePlan

18. About TreePlan • TreePlan is a shareware product developed by Dr. Mike Middleton at the Univ. of San Diego • TreePlan is an Excel add-in for decision trees. • If you like this software package and plan to use for more than 30 days, you are expected to pay a nominal registration fee. • Details on registration are available near the end of the TreePlan help file. See file Fig15-14.xls

19. Anatomy of TreePlan Decision Tree

20. Problem: Marketing Cellular Phones The design and product-testing phase has just been completed for Sonorola’s new line of cellular phones. Three alternatives are being considered for a marketing/production strategy for this product: 1. Aggressive (A) • Major commitment from the firm • Major capital expenditure • Large inventories of all models • Major global marketing campaign

21. 2. Basic (B) • Move current production to Osaka • Modify current line in Tokyo • Inventories for only most popular items • Only local or regional advertising 3. Cautious (C) • Use excess capacity on existing phone lines to produce new products • Minimum of new tooling • Production satisfies demand • Advertising at local dealer discretion Management decides to categorize the level of demand as either strong (S) or weak (W).

22. Managements best estimate of the probability of a strong or weak market. Net profits measured in millions of dollars. The optimal decision if you are risk-indifferent is to select B which yields the highest expected payoff.

23. TreePlan

24. Lab Problem: DriveTekProblem • DriveTek Research discovers that a computer company wants a new storage device for a proposed computer system • The company will subcontract the development to a research firm • The company has offered a fee of $250,000 for the best proposal • DriveTek wants to enter the competition • Management estimates a cost of $50,000 to prepare a proposal with a 50-50 chance of winning the contract

25. DriveTek Problem continued • DriveTek’s engineers are not sure about how they will develop the storage device • Three alternative approaches can be tried: • A mechanical method with a cost of $120,000 and the engineers are certain they can develop a successful model with this approach • An electronic approach, with an estimated cost of $50,000 to develop a model. Engineers estimate only a 50% chance of a satisfactory approach • A magnetic component approach which will cost $80,000 with a 70% chance of success

26. DriveTek Problem continued • DriveTek’s can only work on one approach at a time and has time to try only two approaches. • If it tries either the magnetic or electronic method and the attempt fails, the second choice must be the mechanical method to guarantee a successful model • DriveTek needs help in incorporating this information into a decision to proceed or not.

27. Class Exercise in Creating a Decision Tree: A Glass Factory • Complete the following exercise using TreePlan. • Vary the inputs to determine when the optimal decision will change.

28. Class Exercise: A Glass Factory A glass factory specializing in crystal is experiencing a substantial backlog, and the firm's management is considering three courses of action: A) Arrange for subcontracting – cost is 12 (thousand) B) Construct new facilities – cost is 18 (thousand) C) Do nothing (no change) – cost is 0 The correct choice depends largely upon demand, which may be low, medium, or high. By consensus, management estimates the respective demand probabilities as 0.1, 0.5, and 0.4. Given the payoffs on the next page, create and solve this problem using a decision tree.

29. A Glass Factory: The Payoff Table The management estimates the profits when choosing from the three alternatives (A, B, and C) under the differing probable levels of demand. These profits, in thousands of dollars are presented in the table below: