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:

1. 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.

1 Assumptions for Mernie Badoff Inc. Sales growth rate COGS/Sales Depreciation (percent of fixed assets at cost) Interest rate on LTD Tax rate 7 Common dividends (constant, in millions) Cash/Sales 9 ARISales 10 Inv/Sales NFA/Sales 12 AP/Sales 13 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 16 M2.01-1: Produce a pro-forma model and value the firm using mid-year discounting. 18 Mernie Badoff Inc.: Income Statements for Years Ending December 31 19 (in millions of dollars) Projected 2022 20 2020 2021 2023 2024 21 Net Sales 22 Costs (except depreciation) 23 Depreciation 24 Earning before int. & tax 25 Less: Interest 26 Earning before taxes 27 Tares 28 Net income 29 Common dividends 30 Addition to retained earnings 31 Actual 2019 ##### $911.0 $34.0 $55.0 $16.0 $39.0 $23.4 $7.1 $16.0 Projected 2022 2020 2021 2023 2024 33 Mernie Badoff Inc.: December 31 Balance Sheets 34 (in millions of dollars) 35 36 Cash 37 Marketable Securities 38 Accounts receivable 39 Inventories 40 Total current assets 41 Fixed assets at cost 42 Accumulated depreciation 43 Net plant and equipment 44 Total Assets 45 46 Liabilities and Equity 47 Accounts Payable 48 Other current liabilities 49 Total current liabilities 50 Long-term bonds (debt) 51 Preferred stock 52 Common Stock (par plus paid in capital) 53 Retained earnings 54 Total liabilities and equit Actual 2019 $22.0 80.0 110.0 220.0 $4320 $506.0 $165.0 341.0 $773.0 $22.0 215.0 $237.0 1600 80.0 $200.0 96.0 $773.0 2020 2021 Projected 2022 2023 2024 57 Year 58 Free cash flow calculation 59 Profit after tax 60 Add back depreciation 61 Subtract increase in current assets 62 Add back increase in current liabilities 63 Subtract increase in fixed assets at cost 64 Add back after-tax interest on debt 65 Free cash floy 67 5% 68 Long term growth rate 69 WACC 70 71 FCF 72 Terminal Value 73 Total FCF 75 Value of the firm (midyear discounting) 76 Add cash and marketable securities 77 Subtract long term debt 78 Total value of equity 79 Number of shares 80 Projected price per share 82 83 M2.01-2: 84 Target Price 85 Compare Projected Price with Target Price 50 1 Assumptions for Mernie Badoff Inc. Sales growth rate COGS/Sales Depreciation (percent of fixed assets at cost) Interest rate on LTD Tax rate 7 Common dividends (constant, in millions) Cash/Sales 9 ARISales 10 Inv/Sales NFA/Sales 12 AP/Sales 13 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 16 M2.01-1: Produce a pro-forma model and value the firm using mid-year discounting. 18 Mernie Badoff Inc.: Income Statements for Years Ending December 31 19 (in millions of dollars) Projected 2022 20 2020 2021 2023 2024 21 Net Sales 22 Costs (except depreciation) 23 Depreciation 24 Earning before int. & tax 25 Less: Interest 26 Earning before taxes 27 Tares 28 Net income 29 Common dividends 30 Addition to retained earnings 31 Actual 2019 ##### $911.0 $34.0 $55.0 $16.0 $39.0 $23.4 $7.1 $16.0 Projected 2022 2020 2021 2023 2024 33 Mernie Badoff Inc.: December 31 Balance Sheets 34 (in millions of dollars) 35 36 Cash 37 Marketable Securities 38 Accounts receivable 39 Inventories 40 Total current assets 41 Fixed assets at cost 42 Accumulated depreciation 43 Net plant and equipment 44 Total Assets 45 46 Liabilities and Equity 47 Accounts Payable 48 Other current liabilities 49 Total current liabilities 50 Long-term bonds (debt) 51 Preferred stock 52 Common Stock (par plus paid in capital) 53 Retained earnings 54 Total liabilities and equit Actual 2019 $22.0 80.0 110.0 220.0 $4320 $506.0 $165.0 341.0 $773.0 $22.0 215.0 $237.0 1600 80.0 $200.0 96.0 $773.0 2020 2021 Projected 2022 2023 2024 57 Year 58 Free cash flow calculation 59 Profit after tax 60 Add back depreciation 61 Subtract increase in current assets 62 Add back increase in current liabilities 63 Subtract increase in fixed assets at cost 64 Add back after-tax interest on debt 65 Free cash floy 67 5% 68 Long term growth rate 69 WACC 70 71 FCF 72 Terminal Value 73 Total FCF 75 Value of the firm (midyear discounting) 76 Add cash and marketable securities 77 Subtract long term debt 78 Total value of equity 79 Number of shares 80 Projected price per share 82 83 M2.01-2: 84 Target Price 85 Compare Projected Price with Target Price 50