Thursday, April 4, 2013

My excel sheet project

containing P/E, P/B, dividend and ROE


As I have mentioned I have been actively working on a little project where it will hopefully become easier for me to evaluate the companies according to my contrarian approach and also that I can very easily come back to them and add some more information when I want to look more closely at them again. Since I do not have sufficient data about a long list of companies I am today not evaluating the companies according to all the key figures that I have put in this excel sheet and to be honest I do not know if one have to do that either. Some key figures I just find uninteresting and them I have not added. This explanation can be found continuously here.



Key Figures

Run P/E (the running P/E is based on quarters or half year reports and will be adjusted when I return to look at the stock. Otherwise it will be too hard work to keep it running for all the stocks, I love when this value is below 10)

P/B (I like Price/Book since I want to buy 1 euro of stock for 50 cents!)
P/E*P/B, <22.5 (This is a formula from Graham and anything below 22.5 is a strong buy!)
P/S (Price/Sales might give a branch indication)
E/S (Earnings/Sales will be branch oriented but also shows how heavy costs they have)
E/B (Earnings/Book... I do not look at this)
ROE, % (The return on equity meaning how well does the company transform the shareholders equity into profit)
Book/Debt (To see how much in trouble they are)
Exp Growth, % (this value I add myself usually the same as the calculated CAGR)
CAGR, % (I try to go five years back and see the revenue back then and today and then I calculate the yearly growth since then)
Lynch 3Y of G (Peter Lynch easy formula to buy stock if the P/E of stock is below its expected 3 year growth then it is a strong buy also indicates when one should start to think about selling)
P/E=8.5+2*G (Grahams formula that indicates what the P/E of a company should be if much less then it is a strong buy on the company)
R&D / E, % (Someone once said that 40% of a companies earnings 5 years from now are coming from products currently being developed. Don´t know if that is true or not but being a researcher myself I can see some truth to it and therefore want to measure the technology companies according to this one. Already now I can say that I was very surprised by some results that came out)
Stock Yield, % (want to see dividend I am getting. I like dividends!)
Yield/Earning, % (Are they sending out more money than they earned? If so where did that come from and how many years can they do that before they must borrow money to keep up with the high dividends?)

Collected Values

Currency (only to keep track of what it is in)
Price, Share (the stock price that day. I will indicate that for the stock when I publish the table)
Shares, M (the amount of shares in the company)
Sales, M (The sales or revenue that they had)
Sales, Share (Sales they had per share)
E Q1, M (The running quarter or half year earnings. Often only full year)
E Q2, M
E Q3, M
E Q4, M
Tot Earn, M (add up the running earnings)
Earn Run Year, Share (get that per share)
Book, M (the book value of the company)
Book, Share (how much that is per share)
Debt, M (the debt that the company has)
R&D, M (the research they are making)
V(tn) (The coming three things are for the CAGR calculation, V(tn) is final year sales, V(t0) is the "first year" sales and then tn-t0 to get the amount of years involved for the calculation.
V(t0)
tn-t0
Dividend (the money they pay out)
Comment (usually this is for me complaining on how they use a broken financial year or only full year. I also mentioned if the analysis was requested etc.)

There has been some changes over time and for instance I have brought in ROE (=Return on Equity) which means how well the company is using the money they gain. And I removed the quarter earnings since that just became too much work. Full year earnings with the last year annual report is enough for my contrarian and slow approach to investing.

No comments: