3. {IS marks} 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 Sill} 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.51} per bottle. and 4 cases or more would cost $1135 per bottle. The retail selling price will be $13.15 per bottle. however any bottles left unsold within a month of the beatbefore date will be sold off for $6.50 per bottle. The owner believes that at the regular price the possible demands are I, 2, 3., 4, 5, ti, 1', or S dozens of bottles. with probabilities [Lilli D. It). (115, can. {LEI}. {LI 5. {1.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 Wmendation to the store based on maximizing the expected prot. (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 $l{l. Dyer what range for this value would the rec ommended 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 DataIWhat-if Analysis. In Goal Seek there are three boxes to be lled in. called Set cell:, Ta values, and By changing cell: Malte two cells which calculate the dilTerence in Expected Prot betwm the optimal or der 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 t}. Here. the cell which computes the difference is the Set cell', the To mine: is l], and the By changing cell: is the cell which oontains the salvage value