PLEASE SHOW EXCEL FORMULAS

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.

a.What is the present value of the dividends to be paid during the supernormal growth period? Assume that the required rate of return, ks, is 15 percent.

b.What is the present value of the dividends to be paid during the normal growth period (from year 6 through infinity)?

c.What is the total present value of one share of Flash in the Pans common stock?

A B D E F G H 1 2 3 Student instructions: This worksheet is for problem 12-27. Enter formulas in the blanks where indicated to complete the calculations needed. Year 5 10% Year 6 and on 5% 4 5 Problem 12-27 6 The Nonconstant, or Supernormal Dividend Growth Model 7 8 Flash in the Pan Corporation 9 10 Given: 11 Year Year Year Year 12 1 2 3 4 13 Dividend growth rates 20% 30% 20% 14 Dividend expected 1 year from now $3.00 15 Assumed required rate of return 15% 16 17 Calculations: 18 19 Present value of Dividends during the supernormal growth period: 20 21 Expected future dividends during 22 the supernormal growth period 23 24 Present values of dividends during 25 the supernormal growth period 26 27 Total 28 29 b. Present value of dividends during the normal growth period (year 6 and on) 30 31 Terminal value at end of year 5 32 per Equation 12-7 33 34 Present value of terminal value 35 36 37 C. Total present value per share 38 of Flash in the Pan Corp. stock 39 40