Managerial Decision Modeling with Spreadsheets

1. Managerial Decision Modeling with Spreadsheets Chapter 8 Decision Theory

2. Learning Objectives • List steps of decision making process. • Describe different types of decision making environments. • Make decisions under uncertainty when probabilities are not known. • Make decisions under risk when probabilities are known. • Use Excel for problems involving decision tables. • Develop accurate and useful decision trees. • Use TreePlan to set up and analyze decision tree problems with Excel. • Revise probability estimates using Bayesian analysis. • Understand the importance and use of utility theory in decision making.

3. 8.1 Introduction • Decision theory is analytic and systematic approach to study of decision-making. • What makes difference between good and bad decisions? • Good decisions may be defined as: • Based on logic, • Considered all possible decision alternatives, • Examined all available information about future, and • Applied decision modeling approach. • Bad decision may be defined as: • Not based on logic, • Did not use all available information, • Did not consider all alternatives, and • Did not employ appropriate decision modeling techniques.

4. 8.2 Five Steps of Decision Making • 1. Clearly define problem at hand. • 2. List allpossible decision alternatives. • 3. Identify possible future outcomes for each decision • alternative. • 4. Identify payoff (usually, profit or cost) for each • combination of alternatives and outcomes. • 5. Select one of decision theory modeling techniques, • apply decision model, and make decision.

5. Thompson Lumber Company • Step 1. Identifies problem as: • whether to expand product line by manufacturing and marketing new product which is “backyard storage sheds.” • Step 2. Generate decision alternatives available. • Decision alternative is defined as course of action or strategy that may be chosen by decision maker. Alternatives are to construct: • (1) large plant to manufacture storage sheds, • (2) small plant to manufacture storage sheds, or • (3) build no plant at all. • Step 3. Identify possible future outcomes of various alternatives.

6. Thompson Lumber Company • Step 4. Express payoff resulting from each possible combination of alternatives and outcomes. • Objective is to maximize profits. • Step 5. Select decision theory model and apply it to data to help make decision. • Type of decision model available depends on operating environment and amount of uncertainty and risk involved.

7. Thompson Lumber Company

8. 8.3 Types Of Decision Making Environments • Type 1: Decision Making under Certainty. Decision maker knows with certainty consequence of every decision alternative. • Type 2: Decision Making under Uncertainty. Decision maker has no information about various outcomes. • Type 3: Decision Making under Risk. Decision maker has some knowledge regarding probability of occurrence of each outcome or state of nature. • Examples: • Probability of being dealt club from deck of cards is 1/4. • Probability of rolling 5 on die is 1/6.

9. 8.4 Decision Making Under Uncertainty • Criteria for making decisions under uncertainty. • Maximax. • Maximin. • Equally likely. • Criterion of realism. • Minimax regret. • First four criteria calculated directly from decision payoff table. • Fifth minimax regret criterion requires use of opportunity loss table.

10. Maximax Criterion • Maximax criterion selects alternative maximizes maximum payoff over all alternatives. • First locate maximum payoff for each alternative. • Select alternative with maximum number. • Decision criterion locates alternative with highest possible gain. • Called optimistic criterion. • Table shows maximax choice is first alternative: "construct large plant." • $200,000 payoff is maximum of maximum payoffs for each decision alternative.

11. Maximax Criterion Thompson Lumber Company • Maximax criterion selects alternative that maximizesmaximum payoff over all alternatives. • First alternative, "construct a large plant”, $200,000 payoff is maximum of maximum payoffs for each decision alternative.

12. Maximin Criterion • Maximin criterion finds alternative maximizes minimum payoff over all alternatives. • First locate minimum payoff for each alternative. • Select alternative with maximum number. • Decision criterion locates alternative that has least possible loss. • Called pessimistic criterion. • Maximin choice, "do nothing," is shown in table. • $0 payoff is maximum of minimum payoffs for each alternative.

13. Maximin Criterion Thompson Lumber Company • Maximin criterion finds alternative maximizesminimum payoff over all alternatives. • First locate minimum payoff for each alternative, and select alternative with maximum number.

14. Equally Likely (Laplace) Criterion Thompson Lumber Company: • Equally likely, also called Laplace, criterion finds decision alternative with highest average payoff. • Calculate average payoff for every alternative. • Pick alternative with maximum average payoff. • Assumes all probabilities of occurrence for states of nature are equal. • Equally likely choice is second alternative, "construct a small plant." • Strategy shown in table has maximum average payoff ($40,000) over all alternatives.

15. Equally Likely (Laplace) Criterion Thompson Lumber Company • Equally likely criterion finds decision alternative with highest average payoff. • Calculate average payoff for every alternative. • Pick alternative with maximum average payoff.

16. Criterion of Realism (Hurwicz) • Often called weighted average, the criterion of realism (or Hurwicz) decision criterion is compromise between optimistic and pessimistic decision. • Select coefficient of realism, a, with value between 0 and 1. • When a is close to 1, decision maker is optimistic about future. • When a is close to 0, decision maker is pessimistic about future.  

17. Criterion of Realism • Formula for criterion of realism = • a x (maximum payoff for alternative) + • (1-a) x (minimum payoff for alternative) • Assume coefficient of realism a = 0.80. • Best decision would be to construct a large plant. • Alternative has highest weighted average payoff: $124,000 = (0.80)($200,000) + (0.20)(- $180,000).

18. Criterion of Realism Thompson Lumber Company • Coefficient of realism a = 0.80. • $124,000 = (0.80)($200,000) + (0.20)(- $180,000).

19. Minimax Regret Criterion • Final decision criterion is based on opportunity loss. • Opportunity loss, also called regret, is difference between optimal payoff and actual payoff received. • Develop opportunity loss table. • Determine opportunity loss of not choosing best alternative for each state of nature. • Opportunity loss for any state of nature, or any column, calculated by subtracting each outcome in column from best outcome in same column.

20. Minimax Regret Criterion Thompson Lumber Company • Best outcome for favorable market is $200,000 as result of first alternative, "construct a large plant." • Subtract all payoffs in column from $200,000. • Best outcome for unfavorable market is $0 as result of third alternative, "do nothing." • Subtract all payoffs in column from $0. • Table illustrates computations and shows complete opportunity loss table.

21. Minimax Regret Criterion Thompson Lumber Company • Table illustrates computations and shows complete opportunity loss table.

22. Minimax Regret Criterion Thompson Lumber Company • Once opportunity loss table has been constructed, locate maximum opportunity loss within each alternative. • Pick alternative with minimum number. • Minimax regret choice is second alternative, "construct a small plant." Regret of $100,000 is minimum of maximum regrets over all alternatives.

23. Using Excel to Solve Decision Making Problems Under Uncertainty Formulas

24. Using Excel to Solve Decision Making Problems Under Uncertainty Solutions

25. 8.5 Decision Making Under Risk • Common for decision maker to have some idea about probabilities of occurrence of different outcomes or states of nature. • Probabilities may be based on decision maker’s personal opinions about future events, or on data obtained from market surveys, expert opinions, etc. • When probability of occurrence of each state of nature can be assessed, problem environment is called decision making under risk.

26. Expected Monetary Value • Given decision table with conditional values (payoffs) and probability assessments, determine expected monetary value (EMV) for each alternative. • Computed as weighted average of all possible payoffs for alternative, where weights are probabilities of different states of nature:  • EMV (alternative i) = • (payoff of first state of nature) x (probability of first • state of nature) + • (payoff of second state of nature) x (probability of second • state of nature) + . . . + • (payoff of last state of nature) x (probability of last • state of nature)

27. Expected Monetary Value Thompson Lumber Company • Probability of favorable market is same as probability of unfavorable market. • Each state of nature has a 0.50 probability.

28. Expected Opportunity Loss • Alternative approach in decision making under risk is to minimize expected opportunity loss (EOL). • Opportunity loss, also called regret, refers to difference between optimal profit or payoff and actual payoff received. • EOL for an alternative is sum of all possible regrets of alternative, each weighted by probability of state of nature for that regret occurring. • EOL (alternative i) = (regret of first state of nature) • x (probability of first state of nature) • + (regret of second state of nature) • x (probability of second state of nature) • + . . . + (regret of last state of nature) • x (probability of last state of nature)

29. EOL Decision Thompson Lumber Company • EOL values are computed as shown. • Using minimum EOL as decision criterion, best decision would be second alternative, "construct a small plant" with an EOL of $60,000. • Minimum EOL will alwaysresult in same decision alternative as maximum EMV.

30. Expected Value of Perfect Information • Expected value with perfect information is expected or average return, if one has perfect information before decision has to be made. • Choose best alternative for each state of nature and multiply its payoff times probability of occurrence of that state of nature: • Expected value with perfect information (EV with PI) = • (best payoff for first state of nature) • x (probability of first state of nature) • + (best payoff for second state of nature) • x (probability of second state of nature) • + . . . + (best payoff for last state of nature) • x (probability of last state of nature) • EVPI = EV with PI - maximum EMV

31. EV with PI and EVPI • Best outcome for state of nature "favorable market" is "build a large plant" with a payoff of $200,000. • Best outcome for state of nature "unfavorable market" is "do nothing," with payoff of $0. • Expected value with perfect information • (EV with PI) = ($200,000)(0.50) + ($0)(0.50) • = $ 100,000. • If one had perfect information, an average payoff of $100,000 if decision could be repeated many times. • Maximum EMV or expected value without perfect information, is $40,000. • EVPI = EV with PI - maximum EMV • = $100,000 - $40,000 = $60,000.

32. Using Excel to Solve Decision Making Problems Under Risk Formulas View

33. Using Excel to Solve Decision Making Problems Under Risk Formulas View

34. 8.6 Decision Trees • Any problem presented in decision table can be graphically illustrated in decision tree. • All decision trees are similar in that they contain decision nodes(or points) and state of nature nodes (or points). • These nodes are represented using following symbols: • = A decision node. Arcs (lines) originating from decision node denote all decision alternatives available at that node. • О = A state of nature (or chance) node. Arcs (lines) originating from a chance node denote all states of nature that could occur at that node.

35. Decision Tree Thompson Lumber Company • Tree usually begins with decision node. • Decision is determine whether to construct large plant, small plant, or no plant. • Once decision is made, one of two possible states of nature (favorable or unfavorable market) will occur.

36. Folding Back a Decision Tree Thompson Lumber Company • In folding back decision tree, use following two rules: • At each state of nature (or chance) node, compute expected value using probabilities of all possible outcomes at that node and payoffs associated with outcomes. • At each decision node, select alternative that yields better expected value or payoff.

37. Reduced Decision Tree Thompson Lumber Company • Using rule for decision nodes, select alternative with highest EMV. • Corresponds to alternative to build small plant. • Resulting EMV is $40,000.

38. Decision Trees for Multi-stage Decision Making Problems

39. Decision Tree With EMVs Shown Thompson Lumber Company

40. Expected Value of Sample Information • One way of measuring value of market information is to compute the expected value of sampleinformation(EVSI), as follows:

41. 8.7 Using TREEPLAN To Solve Decision Tree Problems With Excel • Use TreePlan, an add-in for Excel, to set up and solve decision tree problems. • TreePlan program consists of single Excel add-in file, TREEPLAN.XLA, which can be found on CD-ROM that accompanies text. • Creating a Decision Tree Using TreePlan • Once TreePlan is installed and loaded, follow these steps to set up and solve decision tree problem. • 1. Starting TreePlan. • Start Excel and open blank worksheet. • Place cursor in any blank cell (say, cell A1). • Select Tools|Decision Tree from Excel’s main menu.

42. Using TREEPLAN • 2. Starting a new tree. • Select New Tree. • 3. Adding decision nodes, state of nature nodes, decision alternative branches, and state of nature branches. • To bring up TreePlan menu, either select Tools|Decision Tree or press Control (Ctrl) and T keys at same time. • Actual TreePlan menu that appears each time depends on location of cursor when menu is accessed.

43. Using TREEPLAN

44. Using TREEPLAN

45. Solving Using TreePlan Thompson Lumber Company

46. Solved Decision Tree

47. 8.8 Estimating Probability Values Using Bayesian Analysis • There are many ways of getting probability data for problem. • Numbers (e.g., 0.78, 0.22, 0.27, 0.73 ) can be assessed by manager based on experience and intuition. • They can be derived from historical data or computed from other available data using Bayes’ theorem.

48. Calculating Revised Probabilities • Assume following four conditional probabilities • were known.

49. Market Survey Reliability in Predicting Actual States of Nature

50. Market Survey Reliability in Predicting Actual States of Nature