Please answer the below in Excel with calculations.
Pharmagen is a drug company with several initiatives in its R&D pipeline. Its scientists have developed several patented product ideas, one of which has become a lead candidate for a development effort due to its potential market demand. The total estimated cost to launch the product, including its development, is $95 million, in present value terms. It takes five years to fully develop this product. During this development phase, there is no cash flow associated with this product. For each year over the next five years after the product has been fully developed, the finance and marketing groups of the company have estimated the following cash flows (figures in million dollars): 20, 22, 24, 28, and 30, respectively. After that, the annual cash flow is expected to grow only at 2.5% in perpetuity due to competition from generic drugs. The finance group considers 15% to be the appropriate discount rate for this project. Despite its market potential, this product faces stiff competition from other major projects in the company's pipeline. The Vice President of the division decides to explore a strategic abandonment option. At any time during the next five years of development, the company can either continue with the development effort or sell off its intellectual property for $65 million to a strategic partner, who has shown great interest in this technology. The annual standard deviation of logarithmic returns of the future cash flows is estimated to be 40%, and the continuously-compounded annual risk-free rate of return over the next five years is 4%. Using the Binomial Option Pricing approach, what is the value of the abandonment option? Also show when would be optimal for the company to abandon this project. Pharmagen is a drug company with several initiatives in its R&D pipeline. Its scientists have developed several patented product ideas, one of which has become a lead candidate for a development effort due to its potential market demand. The total estimated cost to launch the product, including its development, is $95 million, in present value terms. It takes five years to fully develop this product. During this development phase, there is no cash flow associated with this product. For each year over the next five years after the product has been fully developed, the finance and marketing groups of the company have estimated the following cash flows (figures in million dollars): 20, 22, 24, 28, and 30, respectively. After that, the annual cash flow is expected to grow only at 2.5% in perpetuity due to competition from generic drugs. The finance group considers 15% to be the appropriate discount rate for this project. Despite its market potential, this product faces stiff competition from other major projects in the company's pipeline. The Vice President of the division decides to explore a strategic abandonment option. At any time during the next five years of development, the company can either continue with the development effort or sell off its intellectual property for $65 million to a strategic partner, who has shown great interest in this technology. The annual standard deviation of logarithmic returns of the future cash flows is estimated to be 40%, and the continuously-compounded annual risk-free rate of return over the next five years is 4%. Using the Binomial Option Pricing approach, what is the value of the abandonment option? Also show when would be optimal for the company to abandon this project
