Question

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:

Lay out the cash flows for years 1-5

Calculate the project NPV, using the Excel NPV function

Calculate the project IRR, using the Excel NPV function

Answer 1