Can you do in Excel Please

Problem 1 (50 points):

Dominion Resources (D) has just paid a dividend this past year of $2.50. Dividends are expected to grow at a constant rate of 6% each year in the future. The required market return for Dominion stock is 9.75%.

As soon as the dividend was paid; however, the Dominion Board of Directors decided to finance a large new project by reducing the rate of dividend growth to 2% for the next five years. The project has similar risk to existing projects in the company (the required return will remain at 9.75%), and it is expected that higher dividend increases will begin again in year six. The year six dividend is expected to increase by 7.5% and continue to grow at that rate each year into the future.

a. Using the dividend growth model, calculate the price of the Dominion stock prior to the board's decision.

Price = _____

b. What will be the price of Dominion stock under the new conditions?

Price = _____

c. Do you believe that the board has made a good decision given these estimates?

d. What is the expected total rate of return for the stock in year 1 from question b above? What is the expected capital gains yield and dividend yield?

CGY= _______

DY = _______

Total Return _______

e. After year 5, what will the companys expected total rate of return, capital gains yield and dividend yield be from question b above?

CGY= _______

DY = _______

Total Return _______

Can you do in Excel Please