I'm working on the same project as the attached word document. However, the numbers are a little different. I submitted my project to the professor and he provided this as feedback:

"For question 2, you need to find thePVsof dividends between year 1-5 and addPVof Price at 5. Also, please drop dividends 6 and 7. For Price 5, it should be D5*(1+g industry)/ (R- g industry). Question 1 looks good. Please fix and send back for additional review before you officially submit."

Could you review the attached Excel file and see if I'm on the right track for the Q2 tab? My answer is highlighted in yellow. The reason I need help is because I don't see why I would use the equation he is telling me to.

Mini case 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 in 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. The get started, they have gathered the following information about their main competitors: Ragan Incorporation, Competitors EPS ($) DPS ($) Stock Price ($) ROE (%) R (%) Arctic Cooling, Inc. 0.79 0.20 14.18 10.00 10.00 National Heating & Cooling 1.38 0.62 11.87 13.00 13.00 Expert HVAC Corp. -0.48 0.38 13.21 14.00 12.00 Industry Average 0.56 0.40 13.09 12.33 11.67 Expert HVAC Corporation's negative earnings per share were the result of an accounting writeoff last year. Without the write-off, earnings per share for the company would have been $1.06. Last year, Ragan, Inc., had an EPS of $4.54 and paid a dividend to Carrington and Genevieve of $63,000 each. The company also had a return on equity of 25%. The siblings believe that 20% is an appropriate required return for the company. 1|Page Question 1 Assuming the company continues its current growth rate, what is the value per share of the company's stock? Total of shares outstanding = 50,000units 2 (Carrington & Genevieve) = 100,000units Total dividend paid by the company = $63,000 2 (Carrington & Genevieve) = $126,000 Net income Earnings per share = Shares outstanding Net income 100,000 4.54 = Net income = 4.54 100,000 Net income = $454,000 Dividend payout ratio = Dividends Net Income = $ 126,000 $ 454,000 = 0.28 Retention ratio = 1 - Dividend payout ratio = 1 - 0.28 = 0.72 Growth (g) = Return on equity (ROE) Retention ratio = 0.25 0.72 2|Page = 0.18 or 18% Value per share (V0) = D 0( 1+ g) Rg = 1.26 (1+0.18) 0.200.18 = $74.34 Therefore the value per share of the company's stock is $74.34. Question 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. Although Ragan, Inc., currently has a technological advantage, his research indicates 3|Page 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 this 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? 0.79+ 1.38+1.06 Industry average earning per share (EPS) = 3 Industry average payout ratio = = $1.08 Industry average dividend per share (DPS) Industry average earning per share (EPS) $ 0.40 = $ 1.08 = 0.37 Industry retention ratio = 1 - Industry payout ratio = 1 - 0.37 = 0.63 Industry growth (g) = Industry average return on equity Industry retention ratio = 0.1233 0.63 = 0.078 or 7.8% Stock Price, (P0) = P5 (1+ R)5 4|Page D1 D2 D3 D4 1 + 2 + 3 + 4 (1+ R) (1+ R) (1+ R) (1+ R) D5 + (1+ R)5 + 1+0.1167 5 = 1.49 1.75 2.07 2.44 3.4 + + + + 1 2 3 4 (1+0.1167) (1+ 0.1167) (1+ 0.1167) (1+ 0.1167) = $58.34 Thus, estimate stock price is $58.34. Question 3 5|Page 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? Industry average price-earnings ratio = = Price per share Industry earning per share(EPS ) 13.09 0.56 = 23.38 Ragan Inc. price-earnings ratio = = Price per share Earning per share(EPS) 58.34 4.54 = 12.85 The price-earnings ratio is the current price of the stock divided by the latest EPS figure. It enables investors to anticipate movements in the price of a stock based on projections of earnings per share. It's usually more useful to compare the P/E ratios of one company to other companies in the same industry, to the market in general or against the company's own historical P/E. Based on the P/E ratio shown above, industry average P/E ratio is 23.38; whereas Ragan Inc. P/E ratio is 12.85. Thus, Ragan Inc. has P/E ratio that is less than industry average P/E ratio. This situation happen might be due to dividend paid by Ragan Inc. is too high compared to average competitor company in that particular industry. 6|Page Question 4 Carrington and Genevieve were unsure on how to interpret the price-earnings ratio. After some brainstorming, they've come up with the following equation for the price-earnings ratio: P0 1b = E 1 R( ROE 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 of stock, and the company's ROE? A company will either pays out its earnings as dividend or retains its earnings to reinvest in its business depends on its return on equity (ROE) and on investors' required rate of return (R), which is dependent on the perceived riskiness of the company's stock. If a company's ROE is greater than the market's required rate of return, then it would be benefit the company's stock price if the company reinvested its earnings in more growth and distribute little or no earnings as dividends. The growth of dividends and the stock price is dependent on company growth (g). However, two factors obviously related to the company's growth rate are ROE and the company's earnings retention ratio (b) or plowback ratio, which is the amount of earnings that the company reinvests in its business rather than distributing it to shareholders as a dividend. The expressions imply the relationship between the dividend payout ratio, the required return and company ROE are show below: 7|Page Retention Rate (b) = 1 - Dividend payout ratio (D) Dividend payout ratio (D) = 1 - retention rate (b) Company growth rate (g) = ROE x b In constant dividend growth model, =1-b =1- (1-D) =D =R- (ROE x b) =R- g To show: P D 1b = = E ( Rg ) E R( ROE b) D (Rg)(1b) = E R( ROE b) R= ( D ROE )+( D b)(E g)( E g b) (DE+b) So, this shows that the company's ROE and R has positive correlation. Question 5 8|Page 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? Assume a constant payout ratio; it implies that the value of the stock price of Ragan, Inc. will get lower because of the lower growth rate. The lower the growth rate the lower the value of the stock price we will get. We can get the answer with the formula we have used above. Question 6 9|Page 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, but 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? Josh, Carrington and Genevieve can increase the price of the stock by issuing more dividends. As we know, we calculate the stock price by the formula D/(R-g).But, when the growth rate of Ragan, Inc. get lower, this strategy would not increase the stock price. We can also get the answer with the formula above, and if dividends are not paid in cash such as shares, this strategy would also not increase the stock price. 10 | P a g e QUESTION 1 EPS Stocks Total Earnings ROE 4.85 100,000 485,000 17% Dividends Total Earnings 150,000 485,000 b=1-dividend payout ratio b=1-(150,000/485,000) 0.69 g=b*ROE g=.69072165*.17 11.74% Constant Growth Model D*(1+g)/(R-g) D g R 1.50 11.74% 14.00% $ 74.24 This assumption is based on the company continuing its current growth rate are several factors could change the outcome of the value of the company's can be affected by both earnings per share and number of shares per partn ultimately the growth rate. This newly caluclated growth rate in this instanc if total earnings are increased as would the value of the company's stock. A can affect the stock value with variation in different ways. Regardless If ROE Required rate of return on the other hand causes the opposite affect. As th versa. ontinuing its current growth rate. Though I do not feel there are any caveats in the analysis, there me of the value of the company's stock by altering the growth rate. The total earnings calculation and number of shares per partner being reduced, therefore, lowering the divident payout ratio and clated growth rate in this instance would in turn lower the value of the company's stock. Inversely, value of the company's stock. As far as ROE and required rate of return, both are given, though different ways. Regardless If ROE increases or decreases, growth rate and stock value follow suit. auses the opposite affect. As the rate of return increases, the stock value decreases and vice 0.84 Artic 1.34 Nat Heating 0.54 Expert 0.91 EPS Industry Avg Due to the write off, we should recalculate their rate We use the average rate because this will give us a more realistic picture of how the industry performs as opposed to one company; especially since Ragan had an extremely high EPS compared to the other competitors. The stock price would plummet if we used the 4.85 EPS. 0.49 Industry Average Div 0.54 Industry Payout Retention 0.46 To get... g=ROE*Retention g=.15*.46 0.069 =g Dividends D0 1.50 D1 1.68 D2 1.88 D3 2.11 D4 2.36 D5 2.64 D5* =1-.54 PV 31.446540881 35.220125786 39.446540881 44.180125786 49.481740881 55.419549786 (1+g industry) /(R-g industry) Year 5 55.419549786 1.069 0.071 39.48 1.68 1.88 1.1167 1 2.11 1.1167 2 2.36 1.1167 3 2.64 1.1167 4 55.4195498 1.1167 1.1167 5 5 1.5044327035 1.50759545 1.51521028 1.51763012 1.52027217 31.9139392 e realistic picture of how pecially since Ragan had etitors. The stock price EPS. Constant growth model because the company is growing by 12% per period; as calculated in Q1. The assumption is used that the company will continue to grow at the current rate. If the company grew at a quicker rate, the stock price would be higher. If the company shrank, the stock price would be lower. QUESTION 1 EPS Stocks Total Earnings ROE 4.85 100,000 485,000 17% Dividends Total Earnings 150,000 485,000 b=1-dividend payout ratio b=1-(150,000/485,000) 0.69 g=b*ROE g=.69072165*.17 11.74% Constant Growth Model D*(1+g)/(R-g) D g R 1.50 11.74% 14.00% $ 74.24 This assumption is based on the company continuing its current growth rate are several factors could change the outcome of the value of the company's can be affected by both earnings per share and number of shares per partn ultimately the growth rate. This newly caluclated growth rate in this instanc if total earnings are increased as would the value of the company's stock. A can affect the stock value with variation in different ways. Regardless If ROE Required rate of return on the other hand causes the opposite affect. As th versa. ontinuing its current growth rate. Though I do not feel there are any caveats in the analysis, there me of the value of the company's stock by altering the growth rate. The total earnings calculation and number of shares per partner being reduced, therefore, lowering the divident payout ratio and clated growth rate in this instance would in turn lower the value of the company's stock. Inversely, value of the company's stock. As far as ROE and required rate of return, both are given, though different ways. Regardless If ROE increases or decreases, growth rate and stock value follow suit. auses the opposite affect. As the rate of return increases, the stock value decreases and vice 0.84 Artic 1.34 Nat Heating 0.54 Expert 0.91 EPS Industry Avg Due to the write off, we should recalculate their rate We use the average rate because this will give us a more realistic picture of how the industry performs as opposed to one company; especially since Ragan had an extremely high EPS compared to the other competitors. The stock price would plummet if we used the 4.85 EPS. 0.49 Industry Average Div 0.54 Industry Payout Retention g=ROE*Retention g=.15*.46 0.069 =g Dividends D0 1.50 D1 1.68 D2 1.88 D3 2.11 D4 2.36 D5 2.64 0.46 To get... =1-.54 59.243498721 41.68 D1 1.68 D2 1.88 1.1167 1 D3 2.11 1.1167 2 D4 2.36 1.1167 3 D5 2.64 1.1167 4 P5 59.2434987 1.1167 1.1167 5 5 1.5044327035 1.50759545 1.51521028 1.51763012 1.52027217 34.116001 e realistic picture of how pecially since Ragan had etitors. The stock price EPS. Constant growth model because the company is growing by 12% per period; as calculated in Q1. The assumption is used that the company will continue to grow at the current rate. If the company grew at a quicker rate, the stock price would be higher. If the company shrank, the stock price would be lower