Show clear excel formulas please!

Mini Case 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 has used outside suppliers for various key components of the com- pany'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. CHAPTER 9 Stock Valuation 301 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 $1.09 $.19 $16.32 10.00% 12.00% Blue Ribband Motors Corp. 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 $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 the company's financial statements, as well as examining 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. After that period, the company's growth will likely slow to the industry aver- age. Additionally, Dan believes that the required return the company uses is too high. He believes the industry average required return is more appropriate. Under Dan's assump- tions, what is the estimated stock price? 3. What is the industry average price-earnings ratio? What is Ragan's price-earnings ratio? Comment on any differences and explain why they may exist. 4. Assume the company's growth rate declines to the industry average after five years. What percentage of the stock's value is attributable to growth opportunities? 5. Assume the company's growth rate slows to the industry average in five years. What future return on equity does this imply? 6. Carrington and Genevieve are not sure if they should sell the company. If they do not sell the company outright to East Coast Yachts, they would like to try and increase the value of the company's stock. In this case, they want to retain control of the company and do not want to sell stock to outside investors. They also feel that the company's debt is at a manageable level and do not want to borrow more money. What steps can they take to try and increase the price of the stock? Are there any conditions under which this strategy would not increase the stock price? Shares owned by each sibling Ragan EPS Dividend to each sibling Ragan ROE Ragan required return Blue Ribband Motors Corp. Bon Voyage Marine, Inc. Nautilus Marine Engines Industry average Nautilus EPS w/o write-off 6969 $ $ 69 150,000 5.35 320,000 21% 18% EPS 1.19 $ 1.26 (0.27) 0.73 $ 2.07 DPS Stock price 0.19 $ 16.32 0.55 13.94 0.57 23.97 0.44 $ 18.08 $ ROE 10.00% 12.00% N/A 0.11 $ R 12.00% 17.00% 16.00% 0.15 1) Total earnings Payout ratio Retention ratio Growth rate Total dividends next year Total equity value Value per share Industry payout ratio. Industry retention ratio Industry growth rate Year 1 6 Stock value in Year 5 Total stock value today Value per share 3) Industry PE Ragan PE (original assumption Ragan PE (revised assumption Stock price implied by industry PE 2) Industry EPS SAWN. 4) Total earnings 5) ROE 2 3 4 5 Cash cow value Percentage not attributable to growth opportunities Percentage attributable to growth opportunities 1605,000.00 39.88% 60,12% 12.63% $ 720,807.48 $ 13,413,286.96 Total dividends $ 720,807.48 $ 811,817.84 $ 914,319.33 $ 1029,762.82 1159,782.41 $ $ [] [] []