OuRx, a retail pharmacy chain, is faced with the decision of how much flu vaccine to order for the next flu season. OuRx has to place a single order for the flu vaccine several months before the beginning of the season because it takes four to five months for the supplier to create the vaccine. OuRx wants to more closely examine the ordering decision because, over the past few years, the company has ordered too much vaccine or too little. OuRx pays a wholesale price of $12 per dose to obtain the flu vaccine from the supplier and then sells the flu shot to their customers at a retail price of $20.

Because OuRx earns a profit on flu shots that it sells and it can’t sell more than its supply, the appropriate profit computation depends on whether demand exceeds the order quantity or vice versa. Similarly, the number of lost sales and excess doses depends on whether demand exceeds the order quantity or vice versa. Demand for the flu vaccine is uncertain.

The VaccineDemand worksheet in the file vaccine contains data produced by epidemiologists to help OuRx gain insight on demand for flu vaccine at their retail pharmacies.

a. Construct a base spreadsheet model that correctly computes net profit for a given level of demand and specified order quantity. For an order quantity of 500,000 doses, what is the net profit when demand is 400,000 doses and 600,000 doses, respectively?

b. To help determine how to model flu vaccine demand, construct a histogram of the data provided in the VaccineDemand worksheet in the file vaccine. In column B, compute the natural logarithm (using the Excel function LN) of each observation and construct a histogram of these logged demand observations. Based on the histograms of the non-logged demand and logged demand, respectively, what seems to be a good choice of probability distribution for (non-logged) vaccine demand?

c. Representing flu vaccine demand with the type of random variable you identified in part (b), complete the simulation model and determine the average net profit resulting from an order quantity of 500,000 doses. What is the 95% confidence interval on the average profit? What is the probability of running out of the flu vaccine?