Please help solve

Bio Pharma needs to detennine the proper capacity level for a new drug, Lidocardi. Its goal is to maximize the expected NPV [after tax] eamed from the drug during years 015, assuming a discount rate of 0 percent per year to discoUnt futUIe cash ows. 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 El percent ofthe 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'i' l,000=9,00 units. In subsequent years. the annual percentage growth of the mean demand for Lidocardi is equally likely to assume any 1value between 10 percent and 20 percent. During year 1, each unit of Lidocardi sells for $3. The price ofLidocardi is expected to increase each year at the rate of ination1 which is assumed to be uniformly distributed between 2% and tat. Unit variable cost is known to be 40 percent of the sales price. Bio Pbarma has a tax rate of 35%. Tax code requires the company to depreciate any construction costs over 10 years. You may nd out how to calculate the NEW {after tax) from the example shan on this webpage: ht :'accountitt ex lainedcun'a'mana crial-I'ca italbud clin 1'l yand taxes 3} bl {11 pts) lCreate a spreadsheet model to find the optimal capacity level that maximiEs 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% condence interval for the expected NPV (after tax] with the capacity level you suggest. Assume that the initial construction cost is incurred during year [l and that all cash ows occur at the beginning of the year. ue Excel le Probza for part a}. {12 pts] Suppose that Bio Pharn1a has the additional opportunity to review demtind during year 5 and, if desired, build additional capacity. To model this situation, assume that after observing year 5 demand, Bio Pharma proceeds as follows: Ifycar 5 demand is at least the initial capacity {year 1}, then Bio Pharma will add additional capacity. If year 5 demand is less than the initial capacity, then Bio Phamia will stick with their year 1 capacity. We assume it costs $12 to build one unit of capacity during year 5. Thus, Bio Pharma's capacity strategy is dened by two decisions: I: initial capacity {year 1] I the additional capacity built in year 5. With the additional opportunity to review demand and add capacity in year 5, what 1.r'alues would you recommend for the initial capacity, and the additional capacity so that the expected NPV {after tax} is maximized? What is the expected Nil-W (after tax] with the parameters you suggest? If the company adds additional capacity in year 5, then the additional construction cost incurs in year 5,. the depreciation of this cost starts from year ti, and the additional capacity is available in year 6. ne Excel le m for part b). {1 pt] Write a summary of your solutions to parts a} and b]. What insights can you get from this problem? Insert the dichssion in the sheet of part b}