Reference no: EM131865703

You will complete a capital budgeting Excel spreadsheet solution based on:

Cost of new equipment $14,800,000

Shipping and installation 200,000

Unit sales

Year 1 70,000

Year 2 120,000

Year 3 120,000

Year 4 80,000

Year 5 70,000

Sales price = $300 / unit in years 1-4 and $250/unit in year 5

Variable cost = $140 / unit

Fixed cost = $700,000 per year for all 5 years

Working capital : $200,000 at start of project, recovered at end

Depreciation = straight line over 5 years

Tax rate = 34%

Cost of capital = 15%

You must complete this project using an Excel spreadsheet. Your assignment specifically:

1. Lay out the cash flows for years 1-5

2. Calculate the project NPV, using the Excel NPV function (write the function on your submitted spreadsheet)

3. Calculate the project IRR, using the Excel NPV function (write the function on your submitted spreadsheet)