DECISION MODELING WITH MICROSOFT EXCEL Chapter 8 DECISION

DECISION MODELING WITH MICROSOFT EXCEL Chapter 8 DECISION ANALYSIS Part 1 Copyright 2001 Prentice Hall Publishers and Ardith E. Baker

DECISION ANALYSIS Introduction Decision analysis provides a ______for analyzing a wide variety of management models. The framework establishes 1. A system of ______decision models based on the amount of information about the model that is available 2. A decision _____(a measure of the “goodness” of fit). Decision analysis treats decisions against nature (_____over which you have no control) and the returns accrue only to the decision maker.

In decision analysis models, the fundamental piece of data is a______. In this table, the _____decisions are listed along the side. State of Nature 2 … m Decision 1 d 1 r 12 … r 1 m d 2 r 21 r 22 … r 2 m … … … dn rn 1 rn 2 … rnm The ________are listed across the top. The center values are the _______for all possible combinations of decisions and states of nature.

The decision process is as follows: 1. Select one of the _____decisions (di). 2. After your decision is made, a state of nature occurs that is beyond your_______. 3. The associated _____can then be determined from the payoff table (rij). The decision that we select depends on our belief concerning what nature will do (i. e. , which state of nature will______). To help us make the decision, several ______about nature’s behavior will be made. Each assumption leads to a different _____for selecting the “best” decision.

DECISION ANALYSIS Three Classes of Decision Models The three classes are Decisions Under Certainty Decisions Under Risk Decisions Under Uncertainty

DECISIONS UNDER CERTAINTY A decision under _____is one in which you know (with certainty) which ________ will occur. For example, in the morning you are deciding whether to take your umbrella to work and you know for sure that it will be raining when you leave work in the afternoon. The payoff table for this model is: Rain Take Umbrella 0 Do Not -7. 00 It costs $7. 00 to have your suit cleaned if you get caught in the rain.

All LP, ILP and NLP models as well as other ______models such as the EOQ model can be thought of as decisions against _____in which there is only one state of nature. For example, consider the following LP model: Max 5000 E + 4000 F s. t. 10 E + 15 F < 150 20 E + 10 F < 160 30 E + 10 F > 135 E – 3 F < 0 E+F>5 E, F > 5

For this model, we know (with____) exactly what return we get for each decision. These returns can thus be listed in the payoff table in one_____, representing one state of nature which is certain to occur. Decision State of Nature E = 0, F = 0 - E = 5, F = 4 41, 000 … 44, 000 … … E = 6, F = 3. 5 … It is easy to solve a model with _______state of nature. Simply select the decision that yields the highest return.

DECISIONS UNDER RISK In most models, there is a lack of certainty about ______events. In quantitative modeling, the lack of certainty can be dealt with in various ways. Definition of Risk: _____refers to a class of _____ decision models for which there is more than one state of nature. In addition, we assume that there is a _____ estimate for the occurrence of each of the various states of nature. The probability of state of nature j occurring is generally estimated using historical_____. Otherwise, subjective estimates are made.

The expected value of any _________is the weighted average of all possible values of the random variable, where the weights are the _______of the values occurring. E(X) = Spixi The expected return (ERi) associated with decision i is m ERi = Srijpj = ri 1 p 1 + ri 2 p 2 + … + rimpm j=1 The decision is based on the maximum expected return. In other words, i* is the _______decision where: ERi* = maximum overall i of ERi

The Newsvendor Model: A newsvendor can buy the Wall Street Journal newspapers for 40 cents each and sell them for 75 cents. However, he must buy the papers before he knows how many he can actually sell. If he buys more papers than he can sell, he disposes of the excess at no _____cost. If he does not buy enough papers, he loses ______sales now and possibly in the future. Suppose that the loss of future sales is captured by a loss of ______cost of 50 cents per unsatisfied customer.

The demand distribution is as follows: P 0 = Prob{demand = 0} = 0. 1 P 1 = Prob{demand = 1} = 0. 3 P 2 = Prob{demand = 2} = 0. 4 P 3 = Prob{demand = 3} = 0. 2 Each of these four values represent the _______ nature. The number of papers ordered is the _____. The returns or ______are as follows:

State of Nature (Demand) Decision 0 1 2 3 0 0 -50 -100 -150 1 -40 35 -15 -65 2 -80 -5 70 20 3 -120 -45 30 105 Payoff = 75(# papers sold) – 40(# papers ordered) – 50(unmet demand) Where 75¢ = selling price 40¢ = cost of buying a paper 50¢ = cost of loss of goodwill

Now, the ER is calculated for each decision i : ER 0 = 0(0. 1) – 50(0. 3) – 100(0. 4) – 150(0. 2) = -85 ER 1 = -40(0. 1) + 35(0. 3) – 15(0. 4) – 65(0. 2) = -12. 5 ER 2 = -80(0. 1) – 5(0. 3) + 70(0. 4) + 20(0. 2) = 22. 5 ER 3 = -120(0. 1) – 45(0. 3) + 30(0. 4) – 105(0. 2) = 7. 5 State of Nature (Demand) 0 1 2 3 ER 0 0 -50 -100 -150 -85 1 -40 35 -15 -65 -12. 5 2 -80 -5 70 20 22. 5 3 -120 -45 30 105 7. 5 0. 1 0. 3 0. 4 0. 2 Decision Prob.

Another way to compare the decisions is to look at a _______of their risk profiles: The _____shows all the possible outcomes with their associated probabilities for a given decision and graphically aids in ____making.

The Cost of Lost Goodwill: A Spreadsheet Sensitivity Analysis This decision is based on the cost of_____, whose value is much less certain than selling price and ______cost. Now, perform a _____analysis to determine what would happen to the optimal decision if the _____of lost goodwill were different. An Excel spreadsheet is highly suitable to calculate the payoff ______and expected returns.

Here is the spreadsheet model for the decision table:

Using the _______command, a table of expected returns can easily be generated for a range of Goodwill Costs. After copying the entire “Base Case” into a new _______, enter 0 in cell A 16, then click on Edit – Fill – Series in Columns with a step value of 5. Click on Data – Table and enter $B$3 as the column input cell.

Now, graph the result of the Data Table to help sort out all the______. Highlight the range of data (A 16: E 46) and click on the ______icon. In the resulting dialog, choose the Line graph and click Next. In the next dialog, click on the ______tab and indicate that the Category (X) axis labels are found in A 16: A 46. Click Next and enter titles or labels for the graph. Click _______when done to display the graph.

Here is the resulting graph. For a Goodwill Cost less than 125 cents, the optimal decision is to order 2 papers. For a Goodwill Cost of 125 cents, alternative optima exists: order 2 or 3 papers.

DECISIONS UNDER UNCERTAINTY In decisions under_____, there is more than one possible state of nature. However, now the decision maker is _____or unable to specify the probabilities that the various states of nature will occur. In this case, there are several approaches. Laplace Criterion: The ______criterion approach interprets the condition of “uncertainty” as equivalent to assuming that all states of nature are _______to occur. For example, in the newsvendor model, assuming all states are equally likely means that since there are four states, each state occurs with ____ 0. 25.

Using the Laplace (equally likely) criterion, here are the resulting returns: Each state of nature has equal probability of occurring = 0. 25 Choose the max. return

DECISIONS UNDER UNCERTAINTY Maximin Criterion: The _____criterion is an extremely conservative, or____, approach to making decisions. Maximin evaluates each decision by the _______ possible return associated with the decision. Then, the decision that yields the ____value of the minimum returns (maximin) is selected.

Maximin is often used in situations where the planner feels he or she cannot afford to be_____. Consider the following example decision table: Based on the Maximin criterion, you would choose decision 1. However, is the best decision?

DECISIONS UNDER UNCERTAINTY Maximax Criterion: The _____criterion is an _____decision making criterion. This method evaluates each decision by the ______possible return associated with that decision. The decision that yields the ______of these maximum returns (maximax) is then selected.

Consider the following example decision table: Based on the Maximax criterion, you would choose decision 2. However, is the best decision?

DECISIONS UNDER UNCERTAINTY Regret and Minimax Regret: ______measures the __________of an outcome. The decision is made on the least regret for making that choice. So far, all the decision criteria have been used on a ____table of dollar returns as measured by net cash flows. The calculated regret indicates how much better we can do as far as making a choice. “Regret” is synonymous with the “_______” of not making the best decision for a given state of nature. The following table shows the regret for each combination of decision and state of nature.

To build the Regret table, first choose the ______value in column 1: Now, _____every value in that column from this value: The resulting values are the regrets for the ______decision and state of nature. Decision State of Nature 1 2 0 0 0 - 1 3 85 170 255 0 – (-40 ) = 40 0 85 170 2 0 – (-80 ) = 80 40 0 85 3 0–( -120 ) = 120 80 40 0 0 =0 Repeat these steps for the remaining columns.

Once the Regret table is built, choose the ______value in each row: State of Nature (Demand) Decision 0 1 2 3 Max. Regret 0 0 85 170 255 1 40 0 85 170 2 80 40 0 85 85 3 120 80 40 0 120 Then, of these maximum values, choose the _____[i. e. , the decision that minimizes the maximum regret (______criterion)].

DECISIONS UNDER UNCERTAINTY So, using the 3 criteria under uncertainty, we made the following decisions regarding the newsvendor data: Criteria Maximin Cash Flow Decision Order 1 paper Maximax Cash Flow Order 3 papers Minimax Regret Order 2 papers Note that when making decisions without probabilities, the three criteria listed above can result in different “optimal” solutions.

DECISION ANALYSIS The Expected Value of Perfect Information: Newsvendor Model Under Risk Let’s return to the newsvendor model under risk (with the known _______distribution on demand) in order to introduce the concept of the expected value of information. The newsvendor, without knowing the ____ demand, orders the newspapers based on the ________of demand. At the end of the day, the demand is revealed to the newsvendor and an actual _____can be determined by his order-size decision and the demand.

What if, the newsvendor can purchase “perfect ______” on the demand for his newspapers which would enable him to make better decisions? The question that we need to answer is: What is the largest ____the newsvendor should be willing to pay for this perfect information? This fee is called the _______of perfect information (EVPI): EVPI = (expected return with new deal) – (expected return with current sequence of events) The EVPI gives an ______on the amount that you should be willing to pay for the “perfect information. ”

With perfect information, the newsvendor will always order the number of papers that will give him the _____return for the state of nature that will occur. However, the ______for this information must be made before the newsvendor learns what the demand will be. To calculate the _________with the new deal, choose the maximum value for each outcome (column) and multiply it by its respective______. Then, add the resulting products.

Decision 0 State of Nature 1 2 3 0 0 -50 -100 -150 1 -40 35 -15 -65 2 -80 -5 70 20 3 -120 -45 30 105 0. 1 0. 3 0. 4 0. 2 Prob. ER(new) = 0(0. 1) + 35(0. 3) + 70(0. 4) + 105(0. 2) = 59. 5 ER(current) = 22. 5 EVPI = 59. 5 – 22. 5 = 37. 0 cents

DECISION ANALYSIS Utilities and Decisions under Risk ____is an alternative way of measuring the attractiveness of the result of a decision. It is an alternative way of finding the ____to fill in a payoff table. Previously, we used _____return (net cash flow) and regret as two measures of the “_____” of a particular combination of a decision and state of nature. Utility suggests another type of measure.

THE RATIONALE FOR UTILITY Consider the following game in which an urn contains 99 white balls and 1 black ball. A single ball is drawn from the urn. Each ball is equally likely to be drawn. If a white ball is drawn, you must pay $10, 000. If the black ball is drawn, you receive $1, 000. You must decide whether to play. The payoff table is: DECISION Play Do Not Play STATE OF NATURE White Ball Black Ball -10, 000 0 1, 000 0

The probability of a white and a black ball are 0. 99 and 0. 01, respectively. The expected returns are: ER(play) = -10, 000(0. 99) + 1, 000(0. 01) = -9900 + 10, 000 = 100 ER(do not play) = 0(0. 99) + 0(0. 01) = 0 Since ER(play) > ER(do not play), you should play if the criterion of ______the expected net cash flow is applied. So, will you play this game? This simple example shows that you need to take care in selecting an appropriate criterion.

Most people are______, which means they would feel that the loss of a certain amount of money would be more painful than the _____of the same amount of money. _______in decision analysis measures the “attractiveness” of money. Utility can be thought of as a measure of “______. ” Two characteristics are: 1. It is_______, since more money is always at least as attractive as less money. 2. It is concave (the ______of money is nonincreasing).

To illustrate, first suppose you have $100 and someone gives you an additional $100. Note that your ____increases by U(200) – U(100) = 0. 680 – 0. 524 = 0. 156 Now suppose you start with $400 and someone gives you an additional $100. Now your utility ______by U(500) – U(400) = 0. 910 – 0. 850 = 0. 060 This illustrates that an additional $100 is less _____if you have $400 on hand than it is if you start with $100. The ____of a specified number of dollars increases utility less than the ______of the same number of dollars decreases utility.

Typical _______utility function: Utility 1. 0 0. 910 0. 850 0. 775 0. 680 0. 524 100 200 300 400 500 600 Dollars

Risk-seeking (_____) function: Utility 1. 0 0. 590 0. 260 0. 075 100 200 300 400 Dollars A gain of a specified amount of dollars ____ the utility more than a loss of the same amount of dollars _____the utility.

_________function: Utility 1. 0 0. 90 0. 60 0. 30 100 200 300 400 Dollars A gain or loss of a specified _____amount produces a change of the same ______in utility.

CREATING AND USING A UTILITY FUNCTION To begin, _____select the endpoints of the utility function. For convenience, set the utility of the _______net dollar return equal to 0 and the utility of the ______net return equal to 1. In the newsvendor example, the smallest return is – 150 and the largest is +105. Therefore U(-150) = 0 U(+105) = 1 Now, assume that the decision maker starts with these values and wants to find the utility of 10 [U(10)].

Select an ____probability p for the following two alternatives: 1. Receive a payment of ____for sure. 2. Participate in a lottery in which a payment of ___will be received with probability p or a payment of _____with probability 1 -p. If p = 1, then alternative 2 is _______(a payment of 105 is better than a payment of 10). If p = 0, then alternative 1 is preferred (a payment of 10 is ____than a loss of 150). Somewhere between 0 and 1, there is a value for p such that the decision maker is _____ between the two alternatives.

This value for p will vary from person to person depending on how _______the various alternatives are to them. This value of p is called the utility for 10. For example, choose p = 0. 6, the expected value of the ____is: 0. 6(105) + 0. 4(-150) = 3 In this case, the manager is ____risk since a sure payment of 10 (larger than the expected return of 3) is required to compensate her for the loss of the possibility of making more than the ____return.

Now, choose p = 0. 8, the expected value of the lottery is: 0. 8(105) + 0. 2(-150) = 54. 0 In this case, the manager is _______to risk since an expected value larger than the sure payment of 10 is required to _____for the riskiness of the lottery. The larger the value of p, the more _____ the manager is, because she requires a larger expected value of the lottery to compensate her for its______.

By solving the equation p(105) + (1 -p)(-150) = 10 255 p – 150 = 10 p = 160/255 = 0. 6275 We find the value of p for which the expected value of the lottery is equal to the sure payment of 10. So, p > 0. 6275 _____ to risk p = 0. 6275 p < 0. 6275 ______ to risk _____ risk To completely assess the entire utility function, repeat this procedure for all other possible dollar returns.

Another (and more popular) way to assess utility functions is to use an _____utility function. This function has a predetermined ____(i. e. , it’s concave, risk averse) and requires the assessment of only one_______. The function has the following form: U(x) = 1 - e-x/r Where x is the ____amount that will be converted to utility. r is a ____that measures the degree of risk aversion (the larger the value of r, the less risk-averse, the smaller the value of r, the more risk-averse the company or person is).

One way to determine r, is to first determine the dollar amount r such that the manager is ______between the following two choices: 1. A 50 -50 ____where the payoffs are a gain of r dollars or a loss of r/2 dollars 2. A payoff of_______ Now, suppose the newsvendor is indifferent between a bet where he wins $100 or loses $50 with equal probability and not betting at all. In this case, his r is $100.

The second way to determine r is based on _____evidence from many corporations net income, equity, and net sales to the degree of risk _____, r. This evidence shows that r is approximately equal to 124% of net income, 15. 7% of equity, and 6. 4% of net sales. For example, a large company with net income of $1 billion would have an r of 1. 24 billion, whereas a smaller company with net sales of $5 million would have an r of 320, 000.

Return to the newsvendor example: Because the newsvendor owns a very small business, he opts for using the _______ exponential utility function approach. In addition, he uses the 50 -50 _______approach to determine his r value ($100). Using Excel, develop a ______model of the utility of all the dollar payoffs. In this payoff table, the entries are the ______of the net cash flow associated with each combination of a decision and a state of nature (i. e. , the utility of the net cash flow is _____ for the respective cash flow.

Here is a graph of the utility function:

On the basis of the criterion of maximizing the expected utility, the newsvendor would order 2 papers.

Consider the following auto insurance example: Ten years after graduating from Standford’s Graduate School of Business, Carol Lane purchased a____. The annual premium from her insurance company for collision insurance would be $1000 with $250 deductible. There is only a 0. 5% chance she will cause a _____in the next year. In this case, she can expect about $50, 000 worth of damage. Since she owns the Lexus_____, she is not required to purchase collision insurance. Should she buy the insurance or not?

Here is the spreadsheet model: The Expected Returns indicate that it would be less expensive for her, on average, to not buy the insurance.

However, to be sure, she performs an utility analysis. She decides that she is_____, and estimates her r value to be $10, 000. Based on the expected utility analysis, Carol feels it is ______buying the insurance to have peace of mind that she won’t have to pay $50, 000 if she causes a_____.

End of Part 1 Please continue to Part 2