Please answer questions in excel and write a written explanation

Stock Valuation at Ragan, Inc. Ragan, Inc., was founded nine years ago by brother and sister Carrington and Genevieve Ragan. The company manufactures and installs commercial heating, ventilation, and cooling (HVAC) units. Ragan, Inc., has experienced rapid growth because of a proprietary technology that increases the energy efficiency of its units. The company is equally owned by Carrington and Genevieve. The original partnership agreement between the siblings gave each 50,000 shares of stock. In the event either wished to sell stock, the shares first had to be offered to the other at a discounted price. Although neither sibling wants to sell, they have decided they should value their holdings in the company. To get started, they have gathered the information about their main competitors in the table below. Expert HVAC Corporation's negative earnings per share were the result of an accounting write-off last year. Without the write-off, earnings per share for the company would have been $1.06. The ROE for Expert HVAC is based on net income excluding the write-off. Last year, Ragan, Inc., had an EPS of $4.54 and paid a dividend to Carrington and Genevieve of $60,000 each. The company also had a return on equity of 18 percent. The siblings believe that 15 percent is an appropriate required return for the company. 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 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: E1P0=R(ROEb)1b Stock Valuation at Ragan, Inc. Ragan, Inc., was founded nine years ago by brother and sister Carrington and Genevieve Ragan. The company manufactures and installs commercial heating, ventilation, and cooling (HVAC) units. Ragan, Inc., has experienced rapid growth because of a proprietary technology that increases the energy efficiency of its units. The company is equally owned by Carrington and Genevieve. The original partnership agreement between the siblings gave each 50,000 shares of stock. In the event either wished to sell stock, the shares first had to be offered to the other at a discounted price. Although neither sibling wants to sell, they have decided they should value their holdings in the company. To get started, they have gathered the information about their main competitors in the table below. Expert HVAC Corporation's negative earnings per share were the result of an accounting write-off last year. Without the write-off, earnings per share for the company would have been $1.06. The ROE for Expert HVAC is based on net income excluding the write-off. Last year, Ragan, Inc., had an EPS of $4.54 and paid a dividend to Carrington and Genevieve of $60,000 each. The company also had a return on equity of 18 percent. The siblings believe that 15 percent is an appropriate required return for the company. 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 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: E1P0=R(ROEb)1b