Question 5 NAME: 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 for the next couple of years. 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 2021 through 2025). 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 5%, determine the enterprise value and equity value of the firm. (4 points) Show the formulas the section in orange referencing the cells on column G (G26 through G 70) 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 SA and the effect on equity value of a simultaneous change on COGS and Sales growt rate. Build a chart of the Equity value as a function of the SG&A. 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 15% 15% 8% 70% . 65% 150 10% 5.00% 3.00% 38% 30% 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 It is important to use the appropriate the formulas. 2020 2021 2022 2023 2024 2025 2,645 3,042 3,498 4,023 2,000 (1,300) 2,300 (1,495) Year Income statement Sales Costs of goods sold SG&A Interest payments on debt Interest earned on cash & marketable securities Depreciation Profit before tax Tavec Taxes Profit after tax Dividends Retained earnings (51) 2 (100) 551 (210) 342 (103) 239 80 300 Balance sheet Cash and marketable securities Current assets Fixed assets At cost Depreciation Net fixed assets Total assets 1,700 (300) 1,400 1,780 Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity 160 1,020 450 150 1,780 2020 2021 2022 2023 2024 2025 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 0 0 0 0 0 10% 5% Valuing the firm Weighted average cost of capital Long term FCF Growth Rate Year FCF Terminal value Total 2020 2021 2022 2023 2024 2025 NPV of Total FCFs Add in initial (year 0) cash and mkt. securities Enterprise value Subtract out value of firm's debt today Equity value 0 Data table: Value as function of SG&A 50 100 150 200 250 300 350 400 450 500 550 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% Question 5 NAME: 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 for the next couple of years. 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 2021 through 2025). 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 5%, determine the enterprise value and equity value of the firm. (4 points) Show the formulas the section in orange referencing the cells on column G (G26 through G 70) 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 SA and the effect on equity value of a simultaneous change on COGS and Sales growt rate. Build a chart of the Equity value as a function of the SG&A. 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 15% 15% 8% 70% . 65% 150 10% 5.00% 3.00% 38% 30% 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 It is important to use the appropriate the formulas. 2020 2021 2022 2023 2024 2025 2,645 3,042 3,498 4,023 2,000 (1,300) 2,300 (1,495) Year Income statement Sales Costs of goods sold SG&A Interest payments on debt Interest earned on cash & marketable securities Depreciation Profit before tax Tavec Taxes Profit after tax Dividends Retained earnings (51) 2 (100) 551 (210) 342 (103) 239 80 300 Balance sheet Cash and marketable securities Current assets Fixed assets At cost Depreciation Net fixed assets Total assets 1,700 (300) 1,400 1,780 Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity 160 1,020 450 150 1,780 2020 2021 2022 2023 2024 2025 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 0 0 0 0 0 10% 5% Valuing the firm Weighted average cost of capital Long term FCF Growth Rate Year FCF Terminal value Total 2020 2021 2022 2023 2024 2025 NPV of Total FCFs Add in initial (year 0) cash and mkt. securities Enterprise value Subtract out value of firm's debt today Equity value 0 Data table: Value as function of SG&A 50 100 150 200 250 300 350 400 450 500 550 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%