5. The current (year 0) level of sales is $2,000. The firm is expecting its sales to grow at a rate of 15% per year, and it anticipates the financial statements relations given in the table. 3. Using the cash & mkt. securities as the Plug (Cash & Mkt Securities = Total Liabilities and Equity - Current Assets - Net Fixed Assets) predict the financial statements for the next 5 years (year 2020 through 2024). If the firm does not have enough internally generated funds, then only in that situation, it can borrrow money. Given a WACC of 10% and a Long Term growth rate in FCFs of 4.5%, determine the enterprise value and equity value of the firm. (5 points) Show the formulas the section in orange referencing the cells on column G (G26 through G70) and column B (B71 through B 75) Make the calculations in the turquoise cells. Using the Excel data table, run 2 sensitivity analyses to show the effect on equity value of a change in SGA and the effect on equity value of a simultaneous change in COGS and Sales growth rate. Build a chart of the Equity value as a function of the SG&A. 1526 15% 8% Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets/Sales Costs of goods sold/Sales Sales, general and administrative expenses Depreciation rate Interest rate on debt Interest paid on cash & marketable securities Tax rate Dividend payout ratio The firm neither repays any existing debt nor borrows any more money over the 5 yr horizon of the pro formas Debt is assumed unchanged. Stock does not change either (no new stock issuances and no stock repurchases). However, if the firm does not have enough cash it can increase its debt Cash and Mkt Securities is the PLUG. The average balances of cash and marketable securities are assumed to earn 3% interest Depreciation = Depreciation rate Average fixed assets at cost over the year Interest gain on cash & equivalents = Interest rate on cash & equivalents * Average amount over the year Interest payments on debt = Interest rate on debt * Average debt over the year 70% 65% 110 10% 5.00% 3.00% 2196 30% It is important to use the appropriate the formulas. 2019 2020 2021 2022 2023 2024 2,000 (1,300) 2,300 (1,495) 2,645 3,042 3 ,498 4,023 Year Income statement Sales Costs of goods sold SG&A Interest payments on debt Interest earned on cash & marketable securities Depreciation Profit before tax Taxes Profit after tax Dividends Retained earnings (116) 436 (1311 305 Balance sheet Cash and marketable securities 80 80 300 Balance sheet Cash and marketable securities Current assets Fixed assets At cost Depreciation L 1.700 (2001 Net fixed assets Total assets 1,400 1,780 Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity 160 1,020 450 150 1,780 2019 2020 2021 2022 2023 2024 Year Free cash flow calculation Profit after tax Add back depreciation Subtract increase in current assets Add back increase in current liabilities Subtract increase in fixed assets at cost Add back after-tax interest on debt Subtract after-tax interest on cash & mkt. securities Free cash flow od Valuing the firm Weighted average cost of capital Long term FCF Growth Rate Year FCF Terminal value Total 10% 4.5% 2019 2020 2021 2022 2023 2024 NPV of Total FCFS Add in initial (year O) cash and mkt. securities Enterprise value Subtract out value of firm's debt today Equity value Data table: Value as function of SG&A o 50 100 150 200 250 300 3501 400 4501 500 5501 600 Sales Groth Rate Data table: Equity Value as function of COGS and sales growth COGS 25.00% 35.00% 45.00% 55.00% 65.00% 75.00% 85.00% 95.00% 5. The current (year 0) level of sales is $2,000. The firm is expecting its sales to grow at a rate of 15% per year, and it anticipates the financial statements relations given in the table. 3. Using the cash & mkt. securities as the Plug (Cash & Mkt Securities = Total Liabilities and Equity - Current Assets - Net Fixed Assets) predict the financial statements for the next 5 years (year 2020 through 2024). If the firm does not have enough internally generated funds, then only in that situation, it can borrrow money. Given a WACC of 10% and a Long Term growth rate in FCFs of 4.5%, determine the enterprise value and equity value of the firm. (5 points) Show the formulas the section in orange referencing the cells on column G (G26 through G70) and column B (B71 through B 75) Make the calculations in the turquoise cells. Using the Excel data table, run 2 sensitivity analyses to show the effect on equity value of a change in SGA and the effect on equity value of a simultaneous change in COGS and Sales growth rate. Build a chart of the Equity value as a function of the SG&A. 1526 15% 8% Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets/Sales Costs of goods sold/Sales Sales, general and administrative expenses Depreciation rate Interest rate on debt Interest paid on cash & marketable securities Tax rate Dividend payout ratio The firm neither repays any existing debt nor borrows any more money over the 5 yr horizon of the pro formas Debt is assumed unchanged. Stock does not change either (no new stock issuances and no stock repurchases). However, if the firm does not have enough cash it can increase its debt Cash and Mkt Securities is the PLUG. The average balances of cash and marketable securities are assumed to earn 3% interest Depreciation = Depreciation rate Average fixed assets at cost over the year Interest gain on cash & equivalents = Interest rate on cash & equivalents * Average amount over the year Interest payments on debt = Interest rate on debt * Average debt over the year 70% 65% 110 10% 5.00% 3.00% 2196 30% It is important to use the appropriate the formulas. 2019 2020 2021 2022 2023 2024 2,000 (1,300) 2,300 (1,495) 2,645 3,042 3 ,498 4,023 Year Income statement Sales Costs of goods sold SG&A Interest payments on debt Interest earned on cash & marketable securities Depreciation Profit before tax Taxes Profit after tax Dividends Retained earnings (116) 436 (1311 305 Balance sheet Cash and marketable securities 80 80 300 Balance sheet Cash and marketable securities Current assets Fixed assets At cost Depreciation L 1.700 (2001 Net fixed assets Total assets 1,400 1,780 Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity 160 1,020 450 150 1,780 2019 2020 2021 2022 2023 2024 Year Free cash flow calculation Profit after tax Add back depreciation Subtract increase in current assets Add back increase in current liabilities Subtract increase in fixed assets at cost Add back after-tax interest on debt Subtract after-tax interest on cash & mkt. securities Free cash flow od Valuing the firm Weighted average cost of capital Long term FCF Growth Rate Year FCF Terminal value Total 10% 4.5% 2019 2020 2021 2022 2023 2024 NPV of Total FCFS Add in initial (year O) cash and mkt. securities Enterprise value Subtract out value of firm's debt today Equity value Data table: Value as function of SG&A o 50 100 150 200 250 300 3501 400 4501 500 5501 600 Sales Groth Rate Data table: Equity Value as function of COGS and sales growth COGS 25.00% 35.00% 45.00% 55.00% 65.00% 75.00% 85.00% 95.00%