I need help finishing the cash flow statement, as well as the table in green and cash flow valuation. If possible, please help on formulas, by typing them and listing the cell in Excel I should put them in. Thank you!
1 Formulas needed: Operating NWC = Non-cash Operating CA - Non-interest bearing Operating CL Invested Capital = Debt + Equity - Nonoperating assets Economic Profit Spread = Average Invested Capital * (ROIC - WACC) ROIC = NOPLAT/ Average Invested Capital Capex = (PPE, - PPE.) + Depreciation 2 Invested Capital = Operating NWC + Operating Assets 3 4 Change in NWC Change 2020 in NWC Invested Capital/ Assets 2019 2020 5 Income Statement 6 Net Sales 7 COGS 8 SG&A 9 Legal costs (*) 10 Depreciation & Amortization 11 EBIT 2020 30,280 20,350 6,100 250 Balance Sheet Cash & equivalents Receivables Inventory Total CA 2019 850 1,000 4,000 5,850 2020 915 1,070 4,460 6,445 Operating CA Cash Flow Statement Net Income Depr & Amort Dec (inc) in receivables Dec (inc) in inventory Inc (dec) in accts payable Inc (dec) in accrued salaries Change in Operating Cash Flow 2020 1,872 580 70 460 380 445 3,807 Cash Flow Valuation Non-GAAP EBIT NOPAT Depreciation & Amort Change in NWC Capital expenditure Acquisition Free Cash Flow 580 3,000 This firm grows via acquisition Net PP&E Goodwill Total Assets 7,754 930 14,534 8,870 2,105 17,420 Operating CL 10 130 2,880 1,696 12 13 Investment Income 14 Interest expense 15 EBT 16 17 Income Taxes (35%) 18 Net Income Net Working Capital Capital expenditure Dec (inc) in acquisitions CF investing Non-GAAP ROIC GAAP ROIC ST Debt Accounts Payable Accrued Salaries Total CL 0 2,070 1,400 3,470 10 2,450 1,845 4,305 Economic Profit 1,008 1,872 Invested Capital 19 Inc (dec) in debt Inc (dec) in CS Dividends CF financing LT Debt 4,596 4,775 0 Invested Capital/ Financing 2019 2020 20 21 Other data 2019 22 Plowback ratio 100% 23 24 * Company was sued one time in their history 25 26 27 WACC 11.50% 28 Common stock and PIC Retained earnings Treasury stock Total Equity & Liabilities 2,685 9,243 -5,460 14,534 2,685 11,115 -5,460 17,420 Change in cash Balance sheet change in cash 65 Invested Capital 11,115 Check correct correct 1 Formulas needed: Operating NWC = Non-cash Operating CA - Non-interest bearing Operating CL Invested Capital = Debt + Equity - Nonoperating assets Economic Profit Spread = Average Invested Capital * (ROIC - WACC) ROIC = NOPLAT/ Average Invested Capital Capex = (PPE, - PPE.) + Depreciation 2 Invested Capital = Operating NWC + Operating Assets 3 4 Change in NWC Change 2020 in NWC Invested Capital/ Assets 2019 2020 5 Income Statement 6 Net Sales 7 COGS 8 SG&A 9 Legal costs (*) 10 Depreciation & Amortization 11 EBIT 2020 30,280 20,350 6,100 250 Balance Sheet Cash & equivalents Receivables Inventory Total CA 2019 850 1,000 4,000 5,850 2020 915 1,070 4,460 6,445 Operating CA Cash Flow Statement Net Income Depr & Amort Dec (inc) in receivables Dec (inc) in inventory Inc (dec) in accts payable Inc (dec) in accrued salaries Change in Operating Cash Flow 2020 1,872 580 70 460 380 445 3,807 Cash Flow Valuation Non-GAAP EBIT NOPAT Depreciation & Amort Change in NWC Capital expenditure Acquisition Free Cash Flow 580 3,000 This firm grows via acquisition Net PP&E Goodwill Total Assets 7,754 930 14,534 8,870 2,105 17,420 Operating CL 10 130 2,880 1,696 12 13 Investment Income 14 Interest expense 15 EBT 16 17 Income Taxes (35%) 18 Net Income Net Working Capital Capital expenditure Dec (inc) in acquisitions CF investing Non-GAAP ROIC GAAP ROIC ST Debt Accounts Payable Accrued Salaries Total CL 0 2,070 1,400 3,470 10 2,450 1,845 4,305 Economic Profit 1,008 1,872 Invested Capital 19 Inc (dec) in debt Inc (dec) in CS Dividends CF financing LT Debt 4,596 4,775 0 Invested Capital/ Financing 2019 2020 20 21 Other data 2019 22 Plowback ratio 100% 23 24 * Company was sued one time in their history 25 26 27 WACC 11.50% 28 Common stock and PIC Retained earnings Treasury stock Total Equity & Liabilities 2,685 9,243 -5,460 14,534 2,685 11,115 -5,460 17,420 Change in cash Balance sheet change in cash 65 Invested Capital 11,115 Check correct correct
