Question

Please show in Excel

The Rapport Company currently uses a machine that was purchased 4 years ago. This machine is being depreciated on a straight-line basis, and it has 5 years of remaining life. Its current book value is $5,000, and it can be sold for $6,500 at this time. Thus, the annual depreciation expense is $5000/5 = $1000 per year. If the old machine is not replaced, it can be sold for $500 at the end of its useful life. Rapport is offered a replacement machine which has a cost of $25,000, an estimated useful life of 5 years, and an estimated salvage value of $2,500. This machine fall into the MACRS 5-year class so the applicable depreciation rates are 20%, 32%, 19%, 12%, 11%, and 6%. The replacement machine would permit an output expansion, so sales would rise by $5,000 per year; even so, the new machine's much greater efficiency would cause operating expenses to decline by $1,200 per year. The new machine would require that inventories increase by $1,000, but accounts payable would simultaneously increase by $300. Rapport's marginal federal-plus-state tax rate is 39%, and its WACC is 12%. Should it replace the old machine based on the NPV rule? Show all work. Fill in the template below.

Answer 1

			old machine at time 0
New machine cost		-25000	sales price	book value	tax rate	taxes
salvage value, old machine		5915	6500	5000	39%	585
new cost of new machine		-19085
		Time 0	Time 1	Time 2	Time 3	Time 4	Time 5
Additional revenue			5000	5000	5000	5000	5000
cost savings = new-old			1200	1200	1200	1200	1200
total additional cash flow			6200	6200	6200	6200	6200
after tax savings = cost saving*(1-tax rate)			3782	3782	3782	3782	3782
change in depreciation =(new -old)			4000	7000	3750	2000	1750
Depreciation tax savings = change in depreciation*tax rate			2440	4270	2287.5	1220	1067.5
Incremental cash flow = after tax cost savings+ depreciation tax savings			6222	8052	6069.5	5002	4849.5
Capex at salvage value							2110
change in NOWC	-1000+300	-700					700
total project cash flow		-19785	6222	8052	6069.5	5002	7659.5
tax rate	39%
WACC	12%
NPV	NPV(12%,D16:H16)+C16	4034.58
New	sales price	Book value	tax rate	taxes		ATSV
	2500	1500	39%	390		2110
Old	sales price	Book value	tax rate	taxes =(6500-5000)*39%		ATSV =(6500-585)
	6500	5000	39%	585		5915
		1	2	3	4	5	total accumulated depreciation at the end of year five	Book value of machine= cost of machine-total accumulated depreciation
Depreciation	New = 22500/5	5000	8000	4750	3000	2750	23500	1500
	Old	1000	1000	1000	1000	1000
	Incremental depreciation	4000	7000	3750	2000	1750
Yes machine should be replaced as this replacement results in Positive NPV