DuPont Analysis- Tableau

Use Tableau to create a DuPont Analysis Model

Introduction

DuPont Analysis

DuPont Analysis is a method of breaking down ROE into three components: Profit Margin, Asset Turnover, Financial Leverage. This analysis enables the company itself or investors to understand the source of return by comparing it with companies in similar industries.

The formula for the DuPont Analysis:

Return on Equity = Profit Margin × Asset Turnover × Financial Leverage

This can be written as follows:

Profit margin ratio: The amount of net income that is generated for each dollar of sales.

Asset turnover ratio: The number of sales generated for each dollar of assets the company owns.

Financial Leverage: The amount of debt that a company utilizes to finance its operations, as compared with the amount of equity.

Dataset:

We will use the financial statement data for 180 companies in 6 different industries from 2013 to 2015. The data was extracted from company financial statements posted online from credible sources.

Understand the Industry

Industry Average: DuPont Analysis Ratio

This histogram below enables users to understand each performance indicator in the DuPont method by industry.

DuPont Analysis Ratio by industry in 2015

The “year” is used as a filter so that the users can easily get the information in a specific year by clicking the checkbox.

Use the “filter” to select the year

Industry Average: ROE Growth

To understand the trend, the line graph is created to illustrate the ROE from 2013 to 2015 in each industry. Users can clearly identify the growth in each industry, as well as which industry has the higher or lower ROE of all time.

Compare each company’s performance with the Industry Average

Now, this interactive dashboard enables users to compare any company’s performance with the industry it belongs to. Through this DuPont analysis dashboard, users can easily understand:

1. Change in ROE
2. What financial activities contribute the most to the changes in ROE

Interactive Dashboard of DuPont Analysis

Take Apple Inc. for example. Apple performed very well compared to the industry average from 2013 to 2015. It shows an ROE much higher than the industry average.

Looking into the DuPont Ratio for more detail, we can discover that the main reason why Apple showed an ROE much higher than the industry average is because of its profit margin ratio. In 2015, Apple has a profit margin of 23.09% whereas the industry average is only -1.82%.

DuPont Anaylsis: Apple

Steps

Here’s how to build the view above.

1. Create the Calculation Fields

Used FIXED Level of Detail Expressions

2. Dual Axis

Use “ROE” and “ROE of chosen industry” as dual axis

3. Synchronize Axis

4. Use “Company Name” as a filter & Select “Show Filter”

5. Select “Single Value (dropdown)”

Repeat the above steps to create dashboards for other ratios in DuPont Method.