Projecting Profit Margins in Toolkit 6's Revenue-Based EPS Estimates Tool
Posted by: Doug Gerlach 7/6/2011 2:05:09 PM

The Revenue-Based EPS Estimates calculator in Toolkit 6 can help confirm or identify problems with trend-based earnings growth projections. Key to the success of this method is your assessment of what you expect a company's profit margins to be in the future. Here are some additional thoughts on considering a company's likely pre-tax profit margins.

In last night's Toolkit 6 User Group Webinar, I discussed how to use the Revenue-Based EPS Estimates tool in the program. (You may view the archives of the webinar if you missed it.)

Known in BetterInvesting parlance as the "Preferred Procedure," this approach to estimating Earnings per Share growth is a streamlined approach similar to that used by professional stock analysts in constructing their models of a company's future fundamental approach.

Using a limited set of variables, the Revenue-Based EPS Estimator lets you calculate future earnings based on sales, expenses, taxes, and shares outstanding (and, in rare cases, the amount of preferred dividends to be paid). Of these, sales growth for mature growth companies (of the type best suited for Toolkit 6's approach) tends to be very consistent and relatively straightforward to project into the future. Taxes also generally don't fluctuate too greatly, and trends in an increase or decrease in the number of shares outstanding can be observed relatively easily, allowing you to judge if a company is issuing additional shares or buying back shares on a regular basis.

This leaves the matter of a company's future expenses. We tackle this figure from the opposite direction, though, considering what the company's likely pre-tax profit margin may be in the future, and thus deriving the amount of total expenses the company would incur at the selected level of pre-tax profits.

As I reviewed in last night's webinar, there are several places in Toolkit 6 where you can find information about a company's pre-tax profits that can help you in evaluating a company's future margins:

  • On the Stock Study Section 2, you can of course review the 10-year history of a company's % Pre-tax Profit on Sales on line A, along with the 5-year Average and whether the trend is up, down, or even. For Walgreen Company, the 5-year Average PTP is 5.5%, higher than the 2010 margin of 5.0%. In the past five years, the margin has been as high as 5.9%, but the trend in the last two years has been down.
  • From Section 2, you can click on the magenta (pink) highlighted box to display the graph of the 10-year pre-tax profit margin, which can give you another point of reference of the above trends.
  • On the Quarterly Trend Analysis report (accessed from the "Select View" dropdown menu once you open a company in the program), you can see Pre-Tax Profit as a Percentage of Sales in the left half of the page (the "Quarterly Data" section) for each of the recent quarters. Scroll down to the bottom to see the pre-tax profit margin for the most recent quarter. For Walgreen's quarter ended 5/2011, it is 5.1%.
  • For a retail company such as Walgreen Co., though, recent quarterly results may not be as relevant, since companies in retail industries tend to have strong quarters (at year-end, during the holiday selling season) and weak quarters (during slower-selling summer and fall seasons), so it can be more relevant to look to the right side of the Quarterly Trend Analysis report, the "Last 12 Months Data" (also known as the "Trailing Twelve Months" or "TTM" period). Here you'll see the total results for the last four quarters. For Walgreen's, their TTM PTP is 5.3%, indicating an upturn from 2010 results.
  • Finally, you can select the Quarterly Trend Graph from the dropdown menu to view the quarterly and trailing twelve months data from the above report on a graph. Click the "Quarterly" radio button to display the results by quarter, where you can easily see the seasonal nature of Walgreen's business. Click on "Trailing 4 Qtrs" to display the TTM data, which smooths the data and allows you to see any visible trends. For Walgreen's, you can discern a slight but steady uptick in the TTM PTP over the last five quarters, which seems to be a positive sign for the company.
As you can see, the data for Walgreen's tells a story: margins have fallen in the last five years, especially in 2009 and 2010. However, recent quarters show an uptrend and more positive signs of recovery. Based on your other research about the company, you can use the pre-tax profit margin that you think best supports your expectations of the company's future.
 
In using the Revenue-Based EPS Estimator, I like to test an array of values for the pre-tax profit margin, ranging from conservative to aggressive, to see the results that might occur in an improving, a stable, and a declining environment. For Walgreen Company, I used the same base values for expected revenues, taxes, and shares outstanding, and found quite significant differences in the resulting EPS growth rates based on seemingly small differences in the percentage pre-tax profit.
 
The table below summarizes the range of pre-tax margins that I tested using the tool, and the resulting EPS growth:
 
Projected Pre-Tax Profit Margin Resulting % EPS Growth
5.0% (last fiscal year) 5.9%
5.3% (TTM, from Qtly Trnd Rpt 7.2%
5.5% (5-year average) 8.0%
5.9% (high of last 5 years) 9.5%

 In the webinar, I demonstrated a spreadsheet that I created many years ago to calculate revenue-based EPS estimates. (You can download a copy here.) One of the additional features in the spreadsheet is the ability to calculate the profit margin that is required to support your selected EPS growth rate. This can be illuminating. For Walgreen's, if you assume a 10% growth rate at the same constants for sales growth, taxes, and shares outstanding that I used in the above example, the margin that is required to support that level of growth is 6.0%, which is a higher pre-tax profit margin than the company has ever had, indicating that unless there are some other changes in the company's circumstances, that growth rate might not be supportable.

Used properly, the Revenue-Based EPS Estimates tool is quite valuable in the process of analyzing a stock in Toolkit, providing important insight to guide your conclusions.