Executives at PharmaPro Solutions have decided to establish a new production facility for the company$$s top$-$selling cholesterol medication. The challenge now lies in determining the facility's optimal size $($in terms of production capacity$).$ Last year, the company sold $960,000$ units of this medication at a price of $$14$ per unit. They anticipate the demand for the medication to follow a normal distribution, with the mean increasing by approximately $45,000$ units per year over the next $10$ years and a standard deviation of $25,000$ units. The price of the medication is expected to increase annually with inflation at a rate of $2\mathrm{.}5\%.$ The variable production cost is currently $$9$ per unit and is also projected to increase with inflation at the same rate. Other operating costs are estimated at $$2$ per unit of capacity in the first year of operation, with inflationary increases in subsequent years. The construction cost of the facility is projected at $$18$ million for a baseline capacity of $1$ million units per year. Additional capacity above this level can be added at a cost of $$10$ per unit of extra capacity. Assume the company pays for the facility at the time of its completion, while all other cash flows occur at the end of each year. The company applies a $12\%$ discount rate to cash flows for its financial decisions. Question $2$a Construct a spreadsheet model to calculate the net present value $($NPV$)$ for this project for a given capacity. Identify the variable and show the setting needed. Make any assumptions where necessary. $(25$ marks$)$

Question $2$b What is the expected NPV for a facility with a production capacity per year of $1,000,000+7*100,000$ units, per year? $(5$ marks$)$ Question $2$c Determine what is the best capacity that provides the highest NPV for the given. You should consider various values of the capacities $($at least $10).$ Show the output charts. $(10$ marks$)$ Question $2$d Calculate the facility's capacity that will ensure a $90\%$ probability of achieving a positive NPV for this investment. You should consider various values of the capacities $($at least $10).$