As I have mentioned before, my entire financial existence is contained in two massive spreadsheets. From these spreadsheets, I have extracted the portion that I use for pre-screening dividend stocks. It is linked on the tools page as D4L-PreScreen.xls. The file contains the following tabs:
- Screen: Is where you enter information about a stock and a recommendation is generated.
- Database: Over time I keep "rediscovering" the same stocks. The database tab allows me to keep up with what stocks I have previously screened, the results of the screen and when the stock is eligible to be screened again.
I. Input: All cells requiring your input are shaded yellow. They include:
- Symbol: Enter the stock's symbol here.
- Year: Enter the last year in which annual dividend data is available.
- Current Yield: Enter the stock's dividend yield.
- Calc. Div. Growth: This field calculates the stock's dividend growth [NOT AN INPUT]
- MMA Yield: Enter what you can earn on a money market account.
- Max Div. Growth: Enter here the cap (maximum) for Calc. Div. Growth.
- Override Div. Gro: Enter here an override rate for Calc. Div. Growth.
- Annual Dividend/Share: Enter here historic annual dividend information.
III. Interpretative Analysis: Calculates several relevant pieces of information and allows you to set a minimum threshold on certain items. The items calculated are:
- NPV of MMA Differential: This is the net present value of the MMA Differential calculated in the projected information section above. You can enter the minimum acceptable level in column C.
- Sum of MMA Differential: This is a simple sum of the annual values calculated in the projected information section above.
- Metrics 1-5: Are specifically defined within the worksheet. Metrics 2 and 3 allow you to enter the minimum acceptable level in column C.
V. Disclaimer: Too many attorneys with not enough work for my liking....
The Database tab allows you to keep up with what stocks have been pre-screened along with the results of the of the screening and the recommended year for the next screening. I have it divided into three sections:
- Stocks To Consider (green): These are stocks that you currently own or would consider buying in the future.
- Reconsider Later (gray): These are stocks that have previously failed the pre-screen. They are listed here to let you know that they have been screened before and when they are due to be screened again.
- Never Consider (red): If you are opposed to stock and know you will never consider owning it, listing it in this section ensures it will always be rejected.
- The stock symbol (column A): The ticker symbol is used to look up information that is stored on the Database tab, but is displayed on the Screen tab.
- Flag (column B): A "X" in this column will flag the stock as rejected and display the comment on the Screen tab in cell D7.
- Comment: Whatever you want to say about the stock. As noted above, the comment is displayed on the Screen tab in cell D7 when there is an "X" in the Flag field.
In Part III tomorrow, I will walk you through several examples and point out things to look for.