Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribedplease 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!
image text in transcribed
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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Information Systems Control And Audit

Authors: Et Al. Hyo-Jeong Kim, Michael Mannino, Compiled By Koros Press Editorial Board

1st Edition

1781639426, 978-1781639429

More Books

Students also viewed these Accounting questions