23 Obtain Averages for an Industry and the Competition

Obtain Averages for an Industry and the Competition

figs/moderate.gif figs/hack23.gif

Some web sites place industry average values next to a company's financial measures for convenient comparison; you can obtain averages for a company's direct competitors with carefully configured stock screen results.

Each industry has its peculiarities: utility companies have learned to operate under debt loads that would crush companies in other industries; grocery stores can thrive with profit margins that leave no room for error; software companies enjoy high profit margins that other companies envy and that all software companies must attain to stay in the game. To understand whether a company is performing well or is down with the dogs, you must compare its financial measures with those of the overall industry to which it belongs. And, because most industries include companies of different sizes or subspecialties, it's a good idea to compare a company to its direct competitors. In most cases, a quick online comparison is enough. However, if you want to download values for more thorough investigation, you can play games with stock screening tools to produce downloadable spreadsheets and calculate averages for the competition yourself.

If you want to compare industry and sector averages to the values produced by a company, Reuters Investor (http://investor.reuters.com) makes it easy. To access a stock page, type a ticker symbol in the Symbol box and click Go. On the stock page, click Ratios in the navigation bar. By default, valuation ratios, such as the P/E ratio and institutional ownership, appear in the Ratio Comparison table, but you can view other types of ratios by clicking other ratio category links under the Ratio heading in the navigation bar: Dividend, Growth Rate, Financial Condition, Profit Margin, Management Effectiveness, and Efficiency. In the table, the first column shows the company numbers with additional columns for average values for the industry, sector, and the S&P 500 index.

To find strong competitors in an industry, you want to see above industry average performance. In most cases, a quick inspection of key ratios on these pages can tell you whether a company meets this criterion.

For a quick reminder of how to use different ratios to analyze company performance, click the Learn about Dividend Ratios link below the table.

Yahoo! Finance (http://finance.yahoo.com) has a great concept for comparing a company to its competitors and its industry. When you open a stock page for a company and click the Competitors link in the navigation bar, the Direct Competitor Comparison table shows values for the company you're studying next to values for its closest competitors and the industry averages. Unfortunately, only a few of the measures shown are really helpful in finding the best of the bunch. For example, you can compare competitors using revenue growth, gross margin, and operating margin [Hack #26] and [Hack #32], which are shown as percentages. You can also use the P/E ratio and the PEG ratio [Hack #27] and [Hack #28]] to compare companies.

1 Calculate Competitors' Averages Using Stock Screen Results

You can capitalize on the spreadsheets that industrial-strength stock screens produce to calculate average values for the competition. By screening for stocks in a specific industry, limiting the field to companies with similar annual revenue, or specifying other criteria, the results can reflect your view of a company's closest competitors. Before downloading the spreadsheet, add columns for all the industry figures you want to compare, such as annual revenue and EPS growth, pre-tax profit margin, return on equity, return on assets, inventory turnover, debt to equity ratio, and P/E ratio. To learn the importance of these measures, see Chapter 4. After you download the spreadsheet containing the companies and financial fields you want, use Excel functions to calculate the averages for a set of competitors.

Reuters Investor PowerScreener [Hack #5] provides the most useful set of financial fields for comparing competition. Defining stock screen criteria isn't as easy to build with PowerScreener as with other screening tools. However, for calculating competition averages, you need only one or two criteria. To create a competition screen, follow these steps:

  1. Navigate to http://investor.reuters.com.

  2. Type the ticker symbol for the company you are studying in the Quote box in the menu bar and click Go.

  3. To locate the industry and market cap for the company, click the Snapshot link in the navigation bar. The Key Ratios & Statistics table provides values that you can use for your competition screen, as illustrated in Figure. The industry is located at the top of the figure. Market cap is listed under the Share Related Items heading.

    The Reuters Investor Snapshot contains key ratios you can use to find competitors

  4. To access PowerScreener, click the Ideas & Screening link in the navigation bar and then click the PowerScreener link that appears under the Ideas & Screening heading in the navigation bar.

  5. To define your first criterion, click Add a Criterion at the bottom of the Screen Builder table.

  6. To screen for the industry, follow these steps:

    1. Expand the Descriptive folder, select Industry [IndDescr] in the variable list, and then click Select.

    2. In the Select a value dialog box that appears, check the checkbox for the industry to which the company you're studying belongs. For example, to find competitors for Pfizer, which belongs to the Major Drugs industry, check the Major Drugs checkbox.

    3. Click Go to add the criterion to the screen.

    4. Click Run in the Screen Builder table to execute the screen using the industry criteria. The table shows how many companies match the screen criteria.

  7. To screen for companies of a similar size, follow these steps:

    1. Click Add a Criterion.

    2. To screen for market cap, you want to find companies in a similar size range: small-cap, mid-cap, or large-cap. Typical guidelines for these ranges are less than $600 million for small-cap, $600 million to $6 billion for mid-cap, and more than $6 billion for large-cap. Pfizer's market cap is almost $300 billion—more of a giant-cap, but we'll use large-cap in this example.

    3. Expand the Share Related Items folder, select Market Capitalization ($ millions) [MktCap] in the variable list, and then click Select.

    4. Click the > button to the right of the variable list.

    5. To type the criterion value, click within the criterion window at the bottom of the Screen Builder table, and type 6000.

    6. Click Run in the Screen Builder table to execute the screen using the industry criteria. The table shows how many companies match the screen criteria.

  8. To save the screen, choose FileSave AsScreen. Type the name of the screen in the Save Screen As dialog box, and click OK.

  9. To view the results of the screen, select the Screening Results tab.

  10. To add fields to the results table, choose ViewAdd/Remove/Arrange Columns. In the All Variables list, expand the categories you want and select variables to add them to the Current Variable List. Click OK to display the columns in the results, as shown in Figure.

    Because this screen returns companies in the same industry, select Industry in the Current Variable List and click Remove.

    Select variables in the Reuters Investor PowerScreener to add columns to the screen results

    For example, the results in Figure include columns containing the following information:

    Company Ticker Symbol
    Company Name
    Market Capitalization
    Sales Percent Change, Year Over Year (this is the sales growth rate)
    Earnings Per Share, 5 Year Compound Annual Growth Rate
    Long Term Debt to Equity
    Net Profit Margin
    Return on Average Common Equity (this is ROE using an average value for shareholders' equity)
    Return on Assets
    Price to Earnings Ratio
  11. To save the collection and order of the columns in the table as a layout, choose FileSave AsLayout. In the Save Layout As dialog box, type the name of the layout, such as Industry Comparison, and click OK.

  12. To save the results to a spreadsheet, click the Export to Spreadsheet icon, shown in Figure, below the PowerScreener menu bar, or choose FileExport To Spreadsheet. If the Export to Spreadsheet dialog box appears, click OK. In the File Download dialog box, click Save. In the Save As dialog box, specify the folder and filename for the spreadsheet, and click Save.

Click the Export to Spreadsheet icon to save the screen results to a spreadsheet

With the spreadsheet of results downloaded to your computer, open the spreadsheet in Excel and follow these steps to calculate average values for the competition:

  1. Select a cell below a column of results, such as D22 for Yr Over Yr Sales Change % in Figure.

  2. Choose InsertFunction.

  3. Select the Statistical category, select Average, and then click OK. By default, Excel selects the cells above the current cell and adds that range to the Number1 box in the Function Arguments dialog box. If the range isn't correct, simply select the correct cells on the worksheet, cells D5:D20 in this example.

  4. Click OK.

    To format the results to only one decimal place, choose FormatCells. Select the Number tab and select 1 in the Decimal Places box.

  5. Copy this cell across the row to calculate the averages for the other columns.

Use Excel features to calculate competition averages and highlight above-average performance

2 Hacking the Hack

Use other Excel features to emphasize the values that are better than the average for the competition. For example, you can apply conditional formatting to cells [Hack #8]. Follow these steps to shade values that are better than the industry average:

  1. To highlight sales growth that is higher than the competition's average growth, select the first sales growth cell (D5 in this example) and choose FormatConditional Formatting.

  2. Select Formula Is for Condition 1.

  3. In the formula box, type = D5 > D$22 where D22 is the cell for the industry average sales growth rate.

  4. Click Format, select the Patterns tab, click the colored square for the highlighting you want to apply when the condition is true, and click OK.

  5. Back in the Conditional Formatting dialog box, click OK to apply the formatting to the cell.

  6. To copy the formatting to the other cells in the column, click the Format Painter icon on the Standard toolbar, shown in Figure, and select the other company cells in the column.

Click the Format Painter icon to apply formatting to other cells

As long as you want to highlight cells whose values are higher than the competition's average, you can copy this formatting to cells in other columns. However, for some measures, such as long-term debt to equity and the P/E ratio, a number lower than the average is preferable. For these cells, follow Steps 1 through 6, but replace > with < in the formula.

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows