Home >  Excel Solutions > 

An Excel Tutorial:

Map Your Financial Health
With an Excel DuPont Chart

Your ratio of Return On Equity is a key indicator of financial health.
This report explains you can show the components of that ratio
in a unique Excel display.

Management reporting is all about communication. Reporting Return On Equity (ROE) is a case in point.

The ROE financial ratio is a key measure of financial health. But to non-financial managers, the ROE can be difficult to understand, for two reasons.

One reason is that people wonder what all the fuss is about. Bean counters create ratios faster than grandmothers bake cookies. So, what's the big deal about one more ratio?

Slightly over-simplified, your ROE compares directly with your growth rate in sales. If you grow faster than your ROE, you weaken your financial structure; if you grow more slowly than your ROE, you strengthen your financial structure.

Years ago, I wrote two columns for Inc Magazine about the ROE. The columns provide more background about this important measure. You can read them at: Weighing Your Debt Load and How Fast Is Too Fast? 

The other reason that using ROE can be difficult is that it's a top-level ratio that's affected by virtually every other measure of financial performance. That's the benefit offered by the Excel report shown above.

(To see a full-size copy of this report, click on it with your mouse pointer. To return to this page, click the Back button on your browser.)

I call this report the Enhanced DuPont Chart, because the DuPont Corporation relied on the underlying formula, and promoted it, for years. The formula that this chart relies on therefore became know as the DuPont formula.

The DuPont formula says that ROE is equal to the product of three ratios:

1. Your Profit Margin. (Net Profit divided by Sales.)
   
2. Your Assets-Turnover ratio. (Sales divided by Total Assets.)
   
3. Your Leverage ratio. (Total Assets divided by beginning Net Worth, which is approximately equal to one plus your Debt to Equity ratio.)

How to Create a DuPont Report in Excel

Technically, this report is very simple to create. It consists only of a few values and ratios, formatted in an unusual way.

In actual practice, this report gets its data from an Excel-friendly OLAP. The article Map Your Financial Health With an Excel-Friendly OLAP explains the process

This section of the report illustrates the general approach. Let's look at the section in some detail.

Setting Up the Numbers

The report workbook consists of two sheets. In the first sheet, I set up a summarized Income Statement and Balance Sheet, along with a cell to contain the value of the beginning Net Worth. And then,  I referenced these values in the report sheet illustrated on this page.

Your Income Statement must display the twelve most-recent months of performance. Therefore, if the current month is September, 2004, the Income Statement is for the period October, 2003, through this month. And the beginning Net Worth is the value for September, 2003.

(If you use an Excel-friendly OLAP, you can easily create a formula to return values for the 12 most-recent months of P&L performance. Otherwise, coming up with these values each month could be the most difficult step you will take to create this report.)

The expanded report, which you can see by clicking on the first figure on this page, shows the relationships between the various numbers and subtotals. To illustrate, cell N34 shows that Sales is divided by Total Assets to obtain the ratio that follows.

Formatting the Report

Use borders to create most of the lines in the report. The arrows and the brace (the top of which is shown in cell J40) are drawing objects.

Unlike the cells with numbers and math symbols, the background cells have a light shading. I used a simple trick to apply this formatting easily.

To add the background shading, I applied the shading to the entire report, white cells and all. I used the Formula Go To command to go to a name that referenced all white cells. Then I made these cells white again by removing the shading.

The key step in this procedure is to set up a name that references all white cells. This requires several steps, because there are more white cells than one Excel name can reference directly.

To get around this limitation you need to define several utility names, each of which references a small number of white cells. Then you define a summary name that references all of the utility names.

For example, you might first define the name White1 to reference the white cells shown in columns D and F.

To do so, select cell D31; hold down the Ctrl key; select cell D34, cell D37, and so on. Then define this discontiguous range as White1.

Next you could define the name White2 to reference the remaining white cells in this figure. To check your work so far, you would define the name AllWhiteCells as:

=White1, White2

After you define AllWhiteCells, press the F5 function key or choose Edit, Go To. In the Reference box of the Go To dialog, enter AllWhiteCells. Then press Enter. (Note that you actually must type AllWhiteCells in the Reference box, because this name won't be displayed in the Go To list box.) If you have defined the names correctly, Excel will select all white cells that you have defined in the White1 and White2 names.

Use the same approach to define the other white cells in other range names. You can test your work by adding each new name to the list defined by the AllWhiteCells name.

Summing Up

Depending on the financial sophistication of your audience, this report could be quite useful. It could help you to illustrate how a reduction in inventory, or an increase in profit margin can improve your ROE.

And your ROE, as the linked articles show, has a direct bearing on your company's financial ability to grow.