Tableau Case
Implementing the DuPont Method
(For ACCT 3371 I have edited this EY-written case.)
Background Information
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. 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 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.
DuPont Formula
The DuPont Method has evolved into the following formula:
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
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.
Data
In your data file, 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 modified
financial statement information for fiscal years 2015-2017, inclusive. These companies 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. 1 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.
► Total Sales: 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.
► 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 2015, means
the balance sheet of the company is as of the last day of their fiscal year in 2015 (usually December
31st) and the income statement for all transactions that occurred during the fiscal year.
1
See http://www.nasdaq.com/screening/companies-by-industry.aspx for industry groupings.
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Tableau Download Instructions
We will use Tableau Desktop in this class, not Tableau Prep. You only need to download and install
Tableau Desktop. Tableau Desktop runs on both Windows and Mac computers.
1. Go to https://www.tableau.com/tft/activation. Click on Download Tableau Desktop.
2. When prompted, enter your UH email address for Business E-mail and enter University of
Houston for Organization. Click Download Free Trial.
3. Install the Tableau Desktop software and complete the registration.
•
Enter University of Houston for organization
•
Enter accounting, finance or something similar for department
•
Enter student or analyst for job role
•
Enter state, country zip and phone number
•
Click “Activate Tableau” at the top of the screen.
4. Activate with your product key: TCN6-3034-E510-3929-F5D0.
If you clicked Trial instead of Activate Tableau, open Tableau and follow the menu path Help >
Manage Product Keys > Activate
Information from Tableau:
Students can continue using Tableau after the class is over by individually requesting their own one-year
license through the Tableau for Students program.
Tableau Download Instructions
We will use Tableau Desktop in this class, not Tableau Prep. You only need to download and install
Tableau Desktop. Tableau Desktop runs on both Windows and Mac computers.
1. Go to https://www.tableau.com/tft/activation. Click on Download Tableau Desktop.
2. When prompted, enter your UH email address for Business E-mail and enter University of
Houston for Organization. Click Download Free Trial.
3. Install the Tableau Desktop software and complete the registration.
•
Enter University of Houston for organization
•
Enter accounting, finance or something similar for department
•
Enter student or analyst for job role
•
Enter state, country zip and phone number
•
Click “Activate Tableau” at the top of the screen.
4. Activate with your product key: TCN6-3034-E510-3929-F5D0.
If you clicked Trial instead of Activate Tableau, open Tableau and follow the menu path Help >
Manage Product Keys > Activate
Information from Tableau:
Students can continue using Tableau after the class is over by individually requesting their own one-year
license through the Tableau for Students program.
Tableau Case
Implementing the DuPont Method
(For ACCT 3371 I have edited this EY-written case.)
Background Information
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. 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 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.
DuPont Formula
The DuPont Method has evolved into the following formula:
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
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.
Data
In your data file, 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 modified
financial statement information for fiscal years 2015-2017, inclusive. These companies 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. 1 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.
► Total Sales: 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.
► 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 2015, means
the balance sheet of the company is as of the last day of their fiscal year in 2015 (usually December
31st) and the income statement for all transactions that occurred during the fiscal year.
1
See http://www.nasdaq.com/screening/companies-by-industry.aspx for industry groupings.
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Tableau Download Instructions
We will use Tableau Desktop in this class, not Tableau Prep. You only need to download and install
Tableau Desktop. Tableau Desktop runs on both Windows and Mac computers.
1. Go to https://www.tableau.com/tft/activation. Click on Download Tableau Desktop.
2. When prompted, enter your UH email address for Business E-mail and enter University of
Houston for Organization. Click Download Free Trial.
3. Install the Tableau Desktop software and complete the registration.
•
Enter University of Houston for organization
•
Enter accounting, finance or something similar for department
•
Enter student or analyst for job role
•
Enter state, country zip and phone number
•
Click “Activate Tableau” at the top of the screen.
4. Activate with your product key: TCN6-3034-E510-3929-F5D0.
If you clicked Trial instead of Activate Tableau, open Tableau and follow the menu path Help >
Manage Product Keys > Activate
Information from Tableau:
Students can continue using Tableau after the class is over by individually requesting their own one-year
license through the Tableau for Students program.
Tableau Reference Guide
Contents
Connect to Your Source Excel Data – Join............................................................................................... 2
Introduction to Tableau Workspace .......................................................................................................... 4
Total a Single Data Measure ..................................................................................................................... 5
“Show Me” Button is Missing .................................................................................................................. 5
Count the Entries in a Dimension ............................................................................................................. 5
Count Entries in a Dimension based on a Second Dimension .................................................................. 6
Show Captions for a Worksheet................................................................................................................ 6
Sum/Average a Measure based on a Dimension ....................................................................................... 7
Add Color to a Visualization .................................................................................................................... 7
Move a Color Legend ............................................................................................................................... 8
Sorting Data .............................................................................................................................................. 9
Change the Type of Visualization............................................................................................................. 9
Resize a Visualization ............................................................................................................................... 9
Labels are Not Showing for All Bars ...................................................................................................... 10
Create a Calculated Field ........................................................................................................................ 10
Create a Calculated Field to RANK Items .............................................................................................. 11
Show Multiple Graphs on One Visualization ......................................................................................... 11
Add a Filter to any Visualization ............................................................................................................ 12
Set Default Measure Format ................................................................................................................... 14
Format Numbers on an Axis or in a Pane ............................................................................................... 15
Create a Dashboard ................................................................................................................................. 15
Create a Story.......................................................................................................................................... 16
Increase size of Caption Box on Story Point .......................................................................................... 17
Save your file as a Tableau Package ....................................................................................................... 18
1
Connect to Your Source Excel Data – Join
Use a join when you need to connect two data files that have different fields and you want to combine
them into one large dataset. For example, a master file and a transaction file. You must specify which
field will be used to perform the join (i.e. how you will connect the two files).
Step 1 Start Tableau. If the Connect window
does not appear, go to Data Source tab at the
bottom of the screen. Under Connect, select
a file or database type and browse to find the
file.
Step 2 Drag the first dataset to the workspace on the
right of the screen. Specify Live (provides real-time
updates as source data changes) or Extract (snapshot of
the data, no updates of source data changes) as needed.
Step 3 Tableau has several ways to connect dataset, relationships, joins, unions. When you complete
your homework, you need to use a join and not a relationship.
To create a join instead of a relationship, double-click on the first table. There will be a blue line on the
left side of the first table box. Drag the second dataset to the right of the first table and a Venn diagram
will appear. If the files are large, this could take some time, so be patient.
If you have a red triangle or a plain line between your two tables instead of a Venn diagram, then you
are incorrectly using a relationship. To correct the problem, remove the second table. Click the drop
down arrow in the right side of the second table box and select Remove.
2
Step 4 To edit the basic join to fit your data analysis needs, click on the Venn diagram. Remember you
can have multiple join clauses. For example, you may need to join on both order ID and region. To add
another join clause, click “Add new join clause” and pick the relevant field in both data sources. You
then select the join type.
Join Type
Inner
Result
When you use an inner join to combine tables, the result is a table that
contains values that have matches in both tables.
Left
When you use a left join to combine tables, the result is a table that
contains all values from the left table and corresponding matches from the
right table.
When a value in the left table doesn't have a corresponding match in the
right table, you see a null value in the data grid.
Right
When you use a right join to combine tables, the result is a table that
contains all values from the right table and corresponding matches from the
left table.
When a value in the right table doesn't have a corresponding match in the
left table, you see a null value in the data grid.
Full outer
When you use a full outer join to combine tables, the result is a table that
contains all values from both tables.
When a value from either table doesn't have a match with the other table,
you see a null value in the data grid.
3
Description
Introduction to Tableau Workspace
Now that you have your data, you are ready to begin building visualizations. Each visualization is a
separate sheet which looks a little like a blank pivot table in Excel. Here are some key terms
•
Sheet: A sheet is a singular chart or map in Tableau.
•
Workbook: A workbook is the entire Tableau file containing your sheets, stories and dashboards.
•
Measure: A variable from the data on which math can be performed (sum, average, and so on).
Measures are often continuous data. Examples include GPA, sales, quantity, quota, height, and
salary.
•
Dimension: A categorical variable from the dataset that is used to slice and dice the data into
different categories. Dimensions are often discrete data. Examples include country, gender,
student ID, and name.
•
Filter: A filter is used to limit what data is being displayed on the sheet. Visible controls for a
filter on a sheet or dashboard are called Quick Filters.
•
Tooltip: Tooltips are text boxes that appear when hovering over a mark on a sheet in order to
give more information. The text and text formatting in them are easily edited through the Marks
card.
•
Marks card: The Marks card is the tool used to create a sheet that controls most of the visual
elements in a sheet. Using the Marks card, you can switch between different chart types (bar, line,
symbol, filled map, and so), change colors and sizes, add labels, change the level of detail, and
edit the tool tips.
•
Rows and Columns Shelves: The Rows shelf and the Columns shelf is where you determine
which variables will go on what axis. Put data you want displayed along the X-axis on the
Columns shelf and data you want displayed on the Y-axis on the Rows shelf.
•
Dashboard: A dashboard is a canvas for displaying multiple sheets at a time and allowing them
to interact with each other.
4
Total a Single Data Measure
Step 1 Go to a new sheet. Drag the
measure to data portion of the sheet.
“Show Me” Button is Missing
Step 1 The Show Me” button should
appear in the upper right side of the
screen. If it is missing, press CNTRL-1
(control and one).
Count the Entries in a Dimension
Step 1 Go to a new sheet. Drag the dimension to
row tray of the sheet.
Step 2 Click the drop down menu on the right
side of the name. Click on Measure and then
select the appropriate count function. COUNT
will count all entries. COUNT(DISTINCT) will
count unique entries.
5
Step 3 Click on Label in the Marks card to display the count
number. Click in the Show Marks Label square.
Count Entries in a Dimension based on a Second
Dimension
Step 1 For example, if you want to count
customers by region, drag the first dimension to
the row tray and the second to the column tray.
Step 2 Click the drop down menu on the right side
of the name. Click on Measure and then select the
appropriate count function. COUNT will count
all entries. COUNT(DISTINCT) will count
unique entries.
Step 3 Click on Label in the Marks card to display
the count number. Click in the Show Marks Label
square.
Show Captions for a Worksheet
Step 1 Menu Path: Worksheet>Show
Caption.
Step 2 The caption will appear below the viz. Click the
caption box and drag it to below the column and row
shelves.
6
Sum/Average a Measure based on a Dimension
Step 1 For example, if you want to sum profit by
region, drag the dimension to the row or column
tray and the measure to the other tray.
Step 2 Click the drop down menu on the right side
of the name. Click on Measure and then select the
appropriate function.
Step 3 Click on Label in the Marks card to display
the count number. Click in the Show Marks Label
square.
Add Color to a Visualization
Step 1 After creating the basic visualization, drag
the item you want to color to the Color button on
the Marks shelf.
7
Move a Color Legend
Step 1 By default Tableau puts the color legend in the upper right corner of the screen which is not
always convenient. You can move it to another part of the screen by clicking on the top of the legend
and dragging it to a new location.
Before:
After:
8
Sorting Data
Step 1 Place the cursor over the axis on which you
wish to sort the data. A Sort Icon will appear
Step 2 Click the sort icon that appears. One
click sorts the data in ascending order. Two
clicks sorts it in descending order. Three clicks
clears the sort.
Change the Type of Visualization
Step 1 After creating the basic visualization, click
the Show Me button on the far right side of the
screen to see different visualization ideas. You
will see the name of the visualization at the
bottom of the pop-up menu.
Resize a Visualization
Step 1 Place the cursor over the axis on which you wish to resize the visualization. Brackets will appear
on both ends of the axis.
9
Step 2 Place the
cursor on the right
side bracket and drag
to the left.
Labels are Not Showing for All Bars
Step 1 If the visualization does not show all row labels, expand the height of the chart by putting cursor
on the x-axis, click and drag down to increase the height until the labels appear.
Before:
After:
Create a Calculated Field
Step 1 Menu Path Analysis>Create
Calculated Field.
Step 2 Enter the name of the new field in
the top box.
Step 3 Start typing the formula below the
line and the field names from the source
data will appear. Double click to select
the field. Tableau will place brackets
around the field names.
The calculated field should appear in the
Measures area on the lower left of the
screen with a small equal sign in front of
the name.
10
Create a Calculated Field to RANK Items
Step 1 Create a calculated field as described
above
Step 2 Use the Tableau function RANK
RANK(expression, ['asc' | 'desc'])
Desc = largest is ranked “1”
ASC = smalled is ranked “1”
You can then filter on RankByProfit.
For example Rank Save as.
Step 2 Choose “Tableau Packaged Workbook” from the “Save as Type” drop down menu.
18
Tableau Case – Fall 2020
Implementing the DuPont Method
(For ACCT 3371 I have edited this EY-written case.)
Part 2 - Creating and Reporting Analytics
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, and has been loaded into the Excel file. You can assume that the web scrapper accurately and
completely extracted the information and loaded it into Excel. In this part of the case, you will be verifying
your data loaded successfully into Tableau.
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.
Hint: Read the Tableau Reference Guide on Blackboard for help completing this assignment.
Step 1 – Load and Verify Source Data
Acquire Data
Create a folder for your Tableau assignment on your computer or USB drive. If you move between
computers, you will need to move your Tableau folder and all the files it contains.
Download the source data TableauCaseFile_Fall20.xls spreadsheet from Blackboard and move it to your
Tableau folder.
Load Data
Import the data into Tableau. (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 two spreadsheets on both ticker symbol and year on the Data Source
tab.)
Name your Tableau file “Tableau” - my filename would be TableauMilesCarolyn.
Save it in the Tableau folder you created.
Verify Data
When you are finished loading the data, answer these simple questions to make sure you loaded the data
correctly. You need to show the answers to these questions by creating visualizations (viz) in Tableau.
Each viz will be a separate worksheet tab in Tableau. So we can easily grade your work, name each
worksheet S1-Q1, S1- Q2, etc. and ensure “Show Captions” is activated for each worksheet. I have
provided check figures, so you can verify your viz are correct. Show all numbers on the viz.
1. What are the combined total assets of all companies for all years? (58,026,172,447,200)
2. How many different companies are included in the dataset? (173 companies)
3. How many different companies are in each industry?
Capital goods (29 companies)
Public utilities (29 companies)
Consumer services (28 companies)
Technology (29 companies)
Finance (29 companies)
Transportation (29 companies)
4. What companies had over $10,000,000,000 in net income for 2019? The viz should be a bar graph
and only show the (13) companies meeting this criterion in your viz. Include company name and net
income Sort by highest to lowest in net income. Use color to differentiate industries.
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Apply appropriate data analytic techniques
You are now ready to analyze the data. You will be required to do some data transformation to analyze
the data (e.g., you will need to compute the ratios involved in the DuPont Method).
Step 2 - Create Calculated Fields
Create calculated fields for:
• return on equity
• profit margin ratio
•
•
asset turnover ratio
financial leverage ratio
The formulas and field names are in the Case Background. (No Tableau sheets are created when
calculating fields.)
Formatting for all Viz in Steps 3-5
Return on equity and profit margin ratio should be formatted as percentages. Percentages should have
two decimal places in the sheet and zero decimal places on the axis as shown below.
Asset turnover and financial leverage should be shown as normal numbers with two decimals points in the
sheet and one decimal point on the axis.
Financial numbers (total assets, net income, etc.) should be shown as normal numbers with zero decimals
in the sheet and currency with zero decimal places on the axis. Change the display units as appropriate thousands (k), millions (m), billions (b) etc. Too many zeros can be distracting to readers.
Use data labels to convey detailed information as shown in step 3. Ensure “Show Captions” is activated
for each worksheet, but do not modify the viz caption. Use color to enhance visual appeal.
Keep in mind that it is extremely rare to total or sum a ratio, so normally you will use median, max, min or
one of the count functions to analyze your ratio data.
Step 3 - Questions about Industries
Label your Tableau sheets S3-Q1, S3-Q2, etc. Q1-Q3 should all look like the one below.
1. What is the median return on equity for each industry? Sort by industry as shown above.
2. What is the median profit margin ratio for each industry? Sort by industry as shown above.
3. What is the median asset turnover ratio for each industry? Sort by industry as shown above.
4. Create a dashboard using the three viz you just created in step 3 above. Make sure your dashboard is
visually appealing and there are no scroll bars on the viz.
5. Sometimes it is best to create one viz showing multiple graphs. Create a viz with industry as the
columns, and the median of all four ratios as the rows. Color industry and label data points. This will
show information similar to question 4, but in a different format.
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Step 4 - Questions about Companies
You need to present a proposal to your investor on which individual company stock to purchase. Think
about what information you would want to know if someone asked you to invest money. What four things
would someone need to show you or say to you to get your investment dollars? Use the viz you have
already created to focus your investment proposal ideas.
You need to create at least four additional viz to help your investor and label them S4-Q1, S4-Q2, etc.
The viz must use different graph types (i.e. You may have only one bar graph). You must use the viz you
create in your report to your investor. Resorting or slightly modifying a viz from a previous step does not
count as one of the four required viz. Make sure your graph type is appropriate for the data you are
showing by referring to the Chart Suggestion document.
I have suggested some viz below, but feel free to create your own.
•
Compare your company on any ratio or financial measure to its industry or companies of similar
size in other industries. Use color to emphasize your company on the viz. Don’t use “highlight”
since it ends when the viz is touched.
•
What companies have the best ROE or other ratio within each industry for a single year or all
three years? (Hint: For this analysis, each company needs to be grouped within its industry and
ordered by rank so that the highest return on equity appears on the top. You can add filters if
needed.)
•
Which company has seen the greatest improvement in median ROE or other ratio or measure
during the three years included in this case?
•
What company had the most net income for a single year or all three years and what was the total
amount of those sales? (Hint: For this analysis, total net income needs to be summed for each
company and ordered by rank so that the highest grossing company appears on the top. You can
add filters if needed.)
•
Perform a similar analysis as the one above for total profit or other financial measure for a single
year or all three years. (Note: you cannot sum a balance sheet account across years.)
Filtering
You need to filter your viz, so they fit on approximately one screen (max 20 companies).
•
You cannot use the same filtering method for all your viz (i.e. Top 20).
•
You should filter by industry, year, ratio value and/or another measurement or dimension.
•
When filtering, use “at least” or “at most” if possible. Only use “range” when you are filtering a
range with upper and lower bounds. If you use a range, you must explain in your story why you
used a it
•
You can use more than one filter per viz to really highlight your company.
•
None of your viz should show data for every company individually.
If you filter on company, please follow the following guidelines:
•
You CAN put customer name in the filter box and then use the TOP tab to filter on another field.
There is an example of this method in the Tableau Reference Guide on Blackboard.
•
You CANNOT put customer name in the filter box and then use the GENERAL tab to Select from
List unless you are filtering on just your company.
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Report results of data analytic techniques
Step 5 - Report to Investor
You need to present your findings and give your opinion to your investor on which individual company
stock to purchase.
You need to create one Tableau Story containing at least four story points supporting your opinion. Name
the tab “My Investment Recommendation”. You may include additional viz to help you tell your story.
All of the viz from Step 4 must be included. If you need a viz similar to one from Steps 1-3, create a
duplicate and modify the duplicate. (Don’t directly modify a viz you created in steps 1-3 or you will lose
points on steps 1-3.)
Be sure you clearly state your opinion – “In my opinion…”. Since this is your opinion, there is no right or
wrong answer on what the investor should purchase, but your viz must support your opinion.
You will be graded on how well you support your opinion with your viz, so add a caption to each story
point to guide the reader on how the viz supports your opinion. Your captions should be two to three
sentences. You must tell a story! Why does the viz you are presenting encourage the investor to follow
your advice? Be persuasive.
The caption should not just describe the viz, but explain why the data presented is relevant to the
investment decision. Ensure each of your caption boxes stretches the width of the screen for readability.
You can also drag down the bottom of caption box if your caption does not fit in the box. Do not use text
boxes for your captions.
(This is not a finance class, so don’t stress over the opinion part. Choose a logical opinion and defend it
using at least four viz.)
Step 6 - Submit your Assignment
Ensure “Show Captions” is activated for each worksheet.
Save your Tableau file as a Tableau Package by following the menu path: File>Save As>
Choose “Tableau Packaged Workbook” from the “Save as Type” drop down menu.
Upload your Tableau Package to Blackboard on the Software Modules>Tableau Module Folder page
before the due date. The item name is “Tableau Submission - Attach file here”.
Verify the file extension is .twbx before uploading!
Analytics mindset case studies – DuPont
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02050-161US
Tableau Part 2 Checklist
All Visualizations
All tabs named correctly
Numbers in all viz formatted correctly (percent, decimal, etc)
o
Axis
o
Pane
Color used to improve readability
Data values are labeled
All label marks are visible
“Show Captions” is activated
Step 4 Visualizations
Data sorted to highlight your company
Variety of filters used
Your company is on all viz and easy to locate
Variety of graph types used
Appropriate summarization used (sum, count, median, average, etc.)
Graph type is appropriate for the data shown.
“Highlight” is not used since it disappears when the viz is clicked
Story
Do your viz support your story?
Do you use some of the DuPont ratios in your story?
Do you analyze something other than just the DuPont ratios?
Does your caption match your viz?
Do you have one investment recommendation?
Are all your story points in one story?
Did you check your spelling and grammar?
Are your captions page-width wide?
Did you drag down the bottom of your caption boxes so all rows can be read?
Purchase answer to see full
attachment