In this exercise we consider a stock with three growth stages and investigate the effect of assumed cost of equity and growth rate on the estimated stock price.

Stage $1$: We have been able to forecast company's dividend in the next $5$ years as follows: $$5\mathrm{.}0,$ $$5\mathrm{.}5,$ $$6\mathrm{.}0,$ $$6\mathrm{.}5,$ and $$7\mathrm{.}0.$

Stage $2$: For the next $5$ years $(6$ to $10)$ we expect dividend to grow at $6\%($g$1).$

Stage $3$: After that we expect dividend to grow at $3\%($g$2)$ indefinitely.

Assume cost of equity is $5\%.$

$1.$ What is the estimated stock price $($P$0)?$

$2.$ Using the Data Table function in Excel $($under Data $-->$ What$-$if Analysis$),$ explore how the stock price will change if cost of equity $($r$)$ changes from $1$ to $10\%$ and the final growth rate $($g$2)$ changes from $0$ to $10\%,$ in one percent increment.

$($We covered this feature in class. If you need refresher you can use MS Excel help and$/$or refer to related YouTube videos.$)$

$3.$ Realize how the estimated stock price is sensitive to the assumed cost of equity and growth rate.

$4.$ Any other observations?

Hint: You can start with the following structure in Excel. Compute the dividends in the first two stages and PH in the last. Estimate their present values and the stock price $($P$0).$ Use r and g$2$ as variable to facilitate to answer question $2.$

Year DIV

$15\mathrm{.}0$

$25\mathrm{.}5$

$36\mathrm{.}0$

$46\mathrm{.}5$

$57\mathrm{.}0$

$6$

$7$

$8$

$9$

$10$

$11$