Can i post the excel sheet in a seperate question?

Analytics mindset

Implementing the DuPont Method

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. To gather the most relevant insights, you must start by asking the right questions of the data. In this section, you will first think through the DuPont Method in more detail so you understand some of the business context. From there, you will identify questions for the data that will provide insights to your stakeholder. Assume your stakeholder 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 want 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 ratio that makes up ROE that can be negative (except in relatively rare cases). 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?
4. Before you analyze the data, it is helpful to develop an expectation of what you think you might see. What industry do you believe will have the highest and lowest values for the 2015 fiscal year for each item below? Why?
   • Return on equity
   • Profit margin ratio
   • Asset turnover ratio
   • Financial leverage ratio
5. What questions do you want to ask of the data? Think about relevant insights you want to gain for your stakeholder and identify at least five questions that you want to answer.

Analytics mindset

Implementing the DuPont Method

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 the appropriate analysis tool (e.g., you will need to compute the ratios involved in the DuPont Method).[1] You should load the data into an analytics tool for analysiswe will be using Tableau in this case.

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 correctly 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 correctly (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 2013 (do not round your answers)?
   • Capital goods
   • Finance
   • Public utilities
   • Transportation
   • Consumer tervices
   • 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 III: Apply appropriate data analytic techniques

You are now ready to analyze the data. Previously, you identified five questions that you thought would provide relevant insights of the data. Before answering those questions, 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 2015 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. Compare your answers to what you predicted in Part I. What did you predict correctly and where were you incorrect? Why do you think you were incorrect for some industries?

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 2013 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). Which industries do you think will offer the highest and lowest return in 2016? Which industries will provide the safest and riskiest return in 2016? Does removing outliers 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 observations do you make about differences in ROE for the different companies?

5. Companies that have negative profit margins but are increasing their asset turnover ratio are accelerating into a brick wall (i.e., they are getting better at losing money). What three companies in 2015 are the worst in that they have a negative profit margin and the highest asset turnover ratio? Give the name of the company.

6. Create a dashboard that allows you to evaluate how a companys ratios change between 2014 and 2015. The dashboard should show the ratios for 2014 and 2015, the percentage change from one year to the next, and the percentage change for the companys industry. Choose three different companies and discuss what you learn about the company based on these metrics.

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