Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribedimage text in transcribedimage text in transcribed

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%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Money Banking And Financial Markets

Authors: Stephen Cecchetti, Kermit Schoenholtz

6th Edition

1260226786, 9781260226782

More Books

Students also viewed these Finance questions

Question

Describe major criticisms of Freuds system of thought.

Answered: 1 week ago