How is step 3 supposed to be solved? I can not figure out the formula.

It appears that SGR1 and SGR2 are 0.00%. Not sure if that's solved correctly

additional instructions

c) Step 3: You are required to find the expected dividends. Complete Step 3 Table. Please read Chapter 9 - Section 9.1 The present value of Common Stocks" i. Dividend year n = Dividend year N-1 (1 + SGR) ii. After year 5, we have a different growth rate (the industry SGRs)! The industry SGR and SGR are in "Step 2" table. 1. "SGR & SGR1" implies that the dividend will grow at "SGR" from the year 1 to 5 and the dividend will grow at "SGR," from the year 6. 2. "SGR & SGR2" implies that the dividend will grow at "SGR from the year 1 to 5 and the dividend will grow at "SGR," from the year 6. d) Step 4: Find out "PVtoday of each expected dividends and Stock Valueyears"." Use "Constant Growth Model (Gordon Growth Model)" formula to find out the "Stock Value years" in Step 4. Please, read Chapter 9: Section 9.1 (Case 2: Constant growth). Complete Step 4 Table. e) Step 5: Find out the value of total equity and the value per share. Complete Step 5 Table. Step 3 (formula is provided in the instruction) SGR & SGR $980,000 SGR & SGR2 $980,000 SGR & SGR2 Step 4 (formula is provided in the instruction) SGR & SGR PVtoday of D PVtoday of D2 PVtoday of D3 PVtoday of D4 PVtoday of Ds Stock Value years High Quality Pipes, Inc. Total number of shares: Earnings per share: Total earnings (Net income): Total dividends paid: SGR (Sustainable growth rate): Required Rate of Return: 900,000 $7.35 $6,615,000 $980,000 0.0% 23.00% Tip: The boxes will be filled up automatically once you complete the P2- Q2. Please make sure you have all the correct values in P2-Q2. If you have wrong answers in P2-Q2, the answers in P2-Q3 will be incorrect and there won't be any partial points. Step 2 (formula is provided in the instruction) Industry Information Industry Avg. EPS Bas : Industry Avg. EPS Dit Industry Avg. dividends paid per share: Payout ratio 1 (using EPS Bas): Retention ratio 1 (using EPS Bas): Payout ratio 2 (using EPS Di): Retention ratio 2 (using EPS Dil): ROE End: SGR 1 (using Retention ratio 1 & ROE End): SGR 2 (using Retention ratio 2 & ROE End): Required Rate of Return: $3.37 $3.36 $0.49 14.81% 85.19% 14.81% 85.19% 18.34% 0.00% 0.00% 23.00% Part 2-Q3: Requirements (10 points): a. Use the template (Case Study 3, spreadsheet: P2-03) posted on the Blackboard to complete CS3. b. Read Chapter 3: Section 3.5 (Pay attention to "A Note about sustainable growth rate calculations".) C. Find "the estimated stock price under Dan's assumptions (the company will grow at the industry average rate after the next five years)", by completing the boxes from each step (all green boxes will be filled up automatically) a) Step 1: Once you complete the previous question (P2-Q1 and P2-Q2), all the green boxes will be automatically filled up. b) Step 2: You are required to find two different payout ratios, two different retention ratios, and two different SGRs. Complete Step 2 Table. We can find "Payout ratio" and "Retention ratio" by using EPS and Dividends paid." Total dividends paid Dividend per share = 7 Total number of shares (1) Total earnings Total numberof shares If we divide Equation (1) by Equation (2), we will have the followings: Payout ratio = Total dividends paid Total number of shares) Total earnings Total number of shares) Total dividends paid Total earnings Retention ratio = 1 - Payout ratio Larissa has decided that East Coast Yachts should consider the purchase of a pipe manufacturer to allow East Coast Yachts to better integrate its supply chain and get more control. After investigating several possible companies, Larissa feels that the purchase of HQP (High Quality Pipes), Inc., is a possibility. She has asked Dan Ervin to analyze HQP's value. HQP, Inc., was founded nine years ago by a brother and sister James and Jane and has remained a privately-owned company. The company manufactures PVC pipes for a variety of applications. HQP has experienced rapid growth. James and Jane equally own the company. The original agreement between the siblings gave each 450,000 shares of stock. Please use the following information to complete the Part2 - 01: Larissa has asked Dan to determine a value per share of Ragan stock. To accomplish this, Dan must gather the information about the following companies, which are HQP's competitors that are publicly traded: (Data is given. Look up the values from the financial statements provided: Case_Study_3_Data) o Omega Flex, Inc (Ticker: OFLX) O L.B. Foster Company (Ticker: FSTR) o Westlake Chemical Corp (Ticker: WLK) Complete tasks under "Part2-Q1 instruction below this box. Please use the following information to complete the Part2 - 02: Last year (2017), HQP Inc. had an EPS (Earnings per share) of $7.35 and paid a dividend to James and Jane of $490,000 each. The company also had a return on equity (ROE taken from the ending Balance Sheet) of 20%. Larissa tells Dan that a required return for HOP of 23% is appropriate. Complete tasks under Part2-Q2 instruction below this box. Please use the following information to complete the Part2 - 03: Dan has examined the company's financial statements, as well as examining those of its competitors. Although HQP currently has a technological advantage, Dan's research indicates that HQP's competitors are investigating other methods to improve efficiency. Given this, Dan believes that HQP's technological advantage will last only for the next five years. Hence, the HQP will grow at SGR rate (the information is in the Step 1 table). After that period, the company's growth will likely slow to the industry average (industry SGR, and SGR2). Complete tasks under Part2-Q3 instruction below this box