Tutorial - Basic Spreadsheet Functions

[Justin]

Whenever we see a valuation that seems to be way out of whack, our first suspect is always the data used to arrive at that calculation. With such a barrage of data, the spreadsheets can be a little overwhelming at times. For the sake of this tutorial, we won’t be concering ourselves too much with the area’s blurred out. For the most part, these values are not relevant to the most common problems. It’s comforting to know that the answer to the most common yet unavoidable problems are usually found in one of three places:

The Rule #1 Growth Rate - Cell H18
The Current EPS (TTM) - Cell H19
The Rule # PE - Cell H24

These three variables have the most impact on the resulting Margin of Safety Valuation. They are also the most prone to reporting strange or inaccurate values. This is because the spreadsheet is designed to always use the most conservative figure based on the raw data it has been given from the data site used.

Taking the example of WFMI from above, we see that the spreadsheet reports a margin of safety of $0.05 by default. This suggests that there was an error in calculation somewhere, so we next want to take a look at those three important variables as they are often responsible for this type of problem.

In Cell H18, we see one of the culprits – The Rule #1 Growth Rate the spreadsheet uses by default is 0.1%. Now, since cell H18 (the Rule #1 Growth Rate) is designed to use the lesser of 2 values (the Analysts Predicted Growth Rate, OR, our own prediction based on past EQUITY) we can track this first problem down pretty easy. We take a look over at the table on the left for the past 9/5/3/1 year EQUITY Growth Rates and see that theres a big fat zero for the TTM. Aha! The Analyst’s Predicted Growth Rate was trumped by the Equity Growth for the TTM.

Now that we’ve tracked down at least part of the problem and look into WMFI’s Equity Growth Rate a little deeper. We minimize the spreadsheet and start digging. This means:

Checking other data sites to verify the initial data was accurate to begin with. Yep this happens!

Digging up the conference call transcripts (Google/Reuters/Seeking Alpha are all great resources – Just be sure to report your findings here!!)

Making an executive decision as to whether or not you think its safe to override the spreadsheet’s default value with a new number of your own choosing, thus having a drastic impact on the resulting MOS Valuation.

Let’s say that after our research, we decide that we have no reason to think that WFMI won't sustain a rate of growth at around 20%. In cell I18, we can manually plug in that value of 20% as seen in the picture. By pressing enter, a new valuation pops up in cell E3. This cell is your Margin of Safety derived from the values you have manually entered. We aren’t done yet though, lets take a look at Cells H19 and H24 and make sure that they don’t seem out of whack too.

In cell H19, we see that the data provided for Current EPS (TTM) reads as $1.40. Ok fair enough – but what if, in our research we see that the Earnings were restated and the Data site just hadn’t made the update yet. Yep – this happens too. Yet another reason why we can’t stress – Research…Research…Research!! How about finding out that WFMI’s actual EPS for the trailing twelve was only $1.05. Yikes – now here we are again. Luckily, we’ve done a lot of research and find out that WFMI’s Earnings were indeed $1.05, and not the $1.40 as MSN reported. We plug that into our “Input Value” in Cell I19, and again our MOS has been affected. Pretty dramatically too, remember a variance in EPS by only a few cents can have a drastic effect on the resulting MOS.

You guessed it, we’re not done just yet. We’ve still got to take a quick glance at the Rule #1 PE that the spreadsheet is using. In this case, Cell H24 is causing the spreadsheet to calculate the Margin of Safety with a PE of Zero…the final piece to this little puzzle of ours. Again, we find ourselves shovel in hand. Digging up just what the heck PE we should be using for this calculation. Lets say in our research, we feel justified in declaring a Rule #1 PE of 40. We just plug 40 into our provided input value in cell I24 and we’re done!

Now think about what you’ve just done here – in a matter of a few minutes you’ve calculated an appropriate Margin of Safety price based on historical fundamental data. In what would normally take hours and even days, the ability to test “situation specific” valuations is yours with just a few entries. You can “approximate” best and worst case scenarios with a great deal of “accuracy”. (We challenge you to use approximate and accurate in the same sentence any other way and still make sense haha!)

Provided you have done adequate research to verify that the data isn’t flawed, you can start looking even deeper into the company’s profile. Use the supplemental data on the spreadsheets to provide you with additional valuable information. Although we’ve tried to make this Basic Tutorial as condensed as possible, we’ve only scratched the surface! Sometimes it’s easy to underestimate just how incredibly versatile these spreadsheets are!

More to come..

[Justin]

FAQ & Other Notes:

PROBLEM - BAD DATA SHOWS UP EACH TIME I ENTER A NEW SYMBOL

Just to the right of the Stock Symbol on the Rule1 tab is a cell that will come up with either "Successful" or "Bad Data." Sometimes when you first start Excel and enter a symbol, it will show "Bad Data." If this happens, go to the end of Column B in the Watchlist tab and enter a symbol, then hit the Update button. Go back to the Rule1 tab and it should show as "Successful." After this, you should be able to continue entering symbols into the Rule1 tab "successfully."

Also, You might try setting your Macro Security to LOW. You can do this by going to Tools >> Options >> Security >> Macro Security - Low.