Question

1 Approved Answer

Posted on Jul 17, 2024

Can you check if I've done this right so far and explain how to get the change in working capital and capital expenditures in excel

Can you check if I've done this right so far and explain how to get the change in working capital and capital expenditures in excel format? I'm confused. image text in transcribed

DCF Valuation Model Input Section Base Sales Growth Years 1-5 Growth Year-6 forward COGS % of Sales Gr Assets % of Sales Depr % Gr Assets Inv. % of Sales $ 822,000.00 8.50% 4.00% 39.00% 36.00% 16.00% 22.00% Tax Rate WACC 21.00% 13.00% Value of Debt Shares Outstanding $ 400,000.00 80,000,000 EVERYTHING BELOW THIS MUST BE A FORMULA OR CELL REFERENCE. NO NUMBERS CAN BE ENTERED Valuation Model Year Sales COGS EBITDA Depreciation EBT Cash Taxes Net Income + Depreciation -Chg in WC - Free Cash Flow PV of CFS + TV Total Firm Value Value of Debt Value of Equity 0 1 2 3 4 5 6 $ 822,000.00 $ 891,870.00 S 967,678.95 $ 1.049,931.66 $ 1,139,175.85 $ 1,236,005.80 $ 1,285,446.03 S (320,580.00) S (347,629.30) S (377,394.79) S (409,473.35) S (444,278.58) S (482,042.26) S (501,323.95) $ 501.420.00 $ 544,040.70 $ 590,284.16 $ 640,458.31 $ 694,897.27 $ 753,963.54 $ 784,122.08 S (80,227.20) S (87,046.51)'s (94,445.47) $ (102,473.33) $ (111,183.56) S (120,634.17) 's (125,459.53) $ 421,192.80 $ 456,994.19 $ 495,838.69 $ 537,984.98 $ 583,713.71 $ 633,329.37 $ 658,662.55 $ (86,450.49) S (95,968.78) S (104,126.13) $ (112,976.85) $ (122,579.88) S (132,999.17) $ (138,319.13) $ 332,742.31 $ 361,025.41 $ 391,712.57 $ 425,008.14 $ 461,133.83 S 500,330.20 $ 520,343.41 $ 87,046.51 $ 94,445.47 $ 102,473.33 $ 111,183.56 $ 120,634.17 $ 125,459.53 Terminal Value Value Per Share Shs Outstda Net Working Capital Depreciation Gross Fixed Assets $ 180,840.00 $ 80,227.20 $ 501.420.00 DCF Valuation Model Input Section Base Sales Growth Years 1-5 Growth Year-6 forward COGS % of Sales Gr Assets % of Sales Depr % Gr Assets Inv. % of Sales $ 822,000.00 8.50% 4.00% 39.00% 36.00% 16.00% 22.00% Tax Rate WACC 21.00% 13.00% Value of Debt Shares Outstanding $ 400,000.00 80,000,000 EVERYTHING BELOW THIS MUST BE A FORMULA OR CELL REFERENCE. NO NUMBERS CAN BE ENTERED Valuation Model Year Sales COGS EBITDA Depreciation EBT Cash Taxes Net Income + Depreciation -Chg in WC - Free Cash Flow PV of CFS + TV Total Firm Value Value of Debt Value of Equity 0 1 2 3 4 5 6 $ 822,000.00 $ 891,870.00 S 967,678.95 $ 1.049,931.66 $ 1,139,175.85 $ 1,236,005.80 $ 1,285,446.03 S (320,580.00) S (347,629.30) S (377,394.79) S (409,473.35) S (444,278.58) S (482,042.26) S (501,323.95) $ 501.420.00 $ 544,040.70 $ 590,284.16 $ 640,458.31 $ 694,897.27 $ 753,963.54 $ 784,122.08 S (80,227.20) S (87,046.51)'s (94,445.47) $ (102,473.33) $ (111,183.56) S (120,634.17) 's (125,459.53) $ 421,192.80 $ 456,994.19 $ 495,838.69 $ 537,984.98 $ 583,713.71 $ 633,329.37 $ 658,662.55 $ (86,450.49) S (95,968.78) S (104,126.13) $ (112,976.85) $ (122,579.88) S (132,999.17) $ (138,319.13) $ 332,742.31 $ 361,025.41 $ 391,712.57 $ 425,008.14 $ 461,133.83 S 500,330.20 $ 520,343.41 $ 87,046.51 $ 94,445.47 $ 102,473.33 $ 111,183.56 $ 120,634.17 $ 125,459.53 Terminal Value Value Per Share Shs Outstda Net Working Capital Depreciation Gross Fixed Assets $ 180,840.00 $ 80,227.20 $ 501.420.00