Please use excel and show all formulas and do a written explanation

Question 1 & 2

Need all excel and written explanation for #2

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 companied 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? Stage 1 Stage 2 Based on 2-Stage DGM formula PV of Stage 1 payments if Part 1: DGM if Stage 1 dividend 8 forever payments went on forever B8 Correction for Stage 1 Part 2: Correction for time limit on 9 payments ending =1((1+B4)/(1+B6))D PV of Stage 2 payments at t=D8/(B6B5) Stage 1 dividend payments =B9 PV of Stage 1 payments Part 3: PV of Stage 2 dividend 10 at year 0 =B8B9 PV of stage 2 payments at year 0=D9/(1+B6)D4 paym Sum of stage 1 and stage 2 12 Today's Price =B10+D9/(1+B6)D4 payments at year 0=SUM(B10,D10) 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 companied 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? Stage 1 Stage 2 Based on 2-Stage DGM formula PV of Stage 1 payments if Part 1: DGM if Stage 1 dividend 8 forever payments went on forever B8 Correction for Stage 1 Part 2: Correction for time limit on 9 payments ending =1((1+B4)/(1+B6))D PV of Stage 2 payments at t=D8/(B6B5) Stage 1 dividend payments =B9 PV of Stage 1 payments Part 3: PV of Stage 2 dividend 10 at year 0 =B8B9 PV of stage 2 payments at year 0=D9/(1+B6)D4 paym Sum of stage 1 and stage 2 12 Today's Price =B10+D9/(1+B6)D4 payments at year 0=SUM(B10,D10)