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.
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:
- 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 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.
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.
So, for example, the formula in cell D22 just references the cell in the summarized balance sheet.
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.
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.
