Please do everything in excel

Year	2017
Income statement
Sales	$ 10,000,000.00
Cost of goods sold	$ (5,000,000.00)
Depreciation	$ (1,000,000.00)
Interest payments on debt	$ (320,000.00)
Interest earned on cash and marketable securities	$ 64,000.00
Profit before tax	$ 3,744,000.00
Taxes (40%)	$ (1,497,600.00)
Profit after tax	$ 2,246,400.00
Dividends	$ (898,560.00)
Retained Earnings	$ 1,347,840.00
Balance sheet
Cash	$ 800,000.00
Current assets	$ 1,500,000.00
Fixed assets
Fixed assets at cost	$ 10,700,000.00
Accumulated Depreciation	$ (3,000,000.00)
Net fixed assets	$ 7,700,000.00
Total assets	$ 10,000,000.00
Current liabilities	$ 800,000.00
Debt	$ 3,200,000.00
Equity
Stock (paid-in capital)	$ 4,500,000.00
Accumulated retained earnings	$ 1,500,000.00
Total liabilities and equity	$ 10,000,000.00

Thank you a lot!!!

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