Spreadsheet Modeling & Decision Analysis:
Download
Report
Transcript Spreadsheet Modeling & Decision Analysis:
Decision Analysis
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-1
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...
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-2
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?
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-3
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.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-4
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
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-5
An Example: Magnolia Inns
Hartsfield International Airport in Atlanta, Georgia, is one of
the busiest airports in the world.
It has expanded numerous times to accommodate increasing
air traffic.
Commercial development around the airport prevents it from
building additional runways to handle the future air traffic
demands.
Plans are being developed to build another airport outside the
city limits.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-6
An Example: Magnolia Inns (con’t)
Two possible locations for the new airport have been
identified, but a final decision is not expected to be
made for another year.
The Magnolia Inns hotel chain intends to build a new
facility near the new airport once its site is
determined.
Land values around the two 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
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-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.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-8
The Possible States of Nature
1) The new airport is built at location A.
2) The new airport is built at location B.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-9
Constructing a Payoff Matrix
See file Fig15-1.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-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.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-11
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
1
30
29
State of Nature
2
MAX
-10000
30 <--maximum
29
29
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-12
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
1
1000
29
State of Nature
2
MIN
28
28
29
29 <--maximum
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-13
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
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-14
Anomalies with the Minimax Regret Rule
Consider the following payoff matrix
State of Nature
Decision
1
2
A
9
2
B
4
6
The regret matrix is:
Decision
A
B
1
0
5
State of Nature
2
MAX
4
4 <--minimum
0
5
Note that we prefer A to B.
Now let’s add an alternative...
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-15
Adding an Alternative
Consider the following payoff matrix
State of Nature
Decision
1
2
A
9
2
B
4
6
C
3
9
The regret matrix is:
State of Nature
Decision
1
2
MAX
A
0
7
7
B
5
3
5 <--minimum
C
6
0
6
Now we prefer B to A?
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-16
Probabilistic Methods
Sometimes, the 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, probabilities are often assigned subjectively
based on interviews with one or more domain experts.
Highly structured 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.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-17
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
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-18
EMV Caution
The EMV rule should be used with caution in one-time
decision problems.
Weakness
– Consider the following payoff matrix
Decision
A
B
Probability
State of Nature
1
2
EMV
15,000
-5,000
5,000 <--maximum
5,000
4,000
4,500
0.5
0.5
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-19
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
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-20
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 be the case that, EV of PI = minimum EOL
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-21
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
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
3
4
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-22
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
0.4
Buy B
-12
EMV=3.4
2
0
EMV=3.4
A 4
-8
23
B 0.6
11
0.4
Buy A&B
-30
EMV=1.4
A 35
5
B 29
0.6
-1
3
0.4
Buy nothing
0
EMV= 0
Payoff
4
A 0
0
B 0
0
0.6
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-23
Alternate Decision Tree
Land Purchase Decision
Airport Location
0.4
Buy A
-18
EMV=-2
1
A 31
13
6
B 0.6
-12
0.4
Buy B
-12
EMV=3.4
2
0
EMV=3.4
A 4
-8
23
B 0.6
11
0.4
Buy A&B
-30
EMV=1.4
Payoff
A 35
5
B 29
-1
3
0.6
Buy nothing
0
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
0
15-24
Using TreePlan
TreePlan
See
is an Excel add-in for decision trees.
file Fig15-14.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-25
About TreePlan
TreePlan is a shareware product developed by
Dr. Mike Middleton at the University of San
Francisco and distributed with this textbook at no
charge to you.
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.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-26
Multi-stage Decision Problems
Many problems involve a series of decisions
Example
– Should you go out to dinner tonight?
– If so,
How much will you spend?
Where will you go?
How will you get there?
Multi-stage decisions can be analyzed using
decision trees
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-27
Multi-Stage Decision Example: COM-TECH
Steve Hinton, owner of COM-TECH, is considering whether
or not to apply for a $85,000 OSHA research grant for using
wireless communications technology to enhance safety in
the coal industry.
Steve would spend approximately $5,000 preparing the
grant proposal and estimates a 50-50 chance of actually
receiving the grant.
If awarded the grant, Steve would then need to decide
whether to use microwave, cellular, or infrared
communications technology.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-28
COM-TECH (continued)
Steve would need to acquire some new equipment
depending on which technology is used. The cost of
the equipment is summarized as:
Technology
Equipment Cost
Microwave
$4,000
Cellular
$5,000
Infrared
$4,000
Steve knows he will also spend money in R&D, but
he doesn’t know exactly what the R&D costs will be.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-29
COM-TECH (continued)
Steve estimates the following best case and worst case R&D
costs and probabilities, based on his expertise in each area.
Microwave
Cellular
Infrared
Best Case
Cost Prob.
$30,000 0.4
$40,000 0.8
$40,000 0.9
Worst Case
Cost Prob.
$60,000 0.6
$70,000 0.2
$80,000 0.1
Steve needs to synthesize all the factors in this problem to
decide whether or not to submit a grant proposal to OSHA.
See file Fig15-26.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-30
Analyzing Risk in a Decision Tree
How sensitive is the decision in the COM-TECH
problem to changes in the probability estimates?
We can use Solver to determine the smallest
probability of receiving the grant for which Steve
should still be willing to submit the proposal.
Let’s go back to file Fig15-26.xls...
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-31
Risk Profiles
A risk profile summarizes the make-up of an EMV.
The $13,500 EMV for COM-TECH was created as follows:
Event
Probability
Receive grant, Low R&D costs
0.5*0.9=0.45
$36,000
Receive grant, High R&D costs 0.5*0.1=0.05
-$4,000
Don’t receive grant 0.5
EMV
Payoff
-$5,000
$13,500
This can also be summarized in a decision tree.
See file Fig15-29.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-32
Using Sample Information
in Decision Making
We
can often obtain information about the
possible outcomes of decisions before the
decisions are made.
This
sample information allows us to refine
probability estimates associated with various
outcomes.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-33
Example: Colonial Motors
Colonial Motors (CM) needs to determine whether to build
a large or small plant for a new car it is developing.
The cost of constructing a large plant is $25 million and
the cost of constructing a small plant is $15 million.
CM believes a 70% chance exists that demand for the
new car will be high and a 30% chance that it will be low.
The payoffs (in millions of dollars) are summarized below.
Demand
Factory Size
Large
Small
High
$175
$125
Low
$95
$105
See decision tree in file Fig15-32.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-34
Including Sample Information
Before making a decision, suppose CM conducts a
consumer attitude survey. If the survey response is
unfavorable, this should increase CM’s belief that demand
will be low.
Past performance of survey company is shown below:
– The company predicts high demand with 86% probability P(F|H)
– The company predicts low demand with 78% probability P(U|L)
How much should CM be willing to pay to conduct the
consumer attitude survey?
We will combine prior probabilities with performane of
company (conditional probabilities) using Bayes’ Theorem
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-35
Bayes’s Theorem
Bayes’s Theorem provides another definition of conditional
probability that is sometimes helpful.
P(B|A)P(A)
P(A|B) =
P(B|A)P(A) + P(B|A)P(A)
For example,
P(H|F) =
P(F|H)P(H)
(0857
. )(070
. )
090
.
P(F|H)P(H) + P(F|L)P(L) (0857
. )(070
. ) (0223
. )(030
. )
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-36
Prior Probabilities P(H)=0.7, P(L)=0.3
Conditional Probabilities
P(F|H)=0.86, P(U|L)=0.78, P(F|U)=0.14, P(U|H)=0.22
The joint probabilities indicate:
P(F H) 0.6, P(F L) = 0.067
P(U H) = 0.1, P(U L) 0.233
The marginal probabilities indicate:
P(F) 0.667, P(U) = 0.333
P(H) = 0.700, P(L) 0.300
Posterior Probabilities,
P(H|F) =
P(H F)
0.60
0.90
P(F)
0.667
P(H|U) =
P(H U)
0.10
0.30
P(U)
0.333
P(L F) 0.067
P(L U) 0.233
0.10
P(L|U) =
0.70
P(F)
0
.
667
P(U)
0.333
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
P(L|F) =
15-37
Favourable Survey
Prior
Conditional
Joint
Posterior
Demand Probabilities Probabilities Probabilities Probabilities
High
70%
86%
60%
90%
Low
30%
22%
7%
10%
Marginal Probabilities
67%
Unfavourable Survey
Prior
Conditional
Joint
Posterior
Demand Probabilities Probabilities Probabilities Probabilities
High
70%
14%
10%
30%
Low
30%
78%
23%
70%
Marginal Probabilities
33%
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-38
The Expected Value of Sample Information
How much should CM be willing to pay to conduct the
consumer attitude survey?
Expected Value of
Sample Information
=
Expected Value with
Sample Information
-
Expected Value without
Sample Information
In the CM example,
E.V. of Sample Info. = $126.82 - $126 = $0.82 million
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-39
BILL GALLEN DEVELOPMENT COMPANY
– B. G. D. plans to do a commercial development on a
property.
– Relevant data
Asking price for the property is $300,000
Construction cost is $500,000
Selling price is approximated at $950,000
Variance application costs $30,000 in fees and expenses
– There is only 40% chance that the variance will be approved.
– If B. G. D. purchases the property and the variance is denied, the
property can be sold for a net return of $260,000
– A three month option on the property costs $20,000 which will allow
B.G.D. to apply for the variance.
A consultant can be hired for $5000
– P (Consultant predicts approval | approval granted) = 0.70
– P (Consultant predicts denial | approval denied) = 0.90
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
What should BGD do?
Construction
of the Decision Tree
– Initially the company faces a decision about
hiring the consultant.
– After this decision is made more decisions follow
regarding
Application
for the variance.
Purchasing the option.
Purchasing the property.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.