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

[yostyid]

When you right click on a cell that is truncated (most cells on the page actually, H4:h9, k2:k9,l2:l9, even the 9,5,3,ttm growth #'s) select format cells. The number tab has a decimal field where you can decide how many places out you want to go. I don't know if you can do it on the whole sheet. It probably wouldn't be that difficult to move the cells out to 2 places. I don't think many people would need more than that.

[Rooster]

I still clear the temporary internet files folder between each new company.
C:\Documents and Settings\..yourusername...\Local Settings\Temporary Internet Files

After you select all and hit delete, you can click "no" to save your cookies. Everything else will be deleted. You may have to choose to reveal hidden folders to see all of these folders.

[KathiM]

The Sheet works on my Laptop (XP & Office 2003), so I guess the issue is related to vista and / or office 2007.

Is anyone using the calculator successfully with vista & office 2007?

KathiM

[Justin]

The latest version of this spreadsheet (Version 1.0.7, released 8/3/2007) is now available, and can be downloaded from the original post of this thread.

[markmjok]

Originally Posted by Justin

The latest edition of our Automatic Margin of Safety Calculator can be downloaded from this location:
http://www.roicommunity.com/uploaded/mos_calc107.xls (Updated 8/3/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

Jason,

In working with your latest version of the MOS Calculator, I've noticed two descrepencies (or errors, if you will):
When putting the cursor over over Cells J/K3, the dialog box reads: "The lowest (most conservative) Growth Rate reported in Cells I4 and I5." However, I4 and I5 are blank cells. Similarly, when putting the cursor over Cells J/K5, the dialog box reads: "The lowest (most conservative) Growth Rate reported in Cells I7, I8, and I9." However, I7 is a heading, TTM, and I8 and I9 are ROIC and Equity numbers respectively under the TTM heading. It seems that the cells are mis-aligned. Can you see what I'm saying? Thanks.

Mark

[markmjok]

Sorry, Justin, I referred to you as Jason by mistake (my nephew's name).

[Justin]

Ah, yes I believe so. These were "comments" that are hard coded into the spreadsheet. When I made the adjustments to the actual layout of the tables, the comments did not reflect the new cell locations. This does not change anything about how the spreadsheet works, but I'll get it fixed and uploaded right away.

Thanks for pointing that out,

Justin

[Justin]

Ok that's uploaded. Because this is such a minor adjustment, I didn't re-issue the version number. It can be downloaded at the top of the page.

Thanks for the bug and if you see anything else be sure to let me know!

Justin

[Jim Jones]

First let me say that the margin of safety spreadsheet is a very helpful tool. I use it all the time to analyze stocks. I want to thank Justin and everyone who has worked on it. I also have found it very easy to operate from a technical standpoint.
The spreadsheet calculates downloads and uses the raw data on a yearly basis. Could or should an additional spreadsheet be done to work on a quarterly basis? My thoughts are once the yearly big picture is obtained it might be worthwhile to look at quarterly activity. I recognize companies have quarters that are better than others due to time of the year or season. I am not sure if the spreadsheet should be adjusted for that. I realize that one could go to MSN.com and dig out that information but the download is so nice because it is such a time saver.
Thanks for your consideration and also thanks for such a nice product.

[rharmelink]

Originally Posted by Jim Jones

The spreadsheet calculates downloads and uses the raw data on a yearly basis. Could or should an additional spreadsheet be done to work on a quarterly basis? My thoughts are once the yearly big picture is obtained it might be worthwhile to look at quarterly activity.

I'm not sure how important quarterly information would be relative to a tool being used for a decade-long forecast? IMO, the consistency of the annual data would be more important. I'd rather see more effort put forward to measure the consistency and stability of the factors going into the Rule #1 forecasting.

Also, keep in mind that quarterly data can be:

-- unaudited
-- not restated when things change
-- have less details (some things only apply to annual)
-- inconsistent format from company to company (i.e. YTD vs QTD)