Calcullate the NPV of the firm using the excel sheet below ANNUAL NET WORKNG CAP
ID: 2742004 • Letter: C
Question
Calcullate the NPV of the firm using the excel sheet below
ANNUAL NET WORKNG CAPITAL 2009 2008 2010 2011 2012 2013 0 4,000 10,000 10,000 10,000 10,000 10,000 1000 2007 3 Sales Revenue NWC (ten percent of sales) - Change in NWC (cash flow) 400 400 1000 0 1000 0 1000 1000 600 2007 2008 2009 2010 2011 2012 2013 8 Investment: 9 Capital Outlay 10 Net Working Capital (10% Sales) 11 Total Investment 12 Investment Recovery: 13 Equipment Salvage 14 Net Working Capital (full recovery) 16,0002,000 400 16,0002,400 0 600 600 0 0 0 0 0 ($1,080) (1,000) 16 Earnings before Interest and Taxes (EBIT) 17 Sales Revenue 4,000 10,000 10,000 10,00010,00010,000 19 Cost of Good Sold (75% Sales) 20 SG&A; (5% Sales) 21 Operating Savings 22 Depreciation ($18,000/6) 23 Total Costs & Expenses 24 25 EBIT 26-Taxes (40%) 27 NOPAT 28 29 - Investment 30 Free Cash Flow 31 NPV 32 IRR(%) 75% 5.0% (3,000) (200) 2,000 3.000 (4,200) (200) 80 (120) + DepreciationExplanation / Answer
2007 2008 2009 2010 2011 2012 2013 Sales $ - $ 4,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 NWC $ 400.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 Changes in NWC $ 400.00 $ 600.00 $ - $ - $ - $ - Investment: Capital Outlay $ 16,000.00 $ 2,000.00 $ - $ - $ - $ - $ - Changes in NWC $ 400.00 $ 600.00 $ - $ - $ - $ - Total investment $ 16,000.00 $ 2,400.00 $ 600.00 $ - $ - $ - $ - Investment Recovery: Equipment salvage $ 1,080.00 Net working capital(full recovery) $ 1,000.00 EBIT Sales $ - $ 4,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 COGS @ 75% $ 3,000.00 $ 7,500.00 $ 7,500.00 $ 7,500.00 $ 7,500.00 $ 7,500.00 SG&A @ 5% $ 200.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 Operating savings $ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00 $ 2,000.00 Depriciation $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 EBIT $ (200.00) $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 $ 1,000.00 Taxes(40%) $ (80.00) $ 400.00 $ 400.00 $ 400.00 $ 400.00 $ 400.00 NOPAT $ (120.00) $ 600.00 $ 600.00 $ 600.00 $ 600.00 $ 600.00 Depriciation $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 $ 3,000.00 investment (cash outflow) $ (16,000.00) $ (2,000.00) $ - $ - $ - $ - $ - Equipment salvge $ 1,080.00 Net working capital $ 1,000.00 Free cash flow $ (16,000.00) $ 880.00 $ 3,600.00 $ 3,600.00 $ 3,600.00 $ 3,600.00 $ 5,680.00 NPV Discount rate $ 1.00 $ 0.93 $ 0.87 $ 0.82 $ 0.76 $ 0.71 $ 0.67 Npv discounted flows $ (16,000.00) $ 822.38 $ 3,144.03 $ 2,938.18 $ 2,745.81 $ 2,566.04 $ 3,783.56 IRR 7.006% NPV is zero at this rate
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.