Question

2. Calculating Project NPV The Best Manufacturing Company is considering a new investment. Financial projections for the investment are tabulated here. The corporate tax rate is 22 percent. Assume all sales revenue is received in cash, all operating costs and income taxes are paid in cash, and all cash flows occur at the end of the year. All net working capital is recovered at the end of the project. Year O Year 1 Year 2 Year 3 Year 4 Investment $26,300 $13,400 $15,000 3,100 Sales revenue $16,400 $12,900 2,900 Operating costs Depreciation 4,200 2,800 6,575 6,575 6,575 6,575 300 200 225 150 Net working capital spending

Answer 1

A	Year	0	1	2	3	4
	Sales		13400	15000	16400	12900
	Operating cost		2900	3100	4200	2800
	Depreciation		6575	6575	6575	6575
	EBIT		3925	5325	5625	3525
	Less: Tax at 22%		863.5	1171.5	1237.5	775.5
	NetIncome		3061.5	4153.5	4387.5	2749.5
B
	Year	0	1	2	3	4
	OCF= Net Income+depreciation		9636.5	10728.5	10962.5	9324.5
C	Year	0	1	2	3	4
	Initial cost	-26300
	NWC	-300	-200	-225	-150	875
	OCF= Net Income+depreciation		9636.5	10728.5	10962.5	9324.5
	Net Cash flows	-26600	9436.5	10503.5	10812.5	10199.5
	NPV at 12%	4376.86

Workings

AutoSaver HD Book1 - Excel Sign in File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do - 10 A = = 0 Calibri BIU - B -A A = E insert - Delete - Format - 0 X Share Comments AY O Sort & Find & Filter - Select Editing General 3$ -% to Number Paste La- - . Conditional Format as Formatting Table - Styles Cell Styles Clipboard Font Alignment t o Cells B1 - for Year D F A 1 A Year Sales Operating cost Depreciation EBIT Less: Tax at 22% NetIncome 13400 2900 6575 =D2-D3-D4 =D5*22% =D5-D6 15000 3100 6575 =E2-E3-E4 =E5*22% =E5-E6 16400 4200 6575 =F2-F3-F4 =F5*22% =F5-F6 12900 2800 6575 =G2-G3-G4 =G5*22% =G5-G6 =D7+D4 =E7+E4 =F7+F4 =G7+G4 9B 10 Year 11 OCF= Net Income+depreciation 12 13 C Year Initial cost 15 NWC OCF= Net Income+depreciation Net Cash flows 18 NPV at 12% -26300 -300 16 17 -200 =D11 =SUM(D14:016) -225 =E11 =SUM(E14:E16) -150 =F11 =SUM(F14:F16) --SUM(C15:15) =G11 =SUM(G14:16) =SUM(C14:016) =NPV(12%,D17:G17)+C17 19 Sheet1 Ready E -- ENG -_ 6:29 AM + 100% 0 2