do a scenario analysis for Cooper Dental DCF valuation, using scenario manager under What-if Analysis.
- Optimistic: sales growth 18%, COGS/Sales 0.4, WACC 12%, FCF growth rate 8%
- Pessimistic: sales growth 5%, COGS/Sales 0.55, WACC 16%, FCF growth rate 3%
Generate a summary report showing the result of estimated value per share (from both normal method and mid-year discounting method). Clearly label the variable names in the summary report.
Project five years of financial statements for Cooper Dental, a company that manufactures and sells dental equipment. Calculate FCFs and run a DCF analysis to estimate the equity value. Conduct a sensitivity analysis in the end. Starting point is Cooper Dental's current income statement and balance sheet for year-end 2017. Projection assumptions: -Cooper Dental's current level of sales is $10,000,000. Over the 5-year horizon of the financial planning model, the firm expects its sales to grow at a rate of 10% per year. -Current assets will be 15% of annual firm sales. -Current liabilities will be 8% of annual firm sales. -Net fixed assets are assumed to be 77% of annual sales. - Annual depreciation charge is 10% of average value of the fixed assets on the books during the year. -Cost of goods sold is assumed to be 50% of sales. - Interest rate on debt is 10% of average value of debts. - Interest earned on cash is 8% of the average balance of cash. Tax rate is 40% of firm's profit before taxes. -Dividends: The firm pays out 40% of its profits after taxes as dividends to shareholders. -Debt: Cooper Dental currently has debt of $3,200,000. The company's agreement with the bank specifies that it will repay $800,000 of this debt in each of the next 4 years. Once the debt is fully replaid, the company intends to stay debt-free. -Stock: Company has 1 million shares outstanding and does not intend either to issue new stock or to repurchase stock over the 5-year model horizon. Stock item remains at 2016 level. -Cash: This item is the plug. The cash item is defined so that the left-hand side of the balance sheet always cquals the right hand side of the balance sheets: Cash = total liabilities and equity - current assets - net fixed assets. For DCF valuation, assume the WACC for Cooper Dental is 14% and the long-term growth rate of FCFs is 6%. Analysis: - What's the firm's value based on DCF valuation? -What's the estimated value per share for Cooper Dental? Using mid-year discounting -If the current market value of Cooper Dental share is $10, what's our recommendation for this stock? -Conduct a sensitivity analysis showing the value per share as a function of sales growth rate. -Another sensitivity analysis showing the value per share as a function of long-term FCF growth and WACC. -Conduct a scenario analysis showing the value per share under optimistic/pessimistic scenarios. Optimistic: Sales growth 18%, COGS/sales 40%, WACC 12%, FCF growth 8%. Pessimistic: Sales growth 5%, COGS/sales 55%, WACC 16%, FCF growth 3%. pro forma takes the longest time. Project five years of financial statements for Cooper Dental, a company that manufactures and sells dental equipment. Calculate FCFs and run a DCF analysis to estimate the equity value. Conduct a sensitivity analysis in the end. Starting point is Cooper Dental's current income statement and balance sheet for year-end 2017. Projection assumptions: -Cooper Dental's current level of sales is $10,000,000. Over the 5-year horizon of the financial planning model, the firm expects its sales to grow at a rate of 10% per year. -Current assets will be 15% of annual firm sales. -Current liabilities will be 8% of annual firm sales. -Net fixed assets are assumed to be 77% of annual sales. - Annual depreciation charge is 10% of average value of the fixed assets on the books during the year. -Cost of goods sold is assumed to be 50% of sales. - Interest rate on debt is 10% of average value of debts. - Interest earned on cash is 8% of the average balance of cash. Tax rate is 40% of firm's profit before taxes. -Dividends: The firm pays out 40% of its profits after taxes as dividends to shareholders. -Debt: Cooper Dental currently has debt of $3,200,000. The company's agreement with the bank specifies that it will repay $800,000 of this debt in each of the next 4 years. Once the debt is fully replaid, the company intends to stay debt-free. -Stock: Company has 1 million shares outstanding and does not intend either to issue new stock or to repurchase stock over the 5-year model horizon. Stock item remains at 2016 level. -Cash: This item is the plug. The cash item is defined so that the left-hand side of the balance sheet always cquals the right hand side of the balance sheets: Cash = total liabilities and equity - current assets - net fixed assets. For DCF valuation, assume the WACC for Cooper Dental is 14% and the long-term growth rate of FCFs is 6%. Analysis: - What's the firm's value based on DCF valuation? -What's the estimated value per share for Cooper Dental? Using mid-year discounting -If the current market value of Cooper Dental share is $10, what's our recommendation for this stock? -Conduct a sensitivity analysis showing the value per share as a function of sales growth rate. -Another sensitivity analysis showing the value per share as a function of long-term FCF growth and WACC. -Conduct a scenario analysis showing the value per share under optimistic/pessimistic scenarios. Optimistic: Sales growth 18%, COGS/sales 40%, WACC 12%, FCF growth 8%. Pessimistic: Sales growth 5%, COGS/sales 55%, WACC 16%, FCF growth 3%. pro forma takes the longest time
