MUST have spreadsheet set up in the same order as below:

PLEASE show formulas you used; I'm learning how to use Excel.

Thank you!

Consider the following scenario:

Gamma Inc. is considering the replacement of an existing machine. The new machine costs $1.8 million and requires installation costs of $250,000. The existing machine can be sold currently for $125,000 before taxes. The existing machine is 3 years old, cost $1 million when purchased, and has a $290,000 book value and a remaining useful life of 5 years. It was being depreciated under MACRS using a 5-year recovery period.

If it is held for 5 more years, the machines market value at the end of year 5 will be zero. Over its 5-year life, the new machine should reduce operating costs by $650,000 per year, and will be depreciated under MACRS using a 5-year recovery period.

The new machine can be sold for $150,000 net of removal and cleanup costs at the end of 5 years. A $30,000 increase in net working capital will be required to support operations if the new machine is acquired. The firm has adequate operations against which to deduct any losses experienced on the sale of the existing machine.

The firm has a 15% cost of capital, is subject to a 40% tax rate and requires a 42-month payback period for major capital projects.

Should they accept or reject the proposal to replace the machine? What is the NPV? What is the IRR? What is the payback period?

Use the following table for depreciation:

5-Year MACRS

Year 1- 20%

Year 2- 32%

Year 3- 19%

Year 4- 12%

Year 5- 12%

Year 6- 5%

Assumptions: Tax Rate= 40% Old machine purchased for= $1,000,000 Old Machine - Book Value= $710,000 Initial Investment Installed cost of new asset= Cost of the new machine= $1,800,000 Installation costs= $250,000 Total cost of new machine= After-tax proceeds from sale of old asset= Proceeds from sale of existing machine= Tax on sale of existing machine= Total after-tax proceeds from sale= Increase in net working capital= Initial investment= Terminal CF After-tax proceeds from sale of new asset= Proceeds from sale of new asset= BV of "new" machine at end of project=

Tax on sale of new asset= Total after tax proceedssale of new asset= Change in net working capital= Terminal cash flow= MACRS Yr 1 MACRS Yr 2 MACRS Yr 3 MACRS Yr 4 MACRS Yr 5 CF for New Machine 20% 32% 19% 12% 12% YEAR 0 1 2 3 4 5 EBIT Improvement Depreciation Net Profit before Tax Tax (@40%) Net Profit After Tax Operating Cash Flow CF for Old Machine YEAR 0 1 2 3 4 5 EBIT Improvement (none for business-as-usual) - 0 - 0 - 0 - 0 - 0 Depreciation Net Profit before Tax Tax (@40%) Net Profit After Tax Operating Cash Flow Incremental CF YEAR 0 1 2 3 4 5 New Machine CF Old Machine CF Incremental CF (Difference) Project Cash Flows (to be discounted) Hurdle Rate YEAR 0 1 2 3 4 5 Initial Investment Incremental CF Terminal Value Total CF PV NPV (Sum of PVs, less Initial Investment) NPV (using formula) IRR PBP

Should they accept or reject the proposal to replace the machine?

What is the NPV?

What is the IRR?

What is the payback period?