solve the problem in Excel and shows the formulas

The Problem

A state-of-the-art automobile assembly plant designed to produce a new line of Harley, called the V-Rod, will cost $200 million to build. If V-Rod is successful, the new plant will generate a constant stream of revenues for the next 20 years in the amount of $500 million per annum. However, if V-Rod is a bust the anticipated stream of revenues will be in the amount of $200 million per annum. Financial analysts assessment reveals a 40% chance of success and a 60% chance of failure. Costs of goods are approximately 80% of revenues and are expected to be constant through the years. The level of working capital is 5% of the next years sales revenues. The risk-adjusted cost of capital for this particular project is 20% while the opportunity cost of capital for the company is only 9%. Marginal tax rate is 40% for the company.

You may assume that this project has a twenty-year term and that the firm uses straight-line depreciation method with a zero salvage value.

Part I (15 points)

Determine whether the firm should undertake this investment opportunity. Take your pick of the best investment criterion to use. You need to explain why you use that criterion and solve the problem using only that criterion.

Part 2:

Now, suppose that the firm can pay a third party, the Silly Insurance Co., $12 million today at t=0 to insure the firm against the projects future downside risk exposure. In particular, the writer of the insurance contract agrees to purchase the plant from the company at a pre-determined price of $190 million, to be paid at t=2 (end of year) after two full years of operations. In other words, the firm has an option to abandon the project after two years of operation.

1. Given this insurance option, should the firm undertake the investment project now? (20 points)
2. Should the firm purchase this insurance contract? (5 points)