1YPEG Calcs - How to use the spreadsheet

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

40 Likes

Thanks Kevin,

Great work. As a busy professional, employee, parent and husband I really appreciate the time and effort you put into this. Thanks so much.

David

7 Likes

Kevin,

I will echo David and say thank for the great work. I also tend to be rather busy and appreciate your spreadsheet for saving me some time.

Cheers,
Chris

1 Like

Thanks Kevin,
A time-saver is helpful but I’m finding the spreadsheet immensely educational. My background is in science, not finance, so I am finding this incredibly valuable. Thanks to all who contribute to this amazing board.

Hi Kevin, I also want to express my appreciation for this great sheet. It just gets better almost every day and always incorporates great new ideas.

Just wondering if there is an acceptable way to share this with my son. He is a TMF Stock Advisor member, but not presently involved in the other services.

What do you think? Brian

1 Like

Has anyone tried getting the figures for a foreign company like ARCM.ST aka AMAVF? What figures I see are in SEK, but the big problem is that most of the numbers don’t seem to be available.