Question

*****SOLVE USING EXCEL AND PLEASE SHOW THE EXCEL COMMANDS THAT ARE USED********

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 $100,000.  In addition, the machine must be installed and tested.  The costs of installation and testing will amount to $10,000.  The machine will be depreciated using 3-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 3% a year. The annual operating costs (before depreciation) will consist of fixed operating costs of $25,000 plus variable operating costs equal to 70% of sales.  

         To support the increased level of production, the inventory of raw materials will have to be increased from $30,000 to $50,000when 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 $40,000.  

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

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 and PI for the project.

Check points:

NI in year 2 = $3,834

IRR = 26.73%

Answer 1

NPV is the difference between present value of operating cash flows and initial investment.

present value of operating cash flows = Year 1 operating cash flows/(1+weighted avg. cost of capital) + Year 2 operating cash flows/(1+weighted avg. cost of capital)² + Year 3 operating cash flows/(1+weighted avg. cost of capital)³ .... + Year 5 operating cash flows/(1+weighted avg. cost of capital)⁵

PI is the ratio of present value of operating cash flows to initial investment. A PI of more than 1 indicates a project is profitable.

initial outlay in year 0 = cost of equipment + installation and testing cost + increase in working capital

3-year MACRS depreciation rates are 33.33%, 44.45%, 14.81% and 7.41%.

after tax salvage at the terminal year = (Selling price of equipment - book value of equipment)*(1-tax rate)

equipment is fully depreciated. so, book value is zero.

after tax salvage at the terminal year = ($40,000 - $0)*(1-0.40) = $40,000*0.60 = $24,000

	Years	0	1	2	3	4	5
	Initial cost	-$110,000	0	0	0	0	0
	Increase in working capital	-$20,000	0	0	0	0	0
	Sales	$0	$260,000	$267,800	$275,834	$284,109	$292,632
Less:	Variable operating costs	$0	$182,000	$187,460	$193,084	$198,876	$204,843
Less:	Fixed operating costs	$0	$25,000	$25,000	$25,000	$25,000	$25,000
Less:	Depreciation	$0	$36,663	$48,895	$16,291	$8,151	$0
	Pre-tax cash flow	$0	$16,337	$6,445	$41,459	$52,082	$62,790
Less:	Taxes @40%	$0	$6,535	$2,578	$16,584	$20,833	$25,116
	After-tax cash flow	$0	$9,802	$3,867	$24,876	$31,249	$37,674
Add back:	Depreciation	$0	$36,663	$48,895	$16,291	$8,151	$0
Add back:	recovery of working capital Inv	$0	$0	$0	$0	$0	$20,000
Add:	Sale of equipment	$0	$0	$0	$0	$0	$40,000
Less:	Tax on equip. sale	$0	$0	$0	$0	$0	$16,000
	Operating cash flow	-$130,000	$46,465	$52,762	$41,167	$39,400	$81,674
	NPV	$69,819.79
	PI	1.54

Calculation