So here it is  one Google Spreadsheet with my valuation methods. This current version has the following:
 Yield Theory Valuation Method 
 DDRM total return Method
 P/E earnings multiple Method with EOY 2016 estimate
Please read the previous articles for more information.
 PE Earnings Multiple  http://div4son.blogspot.com/2015/06/usingpefordividendstockvaluation.html
D4s Valuation Tool Spreadsheet
https://docs.google.com/spreadsheets/d/1eIkxYAsQPJkWZmP9bYKcsGnSLTzvFx8hUe4YvGJLS0/edit?usp=sharing
Note1: You need to make a local copy of the spreadsheet
Note2: Focus only on columns 1&2. Modify only the Yellow Shaded cells
Note3: For yield theory, you may need to modify manually the dividends to take account of splits etc.
Most Important Note: These are my methods for valuations, and not investment ideas. The spreadsheet is provided for informational purposes only.
Example  JNJ
1. Yield Theory
 Enter the Ticker in B1
2. Using the “Price Yield” Tab determine the high and low yield points. You can eyeball this.
Estimate for High Yield around 3.75% and Low Yield around 2.50%
The Mid Price gives you an approximate of the “fair price” per the Yield Theory Method.
Current Price:

97

Current Div:

3.00

Current Yield:

3.09%

Tot DIV years

45

Current Year:

2016

High Price:

120.00

Mid Price:

100.00

Low Price:

80.00

Note: The original purpose of the Yield Theory is to buy at the bottom 25% point  from 8090. Clearly, JNJ is at the fair value point, and not yet at the buy point.
2. DDRM
 Fill in the Core Growth Estimate, Future Estimated ROE, Future estimated EPS. You can use Historical Information from Yahoo for your estimate.
Result
DDRM
 
Dividend Rate ($)

2.94

Divided by: share price

97

Current yield (%)

3.03

Core Growth Estimate (%)

7.00

Divided by: ROE (%)

20

Multiplied by: EPS ($)

5.00

Cost of Growth (%)

1.75

Earnings per Share ($)

5.00

Minus: Dividend

2.94

Minus: Cost of Growth

1.75

Funding Gap ($)

0.31

Divided by: Share Price ($)

97

Share Change (%)

0.32

Core Growth (%)

7.00

Plus: Share Change (%)

0.32

Total Dividend Growth (%)

7.32

Plus: Dividend Yield (%)

3.03

Projected Total Return (%)

10.35

Dividend Rate ($)

2.94

Required Return (%)

10.00

Growth (%)

7.32

Price

109.69

Dividend Rate ($)

2.94

Required Return (%)

10.00

Growth (%)

7.00

Price

97.97

Average

103.83

Note: The original intent of the DDRM is to provide an estimated ROI of your investment. However, with a simple math trick, we can estimate the price based on the Growth. If we use the Dividend Rate & Dividend Growth, you can get the “Fair Price”. Another method is to use your Growth Rate to get a lower “Fair Price”. The average of the two is typically what I use as mid point.
3. PE Earnings Multiple
 Fill in the High and Low PEs. You can use the information provided. I’ve allowed the possibility to adjust these values.
2. The tool will return the Fair Price (Mid Price). I’ve included the 2016 fair price based on data from GuruFocus and Yahoo.
P/E High and Low
 
High P/E

24.46

Mid P/E

18.45

Low P/E

12.44

High Price

122.30

Mid Price

92.25

Low Price

62.20

P/E 5 Yr High

24.46

P/E 5 Yr Low

12.44

P/E 5 Yr Median

18.74

Current P/E

18.61

2016 P/E Est  Yahoo

15.16

2016 E/S Est  Guru

6.52

2016 Est Price

98.84

For me, I’ve decided to load up on JNJ whenever it dips below 100. This is based on my investment situation. Your situation is very likely to be different.
Anyway, that’s it for now. I hope my spreadsheet is useful for you.
D4s
Disclosure: Long JNJ
Thanks for sharing your spreadsheet. I love pulling back to covers and seeing the logic behind decisions. I'll test drive this spreadsheet and reply again with my thoughts.
ReplyDeleteThanks
Investment Hunting,
DeleteThanks for test driving my spreadsheet. Please let me know what I can improve on. BTW  I have more to come :)
D4s
Hi D4s,
ReplyDeleteI am now looking at this spread sheet and want to bring something to your notice. On summary sheet, for DDRM, the target price values (in cells B54 & B59) are becoming Negative, when growth rate (B53) number is greater than Required return% (B52) and similarly when B58 > B57. For example, try CVS as symbol, enter growth rate as 13%, current yr EPS as 5.86, and required return as 10% and notice price targets in B54 and B59.
Srini, It is a case of Garbage In Garbage Out. For CVS, the growth rate at 13% is very high. In the book, they actually talk about using reasonable values for input. Moreover negative or nonsense numbers are possible if the input is incorrect.
DeleteFor me to value CVS, I set the Growth = 6 to 8% range, ROE at 13 and EPS at around 4.5. These are based on Guru's data as well as M* & S&P CapIQ data. At 6% growth, I get a price of around $70. At 8% growth, I get a price of around $85. So, based on this alone, the ballpark is around 7585. Yield Theory gives me a price around 100. PE analysis gives me a price of around 80. So, for me an estimate in 80s is probably fair. What I do next is to correlate this with the estimates from M* and S&P if available.
In any case, thanks for using the tool. Let me know if there are other problems you see.
D4s
understand and agree about garbage in.
DeleteJust want to make sure calculations are correct. In general, given the formula, if entered growth rate is > required return, then we are going to see ve numbers. Also, when growth rate = ror (10%), take a look at B54.
Also, i notice you are taking EPS for ttm, than cur yr estimate. Is that you being cautious/conservative? For cur year, it is ~5.86.
DeleteSrini, yes  I try to be conservative for the input parameters.
DeleteD4s
For your first point, DDRM is also based on the Gordon Growth Model  P = D/(RG) where R=Discount Rate, G=Growth Rate. So, if Growth>Discount Rate, then you'll have a negative price.
DeleteD4s