For business users of Microsoft Excel Free guides and templates

Financial Planning

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.

DuPont Dashboard in ExcelThe 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. 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 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.)
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.

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.

DuPont chart assets section from Excel

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 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.

DuPont chart assets section from Excel

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:

=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.

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 more, see Excel Training, Coaching, and Consulting.


Tags: #excel, #chart, #dupont, #roe

DuPont Chart from Excel--A nearly full-size version

Charley's Swipe File #71

Weighing Your Debt Load

How Fast Is Too Fast?

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.