Big Pharma is a large drug company that is trying to decide whether one of its new drugs, Zam, is worth pursuing. Zam is in the last stages of development and the final cost to complete it is $9.3 million. Big Pharma estimates that the demand for Zam will gradually grow and then decline over its useful lifetime of 20 years (which is the period during which it is covered by patent protection). The company expects its cash flows to be $1.2 million in year 1, then to increase at an annual rate of 10% through year 8, and finally to decrease at an annual rate of 5% through year 20. Big Pharma wants to develop a spreadsheet model of its 20-year cash flows, assuming that they are incurred at the end of each year. Assume an annual discount rate of 12% for the purpose of calculating NPV. Remember to use good modeling practices as discussed in the class. Data shell on the M-drive: Ques 3 (Shell) Required: 1. Calculate the NPV for the project. Is the drug worth pursuing, or should Big Pharma abandon it and not incur the $9.3 million development cost? Explain fully. 2. Calculate the IRR for the project. What does the IRR tell you about the project? 3. What is the payback period in years (calculate to the nearest year)? 4. How do changes in the assumed discount rate affect the NPV? (Hint: Create a one- way data table.) 5. How do changes in the rate of cash flow increases and the number of years of increasing cash flows affect the NPV of the project? (Hint: Create a two-way data table.) 6. Use the scenario manager' functionality in Excel to create best-case and worst-case scenarios for this model. (Use the variables relating to the development cost and the cash flow in year 1 to do this.)