Analytics Perspective

Implementing the DuPont Method

Part I: Ask the right questions

A significant portion of developing your analytical mindset happens before you analyze data, as you think through the best way to analyze the data to provide the most relevant insights. What are the right questions to ask?

In this section, you will first think through the DuPont Method in more detail so you understand some of the business context.Assume you will be preparing this analysis for a stakeholder who is an 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.

Required

1.Describe whether you would prefer a high value or a low value for each ratio, independent of the other ratios.

-Return on equity:

-Profit margin ratio:

-Asset turnover ratio:

-Financial leverage ratio:

-

2.The profit margin ratio is the only component of ROE that can be negative (except in the relatively rare case of negative shareholder equity).Describe how the interpretation of the Asset Turnover Ratio and the Financial Leverage Ratio change based on whether the Profit Margin Ratio is positive or negative.

3.How can a company improve each ratio?Make sure to discuss how changes in either the numerator or denominator can improve the ratio.For each ratio, do you think it is "better" to focus on improving the numerator or denominator?

-Return on equity:

-Profit margin ratio:

-Asset turnover ratio:

-Financial leverage ratio

Analytics Perspective

Implementing the DuPont Method

Part II: Extract, transform and load the data

The data for this case was extracted from publicly-available company financial statements posted online.The extraction of the data from the online sources was performed for you and the data has been loaded into an Excel file.Most of the transformation work has been done for you as well.You will need to compute the ratios involved in the DuPont Method using Excel.[1]If you use pivot tables, the work will be much easier, but the analysis can be made without using them.

Required

Please answer these questions.

1.What are the combined total assets of all companies for each of the three years, 2013, 2014 and 2015?

2013:

2014:

2015:

2.How many different companies are listed in the dataset?

3.How many different companies are there in each industry?

-Capital goods

-

-Consumer services

-

-Finance

-

-Public utilities

-

-Technology

-

-Transportation

4.What are the total sales for each industry in 2013?

-Capital goods

-

-Consumer services

-

-Finance

-

-Public utilities

-

-Technology

-

-Transportation

-

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 III: Apply appropriate data analytic techniques

Required

Questions about industries

1.What were the median ratios for fiscal years 2014 and 2015 for each industry? What are the highest and lowest performing industries for each of the four performance indicators in each of those years? For measuring performance, use the median industry performance to control for the potentially large effects of outliers.(You may complete the following table, or attach screenshots from your worksheet.)

Median Performance Indicators by Industry for Fiscal Years 2014 and 2015

Industry

Return on Equity

Profit Margin Ratio

Asset Turnover Ratio

Financial Leverage Ratio

Capital Goods

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

Consumer Services

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

Finance

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

Public Utilities

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

Technology

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

Transportation

2014:

2015:

2014:

2015:

2014:

2015:

2014:

2015:

2.Which industry has the greatest improvement in median ROE from 2014 to 2015?What are the best explanations, based on the ratios in the DuPont Method, for why the ROE has improved in that industry?

3.Assume you want to invest in one of the industries included in the dataset (i.e., buy stock in all companies in one industry).Based upon your analysis, which industries do you think would offer the highest and lowest return in 2016?Which industries would provide the safest and riskiest returns in 2016?Does removing outliers (companies with performance indicators not typical of the industry) change your opinion?

Questions about individual companies

4.What companies have the best ROE within each industry for 2015?Sort the data so you can see the companies listed from highest to lowest ROE.What might cause the differences in ROE for these different companies?

-Capital goods

-

-Consumer services

-

-Finance

-

-Public utilities

-

-Technology

-

-Transportation

5.Companies that have negative profit margins but are increasing their asset turnover ratio could be accused of "accelerating into a brick wall" (i.e., they are getting better at losing money).What are te names of the three companies in 2015 that have a negative profit margin, and also have the highest asset turnover ratio?

6.Based upon your analysis, which industry would you recommend that your investor invest in?Within that industry, which individual company should she invest in?Why?

[1] 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.