Please assist with solving question number 1-4 using Excel. I am struggling especially with the final question (#4). Another solution to number 4 was posted as below, and I am able to follow all calculations successfully up to the year 6 amount of $17,090,894 and finding the remaining solution for #4. I am seeking assistance in understanding how this was found as well as incorporating it into an Excel format.

Industry average growth , gi = ROE * retention ratio for the industry= 11% * (1- 0.44/1.47) = 7.7 %

We can consider g(Regan) = 12.6% for first five years, then constant growth rate of 7.7% till perpetuity. The Dividend in the 6 th year is arrived at by DDM with g=7.7 and k= 15%

0	1	2	3	4	5	6
6,40,000	7,20,640	8,11,441	9,13,682	10,28,806	11,58,436	1,70,90,894

From above we get NPV = $11,034,005 Million

Value attributable to first five years of growth= $ 3,645,140

Thus, % of value attributable to growth period = 3,645,140/11,034,005 = 33 %

Price per share = 11,034,005/300,000= $ 36.78

Beginning of actual assignment:

Larissa has been talking with the companys directors about the future of East Coast Yachts. To this point, the company has used outside suppliers for various key components of the companys 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 Ragans value.

Ragan Engines, Inc., was founded nine years ago by a brother and sisterCarrington and Genevieve Raganand 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 Ragans competitors that are publicly traded:

	EPS	DPS	Stock Price	ROE	R
Blue Ribband Motors Corp.	$1.09	$.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	$ .69	$.44	$18.08	11.00%	15.00%

Nautilus Marine Enginess 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 companys stock?

2. Dan has examined the companys financial statements, as well as examining those of its competitors. Although Ragan currently has a technological advantage, Dans research indicates that Ragans competitors are investigating other methods to improve efficiency. Given this, Dan believes that Ragans technological advantage will last only for the next five years. After that period, the companys growth will likely slow to the industry average. Additionally, Dan believes that the required return the company uses is too high. He believes the industry average required return is more appropriate. Under Dans assumptions, what is the estimated stock price?

3. What is the industry average priceearnings ratio? What is Ragans priceearnings ratio? Comment on any differences and explain why they may exist.

4. Assume the companys growth rate declines to the industry average after five years. What percentage of the stocks value is attributable to growth opportunities?