# Spreadsheet Modeling and Decision Analysis

**View the full content.**View Full Document

0 0 9 views

**Unformatted text preview:**

Spreadsheet Modeling Decision Analysis A Practical Introduction to Management Science 5th edition Cliff T Ragsdale Chapter 15 Decision Analysis 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 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 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 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 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 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 The Possible States of Nature 1 The new airport is built at location A 2 The new airport is built at location B Constructing a Payoff Matrix See file Fig15 1 xls 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 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 Decision A B State of Nature 1 2 30 10000 29 29 MAX 30 maximum 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 Weakness Consider the following payoff matrix Decision A B State of Nature 1 1000 29 2 28 29 MIN 28 29 maximum The Minimax Regret Decision Rule Compute the possible regret for each alternative under each state of nature Identify the maximum possible regret for each alternative Choose the alternative with the smallest maximum regret See file Fig15 1 xls Anomalies with the Minimax Regret Rule Consider the following payoff matrix Decision A B State of Nature 1 2 9 2 4 6 The regret matrix is State of Nature Decision 1 2 A 0 4 B 5 0 Note that we prefer A to B Now let s add an alternative MAX 4 minimum 5 Adding an Alternative Consider the following payoff matrix State of Nature Decision 1 A 9 B 4 C 3 The regret matrix is 2 2 6 9 State of Nature Decision 1 A 0 B 5 C 6 Now we prefer B to A 2 7 3 0 MAX 7 5 minimum 6 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 Expected Monetary Value Selects alternative with the largest expected monetary value EMV EMVi rij p j j rij payoff for alternative i under the jth state of nature p j the probability of the jth state of nature EMVi is the average payoff we d receive if we faced the same decision problem numerous times and always selected alternative i See file Fig15 1 xls EMV Caution The EMV rule should be used with caution in onetime decision problems Weakness Consider the following payoff matrix Decision A B Probability State of Nature 1 2 15 000 5 000 5 000 4 000 0 5 0 5 EMV 5 000 maximum 4 500 Expected Regret or Opportunity Loss Selects alternative with the smallest expected regret or opportunity loss EOL EOLi gij p j j gij regret for alternative i under the jth state of nature p j the probability of the jth state of nature The decision with the largest EMV will also have the smallest EOL See file Fig15 1 xls The Expected Value of Perfect Information Suppose we could hire a consultant who could predict the future with 100 accuracy With such perfect information Magnolia Inns average payoff would be EV with PI 0 4 13 0 6 11 11 8 in millions Without perfect information the EMV was 3 4 million The expected value of perfect information is therefore EV of PI 11 8 3 4 8 4 in millions In general EV of PI EV with PI maximum EMV It will always the the case that EV of PI minimum EOL A Decision Tree for Magnolia Inns Land Purchase Decision Buy A 18 Buy B 12 Airport Location 1 2 0 Buy A B 30 Buy nothing 0 3 4 Payoff A 31 13 B 6 12 A 4 8 B 23 11 A 35 5 B 29 1 A 0 0 B 0 0 Rolling Back A Decision Tree Land Purchase Decision Airport Location 0 4 Buy A 18 EMV 2 1 A 31 13 6 B 0 6 12 A 4 8 23 B 0 6 11 0 4