Q) Please use excel to solve the problem and show the formulas:

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.

Note: Use "the risk-adjusted cost of capital for this particular project is 20%" as discount rate for the problem.

You may assume that this project has a twenty-year term and that the firm uses a 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. (I have solved this part):

Part II (25 points) - Please solve part 2 using excel and show the formula. ??

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)

D E G H B 200 million 20 years 10 million A 1 Cost of Plant 2 Life 3 Annual Depreciation 4 5 Revenue if Successful 6 Probablity of success 7 Revnue if Bust 8 Probablity of Bust 9 Expected Revenue 10 11 Cost of goods sold% 12 Cost of goods sold ($) 13 Annual EBIT 14 Tax Rate 15 Annual operating Cash Flow 16 17 Working Capital % 18 Initial Working Capital 500 million 0.4 200 million 0.6 320 million 0.8 256 million 54 million 0.4 42.4 million 0.05 16 19 20 Discount Rate 0.2 21 Initial Cash Flow -216 Million 22 Annual Operating Cash Flow 42.4 23 Terminal Cash Flow 16 million 24 PV of cash flows $206.89 25 NPV ($9.11) million 26 27 The NPV of the project is negative, so the project should not be accepted. 28