Instructions: Calculate the value of a share of XYZ common stock utilizing the concept of discounted cash flow valuation. Given 1. The forecast for the company's common stock dividend for 2018 through 2022 is: Year Forecast 2018 2019 2020 2021 2022 4.00 4.80 5.72 6.90 8.25 2. From the years 2022 through 2026 dividend growth is expected to slow under the following schedule Year Growth 2023 2024 2025 2026 2027 15% 13% 11% 9% 3. From the year 2028 and beyond the dividend is expected to grow at a constant rate of 7% 4. You also have made the following assumptions: Required Return 10.50% Steps: 1. Populate Constant Growth Rate and Required Return in cell 84 and B5 2. Populate cells E2 through E6 with the forecasted dividend amounts 3. Populate cells F7 through F11 with the given dividend growth rates for those years 4. Using an equation referencing the previous years dividend and the given dividend growth rate for that year calculate the dividend amount for each cell from E7 through E11. An equation must be used in these cells. 5. Populate cell F12 by referencing the constant growth rate figure in the Inputs box of the worksheet. 6. In cell 612 calculate the Terminal Value of all future dividends using the Constant Growth Dividend Discount Model 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. 8. 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