Question

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

Answer 1

Solution 1 : The require return should ber= 6%+1.10(4.5%) =10.95%

FCFE estimates for Medina Werks (C$ in millions)

Year   1    2 3 4 5 6

  

Sales growth rate 20% 16% 12% 10% 8% 7%

Net Profit Margin 14% 13% 12% 11% 10.50% 10%

Sales 720.000 835.200         935.424 1,028.966 1,111.284 1,189.074

Net Profit 100.800 108.576         112.251 113.186          116.685         118.907

Net FC Inve 72.000 69.120           60.134           56.125             49.390           46.674

WCInv 30.000 28.800           25.056           23.386             20.579           19.447

Debt Financing    40.800 39.168           34.076           31.804             27.988           26.449

FCFE 39.600 49.824           61.137           65.480             74.703           79.235

PV of FCFE at 10.95% 35.692 40.475           44.763           43.211             44.433

  

       

Increase in sales 120 115    100    94 82 78

PV 0.90130689 0.81235412 0.73218037 0.659919215 0.594789738 0.53608809

Now we will calculate the terminal value of FCFE in year 6 and beyond

TV(5)    = FCFE(6)/r-g  

                                    2,005.94million

The present Value of this amount is

                                    1,193.11million

The estimated total market value of the firm is the present value of FCFE for year 1 through 5 plus the terminal value

MV =                                  C$       1,401.69million

Solution 2

Dividing C$1401.69 million by the 70 million outstanding shares gives the estimated value per share of C$20.02