Executives at Meds-R-Us have decided to build a new production facility for the company’s best-selling high-blood-pressure drug. The problem they now face is determining the size of the facility (in terms of production capacity). Last year, the company sold 1,085,000 units of this drug at a price of $13 per unit. They estimate the demand for the drug to be normally distributed with a mean increasing by approximately 59,000 units per year over the next 10 years with a standard deviation of 30,000 units. They expect the price of the drug to increase with inflation at a rate of 3% per year. Variable production costs currently are $9 per unit and are expected to increase in future years at the rate of inflation. Other operating costs are expected to be $1.50 per unit of capacity in the first year of operation, increasing at the rate of inflation in subsequent years. The plant construction cost is expected to be $18 million for 1 million units of annual production capacity. The company can increase the annual production capacity above this level at a cost of $12 per unit of additional capacity. Assume that the company must pay for the plant when it is completed and all other cash flows occur at the end of each year. The company uses a 10% discount rate on cash flows for financial decisions.

a. Create a spreadsheet model to compute the net present value (NPV) for this decision.

b. What is the expected NPV for a plant with a production capacity of 1.2 million units per year?

c. What is the expected NPV for a plant with a production capacity of 1.4 million units per year?

d. How large a plant should the company build if they want to be 90% certain of obtaining a positive NPV for this project?