Question

Create a valuation sheet in MS-Excel similar to the format shown below: ng mga tao 99 SUPERNORMAL GROWTH DO $2.00 Cost of Equity 12.00% 10.00% 10.00% 10.00% D3 10.00% 10.00% DS 10.00% 10.00% D7 10.00% D8 10.00% D9 10.00% D101 5.00% D6 ng g10 P10 Assume KTS currently pays a $2 per share dividend and has a cost of equity of 15%.

Please include formulas/ how you got answers

Answer 1

Asssuming Cost of Equity is 12%

Price of Stock :

PV of CFs from it.

Div Calculation:

Year	Div	CF	Formula	Calculation
1	D1	$      2.20	D0(1+g)	2*1.1
2	D2	$      2.42	D1(1+g)	2.2*1.1
3	D3	$      2.66	D2(1+g)	2.42*1.1
4	D4	$      2.93	D3(1+g)	2.66*1.1
5	D5	$      3.22	D4(1+g)	2.93*1.1
6	D6	$      3.54	D5(1+g)	3.22*1.1
7	D7	$      3.90	D6(1+g)	3.54*1.1
8	D8	$      4.29	D7(1+g)	3.90*1.1
9	D9	$      4.72	D8(1+g)	4.29*1.1
10	D10	$      5.19	D9(1+g)	4.72*1.1
11	D11	$      5.45	D10(1+g)	5.19*1.05

P10 = D11 / [ Ke - g ]

D11 = Div after 11 Years

P10 = Price after 10 Years

Ke = required Ret

g = Growth rate

P10 = D11 / [ Ke - g ]

= 5.45 / [ 12% - 5% ]

= 5.45 / 7%

= 77.81

Price of STock:

Year	Div	CF	PVF @12%	Disc CF
1	D1	$      2.20	0.8929	$      1.96
2	D2	$      2.42	0.7972	$      1.93
3	D3	$      2.66	0.7118	$      1.89
4	D4	$      2.93	0.6355	$      1.86
5	D5	$      3.22	0.5674	$      1.83
6	D6	$      3.54	0.5066	$      1.80
7	D7	$      3.90	0.4523	$      1.76
8	D8	$      4.29	0.4039	$      1.73
9	D9	$      4.72	0.3606	$      1.70
10	D10	$      5.19	0.3220	$      1.67
10	P10	$   77.81	0.3220	$   25.05
Price of stcok				$   43.19

Pls comment, if any further assistance is required.