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 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. 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 quantity [Select] the best decision. Show your work on worksheet Mountain Ski Sports.

Answer 1

Spreadsheet model:

B с D E F G H A A 1 с 150 275 100 Cu Со =C3-C2 =C2-C4 S v 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 QID 160 180 200 220 240 Prob 150 175 200 225 250 Expected payoffs =($F$2+$F$3)*MIN(SB7,C$6)-SF$35B7 =($F$2+$F$3)*MIN(SB7,D$6)-$F$3*$B7 =($F$2+$F$3)*MIN(SB7, E$ 6)-$F$3507 =($F$2+$F$3)*MIN(SB7,F56)-SF$35B7 =($F$2+$F$3)*MIN(SB7, G$ 6)-$F$3*$B7 -SUMPRODUCT(C7:47,$C$12:$G$12) =($F$2+$F$3)*MIN(SBS, C$6)-$F$3*$B8 =($F$2+$F$3)*MIN(SB3,D$6)-$F$3*$B8 =($F$2+$F$3)*MIN(SB8, E$ 6)-SF$35B8 =($F$ 2+$F$3)*MIN(SB8,F$ 6)-SF$3$B8 =($F$2+$F$3)*MIN(SB8, G$ 6)-SF$3*$B8 -SUMPRODUCT(CS:GS,SC$12:$G$12) =($F$ 2+$F$3)MINIS B9, C$6)-$F$ 3*$B9 =($F$ 2+$F$3)*MIN(SB9,D$6) SF$3SB9 =($F$2+$F$3)*MIN(SB9, E$ 6)-$F$3*$B9 =($F$ 2+$F$3)*MIN(SB9,F$ 6)-SF$ 3*$89 =($F$2+$F$3)*MIN(SB9, G$ 6)-SF$3$ 89 -SUMPRODUCT(09:G9,$C$12:$G$12) =($F$2+$F$3)*MIN($B10,C$ 6)-$F$3*$810 =($F$2+$F$3)*MIN($B10,D$ 6)-$F$3*5B10 =($F$2+$F$3)*MIN($B10,E$ 6)-$F$3*$B10 =($F$2+$F$3)*MIN($B10,F56)-$F$3$810 =($F$2+$F$3)*MIN($B10,6$ 6)-$F$3*$B10 -SUMPRODUCT(C10:10,$C$12:$G$12) =($F$2+$F$3)*MIN(SB11,C$6)-$F$3$811 =($F$2+$F$3)*MIN(SB11,D$ 6)-$F$3*$B11 =($F$2+$F$3)*MIN(SB11, E$ 6)-$F$3*$811 =($F$2+$F$3)*MIN($B11,F$ 6)-$F$3$811 =($F$2+$F$3)*MIN(SB11,6$ 6)-$F$3$811 -SUMPRODUCT(C11:G11,$C$12:$G$12) 0.05 0.2 0.35 0.3 0.1 EVUPI =MAX(C7:011) =MAX(D7:011) =MAX(E7:E11) =MAX(F7:F11) =MAX(G7:611) -SUMPRODUCT(C14:G14,$C$12:$G$12) Max EVPL = =H14-H10 Demand Prob 150 0.05 175 0.2 200 0.35 225 0.3 250 0.1 Expected demand -SUMPRODUCT(C18:G18,C19:G19) 175 Q\D =H19 150 200 225 250 Expected payoff =($F$2+$F$3)*MIN(SB22,C$21) $F$3*$B22 =($F$2+$F$3)*MIN(SB22,D$21)-$F$35B22 =($F$2+$F$3)*MIN(SB22,E$ 21)-$F$35B22 =($F$2+$F$3)*MIN(SB22,F$21) $F$3*$B22 =($F$2+$F$3)*MIN(SB22,6$21) $F$3*$B22 -SUMPRODUCT(C22:22,$C$12:$G$12)

c	$150		Cu	$125
s	$275		Co	$50
v	$100
Q \ D	150	175	200	225	250	Expected payoffs
160	$18,250	$20,000	$20,000	$20,000	$20,000	$19,913
180	$17,250	$21,625	$22,500	$22,500	$22,500	$22,063
200	$16,250	$20,625	$25,000	$25,000	$25,000	$23,688
220	$15,250	$19,625	$24,000	$27,500	$27,500	$24,088
240	$14,250	$18,625	$23,000	$27,375	$30,000	$23,700
Prob	0.05	0.20	0.35	0.30	0.10
						EVUPI
Max	$18,250	$21,625	$25,000	$27,500	$30,000	$25,238
					EVPI =	$1,150
Demand	150	175	200	225	250	Expected demand
Prob	0.05	0.2	0.35	0.3	0.1	205
Q \ D	150	175	200	225	250	Expected payoff
205	$16,000	$20,375	$24,750	$25,625	$25,625	$23,788

1. Best alternative - Q=200

2. EVPI = 1150

3. Expected demand=205