- FERST spreadsheet ranks and screens U.S. Dividend Champions based on their fundamentals.
- Rating scales and weighting factors are adjustable to reflect the user's own preferences and investment approach.
- Color-coded table visually highlights "good" vs "bad" parameters of each company.
- Downloading latest price data keeps rankings up-to-date.
In late August, Dennis Dugan published two articles (here and here) explaining a system he developed to pick stocks for DGI portfolios. Rather than screening a list of stocks based on absolute pass/fail limits, Dennis uses a formula (which he added to David Fish's U.S. Dividend Champions spreadsheet) to rank companies according to a weighted scale based on certain fundamentals (such as market cap, dividend growth, payout ratio, and EPS growth). This allows him to select companies with the best overall balance of these fundamentals, without excluding "good" companies that might fail a simple screen (by just barely missing one of the screening criteria).
Dennis' articles caught my attention, because I've used a similar method (a weighted rating scale, added to the U.S. Dividend Champions spreadsheet) for my own stock selection. Since my spreadsheet uses flexible (easily adjustable) ratings scales and weighting factors, it can be easily configured to mimic Dennis' rating system. This may provide a simpler alternative for SA readers (than manually recreating Dennis' rating formula). Furthermore, users can easily change the rating system to reflect their own preferences and investment approach (without editing any formulas). I call my spreadsheet the Dividend Stocks FERST (Flexible Evaluation, Ranking, and Screening Tool) spreadsheet.
How It Works
Starting with the "All CCC" sheet of the U.S. Dividend Champions spreadsheet (published here), I added formulas to assign a "score" (between zero and 10) for each of various parameters (such as P/E ratio, dividend yield, EPS growth, etc.) for each company. Users set the rating scale for each parameter by specifying a "poor" (red, undesirable) value, an "average" (yellow) value, and an "excellent" (green, outstanding) value.
A separate "score" is assigned for each of the tracked fundamental parameters of each company. Then a "Final Score" is calculated for each company. This is a weighted average of all the individual scores for that company's various parameters, using weighting factors which are also user-adjustable. Thus, stocks with the highest Final Score have the best overall balance of fundamentals (according to the chosen rating scales and weightings). The entire table can be sorted by Final Score, to rank all the individual stocks.
The table below shows rating scales and weightings which mimic Mr. Dugan's rating system. The parameters involved are shaded purple for this illustration.
The minimum and maximum rating scale values correspond (as much as possible) with the Dugan system. For example, next year's estimated EPS growth earns a zero score if 5% (or below) and a maximum score if 20% or above. The weighting factors give the same relative weight to each parameter as in the Dugan system (1 for next year's EPS growth, 3 for the 5-year EPS growth rate, etc.). Other parameters (that are not used in the Dugan ranking method) are given a weight of zero, which excludes that parameter from the ranking calculations.
Using the FERST spreadsheet, then, a user can essentially set up and run the Dugan rating system (by entering appropriate values in the rating scale table) without entering or editing any formulas.
In the interest of full disclosure, note that this configuration mimics the Dugan system, but does not exactly duplicate it. Ratings in the FERST spreadsheet are infinitely variable, and are calculated using linear interpolation between the chosen (poor, average, and excellent) rating scale values. The Dugan system uses integer rating values, and in some cases uses a non-linear scale. Therefore, final results will vary slightly.
After trying the FERST spreadsheet (to mimic the Dugan system), I'd encourage users to adjust the ratings scales and weightings according to their own preferences. In this way, the spreadsheet can be easily customized, to find companies with the best balance of the specific parameters that are important to each user.
In addition to the scoring / ranking function, the FERST spreadsheet includes a "color table" which displays the actual values of each company's tracked parameters, shading them with a continuous red / yellow / green color scale, depending on their value. This makes it easy to spot the "good" and "bad" values for each company, and to visually scan for companies that are "mostly green":
[In the above screenshot, the entire list of stocks has already been sorted by Final Score (high to low) per the Dugan-esque ratings, although that column is not visible in the picture.]
Clicking the red "Download Prices" button downloads Last Price data for the entire table. This also updates any parameters and scores that depend on price (such as P/E ratio, dividend yield, etc.). Clicking the green "Sort" button then automatically re-sorts the entire table (by Final Score in descending order). The table can also be screened (to display, for example, only stocks with P/E < 20, dividend yield > 2%, payout ratio < 60%, and Final Score > 75) by entering the desired screening limits in the purple cells along the top, and then clicking on the purple "Screen" button.
The FERST spreadsheet also features:
- A Dividend Discount Model calculation for each company (again, with user-adjustable parameters). This calculates fair valuation based on the present value of the expected future dividends, and then calculates (and assigns a score based on) the percent by which the current price is over or under the DDM valuation.
- An Average Future Payout Ratio calculation (based on predicted earnings growth versus historic dividend growth), so you can see whether continued dividend growth (at historic rates) may be unsustainable.
- Printable summary sheets (in two formats) showing the key fundamentals for user-selected ticker symbols. The summary sheets can also optionally flag (using red and green shading) values which are above or below user-specified limits (and these flagging limits may be set differently from the rating scale limits, if desired).
The FERST spreadsheet (pre-loaded with data from the 8/31/15 edition of the U.S. Dividend Champions) can be downloaded via Dropbox here. You do not need a Dropbox account to access and download the file. If you encounter a "Sign Up / Sign In" window, you can simply close that window or (when downloading) click "No thanks, continue to download." This file is provided free of charge. It includes instructions, and is protected to avoid inadvertent alterations. However, all formulas and macros are open and visible, and the sheets may be easily unprotected if desired.
A Few Caveats
- The file is in macro-enabled Excel 2007 format (an .XLSM file). The color scale feature was not available in earlier versions of Excel, therefore the file will have limited functionality if converted and used with earlier versions.
- Warning! This is essentially a beta test. I have not previously published this spreadsheet; therefore it may still contain bugs, or may not function properly in some operating environments. I believe it will be useful / helpful for many. But if you encounter a problem, please let me know. Comments / issues / questions / suggestions may be directed to DividendStocksFERST@gmail.com.
- Do not rely solely on the rankings provided by the FERST tool to make investment decisions! Verify the figures and do your own due diligence before making any investment!
- The default rating scales and weightings (that exist in the FERST spreadsheet as provided) are not purported to produce optimal (or even beneficial) ranking results. Users should adjust the rating scales and weightings to reflect their own personal opinions and investment approach.
I will try to answer comments / emails, and post updated versions of the FERST spreadsheet as they become available. However, I am only one person (with a day job), so responses may be delayed (or nonexistent).
I hope the FERST spreadsheet becomes a helpful addition to your investing toolbox, and that God will give you wisdom in your investment decisions!
Disclosure: I am/we are long GLW, HMN, HP, JPM, LHO, MGA, RS, SSI, VLO.