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

[poisontee]

Originally Posted by petekoch

I keep reminding myself NOT to save the spreadsheet when I'm finished using it, but, invariably, I'll save it, usually just after entering a stock with incomplete information. Sometimes, this corrupts some of the formulas in the MAIN worksheet.

If you don't know your way around spreadsheets all that well, here's a suggestion:

On the Main worksheet, SELECT Cells A1 - P67.
From the MAIN menu, Format -> Cells -> Protection Tab
Click 'Locked', then OK
Now, select Cells D1 and L2-L4
From the MAIN menu, Format -> Cells -> Protection Tab
Click 'Locked' off, then OK
From the MAIN menu, Tools -> Protection -> Protect Sheet -> OK

What this does is protect the entire worksheet from getting stepped on,
except for the 4 cells intended for data entry (D1 and L2, L3 and L4).

Since I've gotten pretty familiar with the entire spreadsheet, I have also elected to hide all of the worksheets except for UserNotes and Main.

Try it. It works well for me. I haven't screwed anything up in days....

That will solve the problem. Alternatively, if one doesn't wanna gp through all that, they can download anaother fresh file from this website on the download section and then forget about the old file.

Or petekoch can send his already upgraded file to members. Thank you in advance!

[poisontee]

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_calc105.xls

Thanks for all of the suggestions and bug fixes folks!

Justin

Thank you Justin. You are such a great person in this community.

[jminderlein]

Hi, Im trying to find out to add symbols to a watch list once you've retrieved the MOS data.....used to be able to do that on the original spreadsheet...not sure if it's possible with the new version...?

many thanks!

[Justin]

Hi jm,

Unfortunately, the watchlist included with the older versions of the calculator caused a great deal of strife in keeping the spreadsheet accurate and up to date. We were having to make corrections to the spreadsheet almost every week in order to compensate.

We may be able to release a new watchlist at some point down the road, although it will likely be a separate tool.

Thanks,

Justin

[bushido]

hi, does the MOS calculator work with Canadian stocks? I tried a couple of different ways but no luck.
thanks

[jes_bad]

One can also save the spreadsheet as a Template (.xlt) in Excel. File > Save As > and select Template in the Save as Type drop down list at the bottom. This way, when you double click the Template file, it will open a regular Excel file (.xls) that you can save without affecting the template.

Thanks for the spreadsheet!



-Jes

[gmac1122]

I am a little confused on some of the company results that the Spreadsheet returns. For example. If I put in BUD(Anheuser-Busch) The current stock price is $52 but the MOS is $1.70 and the Sticker is $3.40 and Future price is only $13. How can a stock have a Future price below current price? The numbers are horrible for a company that has been around forever and you would think that the Asian market like China would propel this forward many times. I went to China last yr and this is the only beer anyone drinks there.

Does the spreadsheet calculate correctly every time? It is super cool and I love it but I am wondering based on my calcs from Morningstar data if I can trust MSN data. Where does the data come from MSN -- Does MSN have an API or a Web service to grab this data?

Thanks

[rharmelink]

>> How can a stock have a Future price below current price?

It would indicate the stock may be currently overpriced and not a bargain.

I think you need to reread parts of the book if you don't understand that?

>> The numbers are horrible for a company that has been around forever
>> and you would think that the Asian market like China would propel this
>> forward many times. I went to China last yr and this is the only beer
>> anyone drinks there.

You can override the factors if you feel your information is more reasonable.

When I go to AdvFN and get annual data back to 1993, a least squares regression on the book value per share is almost flat and on the total equity of the company is actually negative.

For my analysis, I would go with the analyst projections of 9% growth and use the historical P/E of 21. But that still puts the MOS price significantly below the current price.

>> Does the spreadsheet calculate correctly every time?

Obviously not. It is only a tool and is only generating an estimate based on the inputs it is given. GIGO = "Garbage In, Garbage Out". You have to decide if the historical attributes of the company are usable for estimates of projected growth.

>> It is super cool and I love it but I am wondering based on my calcs from
>> Morningstar data if I can trust MSN data. Where does the data come
>> from MSN -- Does MSN have an API or a Web service to grab this data?

The process uses EXCEL Web Queries to grab the information from the MSN web site. The calculator uses the most conservative values for its estimates of the factors. Again, you have to decide if you don't want that safety or disagree with those choices.

[piazzi]

Hi,

Can someone tell me which part of the spreadsheet code I have to modify in order to get Canadian prices show properly? MSN reports some of the numbers with an annoying CAD after the price.

Thanks

piazzi

[lee810]

Originally Posted by gmac1122

I am a little confused on some of the company results that the Spreadsheet returns. For example. If I put in BUD(Anheuser-Busch) The current stock price is $52 but the MOS is $1.70 and the Sticker is $3.40 and Future price is only $13. How can a stock have a Future price below current price? The numbers are horrible for a company that has been around forever and you would think that the Asian market like China would propel this forward many times. I went to China last yr and this is the only beer anyone drinks there.

Does the spreadsheet calculate correctly every time? It is super cool and I love it but I am wondering based on my calcs from Morningstar data if I can trust MSN data. Where does the data come from MSN -- Does MSN have an API or a Web service to grab this data?

Thanks

The real irony of this is that BUD is a stock that Warren Buffet actually owns and it's a relatively recent addition to BRK.A. In fact he's the single largest investor in BUD with 8.6% of its shares, although he has reduced his position a little last year. Part of the reason this stock's MOS price is so low is that Rule#1 ignores dividends, and I used to ignore them as well, until I read Jeremy Siegel's excellent book, 'The Future for Investors' which takes into consideration the gains that accompany reinvested dividends and they make quite a substantial difference over time. Granted, no company pays big dividends, but even a few % over time can add up and BUD's dividends have been fairly consistent. When a company cannot grow as fast as it can generate capital, it must (and should) return that money to the shareholders as dividends.

I visited a Budweiser brewery in Colorado a few months ago and was impressed by how well managed it was and how generous they were about providing tours (free tours every hour of the day, complete with a visit to the Clydesdales impeccable stables and with free beer after the tour ).

The amount of beer they make is absolutely mind boggling. With BUD already having 50% of the U.S. market, it's pretty obvious that Buffett wasn't thinking about domestic growth, but rather if BUD will do in other countries what they did so successfully in the U.S. back in the 60's and 70's when every town had its own local brewery. I looked at the pictorial history during the tour and found that during the 60's and 70's BUD was opening up a new super brewery every two years for at least 15 years in a row. Now that the domestic market is flat and additional market share would require lowering prices, it's possible that they've set their sights on developing countries as the next big growth opportunity. After all, who doesn't appreciate fine beechwood-aged brew no matter where on earth they may live?

Whenever I see inexplicably low numbers for the MOS, I look at the Rule#1 growth rate and Rule#1 PE and those numbers in BUD's case are 2% and 4 repectively, which is why the MOS price comes out so low. If you plug more believable numbers in the orange column of the spreadsheet called 'scenario #2', like 9% and 20, respectively, you'll get an MOS around $15 which is about a third its current price. This means it's trading over sticker price. But who knows what tricks they may have up their sleeves to grow their business? The stock has outperformed the S&P over the past 20 years, although for the past 5 years it has been flat. I wish I had bought some of it a year ago; it's up nearly 20%.

-Lee