1 / 43

DECISION MODELING WITH MICROSOFT EXCEL

DECISION MODELING WITH MICROSOFT EXCEL. Chapter 8. DECISION. ANALYSIS. Decision Analysis Concepts. There are three parts to every decision. A set of Alternatives A set of possible States of Nature

iona
Download Presentation

DECISION MODELING WITH MICROSOFT EXCEL

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 MODELING WITH MICROSOFT EXCEL Chapter 8 DECISION ANALYSIS

  2. Decision Analysis Concepts • There are three parts to every decision. • A set of Alternatives • A set of possible States of Nature • A set of outcomes that are dependent on both the Alternative chosen and the State of Nature that occurs. • There are three types of decisions • Decisions under certainty • Decisions under ignorance • Decisions under risk • There are two easy decisions • Decisions under certainty • Stochastic Dominance

  3. Decision Analysis Concepts • Alternatives • For a decision to be made there must be alternatives to choose from. • The decision maker controls which alternative is selected • Inaction(doing nothing) is an acceptable alternative • For example, I can choose to bring my umbrella to class or I can choose to leave it at home. • States of Nature • For every decision one or more events can occur • Only one state of nature will occur (Mutually Exclusive) • The sum of probabilities for all states of nature must be 1.0 (Collectively Exhaustive) • THE DECISION MAKER HAS NO CONTROL OVER WHICH STATE OF NATURE WILL OCCUR • For example, it either rains while I walk to class or it does not rain. These two events are mutually exclusive and collectively exhaustive. I have no control over which happens.

  4. Decision Analysis Concepts • Outcomes • Outcomes are dependent on which alternative was chosen and which state of nature occurred • Alternatives are ALWAYS chosen BEFORE the state of nature occurs • For each alternative/state of nature combination there is an outcome • For example, if I chose to take my umbrella and it does not rain the outcome is that I stay dry • If I chose to take my umbrella and it rains the outcome is that I stay dry • If I chose not to take my umbrella and it does not rain the outcome is that I stay dry • If I chose not to take my umbrella and it rains the outcome is that I get wet. • Three of the four outcomes in this example are the same. The only time I get wet is when I choose to not take my umbrella and then it rains as I walk to class.

  5. Decision Analysis Concepts • Decisions under Certainty • The state of nature that will occur after the decision has been made is known with certainty (effectively 1 state of nature) • Deterministic models assume certainty • If X then Y1 • Decisions under Ignorance • Ignorance occurs when the possible states of nature are known but the decision maker has no knowledge of the probability of each state occurring • Ignorance assumes each state of nature is equally likely to occur • If X then P(Y1) = 1/Yn • Decisions under Risk • The decision maker does not know which state of nature will occur • The decision maker can asses the likelihood of each state of nature’s occurrence • If X then P(Y1) = Z, where Z is a known/estimable value between 0 and 1

  6. Decision Analysis Concepts • Two “Easy” Decisions • Decisions under Certainty are considered easy. If the state of nature is known before the decision is made the only thing that impacts the outcome is which alternative is chosen. In this case, the decision maker only needs to evaluate all of the alternatives and choose the one that yields the best outcome. Deterministic modeling what-if analysis is an example • Stochastic Dominance occurs when one alternative always yields an equal or better outcome than another alternative regardless of which state occurs. For example, the decision to carry an umbrella to class stochastically dominates the decision to not carry an umbrella. This is because I will always stay dry with the umbrella but there is a chance I can get wet without it.

  7. Payoff Tables Payoff tables are used to evaluate all possible outcomes for any set of alternatives and states of nature. Decision alternatives are listed down the side and make up the rows of the table. States of Nature are listed across the top and make up the columns of the table. Each cell of the table represents an outcome for the row’s alternative when the column’s state of nature occurs. Note 1: Each row/column combination must have an outcome associated with it. Note 2: Decisions under certainty only have one column.

  8. State of Nature 1 2 … m Decision d1r11 r12 … r1m d2r21 r22 … r2m … … … … … dnrn1 rn2 … rnm Payoff Tables In this table, the alternative decisions are listed along the side. The states of nature are listed across the top. The center values are the payoffs for all possible combinations of decisions and states of nature.

  9. Decision Rules under Ignorance • There are four rules that can be used to make a decision under ignorance • All four rules require a payoff table to be built • Each rule evaluates the payoff table to suggest the best decision • The rules are: • Maximax • Maximin • Laplace-Bayes • Minimax regret

  10. Decision Rules under Ignorance • A small regional bank wants to get into the mortgage lending business. They want to include economic impacts and interest rates in their decision. For expedience they determine that the economy can be good or bad and rates could be high or low. Therefore the four possible states of nature are: • 1) Good and Low • 2) Good and High • 3) Bad and Low • 4) Bad and High • The bank is also considering two separate initiatives. The first is whether to have a tight or loose credit risk policy and the second is to set their rates as either high or low. These decisions lead to the following four alternatives. • 1) Tight policy and low rates • 2) Tight policy and high rates • 3) Loose policy and low rates • 4) Loose policy and high rates • Each decision will have different payoffs for each state of nature.

  11. Decision Rules under Ignorance The bank did a lot of analysis and came up with this payoff matrix. • Notice the four states of nature are listed across the top and make the columns. • Notice the four alternatives are listed down the side and make the rows. • Each cell is the payoff the bank will realize for any given alternative/state of nature combination. • Remember, the alternative is selected before the state of nature is known.

  12. Decision Rules under Ignorance • Maximax - “the best of the best” • Identify the best outcome for each alternative • Select the alternative with the best possible outcome Using the Maximax decision rule, the Bank’s best alternative is to have a loose policy and low rates. If the economy turns out to be good and has low interest the bank will realize the best possible return of $2,800,000 in profit.

  13. Decision Rules under Ignorance • Maximin - “the best of the worst” • Identify the worst outcome for each alternative • Select the alternative with the best possible outcome Using the Maximin decision rule, the Bank’s best alternative is to have a tight policy and high rates. If the economy goes bad and rates are high, the worst state of nature, the bank will only have $100,000 in losses.

  14. Decision Rules under Ignorance • Laplace-Bayes - “the best average” • Calculate the average payoff for each alternative • Select the alternative with the best possible outcome Using the Laplace-Bayes decision rule, the banks best alternative is to have a loose policy and high rates. It is not possible for the bank to have $1,062,500 in returns. It will have either $2.25M, $2.75M, -$0.35M, or -$0.4M. Laplace-Bayes assumes that the average payoff will occur if the decision is repeated many times, i.e. in a simulation.

  15. Decision Rules under Ignorance • Minimax Regret - “I can’t afford to be wrong” • Calculate the regret for each alternative. • Regret is effectively the opportunity cost of a decision • To calculate regret look at each state of nature. • The alternative that yields the best outcome has zero regret • The regret for the remaining alternatives is the difference between the best alternative and its own outcome • Identify the maximum regret for each alternative and select the smallest

  16. Decision Rules under Ignorance • Using Minimax Regret the bank should choose a tight policy with low rates. In a good economy with low interest the bank will only regret $925,000 in profit they could have had. This decision rule is the most forward looking. • Note: In this example we had four decision rules and each one yielded a different answer. This will not always be the case. • It is up to the decision maker to chose which decision rule will be applied to each decision.

  17. Decisions under risk • Decisions under risk have more than one state of nature. • Each state of nature has a known/estimable probability. • Each state of nature must be mutually exclusive. • The sum of probabilities for all states of nature must equal 1.0 • This means that they are collectively exhaustive. • Probabilities can be estimated using the following: • Historical frequencies • Subjective judgment • Forecasts • Models

  18. Estimating Probabilities • Regardless of method, historical frequency, model, judgment, forecast the analyst must identify mutually exclusive and collectively exhaustive groups. • Historical Frequency Example: Last January it rained 11 out of 31 days. The probability of rain on any given day in January is 11/31 or 35.48%. The probability it won’t rain is 1-35.48 or 64.52% • Subjective Judgment Example: Your manager, who has a lot of experience, believes that there is a 45% chance that your bid will be accepted, 20% chance it will be countered, and a 35% probability it will be rejected. • Forecast Example: Economists believe that interest rates will raise next year. Their forecasts are right 70% of the time. • Model Example: A person has a credit score of 756. This can be transformed into a probability of default of 6%. That means the probability they will pay as agreed is 94%.

  19. Expected Value • The expected value of a decision is the weighted average of the payoffs for each alternative / state of nature where the weights are the probability of each state of natures occurrence. • Example: • The expected value of choosing a tight policy and low rates is $1,057,750 given these probabilities. • IT IS NOT POSSIBLE FOR A SINGLE DECISION TO HAVE A VALUE OF $1,057,750. This is the expected value if the decision is made many many times. • FYI: Not all decisions are made on dollars, whether profit or costs, in many cases a concept called Utility is used in place of dollars.

  20. Utilities • Would you play this game: For $10,000 you have a 1% chance of winning $1,000,000. • The EV is (1,000,000*.01) + (-10,000*.99) = 10,000 – 9,900 = $100. • If you won’t play this game you are risk averse. • Second game, I’ll give you $10,000. • Feels good doesn’t it. • You just won a million bucks in the lottery • Who cares about my 10 grand. • The more money you have the less value additional money means to you. • Utilities are algorithms to measure how risk averse one is by measuring the value they place on different levels of money. • You won’t be tested on this, just wanted to expose the idea.

  21. Example from Book • A newsvendor can buy newspapers for $0.40 and sell them for $0.75. • As with all decisions, they must decide how many to buy before they learn how many can be sold. • There is no salvage value on unsold newspapers. • There is an opportunity cost of $0.50 for unmet demand which represents the potential loss of future customers. • The four states of nature and the probability of their occurrence are: • P0 = 0.1 • P1 = 0.3 • P2 = 0.4 • P3 = 0.2 • Our newsvendor must decide whether to purchase 0, 1, 2, or 3 papers.

  22. Example – Cont. Using the previous information the payoff matrix was developed where Profit = 0.75*number sold - 0.4*number bought – 0.5*unmet demand

  23. Example – Cont. For each alternative the payoffs were multiplied by the probability of each state of natures occurrence. These were then summed to reach each alternatives expected value. Since we are maximizing profit we will chose the alternative with the highest expected value and order 2 papers. Any given day, if we order 2 papers we will either lose $0.80, lose $0.05, gain $0.70, or gain $0.20. Over the course of a month we could expect to average a $0.23 gain per day.

  24. EVPI • EVPI is the Expected Value of Perfect Information. • It is the expected value of a decision assuming one knows in advance which state of nature will occur. • It is the sum of the best payoff for each state of nature multiplied by the probability of each state’s occurrence minus the original expected value. • The EVPI for our newsvendor is $0.595 - $0.225 = $0.37 • $0.37 is the most we should be willing to pay for perfect information

  25. EVSI • EVSI is the Expected Value of Sample Information. • Sample information provides revised probability estimates for each state of nature. • Calculate the expected value using the sample information then subtract the expected value without the new information. • Sample efficiency is EVSI/EVPI. • The sample information in this example is $0.32 - $0.225 = $0.095. • The sample efficiency is $0.095/$0.37 = 25.67%

  26. Decision Trees • Decision trees map out decisions under risk in a time series. • Trees are read from left to right. The left most node represents the current time. Moving right the tree extends into the future. • Decision nodes are represented as a square. • Event nodes, where states of nature occur, are represented by circles. • Each decision alternative leads to an event node. • Trees can have several sequential decisions

  27. Decision Tree Example • Each spring Joe Farmer has to decide what type of pest treatment to use on his soybean crop. • DDT costs $5000 and is effective 80% of the time when used in the spring. • Hormones are 73% effective and cost $8000. • Scent Lures provide a two stage treatment. The scent lure is applied in the spring at a cost of $3000. It has a 32% effective rate. • By mid summer Joe will know if it worked. • Either way, he can then choose to use DDT or release sterile males. • When applied in the summer DDT works 45% of the time and still costs $5000. • If the scent lure worked the sterile male is 92% effective. • If the scent lure failed the sterile male is only 61% effective. • It costs $7000.

  28. Decision Tree Example • Regardless of treatment, if it is not successful Joe will lose $10,000 • All revenues reported assume the treatment chosen is effective. • The payoffs reported do not account for the treatment cost. • If Spring DDT is effective he will make $16,000 • If the Scent Lure works and he uses DDT he will make $12,000 • If the Scent Lure works and he uses the sterile males he will make $52k • If the Scent Lure fails and he uses DDT he will make $12,000 • If the Scent Lure fails and he uses the sterile males he will make $27,000 • If he uses the Hormone he will make $22,000 • Joe has everything he needs to build his decision tree. • Since one of the options has two decision points this is a sequential decision. • Use the treeplan.xla excel add in to build your tree. It is demonstrated in class and in the text book.

  29. Decision Tree Example Step 1: Represent the three decisions with a decision node – note it is square Step 2: Enter the costs. Step 3: After each decision there is an event. Either the treatment was effective or it was not.

  30. Decision Tree Example Notice that the three event nodes are represented as circles. Also note, that each event has probabilities that reflect the study data. DDT & Hormone are a single decision. Therefore the payoffs associated with their success has been added in.

  31. Decision Tree Example Next, two more decision nodes have to be added after we learn if the scent lure is effective or not. Both new nodes have the same decision alternatives and costs. After choosing whether to use DDT or the Sterile Male there is another event node for each branch. The probabilities and payoffs come from the text. The column on the far right is the actual payoff that any single trip through the tree will generate including all costs.

  32. Decision Tree Example • The expected value of this sequence of decisions is $6,345 and comes from alternative number 2. • If the scent lure is effective the best decision is to use the sterile male. • If the sterile male works the crop is valued at $52,000 but it cost a total of $10,000 in treatments. Joe will profit $42,000. • If the sterile male does not work, Joe will lose $10,000 on his crops plus the $10,000 in treatments. • If the scent lure does not work Joes best alternative is to try to salvage his crops with DDT. • Sensitivity analysis can be performed by applying different values for payoffs as well as probabilities.

  33. Bayes’ Theorem • Bayes’ Theorem allows re-evaluation of probability estimates with new information. • All that is needed are the original probability estimates and how reliable the new information is. • Example, In Atlanta during February the historical probability of rain on any given day is 61%. P(r) = 0.61 P(nr) = 1-0.61 = 0.39. These are known as the “Prior Probabilities.” • Historically, 85% of the time it rains the next day the news said rain. • This means 15% of the time they predicted no rain. • The news accurately predicts non-rainy days 70% of the time. • The historical accuracy of the news are called “Reliabilities” • Before watching the news our best prediction is there is a 61% probability of rain tomorrow. • The news says it will rain tomorrow. • What is our revised probability of rain?

  34. Bayes’ Theorem • Before calculating the revised probability of rain, known as the “Posterior Probabilities” we need to know the “Joint Probabilities” and the “Marginal Probabilities”. • There is one Joint Probability for each reliability estimate. To determine the Joint Probability multiply each reliability estimate by its prior probability. • Predict Rain and Rain = 0.85 * 0.61 = 0.5185 • Predict Rain and No Rain = 0.15 * 0.61 = 0.0915 • Predict No Rain and Rain = 0.3 * 0.39 = 0.117 • Predict No Rain and No Rain = 0.7 * 0.39 = 0.273 • The Marginal Probabilities are the sum of the Joint Probabilities by prediction. They equate to the probability a given prediction will be made. • Predict Rain = 0.5185 + 0.117 = 0.6355 • Predict No Rain = 0.0915 + 0.273 = 0.3645 • A quick test: The sum of the marginal probabilities must equal 1.

  35. Bayes’ Theorem • The Posterior Probabilities are also known as “Conditional Probabilities”. The can be noted as P(r|pr). This is read as the probability of rain given the prediction of rain. • To calculate the posterior probability divide the joint probability by its marginal probability. • P(r|pr) = 0.5185/0.6355 = 0.8159 • P(r|pnr) = 0.915/0.3645 = 0.2510 • P(nr|pr) = 0.117/0.6355 = 0.1841 • P(nr|pnr) = 0.273/0.3645 = 0.7490 • In this example we originally asked what is the probability of rain given the news predicts rain. Our answer is 81.59%.

  36. Bayes’ Theorem

  37. Book Example • New Cell phone introduction • Three campaign levels • Aggressive • Basic • Cautious • Two states of nature • Strong P(s) = 0.45 • Weak P(w) = 0.55

  38. Book ExampleBasic Tree Note: A single stage decision tree produces the same results as the payoff table.

  39. Book ExampleMarket Test • A marketing firm can perform a test to determine whether the market will be strong or weak. • 60% of the time there is a strong market the firm gives an encouraging report. • Conversely, 40% of the time there is a strong market the firm gives a discouraging report. • 70% of the time there is a weak market the firm gives a discouraging report. • Conversely, 30% of the time there is a weak market the firm gives an encouraging report. • Bayes’ Theorem allows us to know the probability of an encouraging report (the marginal probability). • Bayes’ also gives the conditional probabilities (posterior) to use in the revised tree.

  40. Book ExampleBayes’ Theorem

  41. Book ExampleRevised Tree Note: Our original expected value of choosing a basic strategy was $12.85 MM. If there is an encouraging report our best decision is to market aggressively for an expected value of $15.598. If the report is discouraging then we should choose a cautious campaign for an expected value of $11.81MM. The overall tree is valued at $13.46 MM. We can determine the EVSI as $13.46 – 12.85 MM = $0.61 MM. Recall EVPI as (30*0.45 + 15*0.55) – 12.85 (13.5 + 8.25) – 12.85 21.75 – 12.85 = $8.9 MM The sample efficiency is 0.61/8.9 Or 6.8%

  42. Book ExampleSummary • EVSI = $0.61 MM, so long as the market test is less than this we should pay for it. In this case it is $0.5 MM. • Not shown is the final tree which has a decision to test or not test. • The final tree has a single decision node on the left, if test the subtree is identical to the tree on slide 40. Following the no test decision the subtree is identical to the tree on slide 37. • Recall, trees are read from left to right. You can only choose which branch at a decision node (square). • Branches not chosen are said to be pruned.

  43. Key Learning's • Decision analysis concepts • Payoff tables • Rules for decision under ignorance • Expected value • Value of information • Bayes’ Theorem • Decision trees

More Related