Given the following data:

Dividend Discount Model

Three-Stage Example

Discount rate (r):	12,00%
Growth period (g1):
Growth year	5
Initial growth period of EPS	23,00%
Maturity period (g2):
Payout at maturity	45%
Retention rate at maturity (retent)	55%
Growth rate at maturity: r x retent	6,600%
Transition period:
Transition years	12
Growth rate of EPS (incremental)	1,26%
Other information:
Current fiscal year EPS	4,00
Current calendar year dividend	0,50
Current payout (p1)	12,50%
Growth year payout = current	12,50%
Maturity payout (p2)	45,00%
Transition years payout (incremental)	2,50%
Growth + Transition Years	17
Terminal value (TV) = div1/(r-g)

Year	Assumption	EPS	growth		Dividend	payout	TV	PV (div+TV)
1	Growth year EPS	4,92	23,00%	1	0,62	12,50%		0,55
2	Growth year EPS	6,05	23,00%	2	0,76	12,50%		0,60
3	Growth year EPS	7,44	23,00%	3	0,93	12,50%		0,66
4	Growth year EPS	9,16	23,00%	4	1,14	12,50%		0,73
5	Growth year EPS	11,26	23,00%	5	1,41	12,50%		0,80
6	Transition year EPS	13,71	21,74%	6	2,06	15,00%		1,04
7	Transition year EPS	16,52	20,48%	7	2,89	17,50%		1,31
8	Transition year EPS	19,69	19,22%	8	3,94	20,00%		1,59
9	Transition year EPS	23,23	17,95%	9	5,23	22,50%		1,88
10	Transition year EPS	27,10	16,69%	10	6,78	25,00%		2,18
11	Transition year EPS	31,28	15,43%	11	8,60	27,50%		2,47
12	Transition year EPS	35,72	14,17%	12	10,72	30,00%		2,75
13	Transition year EPS	40,33	12,91%	13	13,11	32,50%		3,00
14	Transition year EPS	45,02	11,65%	14	15,76	35,00%		3,22
15	Transition year EPS	49,70	10,38%	15	18,64	37,50%		3,40
16	Transition year EPS	54,23	9,12%	16	21,69	40,00%		3,54
17	Transition year EPS	58,50	7,86%	17	24,86	42,50%	519,648	79,30
18	Maturity	62,36	6,60%	18	28,06	45,00%
	Theoretical price							109,05

Note: EPS = earnings per share

Question:

Fill in the Sensitivity Analysis (two way table):

		Discount Rate
		11%	11,50%	12%	12,50%	13%
Growth rate at maturity	5,80%
	6,20%
	6,60%
	7%
	7,40%

A sensitivity analysis determines how stock values change given different values of discount rate and perpetuity growth rate.

Please demonstrate how you find the solution in Excel (formula)