L M N o Q 5 T U V X B B D E F H 1 1 Non-Constant Growth Rate Dividend Valuation 2 3 Instructions: Calculate the value of a share of XYZ common stock utilizing the concept of discounted cash flow valuation. 4 5 Glven: 1. The forecast for the company's common stock didend for 2021 through 2025 is: Dividend 6 Year Forecast 7 2021 4.00 8 2022 4.80 9 2023 5.72 10 2024 6.90 11 2025 8.25 12 13 2. From the years 2026 through 2030 dividend growth is expected to slow under the following schedule: Dividend 14 Year Growth 15 2026 15% 16 2027 13% 17 2028 115 18 2029 9% -9 2030 7% 20 0 3. From the year 2031 and beyond the dividend is expected to grow at a constant rate of 7% 22 4. You also have made the following assumptions: 4 Required Return 10.50% 25 1. Populate Constant Growth Rate and Required Return in cell B3 and 14 27 2. Populate cells E2 through Eg with the forecasted dividend amounts 9 0 3. Populate cells F7 through F11 with the given dividend growth rates for those years 31 4. Using an equation referencing the previous years dividend and the ghen dividend growth rate for that year, 33 calculate the dividend amount for each cell from E7 through E11. An equation must be used in these cells. 5 Steps 5. Populate coll F12 by referencing the constant growth rate figure in the Inputs box of the worksheet 35 6. In cell G12 calculate the Terminal Value of all future dividencs using the Constant Growth Dividend Discount Model. 89 7. Populate cells H2 through H12 with the total cash flow expected from this share of common stock. Use cell references and equations where appropriate, 31 32 2. In cell H14 calculate the Present Value of all Future Expected Dividends using the NPV formula. You will be referencing the predicted cash flows and the RROR A B D E F H 1 J K L M N 0 Total CF to Investor Dividend Div Growth Term Value 1 Inputs 2 3 Constant Growth 4 Required Return 5 6 7 8 9 10 Year 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 Forecasts 11 Slowing growth Constant growth PV of CF 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 L M N o Q 5 T U V X B B D E F H 1 1 Non-Constant Growth Rate Dividend Valuation 2 3 Instructions: Calculate the value of a share of XYZ common stock utilizing the concept of discounted cash flow valuation. 4 5 Glven: 1. The forecast for the company's common stock didend for 2021 through 2025 is: Dividend 6 Year Forecast 7 2021 4.00 8 2022 4.80 9 2023 5.72 10 2024 6.90 11 2025 8.25 12 13 2. From the years 2026 through 2030 dividend growth is expected to slow under the following schedule: Dividend 14 Year Growth 15 2026 15% 16 2027 13% 17 2028 115 18 2029 9% -9 2030 7% 20 0 3. From the year 2031 and beyond the dividend is expected to grow at a constant rate of 7% 22 4. You also have made the following assumptions: 4 Required Return 10.50% 25 1. Populate Constant Growth Rate and Required Return in cell B3 and 14 27 2. Populate cells E2 through Eg with the forecasted dividend amounts 9 0 3. Populate cells F7 through F11 with the given dividend growth rates for those years 31 4. Using an equation referencing the previous years dividend and the ghen dividend growth rate for that year, 33 calculate the dividend amount for each cell from E7 through E11. An equation must be used in these cells. 5 Steps 5. Populate coll F12 by referencing the constant growth rate figure in the Inputs box of the worksheet 35 6. In cell G12 calculate the Terminal Value of all future dividencs using the Constant Growth Dividend Discount Model. 89 7. Populate cells H2 through H12 with the total cash flow expected from this share of common stock. Use cell references and equations where appropriate, 31 32 2. In cell H14 calculate the Present Value of all Future Expected Dividends using the NPV formula. You will be referencing the predicted cash flows and the RROR A B D E F H 1 J K L M N 0 Total CF to Investor Dividend Div Growth Term Value 1 Inputs 2 3 Constant Growth 4 Required Return 5 6 7 8 9 10 Year 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 Forecasts 11 Slowing growth Constant growth PV of CF 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
