Read Comments (6)

- Pre-interest pretax profit margin = earnings before interest and taxes (EBIT) ÷ sales;
- Asset turnover = sales ÷ average total assets;
- Interest burden = (EBIT – interest expense) ÷ EBIT;
- Tax efficiency = [1 – (tax expense ÷ (EBIT – interest expense))]; and
- Leverage ratio (equity multiplier) = average total assets ÷ average shareholders’ equity.
- B8 is net income; and
- B4 is sales for the fiscal year ending December 2010.
- B4 is sales; and
- ((B9+C9)/2) is the average of total assets for fiscal years 2010 and 2009.
- ((B9+C9)/2) is the average of total assets for fiscal years 2010 and 2009; and
- ((B10+C10)/2) is the average of total shareholders’ equity for fiscal years 2010 and 2009.
- B5 is earnings before interest and taxes (EBIT); and
- B4 is sales.
- B5 is EBIT; and
- B6 is interest expense.
- B7 is tax expense;
- B5 is EBIT; and
- B6 is interest expense.

Investors use return on equity (ROE) to measure the earnings a company generates from its assets. With it, you can determine whether a firm is a profit-creator or a profit-burner and management’s profit-generating efficiency. Why is this important to investors? Companies that are good at coaxing profits from their operations tend to have competitive advantages, which can translate into superior investment returns.

In its simplest form, return on equity is calculated as follows:

ROE = Net income ÷ average shareholders’ equity

Shareholders’ equity is also called book value, which is the difference between total assets and total liabilities.

Investors analyze the trend in ROE for individual firms and compare this to historical and industry benchmarks. A rising return on equity can signal that a company is able to grow profits without adding new equity into the business, which dilutes the ownership share of existing shareholders. The higher a company’s return on equity, the better management is at employing investors’ capital to generate profits.

A company cannot grow earnings faster than its current ROE without raising additional cash. Companies raise cash by either selling new shares or by issuing debt. However, there are costs associated with both activities: Issuing debt leads to interest expense, which can lower net income; and selling more shares lowers earnings per share by increasing the number of outstanding shares.

However, relying on the formula above to derive return on equity tells an incomplete story about a company. For example, a company can boost its ROE by taking on additional debt. If its debt load becomes excessive, it may force the company into bankruptcy. As a result, it is a good idea to examine the drivers of ROE. In order to do that, E.I. du Pont Nemours and Company came up with a system to deconstruct ROE. Over the years, this system has become known as the DuPont model.

The DuPont Corporation created its method for analyzing return on equity in the 1920s. Today, two variants are taught in finance programs—the original three-step model and an extended five-step model.

In this installment of Spreadsheet Corner, we begin by discussing the three-step model and then expand into the five-step model. We then show how to use a spreadsheet to calculate the components of ROE.

The three-step DuPont model is calculated as follows:

ROE = Net profit margin × asset turnover × equity multiplier

Where:

Net profit margin = net income ÷ sales;

Asset turnover = sales ÷ average total assets; and

Equity multiplier = average total assets ÷ average shareholders’ equity

The three-step DuPont model, in effect, captures management’s effectiveness at generating profits (net profit margin), managing assets (asset turnover) and finding an optimal amount of leverage (equity multiplier).

Ideally, we would like to see a company boosting its ROE by increasing its net margin or its asset turnover.

The net profit margin (or net margin) of a company reflects management’s pricing strategy by showing how much earnings they can generate from a single dollar of assets. Companies must be able to price their products and services in such a way as to drive volume. However, all the sales volume in the world is useless if a company cannot turn a profit. Therefore, management must price a product to be as profitable as possible while still generating stable sales growth.

Profit margins are also an expression of the amount of competition a company faces—the more competitive the industry, all else being equal, the lower the profit margins for the companies in the industry. Companies with high profit margins indicate that they have a highly proprietary product or service that carries with it a price premium. Companies in a monopolistic position or those that are part of an oligopoly (only a few main competitors) tend to have higher profit margins. In industries where there are few barriers to entry, high profit margins are quickly eroded as new competitors enter the marketplace. Companies are said to have wide moats if they are able to prevent new competition from entering or are in a position to lower their prices in response to new competition and make up for lower margins with higher volume.

In contrast, there are industries where there is little to differentiate the product of one company from another. When producing such “commodity” products or services, companies must compete based on price. The companies in these types of industries tend to have very low profit margins.

SPECIAL OFFER: Get AAII membership FREE for 30 days!

Get full access to AAII.com, including our market-beating Model Stock Portfolio, currently outperforming the S&P 500 by 2-to-1. Plus 60 stock screens based on the winning strategies of legendary investors like Warren Start your trial now and get immediate access to our market-beating Model Stock Portfolio (beating the S&P 500 2-to-1) plus 60 stock screens based on the strategies of legendary investors like Warren Buffett and Benjamin Graham. PLUS get unbiased investor education with our award-winning *AAII Journal*, our comprehensive ETF Guide and more – FREE for 30 days

Net margins vary from company to company, and, historically, certain ranges can be expected across industries. Therefore, it is important to compare the ROEs and other financial ratios of companies in similar lines of business, as similar business constraints exist in each distinct industry.

Asset turnover measures how much sales a company generates from each dollar of assets. It allows us to gauge management’s effectiveness at using assets to drive sales.

The majority of high-margin companies also tend to have low asset turnover. This is because a firm can only do a certain amount of business without incurring additional costs that would adversely impact profit margins. On the flip side, low-margin firms tend to have high asset turnover, as they rely on high sales volume to generate profits. By improving its asset management policies, a company can boost shareholder returns without necessarily increasing profit margins.

The final component of the three-step DuPont Model is the equity multiplier, which is a measure of financial leverage. It helps us examine how a firm uses debt to finance its assets. A higher equity multiplier indicates higher financial leverage, which means the company is relying more on debt to finance its assets.

A company can boost its return on equity by raising its equity multiplier (increasing the amount of debt it carries). If a company is already sufficiently levered, taking on additional debt increases the risks of not being able to fulfill its obligations to creditors and going bankrupt.

The three-step DuPont Model provides us with insights as to what is driving a company’s return on equity. We can see if a company is boosting its ROE by improving its profitability, by using its assets more efficiently, or by taking on additional leverage. However, companies that boost ROE by adding leverage will eventually reach a point where the cost of debt will diminish profit margins and decrease asset turnover.

This “shortcoming” with the three-step model led to the development of an expanded, five-step model of DuPont analysis, which breaks down the net profit margin even further to assess the impact of higher borrowing costs associated with increased leverage. If a company has a high cost of borrowing, its interest expense on more debt could offset the positive effects of increased leverage. In addition, interest expenses for most companies are tax-deductible, so the extended model considers interest charges and the company’s tax burden.

The extended five-step DuPont Model breaks return on equity down into five components:

When multiplied together, the pre-interest pretax margin, the interest burden ratio, and the tax efficiency ratio give us net profit margin (net income ÷ sales). Multiplying all five ratios together gives us return on equity.

While many financial websites provide return on equity for companies, it is more difficult to find the components of ROE. By analyzing the trends in these components, we can see which factors are driving profitability and identify areas requiring further investigation. Figures 1 and 2 show the calculations for the three- and five-step DuPont models for Buffalo Wild Wings (BWLD) and Cracker Barrel Old Country Store, Inc. (CBRL).

We begin by collecting the required data to calculate the various DuPont ratios. For our examples, we used AAII’s *Stock Investor Pro* fundamental stock screening and research database program for the raw financial statement data. However, you can use free websites such as SmartMoney.com, which provides 10 years of the annual income statement and balance sheet data you need for these calculations.

Once we have the financial statement data, we start with the three-step DuPont model ratios for Buffalo Wild Wings. Net profit margin is net income divided by sales, so in Cell B14 we enter this formula:

**=B8/B4**

Where,

We copy this across for cells C14:G14 to get the net profit margin for each of the last six fiscal years.

Next we calculate the asset turnover ratio by entering this formula in B15:

**=B4/((B9+C9)/2)**

Where,

Since we are using averages here, we need one extra year of balance sheet data—to calculate ROE for six years, we need seven years of financial statement data. Again, we copy this across for cells C15:G15.

The final calculation of the three-step DuPont Model is the equity multiplier. So we use this formula in cell B16:

**=((B9+C9)/2)/((B10+C10)/2)**

Where,

As before, we copy this formula across for cells C16:G16.

Now that we have the components for the three-step DuPont model, we can multiply them together to arrive at ROE. Therefore, in cell B17 we enter this formula:

**=B14*B15*B16**

To get the return on equity for the last six years, we copy the formula from B17 across cells C17:G17.

Once we have finished the three-step DuPont model, we can move on to the five-step model, beginning with the pre-interest pretax profit margin. To calculate this, we use this formula in cell B21:

**=B5/B4**

Where,

We copy the formula from B21 across cells C21:G21 to get this profitability ratio for each of the last six fiscal years.

The next component of the five-step DuPont model is asset turnover. Since we already calculated this for the three-step model, we simply use the results in cell B15 for cell B22, such that the formula is:

**= B15**

We copy the formula from B22 across cells C22:G22 to calculate asset turnover for each of the last six fiscal years.

The interest burden ratio is the next component of the five-step DuPont model, whereby we divide EBIT less interest expense by EBIT. This is the formula we use in cell B23:

**=(B5-B6)/B5**

Where,

We copy the formula from B23 across cells C23:G23 to get the interest burden ratio for each of the last six fiscal years. If a company does not pay any interest, its interest burden value will be 100%, since there is no interest expense to deduct from EBIT. The more a company pays in interest, the lower this value will be.

The tax efficiency ratio is the fourth factor of the five-step DuPont Model and this is the calculation we use in cell B24:

**=(1-(B7/(B5-B6)))**

Where,

The more taxes a company pays, the lower its tax efficiency ratio will be. We copy the formula from B24 across cells C24:G24 to get the tax efficiency ratio for each of the last six fiscal years.

We calculate the equity multiplier for the final factor of the five-step DuPont Model. Since we had also calculated this ratio in the three-step model, we use the formula from cell B16 for cell B25, such that:

**=B16**

We copy the formula from B25 across cells C25:G25 to get the equity multiplier for each of the last six fiscal years.

Once we have the components for the five-step DuPont Model, we can multiply them together using this formula in cell B26:

**=B21*B22*B23*B24*B25**

We, again, copy the formula from B26 across cells C26:G26 to arrive at return on equity for each of the last six fiscal years. As a check, we see that the ROE calculations in row 17 and row 26 match (with slight differences due to rounding).

In comparing the data in Figures 1 and 2 for Buffalo Wild Wings and Cracker Barrel Old Country Store, respectively, note that the companies are in similar lines of business, casual dining. As stated, when analyzing financial ratios, it is important to compare companies in similar industries; otherwise, the conclusions you draw based on your analysis will not be meaningful.

Looking at the trends in return on equity for both firms, we see that they have taken very different tracks over the last six years. Buffalo Wild Wings has seen its return on equity climb over the last six years from 9.8% in 2005 to 16.5% in 2010 (differences in the ROE calculations in rows 17 and 26 can be attributed to rounding).

Cracker Barrel, on the other hand, has seen wild fluctuations in its return on equity over the last six years, jumping from 16.3% in 2006 to 66.3% in 2008 and then tailing off over the next three years to 37.1% in 2011.

Looking at the components of ROE for both companies helps explain the changes in return on equity over time. Based on the three-step DuPont model, the biggest driver of ROE growth for Buffalo Wild Wings has been an increase in its net profit margin. Net margin rose from 4.2% in 2005 to 6.3% in 2010. While asset turnover and the equity multiplier have also risen for the company, net profit margin expansion has been the key contributor to the increase in ROE.

Examining Cracker Barrel’s three-step DuPont calculations, we see that net profit margin has slipped over the last six years from 4.3% to 3.5%. Counteracting this has been an increase in asset turnover from 1.38 to 1.87. Does this explain how the company’s return on equity has jumped from 16.3% in 2006 to 37.1% in 2011? No. For this we look to the equity multiplier, which has gone from 2.74 in 2006 to 5.66 in 2011 (down from a high of 13.10 in 2008). Further investigation reveals that during fiscal-year 2006 Cracker Barrel undertook a significant change in its capital structure. According to the company’s 2006 annual report, the change included establishing a new $1.25 billion credit facility and drawing on the facility to repurchase approximately 35% of the company’s outstanding shares.

Cracker Barrel provides an extreme example of why it is important to analyze the components of ROE. Had we looked at return on equity alone at the end of 2008, its amazing growth rate would perhaps have led us to think that Cracker Barrel was in a stronger position than it may have been in. In hindsight, 2006 may not have been the best time to borrow so heavily to repurchase shares, given the market collapse and economic downturn that took place in 2008.

The components of the five-step DuPont model provide additional insight into Cracker Barrel’s operations. Looking at the three factors that impact net margin—pre-interest pretax interest margin, interest burden and tax efficiency—we see that the added interest burden has had the biggest negative impact on net margin (the higher the number, the less the company is paying in interest expense). At the same time, the pre-interest pretax margin has also slipped over the last six years—an important fact, since this ratio is not impacted by interest and tax expenses (unlike net profit margin). While taking on additional leverage had a strong impact on Cracker Barrel’s return on equity, the added debt also had a negative impact on the company’s operating margin and led to a significant increase in the interest burden on the company.

It pays to invest in companies that generate profits more efficiently than their competitors. Return on equity is one financial metric that can be used to judge a company’s effectiveness at translating investor equity into profits.

However, as we have shown, relying solely on ROE means making decisions based on incomplete information, which can be risky. Breaking down return on equity into its components gives insights into the drivers of return on equity. We are then able to judge whether a company’s return on equity growth points to a strong future or if it is merely masking storm clouds on the horizon.

Click here to download spreadsheet.

→ Wayne A. Thorp, CFA is a vice president and the senior financial analyst at AAII and former editor of *Computerized Investing*. Follow him on Twitter at @WayneTAAII.

** Robert** from PA posted over 4 years ago:

Isn't the actual Excel spreadstreet available for viewing? Forgive me for my inorance. I'm new to this.

Txs

Bob

** Kevin** from IL posted over 4 years ago:

Is there a downloadable copy of the DuPont Three Step and Five Step ROE spreadsheet available? If so could you provide me with a link as to where I could get this so I don't have to reconstruct the spreadsheet from scratch myself.

Thanks for your help and assistance.

** Jean** from IL posted over 4 years ago:

A downloadable Excel spreadsheet is now available here. Look for the links underneath Figure 1 and Figure 2. -AAII

** Subramaniam** from NY posted over 4 years ago:

A very nice article that demonstrates in simple terms how to derive ROE and how it helps in making an investment decision wisely.

Thanks.

** Lowndes** from SC posted over 4 years ago:

Many thanks for spreadsheets.

** nick** from caringi posted over 2 years ago:

I tried downloading the PDF version of the deconstructing write up and I keep getting a message from Adobe that the file is corrupted... I would like to down load a useable version so I can peruse at my leisure.. How can I do this?

Sorry, you cannot add comments while on a mobile device or while printing.