Suppose Flash in the Pan Corporation is expected to pay an annual dividend of $3 per share one year from now and that this dividend will grow at the following rates during each of the following four years (to the end of year 5): Year 2, 20 percent; Year 3, 30 percent; Year 4, 20 percent; Year 5, 10 percent. After this supernormal growth period, the dividend will grow at a sustainable 5 percent rate each year beyond year 5.

I need help with this spreadsheet, please provide formulas for each answer.

Problem 12-27 The Nonconstant, or Supernormal Dividend Growth Model Flash in the Pan Corporation Given: Year Year Year Year Year Year 6 and orn 30% 10% 5% Dividend growth rates Dividend expected 1 year from now Assumed required rate of return 20% 20% $3.00 15% Calculations a. Present value of Dividends during the supernormal growth period Expected future dividends during the supernormal growth period Present values of dividends during the supernormal growth period Total b. Present value of dividends during the normal growth period (year 6 and on) 5 Terminal value at end of year per Equation 12-7 Present value of terminal value c. Total present value per share of Flash in the Pan Corp. stock