1. Overview
1.1 Concept
The DuPont analysis method uses the relationship between several major financial ratios to comprehensively analyze the financial status of a company, to evaluate the company's profitability and the level of return on shareholders' equity, and to evaluate the performance of the company from a financial perspective.
The basic idea is to decompose the return on net assets of a company into the product of multiple financial ratios, which is helpful for in-depth analysis and comparison of business performance.
1.2 Expected effect
The financial status of a company in 2016 is shown in the figure below:
1.3 Implementation ideas
Return on net assets = Net profit margin on sales * Asset turnover * Equity multiplier
The return on net assets is affected by three types of factors:
| indicator | description |
|---|---|
| Net profit margin on sales = Net profit / Sales revenue | Show the profitability of the company |
| Asset turnover = Sales revenue / Total assets | Show the company's operational capabilities |
| Equity multiplier = Total assets / Net assets (equity) = 1/(1-Asset liability ratio) | Financial leverage: measured by the equity multiplier, indicating the capital structure of the enterprise |
2. Operation steps
Sample data:
Upload the sample data to FineBI.
2.1 Consolidated data table
The sample data gives two data tables of profit data and asset-liability data. But when performing DuPont analysis, the data in the two data tables need to be used for calculation at the same time. So the two data tables need to be merged.
Select the "Company profit data" table and select all fields, as shown in the figure below:
Select the "merge up and down" step, select the "Analysis of the company's assets and liabilities" table and click "OK", as shown in the figure below:
Name and save the self-service dataset.
2.2 Create Dashboard
Click "Dashboard" to create a new dashboard and name it, click "OK" to create a component, select "Self-service dataset 4" and click "OK", as shown in the following figure:
2.3 Calculate the net profit margin on sales
Select the "Current amount" indicator, click the drop-down, select "Copy", name it "Net profit" and select "Detail Filtering", as shown in the figure below:
Select the detailed filter condition as "Account name" in "Net profit", click "OK", as shown in the figure below:
Or "Account ID" in "500", as shown in the figure below:
In the same way, copy the "Current amount" indicator again, rename it to "Sales revenue", and perform "Detail Filtering", select the filter condition as "Account Name" in "Including: operating income", click "OK", as shown in the figure below:
Add a calculation indicator, name it "Net profit margin on sales", enter the formula: SUM_AGG (Net profit)/SUM_AGG (Sales revenue). Click "OK", as shown in the figure below:
2.4 Calculate asset turnover
The calculation method is the same as section 2.3.
Copy the "Closing mount" field, rename it to "Total assets" and set detailed filter conditions for total assets: "entry name" in "Total assets", or the Project ID belongs to "100", as shown in the following figure:
Add the calculation indicator again, name it and enter the formula: SUM_AGG (Sales revenue)/SUM_AGG (Total assets). Click "OK", as shown in the figure below:
2.5 Calculate the equity multiplier
1) First calculate the Asset liability ratio=total liabilities/total assets
Therefore, you need to create the "Total Liabilities" indicator, copy the "Closing amount", rename it to "Total liabilities" and set the detailed filter conditions as follows: "Project ID" in "20022099" (or use filter conditions: "Project name" in "Total liabilities"). As shown below:
Add the calculation indicator again, name it and enter the formula: SUM_AGG (Total liabilities)/SUM_AGG Ttotal assets). Click "OK", as shown in the figure below:
2) Add a calculation indicator, name and enter the formula: 1/(1-Asset liability ratio). Click "OK", as shown in the figure below:
2.6 Calculate the return on net assets
Return on net assets = Net profit margin on sales * Asset turnover * Equity multiplier
Add a calculation indicator, name it and enter the formula: Net profit margin on sales * Asset turnover * Equity multiplier. Click "OK", as shown in the figure below:
2.7 Create indicator card
Select "kpi indicator card", drag "Return on net assets" into the text box, and set the format, as shown in the figure below:
Create indicator cards in the same way for other indicators.
2.8 Effect view
See section 1.2 of this article for details.
