A number of people have asked for copies of the spreadsheet that I use to produce this report.
First, let me explain why I use the spreadsheet. I started out using CSA's software but soon found that I needed more flexibility when it came to assigning a growth rate. Specifically, I found that I often wanted to use different growth rates for each of the next five years. As a member of the NAIC, I was aware of Doug Gerlach and his and others spreadsheets. When I looked more closely at his spreadsheet and saw that it read data files, I realized that I had the best of all worlds - an application that was as flexible as my imagination and knowledge could make it fed by a cheap, high quality data source.
Rather than give the spreadsheet away, it seems to me to make more sense to show people how to construct the spreadsheet. I found that when I was constructing it that I got a better understanding of how key drivers such as earnings growth rate, PE and yield interact.
So here is a basic how to. You should have a basic understanding of Excel: how to perform arithmetic calculations, how to sum columns and rows, how to build if/then statements, etc. Or absent that knowledge, a hacking temperament when it comes to working with unfamiliar software.
Start with the basic spreadsheet and then download some sample data, saving it as GWO.ssg. Then open SSG-READER.xls and click the "Click Here to Import New *.SSG Datafile" button in the top left corner of the spreadsheet. Find the GWO.ssg file and open it. This will give you an idea of how fast and simple the basic data update is.
To create page 3 of the report, unprotect the worksheet, go down to the chart, left click on it to highlight it, right click on it, click location and choose new sheet. Now you have pages 3 and 4 of the report.
Page 2 is modeled on the page 2 of the Stock Selection Guide from the CSA . All of the raw data is either linked or derived from the core spreadsheet. Growth rate calculations require this Excel Add-In. I've enhanced page 2 quite a bit from the original Stock Selection Guide Form. Ignore my enhancements and work to replicate the original form. You can work on enhancements later as I did.
An alternate page 2 is modeled on page 2 of the Stock Selection Guide from the NAIC.
Once you get pages 2, 3, & 4 completed, we can talk about integrating page 1.
To feed this application, you need a subscription to the NAIC's Standard & Poor’s (S&P) Stock Data Service which covers some 7,000 stocks including 1,200 Canadian stocks. Stock Prospector is a very powerful screening tool that uses this database subscription, with a demo available here.
This takes care of the quantitative analysis. It's important but is only part of an initial complete examination of a company. You have to do a qualitative analysis as well in order to understand the company and to put the quantitative analysis in perspective. This guideline was modified from one developed by the Crow River Investment Club. You can find a couple of examples here and here. It is tempting to focus on the quantitative analysis because it is so quick and easy to do and because it gives an arithmetic indication of value. The ratio of time/effort spent on quantitative vs. qualitative analysis is about 20:80: it takes time to do a qualitative analysis. To do so is to risk loss. It's not necessary to do a quantitative analysis on an ongoing basis but it is necessary to track financial ratios over time to flag changes that need to be examined.