Folks,
I added another tab to the 1YPEG calc spreadsheet to provide some brief instruction on how to use it. As I’ve added more and more to it, I think the more formal instruction became warranted. I tried to keep it brief and to the point, but if anything is unclear please let me know. I’ve copied the text below for reference, and of course the spreadsheet itself is located here:
https://docs.google.com/spreadsheets/d/1H_v6WOjFi81rM3TH9ZHS…
How to analyze your prospect or holding:
Step 1 Enter your ticker on a blank row in the 'Earnings Growth Information' tab.
Step 1a If the earnings date is approaching, fill that in too. If it is still estimated, don't fill
anything in please. When you put an estimated date in no one will know to go back later to
correct it. If left empty, someone will notice and be on the look out for the correct earnings date.
Step 2 Enter your ADUSTED Non-GAAP EPS values for the last 8 quarters
o Q-1 is the most recent quarter (the one that just past)
o Q-2 is the prior quarter
o Q-3 is the quarter before that
o Q-4 is another quarter before that
o Q-5 is the year ago quarter (from the one that just past)
o Q-6, Q-7 and Q-8 are the year ago quarter from Q-2, Q-3 and Q-4
o *NOTE Q-1, Q-2, etc are NOT Q1, Q2 for the given year. They are Quarter minus 1,
Quarter minus 2, etc. If you entered your values in the wrong way, woops!
Please go back to correct them.
Step 3 Navigate to the 'Revenue Growth Information' tab. Enter your TOTAL SALES or TOTAL REVENUE
values for the last 8 quarters.
o Q-1 is the most recent quarter (the one that just past)
o Q-2 is the prior quarter
o Q-3 is the quarter before that
o Q-4 is another quarter before that
o Q-5 is the year ago quarter (from the one that just past)
o Q-6, Q-7 and Q-8 are the year ago quarter from Q-2, Q-3 and Q-4
o *NOTE Q-1, Q-2, etc are NOT Q1, Q2 for the given year. They are Quarter minus 1,
Quarter minus 2, etc. If you entered your values in the wrong way, woops!
Please go back to correct them.
Step 4 Watch the magic. And do your analysis.
o If a stock is a candidate stock it will be flagged with an X in the Candidate
Stock Column. What makes a candidate stock? A candidate stock is where Earnings
Growth is > 30%, Revenue Growth is > 20% and 1YPEG < 1.0
o Stocks that show quality attributes will have their growth values highlighted in
green (and bold green if better than normal). Likewise, stocks that aren't up to
snuff will show in red (and bold red if really bad). Yellow is passable. There's
a legend on each tab for the color coding if you scroll over to the right some.
o Stocks that are showing signs of earnings acceleration or revenue acceleration
will have an X appear in the respective indicator column. If you want to see
more details on how much they are accelerating, go to the 'Earnings Acceleration'
and 'Revenue Acceleration' tabs respectively.
o The parity price is the price the stock will be if everything continues hunky
dory and the stock price is commeasurate with its growth. That is, the stock has
an appropriate P/E for the growth it provides. Stocks with little or stagnant
growth and yet have modest P/Es in the 20s could have a stock price higher than
the parity price. This is because the P/E expectations are above its actual
track record for growth looking back over the year. It may have better forward
P/E..and it may pay a good dividend.. but it's probably no longer a growth stock.
o Bagger potential - goes along with the parity price formula. Its the number of
'bags' the stock has in it, in theory and if everything continues 'hunky dory'.
How to adjust for a new quarter:
Step 1 In the 'Earnings Growth Information' tab, shuffle all EPS values left by one cell. That is,
copy Q-1 to Q-2, Q-2 to Q-3, etc. Let the value in Q-8 drop off (its no longer needed)
Enter your new ADJUSTED Non-GAAP EPS value in Q-1.
Step 2 In the 'Revenue Growth Information' tab, do the same thing for the Revenue values. Again, let
the value in Q-8 drop off.
Step 3 In the 'Earnings Growth Information' tab, enter the value 'REPORTED' in the earnings date
column. This will let everyone know the company has reported and the values have been updated.
Other running notes:
Note 1 Please make sure to add all tickers to the end. Sorting is a manual thing right now and I have
to do it procedurally to make sure the tabs all stay in sync. Also, although it may be fun,
please refrain from adding a custom sort to one of the columns. If you'd like you can add a
pivot table on a new tab to do some fancy sorting. The 'Candidate Selections' tab has one of
those pivot tables. You can make a copy of it to use as an example.
Note 2 Stock Growth Information is a work in progress. I'm trying to develop a formula that gets me
the average 'high' stock price over each quarter. I developed one function that is taking up
more cycles than Google wants to give out, so, I am still working through it. The concept is
to show how the stock price is growing in relation to its EPS and revenue growth figures.
Ideally you'd want to see these percentages grow in much the same way.
As always, please feel free to drop a note with any feedback or suggestions.
Best,
–Kevin