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.
by Charley Kyd
Management reporting is all about communication.
Reporting Return On Equity (ROE) is a case in point.
ROE financial ratio is a key measure of financial health. But to
non-financial managers, the ROE can be difficult to understand, for two
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
(To see a full-size copy of this report, click on it with your mouse
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
The above report maps out the relationships between the income statement
and balance sheet as they come together to generate these three ratios
that combine to generate the ROE.
- Your Profit Margin. (Net Profit divided by Sales.)
- Your Assets-Turnover ratio. (Sales divided by Total Assets.)
- Your Leverage ratio. (Total Assets divided by beginning Net
Worth, which is approximately equal to one plus your Debt to Equity
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
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
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
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:
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
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
And your ROE, as the linked articles show, has a direct bearing on
your company's financial ability to grow.