CI Market Dashboard

We have put together a collection of market indicators and track them to help you gauge the direction of the market.


CI Analysis Worksheets

Interactive analysis templates covering DuPont analysis (return on equity), valuing stocks the Warren Buffett way, and more to come.


CI Blog Feed

Mac OS X 10.10 Yosemite Available Today
posted 4 days ago by Wayne Thorp

Apple’s Third Quarter Tops Estimates
posted 4 days ago by Wayne Thorp

Nokia Hurts Microsoft’s Earnings
posted 4 days ago by Wayne Thorp

WSJ: Ukrainian Conflict Hurting Tech Sector
posted 5 days ago by Wayne Thorp

Report: Apple Readying Production of iPhone 6
posted 5 days ago by Wayne Thorp



Computerized Investing > Second Quarter 2012

Using Percent Rank in Excel

PRINT | | | | Add your comment! | A A   Reset

by CI Staff

In the Fourth Quarter 2011 issue of CI, I introduced Charles Carlson’s Advanced Big, Safe Dividend BSD screen that he uses to “find stocks with above-average appreciation and safe and growing dividends.” This approach combines 10 different factors, each of which is scored with a different weighting. In my article, I assigned subjective scores based on where a given value fit into the range of values for all of the companies being considered. At the time, I mentioned that it is possible to rank and score the 10 factors more precisely using Excel. For this installment of Spreadsheet Corner we return to Carlson’s Advanced BSD screen and use the percent rank function in Excel.

For a refresher on the Advanced BSD approach, refer to the Feature article in the Fourth Quarter 2011 issue of Computerized Investing; the article is available online at www.computerizedinvesting.com.

Percent Rank Explained

Percent rank (also referred to as percentile rank or percentage rank) shows you how a particular company “stacks up” to the competition. Percent rank fields are useful in comparing a company’s results in a certain area against those of the entire universe of companies. If you are looking at relative strength, for example, a company with a relative strength reading that places it in the 70th percentile has a relative strength figure that is higher than 69% of all the companies in the stock database.

For this article, we will use Excel’s percent rank function to place companies in percentiles based on the value of multiple factors.

Data Set & Universe

The 10 factors Carlson uses in his Advanced BSD screen (and their weightings in the final composite score) are as follows:

  • payout ratio (30%),
  • interest coverage (10%),
  • cash flow to net income (5%),
  • dividend yield (5%),
  • six-month relative strength (10%),
  • tangible change in one-year book value (10%),
  • long-term expected earnings per share growth (10%),
  • three-year cash flow growth (5%),
  • three-year dividend growth (10%), and
  • three-year earnings per share growth (5%).

For our initial analysis, we used AAII’s fundamental stock screening and research database program, Stock Investor Pro. The majority of these data points are already in the program; we created custom fields to calculate those data points that did not exist.

We limited ourselves to a universe of exchange-traded, dividend-paying stocks. Specifically, our initial screening consisted of the following parameters:

  • exchange-listed (stocks trading over the counter are excluded);
  • not in the miscellaneous financial services industry (primarily to eliminate closed-end funds, exchange-traded funds, etc.);
  • a payout ratio no higher than 60%; and
  • expected to pay a dividend over the next 12 months (have a positive indicated dividend).

As of February 24, 2012, there were 1,318 companies meeting these criteria.

We exported this universe, along with the 10 factors used in the Advanced BSD screen, from Stock Investor Pro into Excel for further manipulation.

Ranking on “Valid” Data

In the previous Advanced BSD article, a stock had to have a valid, non-null value in order to be considered. This severely limited the number of potential candidates. For this article, as long as a stock met the initial criteria outlined earlier, it was considered.

This, however, presents a dilemma: How do we treat “null” or missing values when performing our percentile ranking? Do we ignore them or treat them as a zero? A missing value and a zero are two very different things, and how they are treated can have a significant impact in financial and statistical analysis. Treating a null as a zero would alter the distribution of values and alter the outcome of the percentile ranking. A company with a null would receive a ranking value and, in this case, a score for that field. Therefore, the company’s final composite score would be boosted by data that, in effect, does not exist.

To solve this dilemma, we looked to Stock Investor Pro, which also has percentile ranking data. The calculations in the program ignore null values, and rankings are only based on “real” values. This is the methodology we followed for this article. Therefore, a company may still rank highly with its overall composite score, even if it does not receive a score for an individual factor.

Sorting for Non-Nulls

Before calculating the percentile ranks for the 10 Advanced BSD factors, we first sorted each column of data to group together the “valid” values for each factor. This range of values was then used to calculate the percentile rankings for each field.

Figure 1 is a partial screenshot of the Excel export file from Stock Investor Pro containing the 1,318 companies that passed our initial Advanced BSD screening as of February 24, 2012, and the data related to the 10 Advanced BSD factors. It shows how we performed a sort on the data in column C, cash flow from operations to net income data.

We began by selecting all of the data in the spreadsheet by using the Ctrl + A keys. Using Excel 2007, we next clicked on the Data tab at the top of the program window and selected Sort from the Sort & Filter panel. This opened the Sort dialogue box shown in Figure 1. We then clicked on the drop-down menu next to Sort By and selected CFO to NI 12m (note that at the top right of the dialogue box, the “My data has headers” box is checked). We wanted to sort on the values in this column, so we didn’t change the Sort On option. Lastly, to sort the values in the CFO to NO 12m column in descending order (largest to smallest), we clicked on the drop-down menu for Order and selected Largest to Smallest. To sort the data, we clicked OK. As we see in Figure 1, the export file is now sorted in descending order by cash

flow from operations to net income, with Aluminum Corp. of China at the top of the list. Of the 1,318 companies in the export file, 956 had valid cash flow to net income values.

Percent Rank

The next step was to place the data into percentiles using Excel’s percent rank function.

Figure 2 shows that we inserted a blank column (D)—labeled % Rank_CFO to NI 12m—to accommodate our percent rank calculations. In cell D2 we entered the following formula to calculate the percent rank:

=PERCENTRANK($C$2:$C$957,C2,2)

Where $C$2:$C$957 is the range of data we are analyzing (the 956 companies with valid CFO to NI values); C2 is the value for the particular cell we want compared against the data range; and 2 is the number of significant digits for the returned percentage value.

Upon hitting enter, the value 1.00 was returned. The percent rank function returns a decimal number. In percentage terms, 1.00 equates to 100% [to change this figure to a percentage, right-click on the cell, select Format Cells and choose Percentage from the Number tab]. We would expect Aluminum Corp. of China to be in the 100th percentile since it appears at the top after sorting the database in descending order by cash flow to net income.

The final step is to copy this formula down for all the other 955 companies with valid values for this data point. To do this, we clicked in cell D2, held the cursor over the bottom-right corner of the cell until we saw a cross-hair, and then dragged the cell down to D957, the final row of cash flow to net income.

Is Bigger Better?

Of the 10 Advanced BSD factors, all but one favor stocks in higher percentiles. The exception is payout ratio, which is the measure of how much of a company’s profits (earnings) is paid out as dividends. For Carlson, a company’s payout ratio paints a picture of how likely the company is to increase its dividend going forward. The higher the payout ratio, according to Carlson, the greater the risk that the company will cut or suspend its dividend, should earnings decline. It is for this reason that our initial screening eliminated companies with payout ratios greater than 60%. Likewise, companies with lower payout ratios received higher scores. In other words, the closer the payout ratio is to 60%, the lower the company’s percent rank value for this data point.

To adjust for this, we made a slight modification to the percent rank formula for the payout ratio column (L), as shown in Figure 3:

=1-PERCENTRANK($K$2:$K$1318,K2,2)

This time, we deducted the percent rank value from one. A company with a high payout ratio, and thus a high percent rank, will move to the bottom of the list with this formula. You can see this in Figure 3: When we sorted the file in descending order by company payout ratio, BBVA Banco Frances S.A. came in just under the wire with a payout ratio of 59.9% (column K), and it ranks in the lowest percentile (column L).

Data Anomalies

When calculating the percent ranks for the 10 Advanced BSD factors, occasionally Excel would return a #N/A error. These seemed to occur at the extremes, when outliers were significantly larger or smaller than the next closest value. For example, when calculating the percent rank for the expected earnings growth, Excel returned #N/A values for Sterlite Industries and Tata Motors, which have projected earnings growth rates of –24.9% and –23.0%, respectively. These companies had the two lowest projected earnings growth rates in a data range of mainly positive values. In these instances, we manually changed these two #N/A values to 0.0%.

On the opposite end of the extreme, Excel returned a #N/A percent rank value for Thor Industries based on its times interest earned figure. Its times interest earned value of 768.5 was the highest value by a significant margin. Here, we changed the #N/A to 100.0%.

Weighting the Rankings

After ranking the 10 Advanced BSD factors, the final step is to create a composite score, where the ranking of each individual metric is multiplied by its respective weighting.

To accomplish this, we first sorted our export file in alphabetical order by company name and copied columns A and B (company name and ticker) to a separate sheet in our Excel workbook (Figure 4). We entered the following formula in cell C4, to calculate the cash flow from net income score for 1st Source Corporation:

=5*’BSD Percent Ranks’!D2

Where 5 is the percentage weighting that cash flow from net income receives in the overall Advanced BSD composite score; ‘BSD Percent Ranks’! refers to the BSD Percent Ranks worksheet where we calculated all of the percent rank values for the 10 Advanced BSD factors; and D2 refers to the cell in that worksheet with 1st Source’s cash flow to net income percent rank figure. This formula returns a value of 4.35 (5 × 87%). Note that when linking worksheets in this fashion, it is important that you don’t change the sorting of your source worksheet. Doing so may lead to data mismatches.

We again must copy this formula down for all 1,318 companies (companies that do not have a valid value for any of the factors will receive a score of 0 for that field). To do this, we clicked in cell C4 of our new worksheet, held the cursor over the bottom-right corner of the cell until we saw a cross-hair and then dragged the cell down to cell C1321.

Final Results

Table 1 lists the top 30 companies based on the percent rank of their Advanced BSD composite score. The values listed for the 10 Advanced BSD factors are the weighted scores based on the weighting factor and the company’s percent rank for each field. We derived the Advanced BSD percent rank by using the percent rank function on the Advanced BSD Composite scores. The formula is merely the sum of the 10 factor scores for each company.

Company Name (Ticker) CFO to
NI
12-Mo
Score
CFO
Grth
3-Yr
Score
Tang
BV
Grth
Score
EPS
Grth
Est
Score
Pay-
out
Ratio
Score
Int
Cov
Score
Div
Yield
Score
Rel
Strgth
26-Wk
Score
Div
Grth
3-Yr
Score
EPS
Grth
3-Yr
Score
Adv
BSD
Comp
 
Adv
BSD
% Rank
Chicago Bridge & Iron Co. (CBI) 3.1 4.9 9.7 8.1 25.5 8.6 0.2 9.4 6.8 4.9 81.2 100%
Ebix Inc (EBIX) 1.4 4.6 9.5 9.0 30.0 9.2 0.4 9.6 2.8 4.5 81.0 99%
Visa Inc. (V) 1.6 4.8 9.2 8.9 21.3 9.6 0.4 8.8 9.9 4.6 79.0 99%
Dicks Sporting Goods Inc. (DKS) 3.6 3.3 8.5 8.3 30.0 8.5 0.9 8.5 2.8 1.9 76.2 99%
Cummins Inc. (CMI) 1.6 4.1 7.3 8.6 21.9 8.9 1.3 9.0 9.4 4.1 76.1 99%
SAP AG (SAP) 1.6 3.8 9.8 6.9 30.0 8.8 1.3 7.5 0.9 3.5 74.0 99%
Cimarex Energy Co. (XEC) 4.3 1.6 8.1 4.0 26.1 8.1 0.4 8.5 8.8 4.1 73.9 99%
Las Vegas Sands Corp. (LVS) 0.0 4.8 8.2 9.6 30.0 4.9 2.2 6.3 2.8 4.7 73.5 99%
Spreadtrum Communica’ns (SPRD) 2.1 4.7 8.3 8.6 30.0 9.0 3.1 0.7 2.8 4.1 73.3 99%
AngloGold Ashanti Limited (AU) 3.3 4.8 8.0 9.9 22.5 6.8 2.9 0.9 9.8 4.3 73.2 99%
Dana Holding Corporation (DAN) 3.7 4.3 3.2 9.8 30.0 4.0 1.1 9.0 2.8 4.9 72.8 99%
CF Industries Holdings, Inc. (CF) 2.4 4.5 8.2 8.0 27.0 7.8 0.7 1.9 8.9 3.4 72.7 99%
Mastercard Inc. (MA) 2.6 4.8 3.7 9.0 27.3 9.4 0.1 7.5 2.8 4.9 72.1 99%
Nordson Corporation (NDSN) 0.0 4.2 9.8 8.5 21.6 9.2 0.7 8.1 6.3 3.5 71.8 99%
Helmerich & Payne, Inc. (HP) 3.9 3.6 6.7 7.7 26.4 8.9 0.2 5.1 7.9 1.4 71.8 98%
Ross Stores, Inc. (ROST) 1.5 3.9 6.1 6.0 21.3 9.3 0.9 9.4 9.4 4.0 71.8 98%
Shire Plc. (SHPGY) 2.0 2.9 8.9 7.7 24.3 8.5 0.2 3.7 8.4 4.7 71.2 98%
Cooper Companies, Inc. (COO) 3.7 4.6 9.5 8.0 28.2 6.6 0.0 3.7 2.8 4.1 71.2 98%
ASM Inter’l N.V. (USA) (ASMI) 2.1 4.3 9.4 0.4 30.0 9.3 1.6 9.4 0.9 3.6 71.0 98%
Cliffs Natural Resources Inc. (CLF) 1.6 4.4 8.2 5.1 26.7 9.0 1.9 0.4 9.5 4.0 70.8 98%
SouFun Holdings Limited (SFUN) 1.5 4.6 0.9 9.6 30.0 9.0 5.0 2.3 2.8 4.6 70.2 98%
Graham Corporation (GHM) 0.2 0.2 6.4 9.6 26.1 8.9 0.1 9.6 8.7 0.2 70.0 98%
Reinsurance Group of Amer (RGA) 0.0 3.7 7.5 8.4 25.8 5.6 1.1 4.8 8.8 4.3 70.0 98%
Credicorp Ltd. (USA) (BAP) 4.0 4.3 6.9 7.6 30.0 2.4 2.2 7.9 0.9 3.6 69.8 98%
HB Fuller Co. (FUL) 1.7 4.6 7.0 8.5 20.1 6.6 0.8 9.5 6.0 4.7 69.5 98%
Herbalife Ltd. (HLF) 2.0 3.9 9.5 7.1 17.1 9.0 2.1 6.3 9.1 3.6 69.5 98%
SM Energy Co. (SM) 4.7 2.2 7.3 9.9 27.9 5.7 0.0 5.1 2.8 3.9 69.5 98%
Agilent Technologies Inc. (A) 1.9 3.6 9.4 7.0 27.6 6.9 0.7 6.6 2.8 3.0 69.4 97%
Landstar System, Inc. (LSTR) 0.0 1.1 7.8 8.6 24.9 9.0 0.2 8.5 7.4 1.8 69.2 97%
Albemarle Corporation (ALB) 1.6 3.3 6.9 6.7 21.9 7.3 1.1 8.4 8.0 3.9 69.0 97%

Carlson looks for companies that rank in the top 20%, which you can look at as having an Advanced BSD Composite score of 80 or higher, or ranking in the top 80% of all companies. If you choose companies with an Advanced BSD Composite of 80 or more, you have only two companies to consider—Chicago Bridge & Iron (81.2) and Ebix Inc. (81.0). Alternatively, if you look at those with a percent rank of 80% or higher, you have 264 companies for consideration.

Conclusion

Using the percent rank function in Excel, you can take a range of data and manipulate it so that it gains context. By doing so, you are able to more easily compare companies, making your analysis more meaningful.

Click here to download spreadsheet.


Discussion

No comments have been added yet. Add your thoughts to the discussion!

You need to log in as a registered AAII user before commenting.
Create an account

Log In