Automatic MOS Calculator Watchlist for Microsoft Excel --- *Temporarily Offline*

[Justin]

The latest edition of our Automatic Margin of Safety Calculator can be downloaded from this location:
http://www.roicommunity.com/uploaded/mos_calc111.xls (Updated 9/8/2007)

This spreadsheet was created using Windows XP and Microsoft Office 2002. I have not tested it outside of this environment, although I'm hearing reports that it does not work well with Windows Vista or Office 2007. Haven't had a chance to look into this yet, and probably won't because Vista isn't my thing.

This spreadsheet has been designed to perform the rather laborious task of collecting the massive amounts of data needed to make a relatively accurate assesment of a company's fundamental strength, and assist you in placing an appropriate valuation on that company's share price based on this data. I have followed the principles of Phil Town's Rule #1 method as to the best of my understanding, however it should be noted that I am not a financial advisor, nor qualified to instruct you on how to build your portfolio. In fact, I'm just a normal blue collar 9-5 guy that "kills bugs for a living."

I found Phil Town's methods to be a breath of fresh air in the chaotic world of investing, and set out to create a tool that would make the most efficient use of my limited time. I chose to do this by harnessing the amazing power of Microsoft Excel and coupling that with the simple logic of Rule #1 Investing. This spreadsheet is the culmination of efforts not only by my own hand, but also of others who have shown me some pretty intense advanced Excel techniques since the onset of this project nearly two years ago.

I have yet do find the time to make an offical FAQ for this project, and for that I apologize. There is one thing that I do need to address here, as it seems to be the most common problem people run into. The behavior of the spreadsheet is that it always uses the most conservative historical data in the final steps of Margin of Safety Calculation. This means that if a company had a "rough and tumble" year at some point in its 10-Year Past, that value is going throw the final resulting Margin of Safety to be "off".

For example, run SBUX and look at the "Rule #1 Valuation Analysis" table on the top right. The values I'm talking about here are the "Rule #1 Growth Rate, Rule #1 PE, and the Current EPS (TTM)". In the case of SBUX, the default Rule #1 Growth Rate is 4%, the default Rule #1 PE is 8, and the Current EPS (TTM) is $0.81. These numbers may not be the most appropriate values to use, particularly if they are based on an anomolous year from the company's past. If we work backwards and reverse engineer the formula, we see that the 3 Yr. Equity Growth Rate calculated as 4%, even though the 9, 5, and 1 Yr Equity Growth Rates are much more favorable. The Same logic applies to the Rule #1 PE, as in the case of SBUX, the default value is 8, even though the Current PE is actually 32! You should also always look at the Current EPS (TTM), and verify the validity of that value. It is rare but still all too common for the Source Data (In this case MSN Money) to be incorrect. The best way to verify the validity of this number is to compare it with an alternative data source such as Morningstar, ADVFN, etc.

In any case, you'll see an additional set of boxes shaded in blue labled "Scenario #2 Margin of Safety". This feature will allow you to override the conservative values chosen by default, with new numbers of your own choosing. This will help ensure you are getting the most out of the tool.

Use this as a preliminary search tool, and please check and recheck the data! With all the time this spreadsheet saves, you can afford to invest the extra 5 or 10 minutes required to do so. And in those cases where you find an anomolous piece of data do some research. Go beyond the Numbers and find out what went wrong that year. Sometimes you'll find that the most conservative value, isn't necessarily the most appropriate value to use.

This spreadsheet can be copied and redistributed FREELY, so long as it remains completely UNMODIFIED from the original document.

Thanks and have fun!

Justin Brand

admin@roicommunity.com
http://www.roicommunity.com

[supra082]

Thanks a bunch... I'm just curious... what is the exact formula used in the spread sheet to come to MOS?

[tombrown1]

Fixed it! Thanks for the update. No more problems.

Tom

[Justin]

Hi Supra, if you are new to this spreadsheet and would like a brief overview of how it works please visit this page.

You can view the forumula used for any individual cell from any tab on the spreadsheet within excel, however they can be a bit overwhelming. The spreadsheet uses many forumulas, each contingent on the results of another, each contingent on the results of another, etc etc.

[supra082]

Justin, thanks for directing me to that info, it sorted things out a lot! Your website is an awesome resource. I consider myself lucky to have stumbled across this by chance!

I was just wondering... why do you provide this tool for free?

[Justin]

Hey supra, glad that information helped shed some light.

The Excel Spreadsheets are free because they are built and tested and maintained by members of this community. I'll tell ya this much I'm no Excel geek. Releasing the spreadsheet allows us to borrow from the skill and expertise of our 3000 members, each with neat little "tips and tricks" up their sleeve that can make an Excel spreadsheet look pretty darn impressive!

[golfz]

Originally Posted by Justin

Hey supra, glad that information helped shed some light.

The Excel Spreadsheets are free because they are built and tested and maintained by members of this community. I'll tell ya this much I'm no Excel geek. Releasing the spreadsheet allows us to borrow from the skill and expertise of our 3000 members, each with neat little "tips and tricks" up their sleeve that can make an Excel spreadsheet look pretty darn impressive!

The auto_msn303a.xls has truly taken on a life of it's own. This project is the culmination of nearly a dozen individuals who have helped nurture it into what it is today.

Justin, what is the status (availability) of the 303 calculator vis a vis corrections for problems that have arisen with Business Week moving and making unavailable certain data and the other problems as posted on this BBS recently?


TIA

[duke]

Justin,
I tried the new calculator but it would not allow placing stocks in the watch list, it said the list was full, however the list is empty. No stocks were in the downloaded list like they have been on previous downloads. Any ideas?
Duke

[Justin]

Golfz - Once BusinessWeek's charts ceased to work we switched over to stockcharts.com for technical data.

Duke - Try adding the ticker symbol directly into cell B2 of the watch list tab, and then press the update button in the upper left corner of cell A1 also on the watch list tab. This seems to work for me, let us know your results!

[duke]

Justin,
Thanks, that seems to do the trick.
Duke