Instruction From the demo on Calcuating NPV (check Example 1.6 in Canvas) modify the model so that development lasts for an extra 2 years. Specifically, assume that development costs of $12 million and $3 million are incurred at the beginnings of years 1 , and then the sales in the current model occur one year later, that is, from year 2 until year 22. Imagine that in this model the sales increase, then stay steady, and finally decrease. The gross margin is $3 million in year 1 , then increases by 10% annually through year 6 , then stays constant through year 11 , and finally decreases by 5% annually through year 22. Create a one-way data table in the model to see how the NPV varies with discount rate, which is allowed to vary from 6% to 10% in increments of 0.5%. Use conditional formatting to differentiate years of negative NPV and positive NPV (Note: Choose your own colors for positive and negative NPV). Should the company pursue the drug for all of these discount rates? Perform a sensitivity analysis with a two-way data table to see how NPV varies with years in along side (from 3 to 12 with an increment of 1) and discount along top (from 6% to 10% in increments of 0.5% ). Use conditional formatting to differentiate years of negative NPV and positive NPV (Note: Choose your own colors for positive and negative NPV). Comment on whether the company should pursue the drug, given your results. Instruction From the demo on Calcuating NPV (check Example 1.6 in Canvas) modify the model so that development lasts for an extra 2 years. Specifically, assume that development costs of $12 million and $3 million are incurred at the beginnings of years 1 , and then the sales in the current model occur one year later, that is, from year 2 until year 22. Imagine that in this model the sales increase, then stay steady, and finally decrease. The gross margin is $3 million in year 1 , then increases by 10% annually through year 6 , then stays constant through year 11 , and finally decreases by 5% annually through year 22. Create a one-way data table in the model to see how the NPV varies with discount rate, which is allowed to vary from 6% to 10% in increments of 0.5%. Use conditional formatting to differentiate years of negative NPV and positive NPV (Note: Choose your own colors for positive and negative NPV). Should the company pursue the drug for all of these discount rates? Perform a sensitivity analysis with a two-way data table to see how NPV varies with years in along side (from 3 to 12 with an increment of 1) and discount along top (from 6% to 10% in increments of 0.5% ). Use conditional formatting to differentiate years of negative NPV and positive NPV (Note: Choose your own colors for positive and negative NPV). Comment on whether the company should pursue the drug, given your results