Map Your Financial Health With an Excel DuPont Dashboard
Your Return On Equity ratio is a key indicator of financial health. This report
lets you show the components of that ratio in a unique Excel display.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
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
|Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more.
(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. And you
can get a working
version of the DuPont Dashboard here.)
I call this report the DuPont Dashboard, 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 Dashboard in Excel
Technically, this dashboard is easy to create. It consists only
of a few values and ratios, formatted in an unusual way.
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 three sheets. In the Data sheet, I set
up a summarized Income Statement and Balance Sheet with several years of
monthly data. In the Control sheet, I set up the Report Date and few
other settings. And in the Report sheet, I referenced those values.
The expanded DuPont Dashboard, which you can see by clicking on
the link or on the first
figure at the top of this article, shows the relationships between the various numbers
and subtotals. To illustrate, cell N27 shows that Sales (during the
most-recent 12 months) 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 are
the cent (¢) sign with the Wingdings 3 font applied.
To add the background blue 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 D22; hold down the Ctrl key; select cell D25,
cell D28, 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.
Take Your Next Steps
First, you can get
a working version of the DuPont Dashboard here.
Second, if you're looking for additional help with
this topic, I can help you in three ways. To learn
Excel Training, Coaching, and Consulting.
Tags: #excel, #chart, #dupont, #roe