Free lunch!
You know what they say, "There's no free lunch in this world!" Hey, I'm telling you now, there is! If you've been reading my blog, you know that I'm a big fan of Phil Town and his book, Rule #1. In it, he gives the reader a very methodical way of finding the intrinsic value, or its "sticker price", of a stock. He looks at the "Big 5s", 5 numbers that tell you how well a company is doing. They are: i) return on invested capital (ROIC), ii) sales growth, iii) earnings per share growth, iv) book value per share growth, and v) free cash flow growth. He wants all 5 of these numbers to be greater than or equal to 10% on average for the last 10 years.
For those of us who are not accountants, not only do we not know where to find this data, we don't even know what half of these things mean! Well, there are 2 steps to rectify this situation: read his book (link on the right), and use my spreadsheet! I can't provide the first for free, but your local library may be able to. I can, however, offer you my spreadsheet for free (if you feel obligated to repay me, just email your friends about how great this blog is!). You can download it right here. There is a macro embedded in the spreadsheet. Don't worry, it's not a virus...I just wrote some code to simplify the process.
There is a short tutorial inside the spreadsheet itself. So, I will not go into the details of the mechanics here. Essentially, you type in the symbol of the stock of interest, click a few links, copy and paste a few tables, press a couple of buttons, and you have on your monitor, a Rule #1 analysis done for you, all in 2 minutes. If you are having trouble with it, just leave me a comment. I'll be sure to respond.
You guys can all thank Anthony for leaving a comment on my previous post on how to read the Form 10-K of True Religion. He reminded me that I should share this spreadsheet with the rest of the world. Anyway, give it a try. I've saved countless hours with it. On my next post, I'll talk about the results I got for True Religion with this spreadsheet. Stay tuned!
Update February 17, 2011: I've updated the sheet. Go to the Investing Resources to download the latest sheet.
Add The Catholic Investor on Google+!
Hey There,
ReplyDeleteNice work putting this excel model together. Currently I cam creating a google spreadsheet that dynamically updates the market data based on your model. However, I am have a concern on your model, because there is a function I don't understand and I hope you can explain the assumptions behind it.
=IF(ISERROR(($B$66/OFFSET($B$66,$A6,0))^(1/$A6)-1),"Invalid Data",(($B$66/OFFSET($B$66,$A6,0))^(1/$A6)-1))
This function is located in the big 5 tab of the spreadsheet inside the average table. Thank you for your help ahead of time.
Cheers!
Bryan
Hi Bryan,
ReplyDeleteThanks for your comment.
The formula you're talking about is from Cell E6. That cell is used to calculate the BVPS growth from the last 5 years. The ISERROR portion is just an error check. The real important portion is this: ($B$66/OFFSET($B$66,$A6,0))^(1/$A6)-1
The first term, ($B$66/OFFSET($B$66,$A6,0), is basically taking the Cell B66 of the "Big 5" tab, which is the BVPS of last year, and dividing it by Cell B71. By using the offset function, I move down from Cell B66 by A6 cells. Since A6 is 5, I'm moving down to B71.
What I'm trying to do is calculating the growth rate between those 5 years, using the formula:
BVPS_Final = BVPS_Initial x (1 + Growth_rate) ^ No_of_Years
Rearranging it, I get:
Growth_rate = (BVPS_Final/BVPS_Initial)^(1/No_of_Years) - 1
Let me know if I haven't been clear. You can find my email address in the "About Me" page!
I don't use Google docs too much. I'd love to hear about how you can make the spreadsheet dynamic. Shoot me an email! Thanks!