A firm is considering purchasing a new machine for $450,000. This machine will allow the firm to sell an additional 16,000 units of its product per year at $30 per unit for five years, after which the machine will be sold for $40,000. Production costs will be $10 per unit. Maintenance of the machine will be covered under the terms of an existing maintenance contract the firm entered into and paid for four years ago. This contract provides for maintenance for all the firms machines for the next six years for no cost beyond the original price of the contract. The cost of that contract was $10,000.

The firm plans to depreciate the machine over FOUR years using straight-line depreciation. The new machine will require net working capital of $70,000 per year in years zero, one and two and $50,000 per year in years three and four. At the end of year five, the firm will recover its working capital. The tax rate is 35 percent. The cost of capital is 15 percent.

Build a spreadsheet to show the following output variables:

The projects EBIT each year,

The project's net income each year,

The projects operating cash flows each year,

The projects cash flows from working capital each year,

The net cash flows associated with the project each year,

The projects NPV, and

The projects IRR.

Use the following input variables in the spreadsheet:

Unit sales price,

Units sold,

Unit production costs,

Salvage value,

Tax rate,

Cost of capital, and

Cost of new machine.

Put the input variables at the top of the spreadsheet. When I grade your spreadsheet, I will want to be able to change any of the input variables at the top of the spreadsheet and have the spreadsheet automatically recalculate the output variables.

Find NPV, IRR, Payback Period and Discounted Payback Period