PLEASE SOLVE USING EXCELL SHEET AS FOLLOWS, AND GIVE THE DETAILS OF THE CALCULATIONS MADE.

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? Selected data for the Derby Corporation are shown below. Use the data to answer the following questions. INPUTS (In millions) Year Current Projected 2 0 1 3 4 Free cash flow -$20.0 $20.0 $80.0 $84.0 Marketable Securities $40 Notes payable $100 Long-term bonds $300 Preferred stock $50 WACC 9.00% 40 Number of shares of stock a. Calculate the estimated horizon value (i.e., the value of operations at the end of the forecast period immediately after the Year-4 free cash flow). Assume growth becomes constant after Year 3. Current 0 Projected 2 1 3 4 Free cash flow -$20.0 $80.0 $84.0 Long-term constant growth in FCF Horizon value $20.0 b. Calculate the present value of the horizon value, the present value of the free cash flows, and the estimated Year-0 value of operations. PV of horizon value PV of FCF Value of operations (PV of FCF + HV) 3 c. Calculate the estimated Year-0 price per share of common equity. Value of operations Plus value of narketable securities 3 Total value of company Less value of debt Less value of preferred stock Estimated value of common equity Divided by number of shares Price per share =