Background:

A key purpose of financial statements is to provide useful information to decision makers, including investors. Investors can use the information contained in financial statements to better understand company performance so they can make better investment decisions. One introductory framework that has been especially useful in understanding company performance is the Dupont Method (sometimes called Dupont Analysis, Dupont Model or Dupont Framework). To understand the Dupont Method, it helps to use an analytics mindset. For this case, we (1) define an analytics mindset, (2) discuss the history of and ratios involved in the Dupont Method and (3) use the Dupont Method to perform data analytics and data visualization techniques.

Implementing an analytics mindset

Having and using an analytics mindset is critical in accounting and business. This case focuses on developing all aspects of your analytics mindset. As a review, an analytics mindset is the ability to:

Ask the right questions.

Extract, transform and load relevant data into a data analysis tool.

Apply appropriate data analytic techniques.

Interpret and share the results with stakeholders.

History and model

The Dupont Method has an interesting history. E. I. du Pont de Nemours and Company, or more frequently called Dupont, is the oldest stock in the current Dow Jones Industrial Index. Established in July 1802, the company originally focused on producing gunpowder. Today, the company makes chemicals which are in everything from food ingredients and dietary supplements to pharmaceuticals and fabrics. In addition to developing chemicals, the company has been a pioneer with respect to management accounting systems. The company developed the original accounting ratio of return on equity (ROE) and then in 1912, Donaldson Brown decomposed ROE into additional ratios.

ROE measures a company’s profitability as a percentage of shareholder’s equity (i.e., how profitable a company can be using shareholders’ investments.) If ROE is unsatisfactory, the Dupont analysis can identify the aspect of the business that is underperforming. Dupont used this formula for managing its business. In 1914 Dupont invested in General Motors, and using the same basic management accounting formulas led that company to become the world’s largest automobile company.

In 1957, Dupont divested its ownership in General Motors because of antitrust laws. After having been highly useful for both General Motors and Dupont, the basic Dupont Method has been extended and used by many to understand investing and managing businesses.

The Dupont Method has evolved into the following formula:

Return on equity = Profit margin ratio * asset turnover ratio * financial leverage ratio

This can be written as follows:

Return on equity: This represents the amount of net income that is generated for each dollar of shareholder’s equity. It can be interpreted as the amount of net income generated for each dollar of value that a shareholder owns of the company. This number can be either negative or positive.

Profit margin ratio: This represents the amount of net income that is generated for each dollar of sales. It can be interpreted as the percentage of each dollar of sales that the company retains as earnings. Since net income can be negative, this ratio can be either negative or positive.

Asset turnover ratio: This represents the amount of sales that is generated for each dollar of assets the company owns. This is often interpreted as the efficiency of the company—how many sales it can generate given the assets it owns. Except in very unusual circumstances, this ratio is positive.

Financial leverage ratio: This represents the amount of assets that is financed by shareholders, as opposed to debt holders. Except in very unusual circumstances, this ratio is positive.

For this case, you will implement an analytics mindset by comparing different companies within different industries using the accounting ratios from the Dupont method.

In Part I, you will gain an understanding of a few accounting ratios, which will help you to develop the right questions about the companies you are analyzing. In Part II, you will load the data into Power BI and build a few visualizations.

Data

In the accompanying spreadsheet (Dupont – Data – Mini 4 - 2025.XLSX), you have financial statement data for several companies for each of six different industry groups. The sheets contain financial statement information for fiscal years 2017-2019, inclusive. These companies are all publicly traded on the NASDAQ stock exchange and range in size from some of the largest to the smallest in their respective industry groupings.

For this case, you will not be using all data items. The items you are most likely to use are listed by the field name as they appear in the spreadsheet with a small explanation provided below. Note that the field names may be different than what the Dupont ratios use, by name that is. (“Sales” is not in the dataset, but “Net Revenue” is and this field should be used for the calculation.)

Industry: One of six industry groupings as defined by Nasdaq.com. Industries included in the sample are capital goods, consumer services, finance, public utilities, technology, and transportation.

Name: The name of the company for each line of data.

Net income: The bottom-line number on the income statement. This is the final net income number of the company for the fiscal year.

Net revenue: The top line number on the income statement. This represents total revenues (less a few items that you can ignore for this case) earned by the company in the fiscal year. This is also referred to as total sales.

Ticker: The code is used to identify each company on the NASDAQ stock exchange. Each company has their own unique ticker symbol.

Total assets: The total assets of a company at the end of the fiscal year. This number appears on the balance sheet.

Total shareholder equity: The total shareholder equity of a company at the end of the fiscal year. This number appears on the balance sheet and can be called stockholder’s equity.

Year: The fiscal year being reported on the financial statements. For example, the year 2019, means the balance sheet of the company is as of the last day of their fiscal year in 2019 (usually December 31st) and the income statement for all transactions that occurred during the fiscal year.

Part I: Ask the right questions

A significant portion of developing your analytical mindset happens before you analyze data. While you already have a fundamental understanding of the Dupont Method and are aware of the data elements available to you, you have not yet determined the best way to analyze the data to provide the most relevant insights.

Assume your stakeholder is a 2020 investor whose objective is to make some quality investments in the near future based on the performance of the companies in this data set.

Your stakeholder is interested in both an industry recommendation and a company recommendation.

Before I get to a few possible questions, you should understand whether you want a high value or a low value for each ratio, independent of the other ratios, for purposes of evaluating the strength of a company?

Return on equity: high. A high value suggests that the company is generating more net income for each dollar of equity invested in the company. That means each shareholder is earning a greater return on their investment.

Profit margin ratio: high. The higher the value, the more total revenues are being converted into net income. Realize that this number can be negative, which suggests the company is losing money for each additional sale it makes.

Asset turnover ratio: high. In general, this ratio measures how efficient a company is with its assets. If a company has a higher value, it can generate more sales because of its level of assets.

Financial leverage ratio: It depends. There is no clear “best” value for the financial leverage ratio because this ratio measures the amount of assets financed with the shareholders’ equity. What can be said is that higher values of this ratio suggest the shareholders own a smaller percentage of the company. The implication is that a higher value exacerbates the effects of the asset turnover ratio and the profit margin ratio of the overall return on equity ratio for equity shareholders. So, if the profit margin and asset turnover ratios are positive, a higher value is beneficial; however, if the profit margin and asset turnover are poor, then a higher value is detrimental to the equity shareholders’ interests.

Please note that if the profit margin ratio is positive, the descriptions above are valid. If the profit margin ratio is negative, then the interpretation of the asset turnover ratio and financial leverage ratio flips. That is, with a negative profit margin, being more efficient with your assets (i.e., generating more sales per asset) is not positive because the company is getting better at losing money. Thus, having a high asset turnover ratio when the profit margin ratio is negative is a bad thing. Similarly, if the profit margin ratio is negative, having a high financial leverage ratio magnifies the problem for shareholders. That is, the amount of the return to equity holders is less because the high leverage causes the return on equity to go down faster because of the negative profit margin ratio.

Finding the “right” questions to ask is difficult. The better the question, the better your analysis. Remember that nearly anyone can develop the table/chart, it’s the insight that makes the difference. Here are a few questions that might help you get in the right frame of mind.

Would an investor be able to make an informed decision with the data we have, including and especially the change in ratios from period to period OR would additional data such as economic trends and forecasts, correlations to GDP and others be more practical?

With respect to the financial leverage ratio, are there business cycles that the companies or these industries face based on aging equipment, technology improvements, favorable borrowing rates, etc. that will affect the ratios as we compare industries?

How do capitalized versus expensed costs affect the industries and how then does this translate to the difference in ratios by industry? Is there some factor that we need to include to smooth these out so we can do a better comparison?

Does lack of competition help the public utilities or other industries and, in turn, affect the ratios?

How should outliers best be handled, or would these be more indicative of opportunities or risks the companies might encounter?

Part II: Extract, transform and load the data (the ETL process)

The data for this case was extracted from company financial statements posted online from credible sources. The extraction of the data from the online sources was performed for you and the data has been loaded into the Excel file. That is, you can assume that the web scrapper accurately and completely extracted the information and loaded it into Excel. Most of the transformation work has been done for you as well. You will be required to do some transformation to analyze the data once it has been loaded into Power BI (e.g., you will need to compute the ratios involved in the Dupont Method).

Be Careful: when loading the data into Power BI, you need data from both the income statement and the balance sheet tabs. Make sure that you link the income and balance sheet data correctly. You should note just load the Income Statement data into the Balance Sheet or vice versa. If you are missing data from, say, the Income Statement, but have the Balance Sheet, you won’t be able to use that given company’s annual records. Also, if you have data errors, replace these with a zero, same with any nulls.

Apply appropriate data analytic techniques

You are now ready to analyze the data. Answer the questions listed below. For each question, prepare a worksheet(s)/dashboard(s) and label accordingly. Make sure to label your worksheets that are addressing the given question (something much better than “Q1”).

Questions - Overview

Report the four Dupont Method ratios by industry for each year (with a single-selection slicer) For measurement, use the median industry performance to control the potentially large effects of outliers. Use an appropriate color scheme. (You are best to use the simple calculation for each ratio. For example, ROE is simply: Net Income/Total Shareholder Equity. Use Net Revenues in place of Sales for those ratios that use Sales.

Report the top 3 companies by Median ROE for each Industry with a Year slicer.

Using a Box & Whiskers chart, report the median ROE by Industry with a multi-selector slicer for industry and a single-selector slicer for year. Include an enhanced Tooltip Report that will help understand the component parts of the ratio.

As necessary for your analysis, add additional charts as you see fit.

On a separate page, write a recommendation for the client noting the best industry and company in the industry in which to invest for the future. Your recommendation should include the rationale for the selections offered.

Your visual report should include a cover page with the team members’ names. Color and background schemes should be consistent.