Question

11. Please solve in Excel and round to 3 decimal places:

YEAR	DEPRECIATION FACTOR
1	33.33%
2	44.45%
3	14.81%
4	7.41%

The original cost of the new production line is estimated at $3 million and falls into 3 year MACRS. This is a 3 year project. Expected annual sales are $2,950,000 per year, with associated annual costs of $1,250,000. The project requires a net working capital of $375,000. They can usually sell their production lines for 40% of their original costs at the end of the project. The tax rate is 32% and the relevant interest rate is 15%.

a. What is the all-inclusive cash flow for year 3 (please solve in Excel)?

b. What is the NPV (please solve in Excel)?

c. What is the IRR (please solve in Excel)?

d. Does the project add value for shareholders? Why?

Answer 1

Year	0	1	2	3
1.Initial cost	-3000000
2.NWC reqd. & recovered	-375000			375000
3.After-tax Sale value at end yr. 3(see wkgs.)				887136
Operating Cash flows:
4.Expected sales		2950000	2950000	2950000
5.Annual costs		-1250000	-1250000	-1250000
6.Depreciation		-999900	-1333500	-444300
7.EBT(4+5+6)		700100	366500	1255700
8.Tax at 32%(7*32%)		-224032	-117280	-401824
9.EAT (7-8)		476068	249220	853876
10.Add Back:depn.(Row 6)		999900	1333500	444300
11.Opg.Cash flow(9+10)		1475968	1582720	1298176
12.FCFs(1+2+3+11)	-3375000	1475968	1582720	2560312
13.PV F at 15%(1/1.15^Yr.n)	1	0.86957	0.75614	0.65752
14.PV at 15% (12*13)	-3375000	1283450	1196764	1683447
15.NPV(sum of row 14)	788661
16.IRR (of FCFs row 12)	27%

After-tax Sale value at end yr. 3
Original cost	3000000
Less: Acc. Depn.(33.33+44.45+14.81)%*3000000=	2777700
Carrying value	222300
Sale value(3000000*40%)	1200000
Gain on sale(1200000-222300)	977700
Tax on gain(977700*32%)	312864
After-tax Sale value at end yr.3(1200000-312864)	887136

ANSWERS:

a.The all-inclusive cash flow for year 3 -- as in Col.Yr.3- Total $ 2560312(row 12--FCF)

b. NPV = $ 788661

c. IRR =27%

d. Does the project add value for shareholders? YES.

As it generates POSITIVE NPV out of the cash flows of the project.