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 CORRECT EXCEL FORMULAS. ONLY ANSWER HIGHLIGHTED PORTIONS.

Answer 1

AB C D E F G H I 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 IU 17 18 19 $ Aftertax salvage value Sell equipment Taxes Aftertax cash flow 20 $ $ 210,000 44,100 165,900 21 22 23 Sales 24 Costs 25 $ 2,190,000 $ 2,190,000 $ 2,190,000 $ 815,000 $ 815,000 $ 815,000 $ 966,667 $ 966,667 $ 966,667 $ 408,333 $ 408,333 $ 408,333 $ 85,750 $ 85,750 $ 85,750 $ 322,583 $ 322,583 $ 322,583 Depreciation EBT Taxes Net Income 26 27 28 $ $ (2,900,000) $ (300,000) 29 30 31 32 33 34 Capital spending Net working capital OCF Net cash flow 165,900 $ 300,000 $ 1,289,250 $ 1,289,250 $ 1,289,250 $ 1,289,250 $ 1,289,250 $ 1,755,150 $ (3,200,000) 35 NPV $ 228,179.38 36

Excel formulas:

AB D E F G H I 4 5 6 7 8 9 Asset investment 2900000 Estimated annual sales 2190000 Costs 815000 Net working capital 300000 Pretax salvage value 210000 Tax rate 0.21 Project and asset life 3 Required return 0.12 10 11 12 13 14 15 IU 17 18 19 Aftertax salvage value Sell equipment Taxes Aftertax cash flow 20 =D10 =D19*D11 =D19-D20 21 22 23 =D7 =F23 =G23 24 25 Sales Costs Depreciation EBT Taxes Net Income =D8 =D6/D12 =F23-F24-F25 =F26*D11 =F26-F27 26 27 =F24 =D6/012 =G23-G24-G25 =G26*D11 =G26-G27 =G24 =D6/D12 =H23-H24-H25 =H26*D11 28 =H26-H27 29 30 =-D6 --09 31 Capital spending Net working capital OCF Net cash flow =D21 =D9 =H28+H25 =H30+H31+H32 32 =F28+F25 =F32 =G28+G25 =G32 =D30+D31 33 34 35 NPV =D33+NPV(D13,F33:H33) 36 37