1. You have been assigned to a project to determine if a new investment should be made. Your company uses a capital structure of 30% debt and 70% equity. The debt pays 8.5% interest; the required return of the equity is 15%. The investment will be analyzed using a ten-year cash flow analysis. Here are the financial details: Year Expected sales Cost of Goods (%of sales) R&D expense 1 $1,000,000 40% $1,000,000 2 2,000,000 30% 500,000 3 3,000,000 25% 200,000 4 3,600,000 20% 200,000 5 4,000,000 20% 200,000 6 4,500,000 20% 200,000 7 5,000,000 20% 200,000 8 5,000,000 22% 100,000 9 4,000,000 24% 100,000 10 2,000,000 25% 50,000 Sales and marketing expenses are estimated to be 15% of sales. The corporate tax rate is 40% . The investment will be $4,000,000 at the start of the program (Year 0). For tax purposes, the investment will be amortized over 10 years. Administration costs are 4% of sales. The hurdle rate for the project is 1% over the companys cost of capital. Calculate the IRR of this project and determine if it exceeds the hurdle rate or not. (Set up an Excel spreadsheet (Years 0-10) to do this calculation. Make sure you use the depreciation benefit in the tax calculation and the cash flow analysis.) Summarize your findings in a memo as the cover for the Excel spreadsheet. The memo should be written in a business style. A good format would include: Summary paragraph Paragraph explaining problem Paragraph explaining what calculations you did. Paragraph with your key calculations and conclusions