Need help with question #3 on the green excel problem and also 1-6 on the questions below.

Stock Valuation Input area: Shares owned by each sibling Ragan EPS Dividend to each sibling Ragan ROE Ragan required return $ $ 50,000 3.15 45,000 17% 14% $ $ Artic Cooling, Inc. National Heating & Cooling Expert HVAC Corp. Industry average EPS 1.30 1.95 (0.37) 0.96 DPS Stock price 0.16 $ 25.34 0.23 29.85 0.14 22.13 0.18 $ 25.77 ROE 8.50% 10.50% 9.78% 9.59% R 10.00% 13.00% 12.00% 11.67% $ $ Expert EPS w/o write-off $ 1.10 Output area: 1) Total earnings Retention ratio Growth rate Current dividend per share Dividend per share next year Value per share * Assumption: The firm will continue at its current growth rate indefinitely $ 1.45 2) Industry EPS Industry payout ratio Industry retention ratio Value per share *Assumption: The firm will continue at its current growth rate indefinitely 1.45 2) Industry EPS Industry payout ratio Industry retention ratio Industry growth rate Dividend Year 1 2 3 4 5 6 * Assumption: After five years, the company's growth rate will slow down to the industry average growth rate. Stock value in Year 5 *Assumption: It is belived that the industry average required rate of return is more appropriate Stock price today **Assumption: It is believed that the appropriate discount rate for Ragan should be the industry average required rate of return R. 3) Industry PE Ragan PE (original assumption) Ragan PE (revised assumption) Stock price implied by industry PE QUESTIONS Page 271 1. Assuming the company continues its current growth rate, what is the value per share of the company's stock? 2. To verify their calculations, Carrington and Genevieve have hired Josh Schlessman as a consultant. Josh was previously an equity analyst and covered the HVAC industry. Josh has examined the company's financial statements, as well as examining its competitors' financials. Although Ragan, Inc., currently has a technological advantage, his research indicates that other companies are investigating methods to improve efficiency. Given this, Josh believes that the company's technological advantage will last only for the next five years. After that period, the company's growth will likely slow to the industry growth average. Additionally, Josh believes that the required return used by the company is too high. He believes the industry average required return is more appropriate. Under this growth rate assumption, what is your estimate of the stock price? 3. What is the industry average price-earnings ratio? What is the price-earnings ratio for Ragan, Inc.? Is this the relationship you would expect between the two ratios? Why? 4. Carrington and Genevieve are unsure how to interpret the price-earnings ratio. After some head scratching, they've come up with the following expression for the price-earnings ratio: Po E1 1-b R - (ROE X b) Beginning with the constant dividend growth model, verify this result. What does this expression imply about the relationship between the dividend payout ratio, the required return on the stock, and the company's ROE? 5. Assume the company's growth rate slows to the industry average in five years. What future return on equity does this imply, assuming a constant payout ratio? 6. After discussing the stock value with Josh, Carrington and Genevieve agree that they would like to increase the value of the company stock. Like many small business owners, they want to retain control of the company, so they 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. How can they increase the price of the stock? Are there any conditions under which this strategy would not increase the stock price? Stock Valuation Input area: Shares owned by each sibling Ragan EPS Dividend to each sibling Ragan ROE Ragan required return $ $ 50,000 3.15 45,000 17% 14% $ $ Artic Cooling, Inc. National Heating & Cooling Expert HVAC Corp. Industry average EPS 1.30 1.95 (0.37) 0.96 DPS Stock price 0.16 $ 25.34 0.23 29.85 0.14 22.13 0.18 $ 25.77 ROE 8.50% 10.50% 9.78% 9.59% R 10.00% 13.00% 12.00% 11.67% $ $ Expert EPS w/o write-off $ 1.10 Output area: 1) Total earnings Retention ratio Growth rate Current dividend per share Dividend per share next year Value per share * Assumption: The firm will continue at its current growth rate indefinitely $ 1.45 2) Industry EPS Industry payout ratio Industry retention ratio Value per share *Assumption: The firm will continue at its current growth rate indefinitely 1.45 2) Industry EPS Industry payout ratio Industry retention ratio Industry growth rate Dividend Year 1 2 3 4 5 6 * Assumption: After five years, the company's growth rate will slow down to the industry average growth rate. Stock value in Year 5 *Assumption: It is belived that the industry average required rate of return is more appropriate Stock price today **Assumption: It is believed that the appropriate discount rate for Ragan should be the industry average required rate of return R. 3) Industry PE Ragan PE (original assumption) Ragan PE (revised assumption) Stock price implied by industry PE QUESTIONS Page 271 1. Assuming the company continues its current growth rate, what is the value per share of the company's stock? 2. To verify their calculations, Carrington and Genevieve have hired Josh Schlessman as a consultant. Josh was previously an equity analyst and covered the HVAC industry. Josh has examined the company's financial statements, as well as examining its competitors' financials. Although Ragan, Inc., currently has a technological advantage, his research indicates that other companies are investigating methods to improve efficiency. Given this, Josh believes that the company's technological advantage will last only for the next five years. After that period, the company's growth will likely slow to the industry growth average. Additionally, Josh believes that the required return used by the company is too high. He believes the industry average required return is more appropriate. Under this growth rate assumption, what is your estimate of the stock price? 3. What is the industry average price-earnings ratio? What is the price-earnings ratio for Ragan, Inc.? Is this the relationship you would expect between the two ratios? Why? 4. Carrington and Genevieve are unsure how to interpret the price-earnings ratio. After some head scratching, they've come up with the following expression for the price-earnings ratio: Po E1 1-b R - (ROE X b) Beginning with the constant dividend growth model, verify this result. What does this expression imply about the relationship between the dividend payout ratio, the required return on the stock, and the company's ROE? 5. Assume the company's growth rate slows to the industry average in five years. What future return on equity does this imply, assuming a constant payout ratio? 6. After discussing the stock value with Josh, Carrington and Genevieve agree that they would like to increase the value of the company stock. Like many small business owners, they want to retain control of the company, so they 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. How can they increase the price of the stock? Are there any conditions under which this strategy would not increase the stock price