- 135 Views
- Uploaded on
- Presentation posted in: General

CONTEMPORARY MANAGEMENT SCIENCE WITH SPREADSHEETS ANDERSON SWEENEY WILLIAMS

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

CONTEMPORARY

MANAGEMENT SCIENCE

WITH SPREADSHEETS

ANDERSON SWEENEY WILLIAMS

SLIDES PREPARED BY

JOHN LOUCKS

© 1999 South-Western College Publishing

- Structuring the Decision Problem
- Decision Making Without Probabilities
- Decision Making with Probabilities
- Expected Value of Perfect Information
- Decision Analysis with Sample Information
- Developing a Decision Strategy
- Expected Value of Sample Information

- A decision problem is characterized by decision alternatives, states of nature, and resulting payoffs.
- The decision alternatives are the different possible strategies the decision maker can employ.
- The states of nature refer to future events, not under the control of the decision maker, which may occur. States of nature should be defined so that they are mutually exclusive and collectively exhaustive.
- For each decision alternative and state of nature, there is an outcome.
- These outcomes are often represented in a matrix called a payoff table.

- A decision tree is a chronological representation of the decision problem.
- Each decision tree has two types of nodes; round nodes correspond to the states of nature while square nodes correspond to the decision alternatives.
- The branches leaving each round node represent the different states of nature while the branches leaving each square node represent the different decision alternatives.
- At the end of each limb of a tree are the payoffs attained from the series of branches making up that limb.

- If the decision maker does not know with certainty which state of nature will occur, then he is said to be doing decision making under uncertainty.
- Three commonly used criteria for decision making under uncertainty when probability information regarding the likelihood of the states of nature is unavailable are:
- the optimistic approach
- the conservative approach
- the minimax regret approach.

- The optimistic approach would be used by an optimistic decision maker.
- The decision with the largest possible payoff is chosen.
- If the payoff table was in terms of costs, the decision with the lowest cost would be chosen.

- The conservative approach would be used by a conservative decision maker.
- For each decision the minimum payoff is listed and then the decision corresponding to the maximum of these minimum payoffs is selected. (Hence, the minimum possible payoff is maximized.)
- If the payoff was in terms of costs, the maximum costs would be determined for each decision and then the decision corresponding to the minimum of these maximum costs is selected. (Hence, the maximum possible cost is minimized.)

- The minimax regret approach requires the construction of a regret table or an opportunity loss table.
- This is done by calculating for each state of nature the difference between each payoff and the largest payoff for that state of nature.
- Then, using this regret table, the maximum regret for each possible decision is listed.
- The decision chosen is the one corresponding to the minimum of the maximum regrets.

Consider the following problem with three decision alternatives and three states of nature with the following payoff table representing profits:

States of Nature

s1s2s3

d1 4 4 -2

Decisionsd2 0 3 -1

d3 1 5 -3

- Optimistic Approach
An optimistic decision maker would use the optimistic approach. All we really need to do is to choose the decision that has the largest single value in the payoff table. This largest value is 5, and hence the optimal decision is d3.

Maximum

DecisionPayoff

d1 4

d2 3

choose d3d3 5 maximum

- Formula Spreadsheet forOptimistic Approach

- Spreadsheet for Optimistic Approach

- Conservative Approach
A conservative decision maker would use the conservative approach. List the minimum payoff for each decision. Choose the decision with the maximum of these minimum payoffs.

Minimum

DecisionPayoff

d1 -2

choose d2d2 -1 maximum

d3 -3

- Formula Spreadsheet for Conservative Approach

- Spreadsheet for Conservative Approach

- Minimax Regret Approach
For the minimax regret approach, first compute a regret table by subtracting each payoff in a column from the largest payoff in that column. In this example, in the first column subtract 4, 0, and 1 from 4; in the second column, subtract 4, 3, and 5 from 5; etc. The resulting regret table is:

s1s2s3

d1 0 1 1

d2 4 2 0

d3 3 0 2

- Minimax Regret Approach (continued)
For each decision list the maximum regret. Choose the decision with the minimum of these values.

DecisionMaximum Regret

choose d1 d1 1 minimum

d2 4

d3 3

- Formula Spreadsheet for Minimax Regret Approach

- Spreadsheet for Minimax Regret Approach

- Expected Value Approach
- If probabilistic information regarding he states of nature is available, one may use the expected value (EV) approach.
- Here the expected return for each decision is calculated by summing the products of the payoff under each state of nature and the probability of the respective state of nature occurring.
- The decision yielding the best expected return is chosen.

- The expected value of a decision alternative is the sum of weighted payoffs for the decision alternative.
- The expected value (EV) of decision alternative di is defined as:
where: N = the number of states of nature

P(sj) = the probability of state of nature sj

Vij = the payoff corresponding to decision alternative di and state of nature sj

Burger Prince Restaurant is contemplating opening a new restaurant on Main Street. It has three different models, each with a different seating capacity. Burger Prince estimates that the average number of customers per hour will be 80, 100, or 120. The payoff table for the three models is as follows:

Average Number of Customers Per Hour

s1 = 80 s2 = 100 s3 = 120

d1 = Model A $10,000 $15,000 $14,000

d2 = Model B $ 8,000 $18,000 $12,000

d3 = Model C $ 6,000 $16,000 $21,000

- Expected Value Approach
Calculate the expected value for each decision. The decision tree on the next slide can assist in this calculation. Here d1, d2, d3 represent the decision alternatives of models A, B, C, and s1, s2, s3 represent the states of nature of 80, 100, and 120.

- Decision Tree

Payoffs

.4

s1

10,000

s2

.2

2

15,000

s3

.4

d1

14,000

s1

.4

8,000

d2

s2

.2

1

3

18,000

s3

d3

.4

12,000

s1

.4

6,000

4

s2

.2

16,000

s3

.4

21,000

Example: Burger Prince

- Expected Value For Each Decision
- Choose the model with largest EMV -- Model C.

EV = .4(10,000) + .2(15,000) + .4(14,000)

= $12,600

2

d1

Model A

EV = .4(8,000) + .2(18,000) + .4(12,000)

= $11,600

d2

Model B

3

1

d3

Model C

EV = .4(6,000) + .2(16,000) + .4(21,000)

= $14,000

4

- Formula Spreadsheet for Expected Value Approach

- Spreadsheet for Expected Value Approach

- Frequently information is available which can improve the probability estimates for the states of nature.
- The expected value of perfect information (EVPI) is the increase in the expected profit that would result if one knew with certainty which state of nature would occur.
- The EVPI provides an upper bound on the expected value of any sample or survey information.

- EVPI Calculation
- Step 1:
Determine the optimal return corresponding to each state of nature.

- Step 2:
Compute the expected value of these optimal returns.

- Step 3:
Subtract the EV of the optimal decision from the amount determined in step (2).

- Step 1:

- Expected Value of Perfect Information
Calculate the expected value for the optimum payoff for each state of nature and subtract the EV of the optimal decision.

EVPI= .4(10,000) + .2(18,000) + .4(21,000) - 14,000 = $2,000

- Spreadsheet for Expected Value of Perfect Information

- Knowledge of sample or survey information can be used to revise the probability estimates for the states of nature.
- Prior to obtaining this information, the probability estimates for the states of nature are called prior probabilities.
- With knowledge of conditional probabilities for the outcomes or indicators of the sample or survey information, these prior probabilities can be revised by employing Bayes' Theorem.
- The outcomes of this analysis are called posterior probabilities.

- Posterior Probabilities Calculation
- Step 1:
For each state of nature, multiply the prior probability by its conditional probability for the indicator -- this gives the joint probabilities for the states and indicator.

- Step 2:
Sum these joint probabilities over all states -- this gives the marginal probability for the indicator.

- Step 3:
For each state, divide its joint probability by the marginal probability for the indicator -- this gives the posterior probability distribution.

- Step 1:

- The expected value of sample information (EVSI) is the additional expected profit possible through knowledge of the sample or survey information.

- EVSI Calculation
- Step 1:
Determine the optimal decision and its expected return for the possible outcomes of the sample using the posterior probabilities for the states of nature.

Step 2:

Compute the expected value of these optimal returns.

- Step 3:
Subtract the EV of the optimal decision obtained without using the sample information from the amount determined in step (2).

- Step 1:

- Efficiency of sample information is the ratio of EVSI to EVPI.
- As the EVPI provides an upper bound for the EVSI, efficiency is always a number between 0 and 1.

- Sample Information
Burger Prince must decide whether or not to purchase a marketing survey from Stanton Marketing for $1,000. The results of the survey are "favorable" or "unfavorable". The conditional probabilities are:

P(favorable | 80 customers per hour) = .2

P(favorable | 100 customers per hour) = .5

P(favorable | 120 customers per hour) = .9

Should Burger Prince have the survey performed by Stanton Marketing?

- Posterior Probabilities
Favorable Survey Results

StatePriorConditionalJointPosterior

80 .4 .2 .08 .148

100 .2 .5 .10 .185

120 .4 .9 .36.667

Total .54 1.000

P(favorable) = .54

- Posterior Probabilities
Unfavorable Survey Results

StatePriorConditionalJointPosterior

80 .4 .8 .32 .696

100 .2 .5 .10 .217

120 .4 .1 .04.087

Total .46 1.000

P(unfavorable) = .46

- Formula Spreadsheet for Posterior Probabilities

- Spreadsheet for Posterior Probabilities

- Decision Tree (top half)

s1 (.148)

$10,000

s2 (.185)

4

$15,000

d1

s3 (.667)

$14,000

s1 (.148)

$8,000

d2

s2 (.185)

5

2

$18,000

s3 (.667)

d3

$12,000

I1

(.54)

s1 (.148)

$6,000

s2 (.185)

6

$16,000

s3 (.667)

1

$21,000

- Decision Tree (bottom half)

s1 (.696)

1

$10,000

s2 (.217)

7

$15,000

I2

(.46)

s3 (.087)

d1

$14,000

s1 (.696)

$8,000

d2

s2 (.217)

8

3

$18,000

s3 (.087)

$12,000

d3

s1 (.696)

$6,000

s2 (.217)

9

$16,000

s3 (.087)

$21,000

- EMV = .148(10,000) + .185(15,000)
- + .667(14,000) = $13,593

4

d1

- $17,855

d2

- EMV = .148 (8,000) + .185(18,000)
- + .667(12,000) = $12,518

5

2

d3

I1

(.54)

- EMV = .148(6,000) + .185(16,000)
- +.667(21,000) = $17,855

6

1

- EMV = .696(10,000) + .217(15,000)
- +.087(14,000)= $11,433

7

d1

I2

(.46)

d2

- EMV = .696(8,000) + .217(18,000)
- + .087(12,000) = $10,554

8

3

d3

- $11,433

- EMV = .696(6,000) + .217(16,000)
- +.087(21,000) = $9,475

9

- Decision Strategy Assuming the Survey is Undertaken:
- If the outcome of the survey is favorable, choose Model C.
- If it is unfavorable, choose Model A.

- Question:
Should the survey be undertaken?

- Answer:
If the Expected Value with Sample Information (EVwSI) is greater, after deducting expenses, than the Expected Value without Sample Information (EVwoSI), the survey is recommended.

- Expected Value with Sample Information (EVwSI)
EVwSI = .54($17,855) + .46($11,433) = $14,900.88

- Expected Value of Sample Information (EVSI)
EVSI = EVwSI - EVwoSI

assuming maximization

EVSI= $14,900.88 - $14,000 = $900.88

- Conclusion
EVSI = $900.88

Since the EVSI is less than the cost of the survey ($1000), the survey should not be purchased.

- Efficiency of Sample Information
The efficiency of the survey:

EVSI/EVPI = ($900.88)/($2000) = .4504