I have already solved the problem. PLEASE JUST SHOW ME HOW TO PUT IT IN EXCEL CORRECTLY (formulas). See data below

_______________________________________________________________________________________________________________________

Question 1.

DPS=D0 = Dividend to each shareholder / Number of shares with each = $320,000/ $150,000 = $2.13

EPS = 5.35

Therefore, Retention Ration, RR=1-dividend payout ratio = 1 - DPS/ EPS = 1-2.13/5.35=60.12%

ROE = 21%

G= ROE x RR = 60.12% x 21% = 12.63%

Required return, K_e = 18%

So, the value per share, P₀ = D₀ x (1 + g) / (K_e - g) = 2.13 x (1 + 12.63%) / (18% - 5%) = $44.71 per share

______________________________________________________________________________________________________________________________

Question 2.

We must recalculate the industry average EPS = EPS_avg = (1.19 + 1.26 + 2.07) / 3 = $ 1.51

Industry average DPS = DPS_avg = 0.44

So, industry average retention ratio, RR_avg = 1 - dividend payout ratio = 1 - DPS_avg / EPS_avg = 1 - 0.44 / 1.51 = 70.80%

Industry average ROE_avg = 11%

Industry average growth rate, g_avg = ROE x RR_avg = 11% x 70.80% = 7.79%

Industry average Required return, R = 15%

Value per share now = $ 38.75

______________________________________________________________________________________________________________________________

Question 3.

Industry average P / E = P_avg / EPS_avg = 18.08 / 1.51 = 11.97

Ragan's P/E ratio using

• assumptions in question (1) = 44.71 / 5.35 = 8.36
• assumptions in question (2) = 38.75 / 5.35 = 7.24

Ragan's P/E ratio is significantly different from industry average. This is because Ragan's fundamentals are significantly different from industry average (growth rates, ROE, required return)

______________________________________________________________________________________________________________________________

Question 4.

If we consider original growth rate for five years and then industry average growth rate later then Price = 38.75 (as calculated in question (2)).

Price attributed to growth opportunities = 38.75 - 22.84 = 15.91

So, % age of stock value attributed to growth opportunities = 15.91 / 38.75 = 41.06%

______________________________________________________________________________________________________________________________

Question 5.

g = ROE x RR

Therefore, ROE = g / RR = 7.79% / 60.12% = 12.96%

______________________________________________________________________________________________________________________________

Question 6.

Steps to increase the stock price:

1. Retain higher proportion of earnings and plowback into the business
2. Invest into new opportunities with return higher than cost of equity or required rate of return

Stock price will not increase

• if retention ratio is kept lower than what it is currently
• Or if the retained earnings are invested in projects with return lower than cost of equity (capital) or required rate of return

Stock Valuation at Ragan Engines Larissa has been talking with the company's directors about the future of East Coast Yachts. To this point, the company bas used outside suppliers for various key components of the company's yachts, including engines. Larissa has decided that East Coast Yachts should consider the purchase of an engine manufacturer to allow East Coast Yachts to better integrate its supply chain and get more control over engine features. After investigating several possible companies, Larissa feels that the purchase of Ragan Engines, Inc., is a possibility. She has asked Dan Ervin to analyze Ragan's value. Ragan Engines, Inc., was founded nine years ago by a brother and sister-Carrington and Genevieve Ragan-and has remained a privately owned company. The company manufactures marine engines for a variety of applications. Ragan has experienced rapid growth because of a proprietary technology that increases the fuel efficiency of its engines with very little sacrifice in performance. The company is equally owned by Carrington and Genevieve. The original agreement between the siblings gave each 150,000 shares of stock. Larissa has asked Dan to determine a value per share of Ragan stock. To accomplish this, Dan has gathered the following information about some of Ragan's competitors that are publicly traded: EPS DPS Stock Price ROE R Blue Ribband Motors Corp. $1.19 $.19 $16.32 10.00% 12.00% Bon Voyage Marine, Inc. 1.26 .55 13.94 12.00 17.00 Nautilus Marine Engines -.27 .57 23.97 N/A 16.00 Industry average $.73 $.44 $18.08 11.00% 15.00% Nautilus Marine Engines's negative earnings per share (EPS) were the result of an accounting write-off last year. Without the write-off, EPS for the company would have been $2.07. Last year, Ragan had an EPS of $5.35 and paid a dividend to Carrington and Genevieve of $320,000 each. The company also had a return on equity of 21 percent. Larissa tells Dan that a required return for Ragan of 18 percent is appropriate, 1. Assuming the company continues its current growth rate, what is the value per share of the company's stock? 2. Dan has examined both the company's financial statements and those of its competitors. Although Ragan currently has a technological advantage, Dan's research indicates that Ragan's competitors are investigating other methods to improve efficiency. Given this, Dan believes that Ragan's technological advantage will last only for the next five years. Stock Valuation at Ragan Engines Larissa has been talking with the company's directors about the future of East Coast Yachts. To this point, the company bas used outside suppliers for various key components of the company's yachts, including engines. Larissa has decided that East Coast Yachts should consider the purchase of an engine manufacturer to allow East Coast Yachts to better integrate its supply chain and get more control over engine features. After investigating several possible companies, Larissa feels that the purchase of Ragan Engines, Inc., is a possibility. She has asked Dan Ervin to analyze Ragan's value. Ragan Engines, Inc., was founded nine years ago by a brother and sister-Carrington and Genevieve Ragan-and has remained a privately owned company. The company manufactures marine engines for a variety of applications. Ragan has experienced rapid growth because of a proprietary technology that increases the fuel efficiency of its engines with very little sacrifice in performance. The company is equally owned by Carrington and Genevieve. The original agreement between the siblings gave each 150,000 shares of stock. Larissa has asked Dan to determine a value per share of Ragan stock. To accomplish this, Dan has gathered the following information about some of Ragan's competitors that are publicly traded: EPS DPS Stock Price ROE R Blue Ribband Motors Corp. $1.19 $.19 $16.32 10.00% 12.00% Bon Voyage Marine, Inc. 1.26 .55 13.94 12.00 17.00 Nautilus Marine Engines -.27 .57 23.97 N/A 16.00 Industry average $.73 $.44 $18.08 11.00% 15.00% Nautilus Marine Engines's negative earnings per share (EPS) were the result of an accounting write-off last year. Without the write-off, EPS for the company would have been $2.07. Last year, Ragan had an EPS of $5.35 and paid a dividend to Carrington and Genevieve of $320,000 each. The company also had a return on equity of 21 percent. Larissa tells Dan that a required return for Ragan of 18 percent is appropriate, 1. Assuming the company continues its current growth rate, what is the value per share of the company's stock? 2. Dan has examined both the company's financial statements and those of its competitors. Although Ragan currently has a technological advantage, Dan's research indicates that Ragan's competitors are investigating other methods to improve efficiency. Given this, Dan believes that Ragan's technological advantage will last only for the next five years