Homework for Chapter 9: Problem # 4 in the text (Chapter 9) NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED "Homework for Chapter 9 Excel" TO SOLVE THE FOLLOWING PROBLEM, Financial information for four companies is provided in the following table: Company A Company B Company C Company D Last Dividend $0.50 $0.75 Expected Dividend $1.25 $0.90 Required rate of Return 15% 14% 17% 12% Growth Rate #1 10.00% 9.00% 7.00% 8.00% Growth Rate #2 5.00% 3.00% 4.00% 2.00% Growth Rate #1 Time 5.00 years 3.00 years 4.00 years 2.00 years Transition Period 3.00 years 2.00 years 4.00 years 3.00 years Quoted Price $6.88 $8.00 $10.50 $9.00 a) If you expect that the dividend of each company will grow at rate #2 into the foreseeable future (g is constant), at what price would you be willing to buy each of these stocks? (25 points). b) Assume that the companies will grow at rate #1 for the amount of time indicated in the table above. After that, assume that the companies will grow at rate #2 forever. Under these assumptions, what is the price at which you would be willing to buy these companies' stocks using the two-stage dividend growth model? Calculate your solution twice, first using equation 9-5 on page 260 and then using the FAME_TwoStage Value user-defined function (25 points). c) Assume that the transition between growth rates #1 and #2 will be gradual rather than instantaneous. Using the transition period given in the table, what is the price at which you would be willing to buy these companies' stocks using the H Model dividend growth model? Calculate your solution twice, the first time using equation 9-8 on page 264 and then using the FAME HModelValue user-defined function (25 points). d) How does the calculated intrinsic value compare to the quoted price of the stock? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued (25 points). B16 *= TA 1 Company A Company B Company C Company D 2 Last Dividend $0.50 $0.75 3 Expected Dividend $1.25 $0.90 Required rate of Return 15% 14% 17% 12% 5 Growth Rate #1 10.00% 9.00% 7.00% 8.00% 6 Growth Rate #2 5.00% 3.00% 4.00% 2.00% 7 Growth Rate #1 Time 5.00 years 3.00 years 4.00 years 2.00 years 8 Transition Period 3.00 years 2.00 years 4.00 years 3.00 years 9 Quoted Price $6.88 $8.00 $10.50 $9.00 10 11 Valuation 12 Single-Stage 13 Two-Stage (function) 14 Two-Stage (formula) 15 H Model (function) 16 H Model (formula) 17 18 Single-Stage Assesment 19 Two-Stage Assesment 20 Three-Stage Assesment