Bio Pharma needs to determine the proper capacity level for a new drug, Lidocardi. Its goal is to maximize the expected NPV (after tax) earned from the drug during years 0-15, assuming a discount rate of 9 percent per year to discount future cash flows. It costs $10 to build enough capacity to produce one unit of drug per year. All construction cost is incurred during year 0. It costs $1 per year to maintain a unit of annual production capacity. The annual demand of a particular year is estimated to be normally distributed with a mean (described later) and a standard deviation of 6 percent of the mean of the year. In year 1, the mean demand will be 160,000 units. This means that the demand in year 1 is normally distributed with a mean of 160,000 units and a standard deviation of 6%*160,000=9,600 units. In subsequent years, the annual percentage growth of the mean demand for Lidocardi is equally likely to assume any value between 10 percent and 20 percent.

During year 1, each unit of Lidocardi sells for $8. The price of Lidocardi is expected to increase each year at the rate of inflation, which is assumed to be uniformly distributed between 2% and 7%. Unit variable cost is known to be 40 percent of the sales price.

Bio Pharma has a tax rate of 35%. Tax code requires the company to depreciate any construction costs over 10 years.

Create a spreadsheet model to find the optimal capacity level that maximizes the expected NPV (after tax) and replicate it 1000 times.

What is the expected NPV (after tax) with the capacity level you suggest? Establish a 95% confidence interval for the expected NPV (after tax) with the capacity level you suggest. Assume that the initial construction cost is incurred during year 0 and that all cash flows occur at the beginning of the year.