Question

XUR 5 + Calculating NPV - Excel FORMULAS DATA REVIEW FILE HOME INSERT PAGE LAYOUT VIEW Sign in Calibri 11 - A A 23 Paste BIU E! - A % Alignment Number Conditional Format as Cell Formatting Table Styles Styles Cells Editing Clipboard Font . A1 fx A B C D E F G H 1 2 Quad Enterprises is considering a new three-year expansion project that requires an initial fixed asset investment of $2.9 million. The fixed asset will be depreciated straight-line to zero over its three-year tax life. The project is estimated to generate $2,190,000 in annual sales, with costs of $815,000. The project requires an initial investment in net working capital of $300,000, and the fixed asset will have a market value of $210,000 at the end of the project. What is the project's Year O net cash flow? Year 1? Year 2? Year 3? The tax rate is 21 percent. If the required return is 12 percent, what is the project's NPV? 4 5 6 7 8 9 Asset investment $ Estimated annual sales $ Costs Net working capital $ Pretax salvage value $ Tax rate Project and asset life Required return 2,900,000 2,190,000 815,000 300,000 210,000 21% 3 12% 10 11 12 13 14 15

MUST SHOW ALL CORRECT EXCEL FORMULAS

Answer 1

Solution:-

To Calculate Cash Flows and NPV of the Project-

Year 0 Cash Flows = -$3,200,000

Year 1 Cash Flows = $1,289,250

Year 2 Cash Flows = $1,289,250

Year 3 Cash Flows = $1,755,150

NPV of the Project = $228,179

F 1 A B D E 1 Solution :- 2 3 Statement of Cash flows After Tax 4 Year 0 5 Initial Cost ($29,00,000.00) 6 Working Capital Introduce ($3,00,000.00) 7 Sales $21,90,000.00 $21,90,000.00 $21,90,000.00 8 Less: Cost $8,15,000.00 $8,15,000.00 $8,15,000.00 9 Less :- Depreciation $9,66,666.67 $9,66,666.67 $9,66,666.67 10 Pre Tax Cash flows $4,08,333.33 $4,08,333.33 $4,08,333.33 11 Less :- Tax @ 21% $85,750.00 $85,750.00 $85,750.00 12 Net Operating Profit After Tax $3,22,583.33 $3,22,583.33 $3,22,583.33 13 Add :- Depreciation $9,66,666.67 $9,66,666.67 $9,66,666.67 14 Add:- After tax Salvage Value $1,65,900.00 15 Add:- Working Capital Recovery $3,00,000.00 16 Operating Cashflows ($32,00,000.00 $12,89,250.00 $12,89,250.00 $17,55,150.00 17 18 PVF @ 12% 1.000 0.893 0.797 0.712 19 20 Present Value ($32,00,000.00 $11,51,116.07 $10,27,782.21 $12,49,281.10 21 22 Net Present value $2,28,179 23

B D E F 1 3 A 1 Solution :- 2 3 4 Year 5 Initial Cost 6 Working Capital Introduce 7 Sales 8 Less: Cost 9 Less :- Depreciation 10 Pre Tax Cash flows 11 Less :- Tax @ 21% 12 Net Operating Profit After Tax 13 Add :- Depreciation 14 Add:- After tax Salvage Value 15 Add:- Working Capital Recovery 16 Operating Cashflows 17 18 PVF @ 12% 19 20 Present Value 21 22 Net Present value 23 Statement of Cash flows After Tax (Formula Sheet) 0 2 =-2900000 -300000 2190000 2190000 815000 815000 =2900000/3 =2900000/3 =C7-C8-C9 I=D7-D8-D9 =C10*0.21 =D10*0.21 =C10-C11 =D10-D11 =C9 =D9 2190000 815000 =2900000/3 =E7-E8-E9 =E10*0.21 =E10-E11 =E9 =210000*0.79 300000 =E12+E13+E14+E15 =B5+B6 =C12+C13 =D12+D13 =1 =B18*100/112 =C18*100/112 -D18*100/112 =B16*B18 =C16*C18 -D16*018 =E16*E18 =SUM(B20:E20)
If you have any query related to question then feel free to ask me in a comment.Thanks.