Analytics mindset
Implementing the DuPont Method
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 cu
ent Dow Jones Industrial Index. Started 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 fa
ics. 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.
Case overview
For this case, you will implement an analytics mindset by comparing and contrasting different companies within different industries using the accounting ratios from the DuPont method. In Part I, you will load the data into Tableau and perform some simple tests to verify that you have loaded it co
ectly. In Part II, you will analyze the data. Finally, in Part III, you will leverage the power of data visualization to present the data so that it can be easily interpreted.
Data
In the accompanying spreadsheet (Analytics_mindset_case_studies_DuPont.xls), you have financial statement data for approximately 30 companies for each of six different industry groups (total sample size of almost 180 companies). The sheets contain financial statement information for fiscal years XXXXXXXXXX, 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 name they appear in the spreadsheet with a small explanation also provided.
· Industry: One of six industry groupings as defined by Nasdaq.com.[footnoteRef:1] Industries included in the sample are capital goods, consumer services, finance, public utilities, technology, and transportation. [1: See http:
www.nasdaq.com/screening/companies-by-industry.aspx for industry groupings. ]
· 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 refe
ed to as total sales.
· Ticker: The code 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 also can be called stockholder’s equity.
· Year: The fiscal year being reported on the financial statements. For example, a year of 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 occu
ed during the fiscal year.
Analytics mindset case studies – DuPont 1
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No XXXXXXXXXX161US
Analytics mindset case studies – DuPont 2
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No XXXXXXXXXX161US
Analytics mindset
Implementing the DuPont Method
Part I: 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 the appropriate analysis tool (e.g., you will need to compute the ratios involved in the DuPont Method).[footnoteRef:2] You should load the data into an analytics tool for analysis—we will be using Tableau in this case. [2: Realize for many situations, extracting, transforming, and loading (ETL) the data can account for over 80 percent of the time in the entire data analysis process. This case simplifies this process so you can focus on developing other aspects of an analytics mindset. Also, the process does not always strictly follow the ETL format. Some transformation can happen before or after data is loaded into a tool for analysis. ]
Hint: when loading the data into Tableau, you need data from both the income statement and the balance sheet tabs. Make sure that you link the income and balance sheet data co
ectly by matching the data on both ticker symbol and year.
Required
When you are finished loading the data, you should answer these simple questions to make sure you loaded the data co
ectly (by testing for completeness and accuracy).
1. What are the combined total assets of all companies for all years?
2. How many different companies are listed in the dataset?
3. How many different companies are there in each industry?
· Capital goods
· Finance
· Public utilities
· Transportation
· Consumer services
· Technology
4. What are the total sales for each industry in 2017 (do not round your answers)?
· Capital goods
· Finance
· Public utilities
· Transportation
· Consumer services
· Technology
5. What company had the most sales over the three-year period and what was the total amount of those sales?
Analytics mindset
Implementing the DuPont Method
Part II: Apply appropriate data analytic techniques
You are now ready to analyze the data. Please answer the questions that are listed below. For each question:
· Identify the type of the analysis to perform
· Identify which data elements are needed to perform the analysis
· Create the analysis using Tableau
Required
Questions about industries
1. Which industries for fiscal year 2019 are the highest and lowest performing for each of the following performance indicators? For measuring performance, use the median industry performance to control for the potentially large effects of outliers.
Item
Highest
Lowest
Return on equity
Profit margin ratio
Asset turnover ratio
Financial leverage ratio
2. Which industry has seen the greatest improvement in median ROE from 2017 to 2019? What are the best explanations, based on the ratios in the DuPont Method, for why the ROE has improved in that industry?
Questions about individual companies
3. What companies have the best ROE within each industry for 2019? Sort the data so you can see the companies listed from highest to lowest ROE. What observations do you make about differences in ROE for the different companies?
4. Companies that have negative profit margins but are increasing their asset turnover ratio are “accelerating into a
ick wall” (i.e., they are getting better at losing money). What three companies in 2019 are the worst in that they have a negative profit margin and the highest asset turnover ratio? Give the name of the company.
Analytics mindset
Implementing the DuPont Method
Part III: Interpret and share the results with stakeholders
Now that you have completed your analysis, for each question, interpret your analysis and prepare a Tableau story including a visual dashboard or graphic with a concise written analysis of your interpretation of the analytic results. When considering your visualization, make sure to think about what type of visualization will provide the clearest and most compelling format for stakeholders to understand what you want to convey.
Analytics mindset case studies – DuPont 1
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No XXXXXXXXXX161US
Balance Sheet Data
Ticker Name Industry Year Financial Statement Cash Short Term Investment Cash Cash & Short Term Investments Accounts Receivables Inventories Other Cu
ent Assets Total Cu
ent Assets Gross Property, Plant and Equipment Total Depreciation Intangible Assets Total Other Assets Total Assets Accounts Payable Short Term Debt Accrued Expenses (Liability) Other Cu
ent Liabilities Other Liabilities Long Term Debt Defe
ed Taxes (Liability) Other Liabilities Total Liabilities Minority Interest (Equity) Prefe
ed Stock (Equity) Common Equity Total Shareholder Equity Common Shares Outstanding Prepaid Expenses Cash Due From Banks (Asset)
AAL American Airlines Group Inc. Transportation 2017 Balance Sheet 2,200,000,000 660,000,000 10,300,000,000 1,600,000,000 1,000,000,000 1,500,000,000 14,300,000,000 30,400,000,000 (11,100,000,000) 6,400,000,000 8,700,000,000 42,300,000,000 1,400,000,000 1,400,000,000 2,500,000,000 9,800,000,000 13,800,000,000 19,200,000,000 - 0 5,300,000,000 45,000,000,000 - 0 - 0 (2,700,000,000) (2,700,000,000) 261,100,000 - 0 - 0
AAL American Airlines Group Inc. Transportation 2018 Balance Sheet 2,700,000,000 (146,000,000) 8,100,000,000 1,800,000,000 1,000,000,000 1,300,000,000 12,100,000,000 35,300,000,000 (12,300,000,000) 6,300,000,000 8,600,000,000 43,800,000,000 1,400,000,000 1,700,000,000 2,100,000,000 9,200,000,000 13,400,000,000 16,500,000,000 - 0 3,400,000,000 41,800,000,000 - 0 - 0 2,000,000,000 2,000,000,000 697,500,000 - 0 - 0
AAL American Airlines Group Inc. Transportation 2019 Balance Sheet 2,100,000,000 (683,000,000) 6,900,000,000 1,400,000,000 863,000,000 748,000,000 10,000,000,000 40,700,000,000 (13,100,000,000) 6,300,000,000 8,400,000,000 48,400,000,000 1,600,000,000 2,200,000,000 2,300,000,000 8,600,000,000 13,600,000,000 18,500,000,000 (2,500,000,000) 2,500,000,000 42,800,000,000 - 0 - 0 5,600,000,000 5,600,000,000 624,600,000 - 0 - 0
AAPL Apple Inc. Technology 2017 Balance Sheet 8,700,000,000 3,500,000,000 40,500,000,000 20,600,000,000 1,800,000,000 10,300,000,000 73,300,000,000 28,500,000,000 (11,900,000,000) 5,800,000,000 10,900,000,000 207,000,000,000 22,400,000,000 - 0 5,500,000,000 20,300,000,000 43,700,000,000 17,000,000,000 (16,500,000,000) 3,700,000,000 83,500,000,000 - 0 - 0 123,500,000,000 123,500,000,000 6,300,000,000 - 0
AAPL Apple Inc. Technology 2018 Balance Sheet 10,200,000,000 (415,000,000) 25,100,000,000 27,200,000,000 2,100,000,000 14,100,000,000 68,500,000,000 39,000,000,000 (18,400,000,000) 8,800,000,000 12,500,000,000 231,800,000,000 30,200,000,000 6,300,000,000 7,500,000,000 25