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

A New Way to Invest; Motif Investing
posted 1 day ago by Jaclyn McClellan

Unlimited Cloud Storage for Office 365 Subscribers
posted 3 days ago by Jaclyn McClellan

MeGooDo LED Mini Projector Review
posted 4 days ago by Hareesh Jayanthi

Bullish & Bearish Sentiment Hit 8-Week Extremes
posted 7 days ago by Wayne Thorp

CI Market Dashboard Continues in All-Time Bearish Territory
posted 9 days ago by Wayne Thorp



Computerized Investing > First Quarter 2014

Calculating Mohanram’s G-Score Using SI Pro and Microsoft Excel

PRINT | | | | COMMENTS (3) | A A   Reset

by John Bajkowski

In the October 2013 issue of the AAII Journal, we presented a growth-stock screen constructed on the research findings of Partha Mohanram. The article, “Finding Winners and Avoiding Losers Among Glamour Stocks,” looked at stocks with high price-to-book-value ratios that possess financial qualities that should help them succeed in the future. The screen used data from AAII’s stock screening program and fundamental database, Stock Investor Pro.

Mohanram created a scoring system that related company information to industry/sector medians. While we could have captured the spirit of the screen without the industry comparisons, we determined that we could export relevant data from Stock Investor Pro into an Excel spreadsheet to perform the analysis on a sector level. In this Spreadsheet Corner, we present how we used Excel to perform our analysis. We provide the Excel export file for download from the Computerized Investing website, and any cell references in this article relate to that file.

Research Background

Most academic research suggests that investing in glamour stocks is a losing proposition. On average, firms with high valuations determined by factors such as the price-to-book-value ratio underperform the market over the long term. While the market does a good job of valuing securities in the long run, in the short term it can overreact to information and push prices away from their true value.

Stocks with high prices to their book values tend to be glamour, or growth, companies that have attracted significant investor attention. As investors pile into these growth stocks because of hype, strong relative price strength or high past or expected growth, the price deviates further from its underlying fundamental value. As with value investing, some growth stocks deserve their high valuations while many do not. Partha Mohanram, CGA Ontario Professor of Financial Accounting at Rotman, University of Toronto, developed a scoring system to help separate the winners from the losers of stocks trading with high price-to-book-value ratios.

G-Score Components

Mohanram’s eight-point scale helps to identify attractive growth stocks and avoid bad growth companies. Profitability, naive extrapolation and accounting conservatism are examined using popular ratios and basic financial statement data to create a G-score. Mohanram found that high price-to-book-value stocks with higher G-scores outperformed growth stocks with lower G-scores. One of Mohanram’s factors examines advertising spending, a data element not covered in Stock Investor Pro, so our maximum G-score is seven.

Mohanram highlights academic research indicating that ratio analysis benefits from industry comparisons. Since the Mohanram G-score relies on some custom ratios that are not calculated in Stock Investor Pro, we exported the necessary data fields into Microsoft Excel and used the spreadsheet program to create custom ratios, sector medians and the G-scores.

Our overall strategy was to create a “view” in Stock Investor Pro with as many data fields as possible for the calculation of the company financial variables. We would then export the complete universe of exchange-listed stocks so that we could use Excel’s median function to calculate sector medians for the six G-score variables that were tied to sector comparisons.

Profitability

Mohanram awarded up to three points for profitability: one for return on assets above the industry median, one for a ratio of cash flow from operations to assets above the industry median and one point if the cash flow from operations exceeds net income.

SI Pro includes the return on assets, cash from operations and net income data fields, so they were added to the G-score view. However, the ratio of cash flow from operations to assets is not native to SI Pro, so we created a custom field in the program and added the field to our G-score view. In the custom field, we divided the cash from operations over the last four quarters by the average total assets over the last four quarters.

CF ROA 12m = ([Cash from operations 12m]/([Total assets Q1]*.5+[Total assets Q5]*.5))*100

Naive Extrapolation

Too often the market simply examines the past growth pattern of a company and expects it to continue into the future. Two companies with the same historical growth might have the same high valuation, but a company with more stable and predictable earnings and sales is more desirable and more likely to continue its growth. Mohanram measures earnings variability as the variance of a firm’s return on assets in the past five years.

We decided to use Excel’s variance calculation to determine this element. We added the company return on assets for each of the last five company fiscal years to our view. When the data was exported to Excel, we then used the Excel “if, then, else” function to test if a company had enough years of data to perform the calculation. A company must have at least three years of data to calculate the variance or it is assigned a text value of “na” for the variance. In our data export spreadsheet we used this formula in cell AC4:

=IF(COUNT(AD4:AH4)>2,VARP(AD4:AH4),”na”)

In this formula, we use the COUNT function to see how many cells have number values. If more than two cells contain a number, then we calculate the variance based upon the entire population. The variance function ignores text values in the calculation. If the COUNT function comes up with two or less number fields in the range, then it returns text value “na.”

The second growth signal in this category relates to the stability of year-to-year growth. A firm that has stable growth is less likely to disappoint in terms of future growth. Mohanram examined the stability of sales growth to help overcome the issues of negative earnings that many high price-to-book-value stocks may have. Sales growth may also be more persistent and predictable than earnings growth because it is less subject to accounting judgments. Here again, Mohanram compares the company variance of year-over-year sales growth to its sector median. But first we must compute the variance of the year-to-year sales growth for each company.

In our SI Pro view that we prepared for exporting into Excel, we added the annual sales figures for each of the last six fiscal years. We are going to look at five years of changes, so we need six year-end values to calculate five annual growth rates.

In the exported Excel file, we used the IF function to test whether we should calculate the growth rate for a given year or record a text value of “na.” In cell AK4 of our export spreadsheet we used the following formula:

=IF(AQ4>0,(AP4/AQ4-1)*100,”na”)

Reported sales are always positive, so we are testing to make sure the beginning period of each growth rate calculation exists and is positive. If the starting value is greater than zero, we divide the ending sales value by the beginning value, subtract one and raise the value by 100 to equate the result to the presentation of growth rates exported from SI Pro. If the starting value is zero or lower, we place the text value “na” for the year. We used this calculation for each of the five annual growth calculation periods.

We then calculated the variance of five annual growth rates, requiring at least three annual calculated growth rates. We used this formula in cell AJ4 of our data spreadsheet:

=IF(COUNT(AK4:AO4)>2,VARP(AK4:AO4),”na”)

In this formula, we use the COUNT function to see how many cells have number values. If more than two cells contain a number, then we calculate the variance based upon the entire population. If the COUNT function comes up with two or less number fields in the range, then it returns the text value “na.”

Accounting Conservatism

The final three growth signals deal with company actions that might depress current earnings and book value, but should result in greater growth and profitability down the line. Mohanram identified spending on research and development (R&D), capital expenditures (capex) and advertising as factors that may point to future sales and earnings expansion. Conservatism in accounting standards forces companies to expense outlays for many R&D and advertising efforts even if they create valuable intangible assets that do not show up in a firm’s book value calculation. Mohanram scales these values to assets. We could have created custom fields in SI Pro that relate R&D and capex to assets, but we added the raw figures to our exported view and did the calculations in Excel. This is the formula we used in cell AV4 of our data spreadsheet:

=IF(COUNT(AW4,$Z4)=2,AW4/$Z4*100,”na”)

In this function, we first check to see if a given company has numerical values for R&D over the trailing 12 months and total assets one year ago. If a company has values for both of these elements, we divide the R&D figure by total assets and raise the value by 100 to equate its presentation to other ratios in SI Pro. If a company does not have both R&D and total assets, the formula returns a text value of “na.”

The same was true for capital expenditures, and we used this formula in cell AY4:

=IF(COUNT(AZ4,$Z4)=2,AZ4/$Z4*100,”na”)

In this function, we first check to see if a given company has numerical values for capital expenditures over the trailing 12 months and total assets one year ago. If a company has values for both of these elements, we divide the capex figure by total assets and raise the value by 100. If a company does not have both capex and total assets, the formula returns a text value of “na.”

The Stock Investor Pro dataset does not break out advertising expenditures, so we were unable to calculate the growth point for advertising intensity.

Calculating Industry Medians

Before we exported our raw data file from Stock Investor Pro, we did run one screen. We excluded stocks that were not exchange-listed. We added some additional data fields to our view to allow us to sort the companies by sector. And we included the price-to-book-value ratio and the corresponding price-to-book-value percentile rank in our view so we could focus on the growth stocks after calculating the G-score. SI Pro covered a universe of 7,279 NYSE, NASDAQ, Amex and over-the-counter stocks at the time of our analysis. We exported 4,750 exchange-listed stocks to Excel.

In the spreadsheet, we first sorted all of the companies by their sector designations. Some sectors, such as finance, contain a large number of stocks, while others have a smaller number of stocks. There were only five conglomerates.

We inserted a few rows in between each sector and set out to calculate the medians required for our G-score. We were calculating seven elements and needed six medians for our comparisons. We simply used the Excel median function and carefully selected the range for each sector.

Once we created the median calculation for a given variable within the industry, we were able to copy and paste that function for the remaining five median values within the sector. For example, we used the following formula in cell R3 to calculate the median return on assets for the basic materials sector:

=MEDIAN(R4:R336)

We then copied the formula to the columns that had the calculations for cash flow from operation to assets ratio (column S), return on assets variance (column AC), sales growth variance (column AJ), R&D to assets percentage (column AV), and capex to assets percentage (column AY).

The spreadsheet had a mix of raw data and calculated values. We like to color-code the columns and rows to make it easier to find the important columns and check the calculations. We highlighted calculated G-score fields in yellow and industry medians in green (row 3).

Calculating the G-Score

With sector medians calculated for each of the 12 sectors, we were able to calculate the components of the G-score.

ROA Score [Column J]

=IF(R4>$R$3,1,0)

We use the IF function to test if the company’s return on assets (R4) is greater than the sector’s return on assets ($R$3). If the company’s return on assets is greater than the sector median, the company gets a score of one; otherwise, it gets a score of zero. Note that the sector comparison value has “$” before the column (R) and row (3) reference. This locks the sector reference so you can copy the formula down within the sector and have the formula compare the company value to the “locked” sector value. The sector reference needs to be adjusted for each of the sectors.

Cash Flow to Assets [Column K]

=IF(S4>$S$3,1,0)

Here again, we use the IF function to test if the company’s cash flow to assets (S4) is greater than the sector’s cash flow to assets ($S$3). If the company’s cash flow to assets is greater than the sector median, the company gets a score of one; otherwise, it gets a score of zero. Note the locked sector reference that needs to be adjusted for each sector.

Cash Flow Greater Than Net Income [Column L]

=IF(COUNT(T4)=1,IF(T4>0,1,0),0)

This is the only G-score element that is not compared to a sector median. Therefore, the formula can be copied down unchanged throughout all of the sectors. Mohanram reminds us that earnings may be less meaningful than cash flow for the early-stage growth companies that are likely to be found among the high price-to book-value stocks.

Let’s first look at the calculation in column T.

=IF(COUNT(U4,W4)=2,IF(U4>0,U4 W4,”na”),”na”)

We use the IF function to first make sure that a company has values for both the operating cash flow (U4) and net income (W4). If the company has values for both figures, then we use another IF function to see if cash flow from operations (U4) is positive. If cash flow is positive, the calculation subtracts net income; otherwise, we report the text value of “na.” The formula in column L first looks to see if there is a number stored in column T and then looks to see if the cash from operations less net income is greater than zero. A growth point is awarded if cash from operations exceeds net income. A company gets zero if it does not have reported cash from operations and net income, cash from operations is not positive, or if net income is greater than cash from operations. The measure tries to avoid firms making account adjustment to earnings in the short run that may weaken long-term profitability.

Return on Assets Variance [Column M]

=IF(COUNT(AC4,$AC$3)=2,IF(AC4<$AC$3,1,0),0)

This formula is like the return on assets score, but we further bullet-proofed it by also testing that the company and sector have variance calculation. A company’s variance will only be calculated if the company has three or more years of return on assets. If a company meets this requirement, then we check to see if the variance is lower for the company (AC4) than for its sector ($AC$3). A company is awarded one growth point if its variance in ROA is below the sector median.

Sales Growth Variance [Column N]

=IF(COUNT(AJ4,$AJ$3)=2,IF(AJ4<$AJ$3,1,0),0)

This formula also requires that the company and sector have variance calculations with the COUNT function. A company’s variance will only be calculated if the company has three or more years of annual sales growth rates. If a company meets this requirement, then we check to see if the variance is lower for the company (AJ4) than for its sector ($AJ$3). A company is awarded one growth point if its variance in sales growth variance is below that of the sector median.

R&D to Assets [Column O]

=IF(COUNT(AV4)=1,IF(AV4>$AV$3,1,0),0)

This formula first tests if the company has an R&D to assets figure with the COUNT function. If a company meets this requirement, we then check to see if the company’s R&D to assets (AV4) is greater than that of its sector ($AV$3). A company is awarded one growth point if its R&D ratio is greater than the sector median’s ratio.

Capex to Assets [Column P]

=IF(COUNT(AY4)=1,IF(AY4>$AY$3,1,0),0)

This formula first tests if the company has a capex to assets figure with the COUNT function. If a company meets this requirement, we then check to see if the company’s capex to assets percentage (AY4) is greater than that of its sector ($AY$3). A company is award one growth point if its capex percentage is greater than the sector median’s percentage.

Summing It Up

Mohanram sums up the growth points into a G-score with a maximum value of eight. Since we did not calculate the advertising intensity growth score, our maximum G-score is seven.

We simply used the Excel sum function for this in cell H4.

=SUM(J4:P4)

The sum function looks at the range where we have the G-score components. This formula can be copied down for all of the companies.

High Price to Book Value

We then wanted to start sorting companies across sectors in Excel. We felt the safest way to sort was to first convert all of the Excel formulas into fixed values. We made a copy of the whole spreadsheet tab by right-clicking on the tab name and selecting “Move or Copy.” When prompted, we clicked on the “Create a copy” check box and made a duplicate of the whole worksheet tab. We then highlighted all of the contents of the tab, selected copy and then “paste special values” for the whole worksheet. We still had the original worksheet with the formulas and a new worksheet tab with “locked” values.

For the article, we were just focused on the G-Scores of the high price-to-book-value stocks. Mohanram warns investors that these stocks do not perform well as a group, but it is possible to use fundamental analysis to help avoid the biggest losers and select the strongest candidates.

Source: AAII’s Stock Investor Pro/Thomson Reuters. Data as of September 20, 2013.

Mohanram limited his universe to the top 20% of stocks according to their price-to-book-value ratio. We first moved all of the sector medians to their own part of the spreadsheet. We sorted the companies in descending order by their price-to-book-value percent rank. Once we had the companies with percentile rank of 80% or greater, we then sorted that subgroup by the G-score (descending). We had 14 high price-to-book-value companies with a G-score of seven, and 129 high price-to-book stocks with a G-score of six. Figure 1 is a listing of the 14 stocks with G-scores of seven as of September 20, 2013.

Beyond finding winning growth stocks, Mohanram’s work reveals that growth stocks with low G-scores should be avoided. Overall, the higher the G-score, the greater the average portfolio return. Results for individual stocks will vary dramatically.

The individual components of the G-score represent a useful checklist for investors examining growth stocks. Even with these additional financial tests, it is important to perform a careful analysis of any passing stock.


Discussion

John Wiley from AZ posted 10 months ago:

Two comments:

1. Before working with this approach I think you need to let us know if it's worth it. Do you have any data on how well the method has done over many years? One peek is interesting but not convincing.

2. Getting rid of stocks with n/a or zero entries has got to be easier than your count routine. How about supplying a download where you have screened out all the stocks with n/as and zeros, so that we don't have to? This would also be useful for the novy-marx approach. And probably for future stuff too.


William Satterwhite from TX posted 3 months ago:

Takes a while to work thru the SS, but it is good and some more explaining of the results could be helpful.


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

Log In