a. You are given the five year (Years 1-5) dividend growth rate of 12.0% and the constant dividend growth rate of 5% which begins in year 11. You are to decrease the dividend growth rate by an equal percentage each year from year 5 to year 11 to decrease the rate from 12.0% to 5.0%. Construct the table in Excel to show the dividend growth rate and dividend each year for the next 11 years beginning with the 2014 dividend (Year 0). Using the table, calculate the price of the stock at Year 10 and the stock price today in Excel. Assume required return is 11%.

Construct a table depicting differing constant growth rates (the rate which begins in year 11) ranging from 0% to 10% and for each constant growth rate calculate the price of the stock today. On the same tab as the data, graph the stock price (vertical axis) vs the growth rate (horizontal axis).

b. You are given the following additional information: Payout ratio: 20.0% PE at constant growth: 15 (represents Year 11 PE) Knowing that earnings can either be retained (plowed back) or paid out, calculate the Year 11 EPS using the given Year 11 PE ratio of 15 and then calculate the Year 11 stock price. What is the stock price today (Hint: Do not forget to discount the dividends that occur prior to Year 11)?