Question

11. The owner of a ski apparel store in Winter Park, Colorado, must make a decision in July regarding the number of ski jackets to order for the following ski season. Each ski jacket costs $54 each and can be sold during the ski season for $145. Any unsold jackets at the end of the season are sold for $45. The demand for jackets is expected with a average rate of 80 The store owner can order jackets in lot sizes of 10 units a. How many jackets should the store owner order if she wants to maximize her b. What are the best-case and worst-case outcomes the owner may face on this c. How likely is it that the store owner will make at least $7,000 if she implements d. How likely is it that the store owner will make between $6,000 to $7,000 if she expected profit? product if she implements your suggestion? your suggestion? implements your suggestion?

If could be done using excel or google sheets that would be appreciated.

Answer 1

This problem can be solved by using MS Excel Solver as shown below:

The excel sheet with the formula is shown below:

a) From the above workings, we find that the optimum number of jackets to store is 90

Demand Lc Probability Conditional loss Expected loss profit per jacket loss per unsold jacket 2 145-54 3 6 7 solver Stock Real Stock Expected loss -EXP(-$BŞ1 (SB$1AG10)/FACT (G10) -EXP(-SB$18S1 G11)/FACT(G11) F(G11-s8S6,0,IFIG11 SB$6,(S8S6-G11) $8$3, G11-SB$6)S82)) 111 H11 -EXP-$B$1)*(B51*G12)/FACT(G12) -EXP(-$B$1)*(551 G17)/FACTG17) EXP(-$BŞ1 (B$1AG20)/FACT (G20) -EXP(-$B$1) (551 G22)/FACT(G22) IF(G22-$B$6,0,IF(G22 $B$6,($8S6-G22)$B$3,(G22-$B$6) $8$ 122 H22 EXP(-$B$1(SB$1AG25)/FACT G25) EXP(-$B$1(SB$1AG30)/FACTG3O) -EXP(-$B$1)"(5B$1AG32)/FACT(G32) IF(G32-$8$6,0,IF(G32 $B$6,($856-G32)$B$3,(G32-$B$6) $8$2 132 H32

b) The best case outcome when stocking 90 is when the demand is 90. The total profit earned is 90*(145-54)= $8190

The worst case can be as high as any demand as we are considering the opportunity cost which is the opportunity of making a profit of $91 by selling a jacket

c) The profit for each jacket sold is $91 and loss due to an unsold jacket is $9.

Profit when the demand is 78 and stock is 90 = 78*91-(90-78)*9 = $6990

When the demand is 79 and above the profit, while stacking 90 is at least 7000. Profit = 79*91-(90-79)*9 = $7090

Hence when the demand >=79 the profit is at least $7000.

The probability that the demand is >=79 is 0.5594 (use excel formula =1-POISSON.DIST(78,80,TRUE))

d. the quantity to at which profit is >$6000 is 69 = (69*91-(90-69)*9)

From above we know that at 78 the profit is less than $7000

The required probability is when Qty is between 69 and 78 which is 0.3436 [ use the formula =POISSON.DIST(78,80,TRUE) - POISSON.DIST(68,80,TRUE)]