Use Sheet 8 for this assignment. After completing DCF valuation and sensitivity analysis as shown in the video lecture, do a scenario analysis for Cooper Dental's 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%.

HL L V W M N 0 P Q R Formula Bar S T. U Troject 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 equals 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%. HL L V W M N 0 P Q R Formula Bar S T. U Troject 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 equals 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%