QIK_SSG.xls
By Richard Beaubien
Email RichBeau@tiac.net

The SSG I have been looking for does no more than official 
sheet. The very first SSG I did with pencil, paper, and 
calculator took me four hours. The next weekend I was able 
to crank out an SSG every twenty to forty minutes. After 
that experience I understood the methods. I also understood 
that there were a lot of ways to screw up the calculations 
and data entry. Once I got started calculating SSG's I 
realized that I needed software to help minimize the errors 
and to speed up the process. Looking at ads in Better 
Investing I realized that the expense for the software was 
currently beyond my capital resources.

This Excel file is based upon the NAIC Stock Study Guide 
sheet. My goal here is to get a 'simple' to do SSG. Most of 
the spreadsheets I have obtained cram in too much data 
thereby making them to complex.

I use Value Line to obtain my data. So the labels of 
the data entry cells reflect my bias. S&P sheets probably 
work but I haven't tried it. Also, to calculate financial 
companies, you'll have to work out your own revenues/sales 
numbers.  Maybe later I can figure out a method that is 
reasonable. I have a dozen or so various financial company 
VL's and the terminology varies. I would like to have a 
consistent method in keeping with the 'simple' theme.

The sheets and workbook are protected (no password) mainly 
to keep me from destroying my own handy work. It will also 
keep those with less experience from destroying the 
formulas and formatting.

There are 4 methods for calculating the Historical growth 
and Future growth trend lines. There is a question asked to 
determine which one of four methods to use to calculate 
your growth rates. You'll see a number appear on SSG pg1 
under the Future Growth rates to tell you which method is 
being used.

The first is the old fashion Preferred Method. The 
sales/revenues, number of shares (both preferred and 
common), and tax rate comes directly off the Value Line 
sheet 5 year projected values. I back calculate the years 
between the future and today. The Percentage Pre-Tax Profit 
number is a weighted average of the last five years. I 
think that this is a conservative way to calculate this 
figure. Those of you that have more Excel savvy can 
unprotect the file and adjust the various items anyway you 
see fit.

The Exponential Growth method uses the Growth function 
inherent in Excel to fit an exponential curve to the known 
data. I use this calculation to project the EPS and Sales 
numbers five years out. Again, I back calculate the years 
between the future and today. This method requires positive
numbers in the data entire series otherwise it craps out 
(the #NUM! error is a good indicator).

The Two-Point Averaging method, basically takes the first 
half of the total number of data years and figures an 
average, does the same for the most recent half and then, 
by drawing an imaginary trend line through the two averaged 
points, projects the future. A kind of area method, in 
statistical terms. I think this may be the most common 
method and probably the SSG toolkit uses something similar.

The Straight Line Fit method (aka, least squares method) 
uses the Linest function inherent in Excel to calculate a 
straight line that best fits the all the data. I use this 
calculation to project the EPS and Sales numbers five years 
out. Again, I back calculate the years between the future 
and today. It seems to be the most conservative. The trend 
lines tend to flatten out.

Negative data tends to skew the whole system and I really 
haven't got a perfect fix (does this sound like a familiar 
problem?). I'm assuming that the most recent years will have 
positive numbers (though they all work with negative 
numbers, except the Exponential Growth method). In order 
for the various methods to work properly I need positive 
numbers in the first year. So I have inserted a series of 
checks to see if the first year data is less than or equal 
to zero. If it is, I drop that year. I continue testing 
each year until I either get good data or I run into the 
current year minus 4 where upon I will sent an "Insufficient 
Data" error (remember, according to the NAIC we need at 
least five years of data to make an SSG worth while). If I 
have a number greater than zero than I calculate based upon 
that number of years (you'll see a number appear on SSG pg1 
next to the Historical Growth rates to tell you how many 
years). Except for the Exponential Growth method, which 
requires positive numbers in the entire data series, it 
appears that all the other methods work. Finally, Excel 
cannot plot negative numbers on the graph You should be 
able ignore it and the have plots come out correctly it's 
just an annoyance.

There is also a question to help determine which of the 4 
methods to use for the 5 year low price. Note that there's 
an indicator to tell you which one you used. It will plug 
in the correct number into the section C on Zoning.

A word of warning to those who are just starting out. 
Despite arcane and convoluted discussions about all the 
facts and figures contained in an SSG, there is no other 
way to understand the Stock Study Guide other then by 
sitting down with pencil, paper, calculator, and the NAIC 
manual (I also suggest Doug Gerlach's excellent SSG 
tutorial at his web site, Invest-o-Rama). Once you have 
done a number of SSG's and referred to the manual countless 
times this will become clearer. Again, please learn what 
this SSG does before using it. To those who are more 
expeienced, remember this is just a tool it is only as good
as you use it. 

I think I got the printing down well enough to say that if 
you just hit the print button that the current page 
(including the data sheet, albeit in small font) will print 
out correctly. I am able to print the first sheet and then 
flip it over and then print page two. Now I'm able to put 
it in a three hole punch and save them.

I want to thank everyone on the NAIC email list that helped 
out with the testing. Your diligence and suggestions got me
off my duff to fix it right. Thanks again!

Of course the usual disclaimers apply.  I am not responsible 
for anything at all.  Nothing.

Hope no one minds my little sig at the bottom of the second 
page :-)

Good Luck!
Rich