Please solve this problem as soon as possible ,I'm on a test , and please show and describe how the calculations had been made on an excel sheet

Thanks

(7-27) Build a Model: Dividend Growth Valuation Model resource Start with the partial model in the file Ch07 P27 Build a Model.xlsx on the textbook's Web site. Hamilton Landscaping's dividend growth rate is expected to be 30% in the next year, drop to 15% from Year 1 to Year 2, and drop to a constant 5% for Year 2 and all subsequent years. Hamilton has just paid a dividend of $2.50, and its stock has a required return of 11%. a. What is Hamilton's estimated stock price today? b. If you bought the stock at Year 0, what are your expected dividend yield and capital gains for the upcoming year? c. What are your expected dividend yield and capital gains for the second year (from Year 1 to Year 2)? Why aren't these the same as for the first year? Hamilton Landscaping's dividend growth rate is expected to be 30% in the next year, drop to 15% from Year 1 to Year 2, and drop to a constant 5% for Year 2 and all subsequent years. Hamilton has just paid a dividend of $2.50 and its stock has a required return of 11%. a. What is Hamilton's estimated stock price today? Do $2.50 Ts 11.0% 90,1 30% Short-run g; for Year 1 only. Short-run g; for Year 2 only. 91,2 15% GL 5% Long-run g; for Year 3 and all following years. 15% 5% 5% 30% 1 + 0 3 Horizon value = P g Year Dividend PV of dividends and PV of horizon value = Po = = D (1+g) = D3 = Ts-gL a. What is Hamilton's estimated stock price for Year 1? P + D P (1 + rs) + P P b. If you bought the stock at Year 0, what your expected dividend yield and capital gains for the upcoming year? 1. Find the expected dividend yield. Dividend yield = D I Po Dividend yield = Dividend yield = 1 9 2. Find the expected capital gains yield. 0 1 Use the estimated price for Year 1, P, to find the expected gain. 2 3 Cap. Gain yield= (P - Po) I Po 4 Cap. Gain yield= I 5 Cap. Gain yield= 6 7 Alternatively, the capital gains yield can be calculated by simply subtracting the dividend yield 8 from the total expected return. 9 0 Cap. Gain yield= Expected return Dividend yield 1 Cap. Gain yield= 2 Cap. Gain yield= 3 4 5 c. What your expected dividend yield and capital gains for the second year (from Year 1 to Year 6 2)? Why aren't these the same as for the first year? 7 8 1. Find the expected dividend yield. 9 D 1 P 0 Dividend yield = 1 Dividend yield = 2 Dividend yield = 3 4 2. Find the expected capital gains yield. 5 5 Use the estimated price for Year 2, P2, to find the expected gain. J 3 Cap. Gain yield= (P - P) I P Cap. Gain yield= I Cap. Gain yield= 1 2 Alternatively, the capital gains yield can be calculated by simply subtracting the dividend yield 3 from the total expected return. 4 5 Cap. Gain yield= Expected return Dividend yield 5 Cap. Gain yield= 7 Cap. Gain yield= 3 9 0 (7-27) Build a Model: Dividend Growth Valuation Model resource Start with the partial model in the file Ch07 P27 Build a Model.xlsx on the textbook's Web site. Hamilton Landscaping's dividend growth rate is expected to be 30% in the next year, drop to 15% from Year 1 to Year 2, and drop to a constant 5% for Year 2 and all subsequent years. Hamilton has just paid a dividend of $2.50, and its stock has a required return of 11%. a. What is Hamilton's estimated stock price today? b. If you bought the stock at Year 0, what are your expected dividend yield and capital gains for the upcoming year? c. What are your expected dividend yield and capital gains for the second year (from Year 1 to Year 2)? Why aren't these the same as for the first year? Hamilton Landscaping's dividend growth rate is expected to be 30% in the next year, drop to 15% from Year 1 to Year 2, and drop to a constant 5% for Year 2 and all subsequent years. Hamilton has just paid a dividend of $2.50 and its stock has a required return of 11%. a. What is Hamilton's estimated stock price today? Do $2.50 Ts 11.0% 90,1 30% Short-run g; for Year 1 only. Short-run g; for Year 2 only. 91,2 15% GL 5% Long-run g; for Year 3 and all following years. 15% 5% 5% 30% 1 + 0 3 Horizon value = P g Year Dividend PV of dividends and PV of horizon value = Po = = D (1+g) = D3 = Ts-gL a. What is Hamilton's estimated stock price for Year 1? P + D P (1 + rs) + P P b. If you bought the stock at Year 0, what your expected dividend yield and capital gains for the upcoming year? 1. Find the expected dividend yield. Dividend yield = D I Po Dividend yield = Dividend yield = 1 9 2. Find the expected capital gains yield. 0 1 Use the estimated price for Year 1, P, to find the expected gain. 2 3 Cap. Gain yield= (P - Po) I Po 4 Cap. Gain yield= I 5 Cap. Gain yield= 6 7 Alternatively, the capital gains yield can be calculated by simply subtracting the dividend yield 8 from the total expected return. 9 0 Cap. Gain yield= Expected return Dividend yield 1 Cap. Gain yield= 2 Cap. Gain yield= 3 4 5 c. What your expected dividend yield and capital gains for the second year (from Year 1 to Year 6 2)? Why aren't these the same as for the first year? 7 8 1. Find the expected dividend yield. 9 D 1 P 0 Dividend yield = 1 Dividend yield = 2 Dividend yield = 3 4 2. Find the expected capital gains yield. 5 5 Use the estimated price for Year 2, P2, to find the expected gain. J 3 Cap. Gain yield= (P - P) I P Cap. Gain yield= I Cap. Gain yield= 1 2 Alternatively, the capital gains yield can be calculated by simply subtracting the dividend yield 3 from the total expected return. 4 5 Cap. Gain yield= Expected return Dividend yield 5 Cap. Gain yield= 7 Cap. Gain yield= 3 9 0