Question

Needs to be in EXCEL

A new engineer who just was hired by Machine Excellence is pushing for a brand new laser machining center. He insists that it is a money maker from the first day. He did not figure the deprecation and the after taxes cash flow (CFAT). Give this rookie engineer a little intelligent guidance here on the CFAT for a ROR analysis of the new laser equipment. Here are the figures for the production machining equipment for making detailed laser signs and complex shapes. Calculate the CFAT and ROR for the end of 6 years. Answer the question: "Is the investment economically viable"?

Depr method: MACRS GDS

Class life: 5 Years

Depr recapture: Taxed as TI

First cost: $550,000

Gross Income:$250,000

Expenses: $80,000

Salvage: 0 in year 6

Effective Tax Rate: 45%

After-tax MARR: 10% per year compounded semiannually

Answer 1

Year	0	1	2	3	4	5	6
1.First cost	-550000
2.Gross Income		250000	250000	250000	250000	250000	250000
3.Expenses		-80000	-80000	-80000	-80000	-80000	-80000
4.MACRS Depreciation		-110000	-176000	-105600	-63360	-63360	-31680
5.EBT(2+3+4)		60000	-6000	64400	106640	106640	138320
6.Tax at 45%(5*45%)		-27000	2700	-28980	-47988	-47988	-62244
7.EAT(5-6)		33000	-3300	35420	58652	58652	76076
8. Add back:depn.(Row 4)		110000	176000	105600	63360	63360	31680
9.OCFs(7+8)		143000	172700	141020	122012	122012	107756
10.CFAT---FCFs(1+9)	-550000	143000	172700	141020	122012	122012	107756
11.PV F at MARR 10.25%p.a.(1/1.1025^Yr.n)	1	0.90703	0.82270	0.74622	0.67684	0.61391	0.55684
12.PV at 10.25%(10*11)	-550000	129705.2	142080.7	105231.3	82582.52	74904.78	60002.57
13.NPV(sum of row 12)	44507.11
14.IRR/ROR (Of FCF row 10)	13.16%
MARR= 10% p.a. compounded semi-annually is
Interest compounded twice in a year, so
(1+10%/2)^2-1=
10.25%
p.a.
As salvage is $ 0,
there is no depreciation recapture
ANSWERS:
10.CFAT---FCFs(1+9)	-550000	143000	172700	141020	122012	122012	107756
13.NPV(sum of row 12)	44507.11
14.IRR/ROR (Of FCF row 10)	13.16%