Reference no: EM133209322
Question:
A drugstore has been in the habit of ordering just one case of hand sanitizer at a time. Each case contains 24 bottles, and each bottle contains 500 mL of hand sanitizer. However, recently demand has been very strong, and they are thinking of placing larger orders, which would lower the cost per case, and hence lower the cost per bottle.
If they order one case, the cost would be $14.50 per bottle; 2 cases would cost $13.75 per bottle, 3 cases would cost $12.50 per bottle. and 4 cases or more would cost $11.75 per bottle. The retail selling price will be $18.75 per bottle, however any bottles left unsold within a month of the best-before date will be sold off for $6.50 per bottle.
The owner believes that at the regular price the possible demands are 1, 2, 3, 4, 5, 6, 7, or 8 dozens of bottles, with probabilities 0.05, 0.10, 0.15, 0.20, 0.20, 0.15, 0.1, and 0.05 respectively. The drugstore must place its entire order now. Assume that they will suffer no loss of goodwill if they happen to be out of stock.
(a) Make and solve a model in Excel to provide a recommendation to the store based on maximizing the expected profit.
(b) Determine the expected value of perfect information.
(c) Suppose that the $6.50 to be received for each leftover bottle is negotiable within the range $4 to $10. Over what range for this value would the recommended order quantity found in part (a) be valid?
(i) This can be found by manually varying the number in whatever cell was used for the salvage value in part (a).
(ii) This can also be done by using Goal Seek in Excel, which is found under Data/What-if Analysis. In Goal Seek there are three boxes to be filled in, called Set cell:, To value:, and By changing cell:. Make two cells which calculate the difference in Expected Profit between the optimal order quantity row and (i) the row above it, and (ii) the row below it. Now run Goal Seek twice, where the objective in each is to make one of these two cells equal to 0. Here, the cell which computes the difference is the Set cell:, the To value: is 0, and the By changing cell: is the cell which contains the salvage value