Question

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 $275. Any skis left over at the end of the winter are sold at the store's spring sale (for $100). 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.20 200 0.35 225 0.30 0.10 250 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. 1. 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? The best strategy is to order [Select] pairs. Show your work on worksheet Mountain Ski Sports. Follow the principles of good spreadsheet engineering. 2. The expected value of perfect information is [Select] . Show your work on worksheet Mountain Ski Sports. 3. The expected demand is (Select] and ordering this amantit Select 1 tba bost

Answer 1

Payoff table is following:

B с D E F G H А 1 Mountain Ski 2 3 Cost 4 Price 5 Discount price 6 150 275 100 0.05 0.20 0.30 0.10 175 225 7 8 Probability 9 10 Order size 11 160 12 180 13 200 14 220 15 240 16 150 18250 17250 16250 15250 14250 20000 21625 20625 19625 18625 0.35 Demand 200 20000 22500 25000 24000 23000 20000 22500 25000 27500 27375 250 20000 22500 25000 27500 Expected payoff 19912.5 22062.5 23687.5 24087.5 23700 30000 17 18

EXCEL FORMULA:

Write this formula in cell B11 :  =MIN($A11,B$10)*$B$4-$A11*$B$3+MAX(0,$A11-B$10)*$B$5  

then drag and copy it across to column F and down to row 15

Write this formula in cell G11 : =SUMPRODUCT(B11:F11,$B$8:$F$8)

then drag and copy it down to row 15

------

1)

Best decision from an expected value basis

The best strategy is to order 220 pairs

because expected value or payoff is the highest for order size of 220 pairs. Maximum expected value = $ 24087.5

--------------------------

2)

Expected Value with Perfect Information, EVwPI = SUMPRODUCT of maximum profit for each demand state and corresponding probability of that demand state

= 0.05*18250+0.20*21625+0.35*25000+0.30*27500+0.10*30000

= $ 25,237.5

Expected Value of Perfect Information, EVPI = EVwPI - Maximum EV without perfect information

= 25237.5 - 24087.5

= $ 1,150

--------------------------

3)

The expected demand is = 0.05*150+0.20*175+0.35*200+0.30*225+0.10*250 = 205

and ordering this quantity 220 the best decision.