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