Dupont Analysis

Concept

DuPont analysis uses the relationship between key financial ratios to comprehensively analyze the financial condition of a company, which can evaluate a company's profitability and Rate of Return on Common Stockholders’ Equity (ROE) and assess its performance financially.

The idea is to break ROE into a product of multiple financial ratios, which helps to analyze and compare the business performance of a company.

Demonstration

For details about the dashboard, see DuPont Analysis. You can click to save and view the dashboard.

The financial condition of a company in 2016 is shown in the following figure.

Design Idea

ROE = Sales net profit margin * Asset turnover * Equity multiplier

ROE is affected by three factors.

Example data: DuPont Analysis Demo.zip

Processing Data

Delete the spaces before Project name in the data table and clean the data.

1. Create an analysis subject and add the example data.

2. Select Profit Data, click Formula Column, and name it Final project name. Enter the formula IF(FIND(" ",Project name)=1,INDEXOF(SPLIT(Project name," "),LEN(SPLIT(Project name," "))-1),Project name), and click OK and Save and Update.

Repeat the above steps in Asset Liability Data.

Merging Data Tables

You need to merge the two data tables Profit Data and Asset Liability Data because DuPont analysis needs data of both tables.

1. Select the table Profit Data you have processed in section "Processing Data". Select fields except Project ID and Project name.

2. Click Union All, select Asset Liability Analysis you have processed in section "Processing Data", and click OK.

Rename Profit Data as Asset Liability-Profit Data and click Save and Update.

Creating a Component

Click Component in the bottom left corner to create a component and select Asset Liability-Profit Data.

Calculating Net Profit Margin

Select the indicator Current amount, click , select Copy, and rename the copied indicator Net profit. Select Detail Filtering, and the condition is Final project name IN 5. Net profit or Project ID Equal to 500. Click OK.

Copy the indicator Current amount and rename it. Select Detail Filtering, and the condition is Final project name IN Including: operating income.

Choose > Add Calculation Indicator. Rename the filed and enter the formula SUM_AGG(Net Profit)/SUM_AGG(Sales Revenue). Click OK.

Calculating Asset Turnover

The calculation methods are the same as section "Calculating Net Profit Margin".

Copy the field Final amount and rename it. Set Detail Filtering for Total Asset: Final project name IN Total assets or Project ID IN 100.

Add a calculation indicator, rename it, and enter the formula SUM_AGG(Sales Revenue)/SUM_AGG(Total Assets). Click OK.

Calculating Equity Multiplier

1. Calculate Asset liability ratio. Asset liability ratio = Total liability / Total assets

You need to create the indicator Total liability. Copy the field Final amount, rename it Total Liability, and set Detail Filtering to Project ID Equal to 200220999 or Final Project Name IN Total Liabilities.

Add a calculation indicator, rename it, and enter the formula SUM_AGG(Total Liability)/SUM_AGG(Total Assets). Click OK.

2. Add a calculation indicator, rename it, and enter the formula 1 / (1 - Asset liability ratio). Click OK.

Calculating ROE

ROE= Sales net profit margin * Asset turnover * Equity multiplier

Add a calculation indicator, rename it, and enter the formula Sales net profit margin * Asset turnover * Equity multiplier. Click OK.

Creating a KPI Indicator Card

Select KPI Indicator Card, drag the field ROE into Text in Graphic Properties.

Repeat to create KPI indicator cards for other indicators.

Demonstration

For details, see section "Demonstration".