PLEASE SHOW THE FORMULA VIEW OF EXCEL IF POSSIBLE

Question 2. Suppose you are working for a pharmaceutical company, and your company has a novel drug entering Phase III clinical trial next year. You analyzed the FDA regulation and the R&D history of your company and estimated that there will be 40% chance for the new drug to pass the Phase III clinical trial and get approved, 60% chance to fail in Phase III or could not get approved. Once approved, your marketing team estimated the drug can bring in a net profit of $100 million in the first year, and the profit will increase by $10 million every year until it hits $200 million. The profit stream will remain stable then till the 20th year on the market. After the 20th year on the market, the drug is expected to bring in negligible profit due to the expiration of patent and the new drugs that may replace it. There is a 30% chance to finish the trial in 3 years (short trial), a 50% chance to finish the trial in 5 years (medium trial), and a 20% chance to finish the trial in 7 years (long trial). The cost of running the clinical trial is $40 million every year. The company uses a discount rate of 4%. Please find the rNPV of this new drug project. You can use the rNPV spreadsheet posted along with the lecture slides as a reference. You need to submit your spreadsheet on myCourses. Again, please keep your spreadsheet presentable