Microsoft Word - DuPontCaseStudy_Part 2and3 Analytics mindset case studies – DuPont © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No XXXXXXXXXX161US Analytics mindset Implementing...

Sine I have been using this web ,none of your expert work is good. please be honest, if you can do the work do not sign for it.


Microsoft Word - DuPontCaseStudy_Part 2and3 Analytics mindset case studies – DuPont © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02050-161US   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 scraper 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).2  You should load the data into Tableau. 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, use Tableau visualizations to answer these simple questions. Each question should be answered with its own visualization which should be saved to its own worksheet and labeled “Part II – Q#” with # representing the number from below. In addition to the Tableau visualizations, provide a quick summary of the answers on a separate Word document with the question number and answer (include individual industries when appropriate). Check your answers against the check figures provided on BB to make sure you loaded the data correctly. 1. What are the combined total assets of all companies for all years? 2. How many different companies are listed in the dataset? (Use count – distinct) 3. How many different companies are there in each industry? (Use count – distinct) – Capital goods – Finance – Public utilities – Transportation – Consumer services – Technology 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.  Analytics mindset case studies – DuPont © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02050-161US   4. What are the total sales for each industry in 2013 (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? Part III: Apply appropriate data analytic techniques You are now ready to analyze the data by answering the questions that are listed below. For each question: ► Identify the type of analysis to perform ► Identify which data elements are needed to perform the analysis ► Create the analysis using Tableau Required Using visualizations in Tableau, determine answers to each of the following questions. Each question should be answered with its own visualization which should be saved to its own worksheet/dashboard and  labeled “Part III – Q#” with # representing the number from below; some questions may need more than  one worksheet. In addition to the Tableau visualizations, provide a summary of your answers on a separate  Word document labeling each question separately. Note that some questions have multiple parts; ensure  your answers are complete.  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. Item Highest Lowest Return on equity     Profit margin ratio     Asset turnover ratio     Financial leverage ratio       Analytics mindset case studies – DuPont © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02050-161US   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 companies. 6. Create a dashboard that allows you to evaluate how a company’s 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 company’s industry. Choose three different companies and discuss what you learn about the company based on these metrics.       Required Deliverables  By the due date, submit in Blackboard using the appropriate Assignment link the following:   One Word document containing all of your answers to questions 1 through 5 in Part II and  questions 1 through 6 in Part III.    One Word document which includes screen shots of EVERY worksheet/dashboard in Tableau. You  can use Print Screen or the Snipping Tool (Snipping Tool is much easier). Be sure the  worksheet/dashboard name is visible in the picture saved to Word and be sure the pictures are  in the same order as the questions they are answering.   One Tableau file with all worksheets used to complete this assignment.