# Decision Analysis in EXCEL

Please see attached files for further information.

1. A toy company has developed a new toy for the upcoming Christmas season. Since this toy is considerably different from the ones it has manufactured previously, the company will need to develop a new production facility for it. Three facility sizes-small, medium, and large-are under consideration. Given the nature of the toy market, the company is unsure as to what demand level it will encounter. The preliminary analysis is to be based on the demand being low, average, or high. A small amount of subcontracting will be available, so that if the production facility is undersized it will be possible to meet some of the excess demand. The accompanying table shows the estimated profits, in $1,000s, of the various facility-size-demand-level combinations.

Demand Level

Facility Size Low Average High

Small 750 900 900

Medium 350 1,100 1,300

Large -250 600 2,000

a. Determine the best production facility size using maximax, maximin, equally likely, and minimax regret.

b. The company's initial assessment of the probabilities of the different market sizes is: P(low) = .5, P(average) = .3, P(high) = .2. Determine the production facility size that maximizes expected profits and find the expected value of perfect information.

2. A publisher has received an unsolicited manuscript of a first novel. The decision is whether to offer the author a contract. Based on an initial reading of the manuscript, the publisher estimates the following profits if a contract is offered: If the sales level is high, profits will be $100,000; if moderate, profits will be $20,000; if low, they will lose $30,000. The publisher estimates the probabilities for the sales level to be: P(high) = .1, P(moderate) = .4, P(low) = .5. Determine, based on expected profits, whether the author should be offered a contract or not. Determine the expected value of perfect information.

3. A plumbing contractor has the opportunity to bid on a contract to do the plumbing work for a new office building. After reviewing the blueprints and specifications, the contractor estimates that the job will cost $300,000. The possible bids the contractor might make and his estimates of the probability of winning the contract at each bid level are:

Bid Probability Win

$330,000 .90

$350,000 .75

$375,000 .50

$400,000 .25

$425,000 0

What should the contractor bid if he wishes to maximize his expected profits?

4. A developer is planning a new office complex, which may include some retail space. The possible percentages of retail space that the developer is considering are: none, 20%, or 40%. The desirability of the various percentages of retail space depends on the demand for office space. The estimated yearly profits (in $1,000s) for the different retail percentages and office space demand levels are given in the accompanying table.

Office Space Demand

Retail Percentage Low Medium High

None -100 100 250

20 percent 150 200 200

40 percent 300 150 100

a. Determine what the percentage allocation of retail space should be using the maximax, maximin, equally likely, and minimax regret procedures.

b. The developer's assessments of the probabilities of the different office space demand levels are: P(low) = .3, P(moderate) = .4, P(high) = .3. Determine the percentage allocation of retail space that maximizes expected yearly profits. Find the expected value of perfect information.

5. Mountain Ski Sports, a chain of ski equipment shops in Colorado, purchases skis from a manufacturer each summer for the coming winter season. The most popular intermediate model costs $150 and sells for $260. Any skis left over at the end of the winter are sold at the store's half price sale (for $130). Sales over the years are quite stable. Gathering data from all its stores, Mountain Ski Sports developed the following probability distribution for demand:

Demand Probability

150 0.05

175 0.15

200 0.40

225 0.30

250 0.10

The manufacturer will take orders only for multiples of 20, so Mountain Ski is considering the following order sizes: 160, 180, 200, 220, and 240.

a. Construct a payoff table for Mountain Ski's decision problem of how many pairs of skis to order. What is the best decision from an expected value basis?

b. Find the expected value of perfect information.

c. What is the expected demand? Is the optimal order quantity equal to the expected demand? Why?

6. Bev's Bakery specializes in sourdough bread. Early each morning, Bev must decide how many loaves to bake for the day. Each loaf costs $0.75 to make and sells for $2.85. Bread left over at the end of the day can be sold the next for $1.00. Past data indicate that demand is distributed as follows:

Number of loaves Probability

15 0.05

16 0.05

17 0.10

18 0.10

19 0.20

20 0.40

21 0.05

22 0.05

a. Construct a payoff table and determine the optimal quantity for Bev to bake each morning.

b. What is the optimal quantity for Bev to bake if the unsold loaves cannot be sold to the day-old store at the end of the day (so that unsold loaves are a total loss)?

https://brainmass.com/statistics/confidence-interval/decision-analysis-in-excel-343024

#### Solution Summary

The solution is comprised of detailed step-by-step calculations and explanations of the various aspects of Decision Analysis. This solution provides students with a clear perspective of Maximax Criterion, Maximin Criterion, Equal Likelihood Criterion, Minimax Regret Criterion, Expected Value Criterion, Expected Value of Perfect Information, Optimal Quantity etc.

Decision Analysis

Use the data in the following payoff matrix and regret matrix to answer the following questions:

Payoff Matrix

Airport is Built at Location

Land Purchased at Location(s) A B

A $75.0 $15.0

B ($25.0) $125.0

A&B $52.0 $66.0

None $0.0 $0.0

Regret Matrix

Airport is Built at Location

Land Purchased at Location(s) A B

A $0.0 $110.0

B $100.0 $0.0

A&B $23.0 $59.0

None $75.0 $125.0

17. What is the optimal decision regarding at which location(s) to purchase property using the MAXIMAX decision rule?

18. What is the optimal decision regarding at which location(s) to purchase property using the MAXIMIN decision rule?

19. What is the optimal decision regarding at which location(s) to purchase property using the Criterion of Realism decision rule, assuming that the coefficient of realism is 0.55?

20. What is the optimal decision regarding at which location(s) to purchase property using the Equally Likely decision rule?

21. What is the optimal decision regarding at which location(s) to purchase property using the MINIMAX Regret decision rule?

22. What is the optimal decision regarding at which location(s) to purchase property using the Expected Monetary Value decision rule, assuming the probability of the airport being built at location A is 0.55?

23. What is the optimal decision regarding at which location(s) to purchase property using the Expected Opportunity Loss decision rule, assuming the probability of the airport being built at location A is 0.55?

A consulting firm has contacted your company claiming that their analysis conclusively indicates that the probability the airport will be built at location A is 0.55 (i.e., they have perfect information regarding the probability of the airport being built at location A). The consultant has offered to share their analysis with your company for a fee of $25.0 million.

24. What is the Expected Value of Perfect Information in this scenario?

25. Should your company accept the consultant's offer?

Develop a sensitivity analysis matrix that summarizes the expected monetary value for each possible alternative relative to the probability of location A being selected. Vary the probability of location A being selected from 0.0 to 1.0 in increments of 0.01. Plot the expected monetary value for each possible alternative versus the probability of location A being selected.

26. For what range of probability of the airport being built at location A is purchasing property at location A the optimal decision?

27. For what range of probability of the airport being built at location A is purchasing property at location B the optimal decision?

28. For what range of probability of the airport being built at location A is purchasing property at both locations A and B the optimal decision?

29. For what range of probability of the airport being built at location A is purchasing property at neither location A nor location B the optimal decision?

View Full Posting Details