You work as an analyst covering Courtney 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 Courtney Inc. 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. 2. Your boss is looking at a target price of $50 for Courtney Inc. 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'. 3. Perform a sensitivity analysis of price per share to changes in Costs/Sales. Let Costs/Sales vary from 40% to 95% in increments of 5%. Make a plot of price per share vs Costs/Sales and briefly explain your results. + Assumptions for Courtney Inc. Sales growth rate 0.12 3 COGS/Sales 0.75 Depreciation (percent of fixed assets at cost) 0.04 5 Interest rate on LTD 0.08 6 Tax rate 0.26 7 Common dividends (constant, in millions) 45.3 8 Cash/Sales 0.02 9 AR/Sales 0.12 10 Inv/Sales 0.23 11 NFA/Sales 0.37 12 AP/Sales 0.02 13 Other current liabilities/Sales 0.15 14 Long Term Debt PLUG 15 16 M2.01.1: Produce a pro forma model and value the firm using mid-year discounting 17 Courtney Inc.: Income Statements for Years Ending December 31 19 (in millions of dollars) 20 21 Net Sales 22 Costs (except depreciation) 23 Depreciation 24 Earning before int. & tax 25 Less: Interest 26 Earning before taxes 27 Taxes 28 Net income 29 Common dividends 30 Addition to retained earnings Projected 2022 2020 2021 2023 2024 Actual 2019 $1,000,0 $911.0 $34.0 $55.0 $16.0 $39.0 $15.6 $23.4 $7.4 $16.0 Projected 2022 2020 2021 2023 2024 3 Courtney Inc.: December 31 Balance Sheets 4 (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 Long-term bonds (debt) 51 Preferred stock 52 Common Stock (par plus paid in capital) 53 Retained earnings 54 Total liabilities and equity 55 Actual 2019 $22.0 80.0 110.0 220.0 $432.0 $506.0 $165.0 341.0 $773.0 $22.0 215.0 $237.0 160.0 80.0 $200.0 96.0 $773.0 Projected 2022 2020 2021 2023 2024 56 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 flow 66 67 68 Long term growth rate 69 WACC 70 71 FCF 72 Terminal Value 73 Total FCF 74 75 Value of the firm (midyear discounting) 76 Add cash and marketable securities TI Subtract long term debt 78 Total value of equity 79 Number of shares 80 Projected price per share 81 82 5% 29 100 You work as an analyst covering Courtney 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 Courtney Inc. 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. 2. Your boss is looking at a target price of $50 for Courtney Inc. 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'. 3. Perform a sensitivity analysis of price per share to changes in Costs/Sales. Let Costs/Sales vary from 40% to 95% in increments of 5%. Make a plot of price per share vs Costs/Sales and briefly explain your results. + Assumptions for Courtney Inc. Sales growth rate 0.12 3 COGS/Sales 0.75 Depreciation (percent of fixed assets at cost) 0.04 5 Interest rate on LTD 0.08 6 Tax rate 0.26 7 Common dividends (constant, in millions) 45.3 8 Cash/Sales 0.02 9 AR/Sales 0.12 10 Inv/Sales 0.23 11 NFA/Sales 0.37 12 AP/Sales 0.02 13 Other current liabilities/Sales 0.15 14 Long Term Debt PLUG 15 16 M2.01.1: Produce a pro forma model and value the firm using mid-year discounting 17 Courtney Inc.: Income Statements for Years Ending December 31 19 (in millions of dollars) 20 21 Net Sales 22 Costs (except depreciation) 23 Depreciation 24 Earning before int. & tax 25 Less: Interest 26 Earning before taxes 27 Taxes 28 Net income 29 Common dividends 30 Addition to retained earnings Projected 2022 2020 2021 2023 2024 Actual 2019 $1,000,0 $911.0 $34.0 $55.0 $16.0 $39.0 $15.6 $23.4 $7.4 $16.0 Projected 2022 2020 2021 2023 2024 3 Courtney Inc.: December 31 Balance Sheets 4 (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 Long-term bonds (debt) 51 Preferred stock 52 Common Stock (par plus paid in capital) 53 Retained earnings 54 Total liabilities and equity 55 Actual 2019 $22.0 80.0 110.0 220.0 $432.0 $506.0 $165.0 341.0 $773.0 $22.0 215.0 $237.0 160.0 80.0 $200.0 96.0 $773.0 Projected 2022 2020 2021 2023 2024 56 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 flow 66 67 68 Long term growth rate 69 WACC 70 71 FCF 72 Terminal Value 73 Total FCF 74 75 Value of the firm (midyear discounting) 76 Add cash and marketable securities TI Subtract long term debt 78 Total value of equity 79 Number of shares 80 Projected price per share 81 82 5% 29 100