Stock Analysis Spreadsheet
Stock Analysis Spreadsheet
In an effort to help properly diversify my portfolio I've created a stock analysis spreadsheet predominately based on stocks found in Morningstar with 4 or 5 star ratings. An initial search was implemented looking for 4 or 5 star stocks and about 350 stocks were returned. I went through the list and picked out about 65 of them that I either recognized or had been studying in recent weeks. I needed to know how any of these stocks might fit into my portfolio which is why I created the spreadsheet. You can view the spreadsheet by clicking on the link at the beginning of this post however a bit of explanation may prove useful in helping to use the spreadsheet to its fullest potential.
Stock Matrix
At the top of the spreadsheet is a stock matrix with the stock style going across the top and the stock type along the side. The stock style refers to whether a stock is Large, Medium or Small Cap and then each market cap size is further subdivided into Value, Core or Growth.
The stock type defines the stocks as either High Yield, Distressed, Hard Assets, Cyclical, Slow Growth, Classic Growth, Aggressive Growth or Speculative Growth.
Within the matrix are various stock symbols in their proper position and, as such, one may easily identify a stocks style and type. To learn more about a company of interest or to just learn what a particular symbol represents you can simply scroll down the column the symbol is in until you run into that symbol again. Then, if you look to the left you'll see the name of the company and you may also notice the symbol you find to be in blue or bold faced, in italics or have a box around it. These indicators give additional information concerning the stock in question.
Additional Stock Screening
In addition to screening for stocks with 4 or more stars I then took the results of that screening and subjected the results to additional screenings which include three screens which look at the companies financials and one screen which looks how the stock has done compared to the S&P 500. These screens are described in greater detail below.
Terrific 10 Year Records - Blue
Not many companies can boost sales every year for 10 years and maintain consistently positive earnings. This screen looks for stocks that have increased their revenues every year for the past 10 years and have had earnings per share greater than zero for the past 10 years. Distressed stocks are excluded -- 347 stocks pass this screen. When we limit the 347 to only stocks with 4 or more stars then only 48 stocks pass this test. These companies are highlighted in blue.
Wealth Creators - BOLD
The key to creating wealth for shareholders is maintaining healthy returns on capital. Here we screen for companies that have maintained returns on equity of at least 15% in each of the past five years, and which have had positive ROEs for the past 10 years -- 143 stocks pass this test. When subjected to the 4+ star requirement we are left with 33 stocks. These companies have their stock symbol in bold.
Cash Cows - bordered cells
If cash is king, these companies wear crowns. The companies that pass this screen have generated growing free cash flows for the past three years, and boast cash on the balance sheet equal to at least 5% of total assets. Plus, for every dollar they generate in sales, these companies have generated at least $0.05 per share in free cash flow in each of the past three years.
I'm probably not sophisticated enough to fully appreciate what a generous cash flow means in terms of stock picking but, Benjamin Graham (Warren Buffett's teacher) liked companies with a lot of cash and even Buffett is purported to like companies with positive free cash flows and this screen would certainly fit the bill. 177 stocks pass this screen but only 23 once we add the 4+ star requirement.
Stock Beats the S&P - italics
We all know that past performance doesn't necessarily indicate future results but, isn't it nice to at least consider stocks that have beat the S&P 500 over the last 3, 5 and 10 years?
A whopping 1,370 stocks have beat the S&P 500 over the last 3, 5 and 10 years but, only 76 of them are considered good values now by virtue of being rated with 4 or 5 stars.
incidentally - there are only 15 companies with 5 star ratings that have beat the S&P for the last 3, 5 and 10 years ...they are Alleghany, Apache, Berkshire Hathaway B, Boyd Gaming, Centex,
Cheniere Energy, Devon Energy, Fastenal, First American, Legg Mason, MSC Industrial Direct Co., Newfield Exploration, Novartis AG ADR, Urban Outfitters & Whole Foods Market.
MorningStar Rating - 4* or 5*
The more stars a stock has the greater the value according to MorningStar (MS). MS analysts attempt to quantitatively determine a fair value estimate for each stock as well as a price to consider buy and a price to consider selling. Let's use DELL as an example.
DELL
Morningstar Rating 5*
Consider Buying $30.10
Fair Value Estimate $39.00
Consider Selling $48.90
DELL is a Large Core type of company with a Classic Growth style which closed today at $24.62 which is lower than the Consider Buying price cited by MS which tends to make for an attractive entry price. Perhaps a downside is the fact that MS gave DELL a Stewardship Grade of D. The Stewardship Grade refers both to the quality of management and how well managements goals are in line with the shareholders.
If available, to the left of each stock symbol will be the MS star rating.
Now, as a stocks price goes up then stars are removed - by the time it current price is around it's Fair Value Estimate the number of stars would drop to 3. When a stock gets to one star then MS suggest one may consider selling.
These star ratings are dynamic. About a month ago there were about 126 five star stocks and, having just checked, that number is now down to 99. Case in point - this morning I placed an order which included buying EXPD which, at the time, was rated with 5 stars but since the stock went up over 3% today it's now a 4 star stock.
It's nice to see Blue Bold faced stocks in italics and from the current list of 67 stocks only two of them qualify - Walgreens (WAG) and Student Loan (STU)
WAG was rated five stars during the months of April, May and most all of June when it's price was at or below $43/share then enjoyed a nice run to a high of $51/share - a nice 18+% return over a few months time until Wal-Mart scared some investors with their $4 prescription plan.
Student Loan I've been tracking since 09/22/06 when it was $186.50 and was 5 stars until a few days ago. STU closed today at $206.75
In an effort to help properly diversify my portfolio I've created a stock analysis spreadsheet predominately based on stocks found in Morningstar with 4 or 5 star ratings. An initial search was implemented looking for 4 or 5 star stocks and about 350 stocks were returned. I went through the list and picked out about 65 of them that I either recognized or had been studying in recent weeks. I needed to know how any of these stocks might fit into my portfolio which is why I created the spreadsheet. You can view the spreadsheet by clicking on the link at the beginning of this post however a bit of explanation may prove useful in helping to use the spreadsheet to its fullest potential.
Stock Matrix
At the top of the spreadsheet is a stock matrix with the stock style going across the top and the stock type along the side. The stock style refers to whether a stock is Large, Medium or Small Cap and then each market cap size is further subdivided into Value, Core or Growth.
The stock type defines the stocks as either High Yield, Distressed, Hard Assets, Cyclical, Slow Growth, Classic Growth, Aggressive Growth or Speculative Growth.
Within the matrix are various stock symbols in their proper position and, as such, one may easily identify a stocks style and type. To learn more about a company of interest or to just learn what a particular symbol represents you can simply scroll down the column the symbol is in until you run into that symbol again. Then, if you look to the left you'll see the name of the company and you may also notice the symbol you find to be in blue or bold faced, in italics or have a box around it. These indicators give additional information concerning the stock in question.
Additional Stock Screening
In addition to screening for stocks with 4 or more stars I then took the results of that screening and subjected the results to additional screenings which include three screens which look at the companies financials and one screen which looks how the stock has done compared to the S&P 500. These screens are described in greater detail below.
Terrific 10 Year Records - Blue
Not many companies can boost sales every year for 10 years and maintain consistently positive earnings. This screen looks for stocks that have increased their revenues every year for the past 10 years and have had earnings per share greater than zero for the past 10 years. Distressed stocks are excluded -- 347 stocks pass this screen. When we limit the 347 to only stocks with 4 or more stars then only 48 stocks pass this test. These companies are highlighted in blue.
Wealth Creators - BOLD
The key to creating wealth for shareholders is maintaining healthy returns on capital. Here we screen for companies that have maintained returns on equity of at least 15% in each of the past five years, and which have had positive ROEs for the past 10 years -- 143 stocks pass this test. When subjected to the 4+ star requirement we are left with 33 stocks. These companies have their stock symbol in bold.
Cash Cows - bordered cells
If cash is king, these companies wear crowns. The companies that pass this screen have generated growing free cash flows for the past three years, and boast cash on the balance sheet equal to at least 5% of total assets. Plus, for every dollar they generate in sales, these companies have generated at least $0.05 per share in free cash flow in each of the past three years.
I'm probably not sophisticated enough to fully appreciate what a generous cash flow means in terms of stock picking but, Benjamin Graham (Warren Buffett's teacher) liked companies with a lot of cash and even Buffett is purported to like companies with positive free cash flows and this screen would certainly fit the bill. 177 stocks pass this screen but only 23 once we add the 4+ star requirement.
Stock Beats the S&P - italics
We all know that past performance doesn't necessarily indicate future results but, isn't it nice to at least consider stocks that have beat the S&P 500 over the last 3, 5 and 10 years?
A whopping 1,370 stocks have beat the S&P 500 over the last 3, 5 and 10 years but, only 76 of them are considered good values now by virtue of being rated with 4 or 5 stars.
incidentally - there are only 15 companies with 5 star ratings that have beat the S&P for the last 3, 5 and 10 years ...they are Alleghany, Apache, Berkshire Hathaway B, Boyd Gaming, Centex,
Cheniere Energy, Devon Energy, Fastenal, First American, Legg Mason, MSC Industrial Direct Co., Newfield Exploration, Novartis AG ADR, Urban Outfitters & Whole Foods Market.
MorningStar Rating - 4* or 5*
The more stars a stock has the greater the value according to MorningStar (MS). MS analysts attempt to quantitatively determine a fair value estimate for each stock as well as a price to consider buy and a price to consider selling. Let's use DELL as an example.
DELL
Morningstar Rating 5*
Consider Buying $30.10
Fair Value Estimate $39.00
Consider Selling $48.90
DELL is a Large Core type of company with a Classic Growth style which closed today at $24.62 which is lower than the Consider Buying price cited by MS which tends to make for an attractive entry price. Perhaps a downside is the fact that MS gave DELL a Stewardship Grade of D. The Stewardship Grade refers both to the quality of management and how well managements goals are in line with the shareholders.
If available, to the left of each stock symbol will be the MS star rating.
Now, as a stocks price goes up then stars are removed - by the time it current price is around it's Fair Value Estimate the number of stars would drop to 3. When a stock gets to one star then MS suggest one may consider selling.
These star ratings are dynamic. About a month ago there were about 126 five star stocks and, having just checked, that number is now down to 99. Case in point - this morning I placed an order which included buying EXPD which, at the time, was rated with 5 stars but since the stock went up over 3% today it's now a 4 star stock.
It's nice to see Blue Bold faced stocks in italics and from the current list of 67 stocks only two of them qualify - Walgreens (WAG) and Student Loan (STU)
WAG was rated five stars during the months of April, May and most all of June when it's price was at or below $43/share then enjoyed a nice run to a high of $51/share - a nice 18+% return over a few months time until Wal-Mart scared some investors with their $4 prescription plan.
Student Loan I've been tracking since 09/22/06 when it was $186.50 and was 5 stars until a few days ago. STU closed today at $206.75
posted by Scott at
3:18 AM
0 Comments:
Post a Comment
<< Home