Valuation Sheet - Buffet esq FV Spreadsheet

	A	B	C	D	E	F	G	H	I	J	K
1	Buffett Valuation Worksheet
2	Enter values into shaded cells
3		Date of Analysis:	21/02/2016								INPUT DATA
4
5		Current Stock Data				Seven Year Averages
6		Company:	Renishaw				Return on Equity:	22.3%
7		Ticker:	RSW				Payout Ratio:	45.3%
8		Price:	$16.85				P/E Ratio-High:	25.1
9		EPS:	$1.34				P/E Ratio-Low:	14.6
10		DPS:	$0.46				P/E Ratio:	19.8
11		BVPS:	$5.58				Sustainable Growth	12.2%
12		P/E:	12.6				(ROE * (1 - Payout Ratio))
13		Earnings Yield:	8.0%
14		Dividend Yield:	2.7%
15		P/BV:	3.0	Industry Av P/BV	1.46
16
17		Gv't Bond Yield:	3
18
19	Historical Company Data
20					Price		P/E Ratio			Payout
21	Year	EPS	DPS	BVPS	High	Low	High	Low	ROE	Ratio
22
23
24	2010	0.30	0.17	2.18	£11.83	£5.48	39.4	18.3	13.8%	56.7%
25	2011	0.89	0.35	2.77	£17.80	£8.25	20.0	9.3	32.1%	39.3%
26	2012	0.95	0.38	3.34	£17.72	£8.74	18.7	9.2	28.4%	40.0%
27	2013	0.89	0.40	3.82	£20.00	£15.00	22.5	16.9	23.3%	44.9%
28	2014	0.92	0.41	4.84	£22.00	£14.70	23.9	16.0	19.0%	44.6%
29	2015	1.00	0.46	5.92	£26.00	£18.00	26.0	18.0	16.9%	46.0%
30		EPS	DPS	BVPS	High Price	Low Price
31	Annually Compounded Rates of Growth (5 year)						[(Year 1 / Year 6) ^ (1/5)] - 1
32		27.2%	22.0%	22.1%	17.1%	26.9%
33	Annually Compounded Rates of Growth (3 year)						[(Year 1 / Year 4) ^ (1/3)] - 1
34		1.7%	6.6%	21.0%	13.6%	27.2%
35											CALCULATION 1
36	Projected Company Data Using Historical Earnings Growth Rate
37
38	Year	EPS	DPS
39	Current	£1.34	0.61		14.89	Earnings after 10 years
40	Year 1	1.70	0.77		29.26	Sum of dividends paid over 10 years
41	Year 2	2.17	0.98		£16.85	Current Price
42	Year 3	2.76	1.25		£295.35	Projected price (Average P/E * EPS)
43	Year 4	3.51	1.59		£324.60	Total gain (Projected Price + Dividends)
44	Year 5	4.47	2.02
45	Year 6	5.68	2.57		34.4%	Projected return using historical EPS growth rate
46	Year 7	7.23	3.27			[(Total Gain / Current Price) ^ (1/10)] - 1
47	Year 8	9.20	4.16
48	Year 9	11.70	5.30
49	Year 10	14.89	6.74
50											CALCULATION 2
51	Projected Company Data Using Sustainable Growth Rate
52
53	Year	BVPS	EPS	DPS
54	Current	£5.58	1.24	0.56	3.92	Earnings after 10 years (BVPS * ROE)
55	Year 1	6.26	1.39	0.63	11.72	Sum of dividends paid over 10 years
56	Year 2	7.02	1.56	0.71	£16.85	Current Price
57	Year 3	7.88	1.75	0.79	£77.78	Projected Share price (Average P/E * EPS)
58	Year 4	8.84	1.97	0.89	£89.51	Total gain (Projected Price + Dividends)
59	Year 5	9.92	2.21	1.00
60	Year 6	11.12	2.48	1.12	18.2%	Projected return using sustainable growth rate
61	Year 7	12.48	2.78	1.26		[(Total Gain / Current Price) ^ (1/10)] - 1
62	Year 8	14.00	3.12	1.41
63	Year 9	15.71	3.50	1.58
64	Year 10	17.62	3.92	1.77
65
66
67
68
69											MY CALCULATION
70		Calculating projected annual compound rate of return
71
72		we can calculate future shareholder equity using rate of return on shareholders equity
73		share eq/ s or BVPS		historic ROE	earning per yr
74	PV	2016	£5.58	0.25	£1.40
75		2017	£6.98	0.25	£1.74
76		2018	£8.72	0.25	£2.18
77		2019	£10.90	0.25	£2.72
78		2020	£13.62	0.25	£3.41
79		2021	£17.03	0.25	£4.26
80		2022	£21.29	0.25	£5.32
81		2023	£26.61	0.25	£6.65
82		2024	£33.26	0.25	£8.31
83		2025	£41.57	0.25	£10.39
84	FV	2026	£51.97	0.25	£12.99
85
86
87
88	Is it worth it?	Question: How much should you be willing to pay in 2016 for shareholders equity in 2026 (10 years)?					aim= min 15%
89
90			BVPS	min 15% roi	years backwards
91	FV	2026	£51.97	15%	10
92	PV - max	2016	£12.85	(backwards compounding equation) - this value @ 15 % rate return =
93
94		To own BVPS of	£51.97	in 2016@15% ann rtn	you want to pay max	£18.75	per share now based on industry av P/BV multiple
95						£38.79	per share now based on current P/BV multiple
96
97
98		Current SP	PV bvps	Current P/BV	FV BVPS		CAGR %
99	ACTUAL PV	£16.85	£5.58	3.02	£51.97		25.00%
100