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)?