please see the problem and set it in the excel. please provide the formula in every cell in the excel so i understand how it is done.
thank you very much.
the last pic is what i did so far. so please refer to the problem and complete it with all formulas provided for me( for every cell)
thank you!
2. Medina werks, a manufacturing company headquartered in Canada, has a competitive advantage that will probably deteriorate over time. analyst Flavio torino expects this deterioration to be reflected in declining sales growth rates as well as declining profit margins. to value the company, torino has accumulated the following information: Current sales are C$600 million. over the next six years, the annual sales growth rate and the riet profit margin are projected to be as follows: Year1 Year 2 Year 3 Year 4 Year 5 Year 6 (96) 20 Net profit margin412 Sales growth rate 16 12 10 10510 Beginning in Year 6, the 7 percent sales growth rate and 10 percent net profit margin should persist indefinitely Capital expenditures (net of depreciation) in the amount of 60 percent of the sales increase will be required each year investments in working capital equal to 25 percent of the sales increase will also be required each year Debt financing will be used to fund 40 percent of the investments in net capital items and working capital. The beta for Medina werks is 1.10; the risk-free rate of return is 6.0 percent; the equity risk premium is 4.5 percent. The company has 70 million outstanding shares. . what is the estimated total market value of equity? what is the estimated value per share? 1) 2) 2. Medina werks, a manufacturing company headquartered in Canada, has a competitive advantage that will probably deteriorate over time. analyst Flavio torino expects this deterioration to be reflected in declining sales growth rates as well as declining profit margins. to value the company, torino has accumulated the following information: Current sales are C$600 million. over the next six years, the annual sales growth rate and the riet profit margin are projected to be as follows: Year Year 2 Year 3 Year 4 Year 5 Year 6 Sales growth rate Net profit margin 20 14 16 13 12 10 10.5 10 Beginning in Year 6, the 7 percent sales growth rate and 10 percent net profit margin should persist indefinitely Capital expenditures (net of depreciation) in the amount of 60 percent of the sales increase will be required each year. of te s llah year. Debt financing will be used to fund 40 percent of the investments in net capital items and working capital. The beta for Medina werks is 1.10, the risk-free rate of return is 6.0 percent, the equity risk premium is 4.5 percent The company has 70 million outstanding shares. . 1) what is the estimated total market value of equity? 2) what is the estimated value per share? 1 Assumptions 3 Current sales 4 FCInv/sales growth 5 Depreciation/sales growth 6 WCInv/Sales growth 7 sales growth rate following year 6 8 operating profit margin following year 6 9 Debt Financing 10 Outstanding shares 11 Risk free rate 12 Beta 13 Risk Premium 14 Cost of equity 600.00 35% 10% 70 million 4.5% 10.95% 16 Years 17 Sales growth rate 18 Operating profit Margin 20% 14% 16% 13% 12% 12% 10% 11% 8% 11% 7% 10% 10% 19 21 |Years 22 Sales 23 Operating profit (EBIT) 24 Net Capex 25 Increane in Capex 26 NWC 27 Increase in NWC 28 Debt value 29 FCFE 30 Terminal Value 31 (FCFE 32 Discount factor 33 IV 34 Value per share $600.00 $720.00 $835.20 $935.42 $1,028.97 $1,111.28 $1,189.07 $1,272.31 100.80 $108.58$112.25$113.19 $116.68 $118.91 $127.23 72.00$69.12$60.13 $56.13$49.39$46.67 $49.94 $30.00$58.80$83.86 $10724$1 127.82$14727$168.08
