M2.Q1:
You work as an analyst covering a firm called Mernie Badoff Inc. Your boss is concerned about the large investment he has in this firm and wants you to produce a valuation template to help him decide whether he should maintain his large holdings or not. He already came up with what he believes are realistic assumptions about the future of Mernie Badoff. Your task is two-fold:
- Produce a pro-forma model using the given assumptions. Consider Debt the plug variable. Using this model, go through a valuation exercise and come up with the projected price per share using midyear discounting.
- Your boss is looking at a target price of $50 for Mernie Badoff. Use Excel functions to identify three scenarios: if your projected price per share is within +/- 5% of the target, your cell should say TARGET ACHIEVED; if your projected price per share is lower than the target by more than 5%, your cell should say TOO LOW; if the value per share is higher than the target by more than 5% your cell should say TOO HIGH.
Assumptions for Mernie Badoff Inc. Sales growth rate COGS/Sales Depreciation (percent of fixed assets at cost) Interest rate on LTD Tax rate Common dividends (constant, in millions) Cash/Sales AR/Sales Inv/Sales NFA/Sales AP/Sales Other current liabilities/Sales Long Term Debt 0.12 0.9 0.04 0.08 0.26 45.3 0.02 0.12 0.23 0.37 0.02 0.15 PLUG M2.01.1: Produce a pro-forma model and value the firm using mid-year discounting Mernie Badoff Inc.: Income Statements for Years Ending December 31 (in millions of dollars) Projected 2022 2020 2021 2023 2024 Net Sales Costs (except depreciation) Depreciation Earning before int. & tax Less: Interest Earning before taxes Taxes Net income Common dividends Addition to retained earnings Actual 2019 $1,000.0 $911.0 $34.0 $55.0 $16.0 $39.0 $15.6 $23.4 $7.4 $16.0 Mernie Badoff Inc.: December 31 Balance Sheets (in millions of dollars) Projected 2022 2020 2021 2023 2024 Cash Marketable Securities Accounts receivable Inventories Total current assets Fixed assets at cost Accumulated depreciation Net plant and equipment Total Assets Actual 2019 $22.0 80.0 110.0 220.0 $432.0 $506.0 $165.0 341.0 $773.0 Liabilities and Equity Accounts Payable Other current liabilities Total current liabilities Long-term bonds (debt) Preferred stock Common Stock (par plus paid in capital) Retained earnings Total liabilities and equity $22.0 215.0 $237.0 160.0 80.0 $200.0 96.0 $773.0 2020 2021 Projected 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 Free cash flow 5% Long term growth rate WACC 7% FCF Terminal Value Total FCF Value of the firm (midyear discounting) Add cash and marketable securities Subtract long term debt Total value of equity Number of shares Projected price per share 100 M2.01-2: Target Price Compare Projected Price with Target Price 50 Assumptions for Mernie Badoff Inc. Sales growth rate COGS/Sales Depreciation (percent of fixed assets at cost) Interest rate on LTD Tax rate Common dividends (constant, in millions) Cash/Sales AR/Sales Inv/Sales NFA/Sales AP/Sales Other current liabilities/Sales Long Term Debt 0.12 0.9 0.04 0.08 0.26 45.3 0.02 0.12 0.23 0.37 0.02 0.15 PLUG M2.01.1: Produce a pro-forma model and value the firm using mid-year discounting Mernie Badoff Inc.: Income Statements for Years Ending December 31 (in millions of dollars) Projected 2022 2020 2021 2023 2024 Net Sales Costs (except depreciation) Depreciation Earning before int. & tax Less: Interest Earning before taxes Taxes Net income Common dividends Addition to retained earnings Actual 2019 $1,000.0 $911.0 $34.0 $55.0 $16.0 $39.0 $15.6 $23.4 $7.4 $16.0 Mernie Badoff Inc.: December 31 Balance Sheets (in millions of dollars) Projected 2022 2020 2021 2023 2024 Cash Marketable Securities Accounts receivable Inventories Total current assets Fixed assets at cost Accumulated depreciation Net plant and equipment Total Assets Actual 2019 $22.0 80.0 110.0 220.0 $432.0 $506.0 $165.0 341.0 $773.0 Liabilities and Equity Accounts Payable Other current liabilities Total current liabilities Long-term bonds (debt) Preferred stock Common Stock (par plus paid in capital) Retained earnings Total liabilities and equity $22.0 215.0 $237.0 160.0 80.0 $200.0 96.0 $773.0 2020 2021 Projected 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 Free cash flow 5% Long term growth rate WACC 7% FCF Terminal Value Total FCF Value of the firm (midyear discounting) Add cash and marketable securities Subtract long term debt Total value of equity Number of shares Projected price per share 100 M2.01-2: Target Price Compare Projected Price with Target Price 50
