Assignment - Capital Budgeting

• Open a new (fresh) excel workbook to perform you work.
• You are allowed only one submission, so please make sure it is the correct one.
• Work independently and do not use class exercise template (or any other template)

A company is considering the purchase of a new machine that will enable it to increase its expected sales.  The machine will have a price of $120,000.  In addition, the machine must be installed and tested.  The costs of installation and testing will amount to $30,000.  The machine will be depreciated using 5-years MACRS. (Use MACRS table from class excel exercise by copying the table and pasting it)

The equipment will be operated for 5 years.  The sales in the first year of operation are expected to be $260,000.  Then, sales will grow by 5% a year. The annual operating costs (before depreciation) will consist of fixed operating costs of $25,000 plus variable operating costs equal to 75% of sales. 

To support the increased level of production, the inventory of raw materials will have to be increased from $40,000 to $50,000 when the machine is purchased.  The additional inventory will be carried until the machine is scrapped following the 5 years of operation.

At the end of the 5-year operating life of the project, it is assumed that the equipment will be sold for $50,000. 

The tax rate is 40% and the company's weighted average cost of capital is 9.50%. 

Build a capital budgeting model to answer the following questions:

1) What is the operating cash flow in year 1-5?

2) What is the initial outlay in year 0?

3) What is the after tax salvage at the terminal year?

4) Calculate NPV, IRR, and PI for the project.