Question from Financial Analysis with Microsoft Excel 2013 (7th Edition).

P.259 (Chapter 8, Problem 2)

As an analyst at Churnem & Burnem Securities, you are responsible for making recomendations to your firm's clients regarding common stocks. After gathering data on Denver Semiconductors, you have found that its dividend has been growing at a rate of 5% per year to the current (D0) $0.60 per share. The stock is now selling for $20 per share, and you believe that an appropriate rate of return for this stock is 9% per year.

a. If you expect that the dividend will grow at a 5% rate into the foreseeable future, what is the highest price at which you would recommend purchasing this stock to your clients? (need a snapshot of Excel formula(s) with results).

b. Suppose now that you believe that the company's new product line will cause much higher growth in the near future. Your new estimate is for a three-year period of 15% annual growth to be followed by a return to the historical 5% growth rate. Under these new assumptions, what is the value using the two-stage dividend growth model? (need a snapshot of Excel formula(s) with results).

c. You now realize that it is likely that the growth will transition from 15% down to 5% gradually, rather than instantaneously. If you believe that the transition will take five years, what is the value of the stock? Use the three-stage and H-Model valuation methods. (need a snapshot of Excel fomula(s) with results).

d. For each of the answers from above, create an If statement that shows whether the stock is undervalued, overvalued, or fairly valued (need instructions on how to do this).

Thanks in advance!