Pivot table assignments in excel

BA 325: Excel PivotTable Assignment

In this assignment, you will learn the basics of data analytics and use an analytics approach to explore and evaluate a data set. Having the skills to work with data isn’t just valuable, it’s a necessity in many business jobs. Analytical skills will become increasingly important in the future, and the PSU School of Business believes that every student should have opportunities to learn data analytics.

At its core, data analytics is about solving problems. In BA Financial and Managerial Accounting, you learned about DuPont analysis and how it can be used to evaluate the performance of companies. In this assignment, we’ll expand the DuPont analysis and apply it to a set of real companies in a variety of industries. Our goals are to learn more about data analytics and Excel. We’ll be using an assignment adapted from a case developed by Ernst and Young, one of the world’s largest accounting firms.

Practice assignment and DuPont assignment

The PivotTable assignment includes two smaller assignments: The Practice assignment and the DuPont assignment. For this assignment, we’ll be focusing on PivotTables. PivotTables are among the most valuable tools used by business analysts to explore, understand, format and present data. By the end of this assignment, you’ll be proficient in using this tool.

You’ll use the Practice assignment to learn or refresh analytical skills in Excel and learn or refresh PivotTable skills. The Practice assignment has guided instructions at each point. Additionally, there are screenshots and answers below all the questions. This should allow you to check your work and make sure you understand before moving on. While you go through the Practice assignment you will be required to take 4 screen clippings that you will paste in the ‘PivotTable Assignment Answer Sheet.’

After you complete the Practice assignment, you will begin the DuPont assignment. As you work through the DuPont assignment, you will be prompted with questions. You will answer these questions in the ‘PivotTable Assignment Answer Sheet.’ Like the Practice assignment, the DuPont assignment has a ‘Check Figure’ section below questions 1-20. In the ‘Check Figure’ section, there are some answers that you can compare to your PivotTables. If you get the same answers as the ‘Check Figure’ answer, it is likely that your other answers will be correct. While you go through the DuPont assignment, you will be required to take 7 screen clippings that you will paste in the ‘PivotTable Assignment Answer Sheet.’

Once you have completed both the Practice assignment and the DuPont assignment, and completely filled out the ‘PivotTable Assignment Answer Sheet,’ you can upload your ‘PivotTable Assignment Answer Sheet’ Word document to the Assignments folder in D2L.

Requirements

1. Practice assignment: Follow the instructions for the Practice Assignment. Take the required 4 screen clippings and paste them in the designated locations in the ‘PivotTable Assignment Answer Sheet.’

2. DuPont assignment: Follow the instructions for the DuPont Assignment. Take the required 7 screen clippings and paste them in the designated locations in the ‘PivotTable Assignment Answer Sheet.’

3. Upload your completed ‘PivotTable Assignment Answer Sheet’ to the Assignments folder in D2L. Name your file: BA 325 First name Last name (use your actual name).

Data Sets

Download the following files from the PivotTable Assignment in D2L:

· Pivot Table Assignment Answer Sheet - Student Template.docx

· Practice Data.xlsx

· DuPont Data.xlsx

In the Excel spreadsheets, you will find 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 2013-2015, 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.

In any data analysis you should ensure you have an established Data Dictionary before you begin. A Data Dictionary is a brief explanation of every field in your starting data set.

Data Dictionary: Practice Data

· Ticker: The code used to identify each company on the NASDAQ stock exchange. Each company has their own unique ticker symbol.

· Name: The name of the company for each line of data.

· Industry: One of six industry groupings as defined by Nasdaq.com. Industries included in the sample are capital goods, consumer services, finance, public utilities, technology, and transportation.

· Year: The fiscal year being reported on the financial statements. For example, a year of 2015 refers to the balance sheet of the company as of the last day of their fiscal year in 2015 (usually December 31st) and the income statement for all transactions that occurred during that fiscal year.

· Total Assets: The total assets of a company at the end of the fiscal year. This number appears on the balance sheet.

· Total Debt: The total debt of a company at the end of the fiscal year. This number appears on the balance sheet and can also be called total liabilities.

· Net Revenues: 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 referred to as total sales.

· Cost of Goods Sold (COGS): The costs a company incurred to produce its products or services. This number appears on the income statement.

Data Dictionary: DuPont Data

· Ticker: The code used to identify each company on the NASDAQ stock exchange. Each company has their own unique ticker symbol.

· Name: The name of the company for each line of data.

· Industry: One of six industry groupings as defined by Nasdaq.com. Industries included in the sample are capital goods, consumer services, finance, public utilities, technology, and transportation.

· Year: The fiscal year being reported on the financial statements. For example, a year of 2015 refers to the balance sheet of the company as of the last day of their fiscal year in 2015 (usually December 31st) and the income statement for all transactions that occurred during that fiscal year.

· 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 can also be called stockholder’s equity.

· Net Revenues: 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 referred to as total sales.

· Net Income: The bottom line number on the income statement. This is the final net income number of the company for the fiscal year.

Tools

There are three additional tools that you will use to accomplish these assignments:

Screen Clipping

There are a few ways to take a screen clipping of your Excel file and paste it into your Word document:

1. To do this from within your Word document:

· Select Insert

· Screenshot

· Screen Clipping

· Then click and drag your cursor to highlight the area you want to clip.

2. Alternatively select the Snipping Tool app from your Windows menu:

· Select New

· Click and drag to highlight the area you want to clip

· Select Copy

· Then move to your Word document and select Ctrl+V to paste the clip.

Data Cleaning

It is very rare that you will be given data that can be immediately analyzed/used. The process of removing/addressing the errors in your core data is called Data Cleaning, and is a big part of a data analyst’s job.

One of the situations that you may have to fix when you are Cleaning Data is function errors. Depending on the calculations you are doing with your data, cells that have 0’s in them may cause errors in your calculated formulas.

· Return to your original data sheet.

· To find the errors:

· Press F5

· Click the Special button

· Select the radio button for Formulas

· Make sure Errors is the only box checked

· Click OK

· Highlight the cells in red and then press the Delete key to remove those formulas.

Note: DO NOT DELETE THE CORE DATA! When you have an error in a calculation/function, only delete the calculation itself. If you delete the core data you may cause other calculations/functions to change, causing much greater problems.

Sort & Filter

Sort & Filter can be a fast way to explore your data. It is best used as a manual way of checking if something is present/missing, or organizing your data by a specific criteria. Both are used for slightly different purposes, but are most powerful when used together.

· To enable Sorting or Filtering:

· Go to the Home tab on the Ribbon.

· Got to the Editing menu.

· Click the Sort & Filter button.

· Click the Filter button.

· This will add Filters to your columns.

· Sort: The basic Excel Sort allows you to organize data based on some criteria, usually alphabetically (A-Z or Z-A) or numerically (lowest to highest or highest to lowest). This is best used when you want to have your data organized in a specific order.

· To execute Sorting (after you have added the Filters to your columns):

· Click the dropdown on the column you want to Sort.

· The Sort area is identified with the Red 1 in the screen clipping below.

· If your column data are numerical it can be Sorted lowest to highest or highest to lowest. If your column data are text it can be Sorted A-Z or Z-A.

· Once you Sort, it reorganize all your data based on the Sort you applied to the given column.

· Filter: The basic Excel Filter allows you to view specific rows in an Excel spreadsheet, while hiding the other rows. This is best used when you are looking for only a specific portion of your data. There are two main types of Filters you will make use of in Excel, a Simple Filter, and a Custom Filter.

· To execute a Simple Filer (after you have added the Filters to your columns):

· Click the dropdown on the column you want to Filter.

· The Simple Filter area is identified with the Red 2 in the screen clipping below.

· Any of the boxes you uncheck will not be shown.

· A Simple Filter is best when you want to Filter out everything except a few options from one column.

· To execute a Custom Filer (after you have added the Filters to your columns):

· Click the dropdown on the column you want to Filter.

· The Custom Filter area is identified with the Red 3 in the screen clipping below.

· You can create a customized Filter based on the type of data in your column (either numerical or text), and then apply specific criteria that only show data that meet your criteria.

· A Custom Filter is best when you want your Filter to leave only a small, specific group of records.

Note: You can also use Sort & Filter when you are cleaning your data. The method listed in the Data Cleaning above is great when you have functions with errors. Sometimes your functions won’t have a mathematical error, but with not make sense due to the core data containing an outlier. In this case it is often easier to use Sort & Filter to find the problematic value, and delete it.

PRACTICE ASSIGNMENT
Open the Excel file called Practice Data.xlsx. We’ll use this data to practice analytical skills that we’ll then apply to the DuPont data.

The first step in any analytics assignment is to adopt an analytics mindset.

· Think about what you already know about the context and what questions you would like to answer as you analyze the data.

· Explore the spreadsheet and look at the data.

· You’ll notice that the file contains financial performance data (stated in millions of dollars) from companies over a three-year period.

· Do you recognize any of these companies?

· These companies come from 6 industries.

· Consider what you know about these industries.

We’ll use the data to calculate the following ratios:

· Debt to Asset ratio: This represents the amount of the assets that is financed through borrowing. This is a measure of leverage. Higher debt is typically associated with higher risk. The calculation for this ratio is Total Debt divided by Total Assets.

· Gross Margin ratio: Gross Margin is Net Revenues less Cost of Goods Sold (COGS). The Gross Margin ratio is the relationship between Gross Margin and Net Revenues. It is a measure of profitability and can be thought of as the company’s profit margin. The Gross Margin ratio is Gross Margin divided by Net Revenues.

Before you begin performing calculations, consider what you expect to see. Which industries would you expect to have the largest and smallest revenues and assets? Which would you expect to have the highest and lowest debt to asset and gross margin ratios?

As you work through the instructions below, think about what you can learn from the data, and after completing the assignment, do some additional exploring on your own. Also take your time to understand the analytical capabilities you’re using in Excel and think how they can be used in business. The tools you learn, and practice here, will be helpful in many classes in your business program.

Requirements

Work through the instructions below. As you work, you’ll be asked to:

· Follow the steps to perform the required analysis.

· Paste 4 screen clippings into the PivotTable Assignment Answer Sheet.

The Practice Assignment is to get you comfortable with PivotTables and help you apply some of the Excel features you have been learning in this class. You’ll likely be using a lot of unfamiliar features of Excel, so be prepared to struggle a little. If you are already experienced with PivotTables, the Practice Assignment will be a nice refresher. Answers are provided so that you can check your work. Have fun!

1) Move through the data set.
· Use Ctrl + the arrow keys to move quickly around the Practice data.

· This will have you jump to the end of the row/column.

· These are the same companies in the DuPont data file.

Question 1: How many Rows of company data (don’t count headings) are in the file?

516

2) Examine the numbers.
· All the numbers are stated in millions, so the Total Assets of American Airlines in 2013 was over $40 billion-- $42,300,000,000.

· For this assignment, we’ll state numbers in millions, so we’ll write this number as $42,300M.

· Note: The formatting is not arbitrary. There are a few different numerical formats you will use with different types of calculations, so be mindful of which one you should be using.

Question 2: How much were Apple’s assets in 2015?

$290,500M

3) Calculate the Debt to Assets ratio.
· In cell I1(Column I, Row 1), type Debt to Assets.

· In cell I2, type =F2/E2 and hit return. Your answer should be 0.486997636, which means the company’s debts are about half the size of total assets.

· Format that cell as a percentage by selecting Home and clicking on the % icon in the Number menu.

· Copy that cell to show the Debt to Asset ratio for all rows in your spreadsheet by moving your cursor to the bottom right corner of the cell until you see a + and then double-clicking.

· (If you don’t see the +, go to File, Options, Advanced, and check the box next to Automatically Flash Fill).

Question 3: What was the Debt to Assets ratio for Apple Inc. in 2013?

8%

4) Calculate the Gross Margin ratio.
· In cell J1, type Gross Margin %. In cell J2, type =(G2-H2)/G2 and hit return.

· Your answer should be approximately 0.28, which means that the company keeps about 28% of every dollar of sales to cover other expenses and profits.

· Format the cell as a percentage and copy the formula for the remaining rows.

Question 4: What was Arrow Financial’s Gross Margin ratio for 2015?

100%

5) Create a PivotTable.
· Move your cursor to cell A1.

· Select Insert from the menu and click the PivotTable icon.

· Note: When you are creating a PivotTable you have a few options other than the default settings. You can specify a specific worksheet your PivotTable will go. You can also create your PivotTable from the Recommended PivotTables option. This will look at relationships in your particular dataset and give you some initial ideas for your PivotTable setup.

· Accept the default settings for the Table/Range and New Worksheet by clicking OK.

· On the left of your screen, you’ll see the area where your PivotTable will be built.

· To the right of your screen, you will see all the fields (columns) listed, along with four areas of the PivotTable.

· If you click outside the PivotTable, the fields area will disappear. It will reappear when you click on the table.

Question 5: What is the last Field in the list of PivotTable Fields?

Gross Margin %

6) Build your PivotTable.
· Add values and rows to the table.

· Note: There are four areas that you will place the fields you are making use of in your PivotTable (see image below). Each of the areas has a specific purpose, and facilitates different actions in the PivotTable:

· Filters: Placing a specific field in the Filter area will make it easier for you to filter your PivotTable by the different categories within the field (meaning, if you placed Industry in the Filter area, it would allow you to filter your data by any of the different categories within Industry). It will place the Filter above the PivotTable itself, allowing you to more rapidly navigate and see trends.

· Columns: Placing a specific field in the Columns area will create individual columns for each of the different categories within the field (meaning, if you placed Industry in the Columns area, it will show a sub-column for each of the different categories within Industry). If you have multiple fields in your Column area you can move them up/down to create a specific hierarchy. It is worth playing around with the order of your fields to best showcase your findings.

· Rows: Placing a specific field in the Rows area will create individual rows for each of the different categories within the field (meaning, if you placed Industry in the Rows area, it will show a row for each of the different categories within Industry). If you have multiple fields in your Rows area you can move them up/down to create a specific hierarchy. It is worth playing around with the order of your fields to best showcase your findings.

· Values: Placing a specific field in the Values area will add a basic mathematical calculation (Sum, Count, Average, etc.) to that specific field. The Excel default calculation is Sum for numerical data and Count for non-numerical data. Any calculation that is created by placing a field in the Values area will show as a column on your PivotTable. If you have multiple fields in your Values area you can move them up/down to create a specific hierarchy. It is worth playing around with the order of your fields to best showcase your findings. Also, if you have multiple fields in your Values area it will actually create a field called Values in the Columns area. You can also use this field with your other fields in the Columns area to customize your PivotTable further.

· First, click the Net Revenues field.

· You’ll see Sum of Net Revenues in the Values area.

· Next click on Year, and you may see it appear in the columns and values areas.

· Unclick Year and drag Year to the Rows area.

· Your Pivot Fields area should look like this:

Question 6: What number shows for Sum of Net Revenues in 2015?

4188205.199

7) Format the numbers in the PivotTable.
· Click on the Field labeled Sum of Net Revenues in the Values area.

· Select Value Field Settings and click the Number Format button in the lower left of the pop-up menu.

· Select Number, change the decimal places to 0, and click the box to show commas.

· Click OK, and OK again.

· Double click on the tab labeled Sheet 1 and change it to Sales by Year.

· When there are multiple worksheet tabs in a spreadsheet file, you can use Ctrl + Page Down or Page Up to quickly navigate through them.

Question 7: Now what number shows for Sum of Net Revenues in 2015?

4,188,205

Practice Assignment Screen Clipping 1
· Take a screen clipping of your PivotTable from Question 7 and paste below ‘Practice Assignment Screen Clipping 1’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

· Your screen clipping should look like the one from Question 7.

8) Sort data in a PivotTable.
· Return to your Practice Data tab.

· Create a new PivotTable with Net Revenues in the Values area, Year in the Columns area, and Name in the Rows area.

· You’ll see each company in its own row, with Net Revenues for each of the three years next to it.

· You will also see a Grand Total column. That column is created when you have at least one field in the Columns area and at least one field in the Values area.

· Click the down arrow next to Sum of Net Revenues in the Values area.

· Select Value Field Settings and click the Number Format button in the lower left of the pop-up menu.

· Select Number, change the decimal places to 0, and click the box to show commas.

· Click OK, and OK again.

· Can you find the companies with the highest Net Revenues for all three years?

· To make it easier, right click a number in the Grand Total column, select Sort, and sort from Largest to Smallest.

Question 8: Which Company had the highest Total Net Revenues?

Wall-Mart Stores Inc.

9) Change PivotTable calculations.
· Let’s change the Grand Total to an average of the three years.

· To do this we must change the calculation used in the Values area. This will update BOTH the calculation used in each column, AND the calculation used in the Grand Total column.

· Click the down arrow next to Sum of Net Revenues in the Values area.

· Select Value Field Settings and choose Average.

· Change the name of the sheet to Company Ave Sales by Year.

Question 9: What was the Average Net Revenues for Wall Mart?

477,067M

10) Count records.
· Return to your Practice Data tab.

· Create a new PivotTable with Net Revenues in the Values area, Year in the Columns area, and Name in the Rows area.

· Now change the Value Field Setting for Sum of Net Revenues to Count.

· You should see a table full of 1’s with 3’s for most Grand Totals.

· If you scroll to the bottom of the chart, you can see that you had Revenue information for 172 companies in 2013.

· Now drag Name out of the rows area and drag Industry in to see the number of Revenue records for each industry.

· Your PivotTable should look like the one below.

· Notice that there are 516 total values.

· Change the name of this worksheet tab to Industry Count.

Question 10: For how many Technology Companies do you have Revenue info?

30

11) Make a PivotChart.
· Return to the Practice Data tab and create a new PivotTable with Industry in the Rows, Years in the Columns, and Total Assets in the Values.

· Change the Value Field Setting for Sum of Total Assets to Average and change the format to Number with commas and 0 decimals.

· The Average of Total Assets for Capital Goods companies over the three-year period should be 41,097M.

· Click Insert, Charts, PivotChart from the menu and click OK to accept the Clustered Column format.

· Drag the corners of the chart to resize it so that you can see both the table and chart.

· Note: The true power of the PivotChart is how it is linked to the PivotTable it is created from. Any adjustment you make to either PivotChart or PivotTable will update the other one. This means any filter/sort action, or any larger modification, will affect both.

Question 11: Which Industry has Companies with the most Assets?

Finance

12) Filter the PivotTable and PivotChart and add a Slicer.
· In the chart, click on the drop-down arrow next to Industry and click the box next to Finance to unselect it, then click OK.

· Notice how the PivotTable and chart changed when you removed Finance.

· All the dropdown menus work like this, and it is a valuable analytical feature.

· Some users find slicers to be more intuitive way to filter their data.

· Note: Slicers are a visual way to filter your data. They are functionally the same as placing your field of interest in the Filters area, except instead of having a drop-down above the PivotTable, you will have a clickable interface for your filtering.

· Click on Insert Slicer from the PivotTable Analyze tab on the Ribbon and select Industry.

· Move and resize your slicer as desired.

· Notice that Finance is not selected.

· Click on the Finance button in the slicer.

· Hold Ctrl and click the cursor to select and deselect industries.

· Select all industries except Finance.

· Save this tab as Assets by Industry.

Question 12: Without Finance selected, in which Industry and Year have the highest Average of Total Assets?

Public Utilities 2015

Practice Assignment Screen Clipping 2
· Take a screen clipping of your PivotTable, PivotChart, and Slicer from Question 12 and paste below ‘Practice Assignment Screen Clipping 2’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

· Your screen clipping should look something like the image from Question 12.

13) Use Conditional Formatting.
· Now return to the Practice data sheet and create a new PivotTable with Debt to Assets in the Values area, Industry in the Rows area, and Years in the Columns area.

· Change Sum of Debt to Assets to an Average, and format it as a Percentage with 0 decimal places.

· In which Year did this group of companies have the largest Debt to Asset ratio?

· In which Industry was the Debt to Asset ratio the highest?

· The lowest?

· Highlight cells B5 through D10 and select Home, Conditional Formatting. Move your cursor to Color Scales and pick the first option, the Green-Yellow-Red Color Scale.

· Note: Conditional Formatting can be a great visual indicator to show trends when you have a lot of numerical data. Often times it can be hard for your audience to process a huge PivotTable, and this can make it easier to have quick takeaways. Be mindful to not over do it! Be thoughtful about what you are trying to showcase, and choose a color scheme that will make only the truly important things stand out!

· You’ll see the highest numbers highlighted in green and the lowest highlighted in red as shown below.

· Name the tab Debt to Assets.

Question 13: In which Industry and Year was Debt to Assets highest?

Public Utilities 2013

14) Explore data behind a number in the PivotTable.
· Notice that the 2013 Public Utilities ratio of 281% appears to be an outlier.

· Move your cursor to that cell (B8) and double-click.

· You’ll see all the underlying data for Public Utilities.

· Click on the drop-down menu next to the Debt to Assets heading and sort from Largest to Smallest.

· You can see that Sunworks Inc. is an outlier because the company’s Total Assets in 2013 were so small.

· Note: This view has all the numbers formatted in the General number format. This makes the value for Sunworks that much more significant. This is a ratio that should be formatted as a percentage. This means that the 69.92 is actually 6992%!

· Flag the value as an outlier by highlighting the cell in red:

· Right clicking on the cell

· Clicking the bucket icon (Fill Color)

· Changing the Fill Color to Red

· We’ll remove this data point in the next step.

· Name the tab Outlier.

Question 14: What was Sunworks’ Debt to Assets ratio in 2013?

69.91525424

15) Filter and modify data; refresh the PivotTable.
· Return to the Practice Data tab.

· Optional: Since we’ll be changing data in this tab (which is your original data), you may wish to make a duplicate. I always recommend this as a safety precaution so you don’t permanently change your data source.

· To do this, hold down the Ctrl key while you Click on the tab and drag it to a new sheet (the circle with the plus sign.). If you make a copy name it Original Practice Data to make it easy for you to remember the changes.

· We are now going to use Sort & Filter to clean our data (explained in greater detail in the Tools section on pages 3-4).

· From the Practice Data tab, click in the table and select Data, Filter.

· This will add drop-down menus to your field headings.

· Find the 2013 Debt to Assets ratio for Sunworks by sorting the Debt to Assets column from Largest to Smallest.

· Sunworks’ 2013 Debt to Assets ratio should be the first row. Highlight the cell with the Debt to Assets value in Red, and then delete it.

· Note: It is important that you delete the calculated value found in the Debt to Assets ratio, and not the Total Assets value (which was the actual outlier causing our calculation to be weird). If you delete the Total Assets value that will change other calculations in other PivotTables, and that can lead to come major confusion.

· Now return to your Debt to Assets PivotTable and select Data, Refresh All.

· Now the numbers are more in line with the other industries.

Question 15: Now what is the 3-year Average Debt to Assets ratio for Public Utilities?

34%

16) Use the PivotTable function to explore data.
· Think about which industries and companies you would expect to have the lowest and highest Gross Margin ratio. Why do you think that?

· Return to your Practice Data tab.

· Create a PivotTable with Industry in the Rows and Gross Margin % in the Values.

· Change the Value Field Settings for Sum of Gross Margin % to Average and change the format to Percentage with 0 decimal places.

· Check your results vs your initial expectations. Were you correct? Why or why not?

Question 16: Which Industry has the highest Gross Margin %?

Finance

17) Expand and collapse rows.
· Now add Name below Industry in the Rows area.

· Click on the Gross Margin ratio for any company and then right click and sort from Largest to Smallest.

· Notice that the data are sorted within the Industry categories.

· Collapse the Capital Goods Industry category by clicking on the small minus box to the left of Capital Goods.

· Collapse Consumer Services, Finance, Public Utilities, and Transportation as well.

· Rename the tab as Average Gross Margin %.

Question 17: Which Technology Company has the highest Average Gross Margin %?

Facebook

18) Add a calculated field to a PivotTable.
· While PivotTables are a great way of consolidating large data sets, and quickly showing trends and relationships, their built in calculations can be limiting at times (the basic ones found by placing a field in the Values area). When you want to do a specific calculation that is not found in the Values area you will need to create a Calculated Field.

· Return to the Practice Data tab and create a new PivotTable with Industry in the Rows, Years in the Columns, and Total Assets in the Values.

· Change the Value Field Setting for Sum of Total Assets to Average and change the format to Number with commas and 0 decimals.

· Now, drag another copy of Total Assets to the value area below Average of Total Assets.

· Click on the second Total Assets Field and open the Value Field Settings menu and follow these instructions:

· Choose Average from the Summarize fields by tab

· Change the Custom Name to Change in Total Assets (M)

· Click on the Show Values As tab

· Click on the Show Values as dropdown menu (the default is No Calculation) and select % Difference From.

· In the Base field area, select Year

· In the Base item area, select (previous)

· Click OK to create the table.

· Columns B, D, and F show the Average of Total Assets for the given years.

· Columns C, E, and G show the percentage change in Average Total Assets from the previous year. Column C is blank because our data set does not have a year before 2013. Column E shows the percentage change in Average Total Assets from 2013 to 2014. Column G shows the percentage change in Average Total Assets from 2014 to 2015.

· Note: Because of the calculations we are making, columns C, H and I are unnecessary/don’t make sense. Column C is blank because there is not a previous year from 2013, so the calculation can’t happen. Columns H and I don’t make sense because a Grand Total can’t be calculated from a previous year. To avoid confusion, we will hide the three unnecessary columns.

· Highlight column C by clicking on the letter C.

· Right click and select Hide. Repeat for H and I.

· Rename the tab Year over Year Assets.

Question 18: What was the Average Change in the value of Total Assets between 2014 and 2015 for the Transportation Industry?

-1.49%

Practice Assignment Screen Clipping 3
· Take a screen clipping that shows all the worksheet tabs in your file.

· To see more tabs, you can drag the three vertical dots to the right of the tabs further to the right.

· Alternatively, you can right click on one of the arrows to the left of the first tab and a list of your worksheets will appear.

· Paste your screen clipping below ‘Practice Assignment Screen Clipping 3’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

19) Create a Dashboard!
· When you are looking at the trends/relationship in a data set you will often create multiple PivotTables/PivotCharts (In this assignment alone you made 7). It can often be helpful for your audience to see how multiple of your relationships change over time. The best way to show this is in a Dashboard.

· Note: There are a few different ways you can create a Dashboard out of PivotTables in Excel. Normally, I would take some of the PivotTables we already built and place them in a new worksheet (I would copy them and paste them into the new worksheet, and then build the Dashboard). In this case, we are going to build them from scratch. This is to show the full process of doing this, and because some of the PivotTables we will use in the Dashboard are modifications of ones made previously.

· Return to the Practice Data tab and create a new PivotTable in a New Worksheet with Industry in the Rows, Years in the Columns, and Total Assets in the Values.

· Change the Value Field Setting for Sum of Total Assets to Average and change the format to Number with commas and 0 decimals.

· Create a Clustered Column PivotChart and place it’s upper left corner in cell G3.

· Click the Format tab in the Ribbon and change the height to 3.6 and the width to 6.0.

· Rename the tab Dashboard.

· Note: These next steps are where we change our process slightly.

· Now return to the Practice data sheet and create a new PivotTable in an Existing Worksheet.

· Click the arrow to the right of the Location box.

· Click the Dashboard tab.

· Click cell A22.

· Hit Enter.

· You now have a second PivotTable within the same worksheet. In this new PivotTable, place Debt to Assets in the Values area, Industry in the Rows area, and Years in the Columns area.

· Change Sum of Debt to Assets to an Average, and format it as a Percentage with 0 decimal places.

· Create a Clustered Column PivotChart and place it’s upper left corner in cell G2.

· Click the Format tab in the Ribbon and change the height to 3.6 and the width to 6.0.

· Now return to the Practice data sheet and create a new PivotTable in an Existing Worksheet.

· Click the arrow to the right of the Location box.

· Click the Dashboard tab.

· Click cell A41.

· Hit Enter.

· You now have a second PivotTable within the same worksheet. In this new PivotTable, place Gross Margin % in the Values area, Industry in the Rows area, and Years in the Columns area.

· Change Sum of Gross Margin % to an Average, and format it as a Percentage with 0 decimal places.

· Create a Clustered Column PivotChart and place it’s upper left corner in cell G2.

· Click the Format tab in the Ribbon and change the height to 3.6 and the width to 6.0.

· You now have all the PivotCharts you will need for your Dashboard, so you can hide the PivotTables. Depending on how you wanted to organize your Dashboard, you might keep some/all of the PivotTables. For simplicity, we will use only the PivotCharts here.

· Highlight columns A through E, and hide them.

· Now move the upper left of the Average of Debt to Assets PivotChart to cell Q3.

· Next, move the upper left of the Average of Gross Margin % PivotChart to cell Q22.

· We now have all the PivotCharts in one location, but to create an actual Dashboard we need to create a way to filter them all simultaneously. To do this we will create some Slicers, and then link the Slicers to all three PivotCharts.

· Click the Average of Total Assets PivotChart.

· Click the PivotChart Analyze tab and click Insert Slicer.

· Select both Industry and Year to insert Slicers for both fields.

· Place the Industry Slicer in cell G22, and the Year Slicer in cell L22.

· To link the Slicers to all the PivotCharts, first select the Industry Slicer, right click the Slicer and select Report Connections.

· Here you will have a list of all the PivotTables that exist in the entire Excel file.

· Notice that all the PivotTables simply have a number in their name. This is because we didn’t create names for the specific PivotTables (there wasn’t a reason to do so at the time).

· Notice that the Sheet tells us where the given PivotTables are found.

· Select all three PivotTables that are found in the Dashboard Sheet.

· You have now connected the Industry Slicer to all three Dashboard PivotTables (and their PivotCharts since the PivotTables and PivotCharts are already linked).

· Follow the same process to link the Year Slicer to all the PivotCharts.

· Change both Slicers to Multi-select to allow you to see how all three PivotCharts respond when you filter out different things.

· Remove Finance. What standout out now that it is gone?

· Play around with your new Dashboard by selecting/de-selecting things you think might be interesting. What do you find?

Question 19: When Finance is removed what Industry and Year have the highest Average of Total Assets? Average of Debt to Assets? Average of Gross Margin %?

Public Utilities 2015, Public Utilities 2015, and Technology 2014 (respectively).

Practice Assignment Screen Clipping 4
· Take a screen clipping of your PivotTable, PivotChart, and Slicer from Question 19 and paste below ‘Practice Assignment Screen Clipping 4’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

· Your screen clipping should look something like the image from Question 19.

DUPONT ASSIGNMENT
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.

In a slightly simplified version of DuPont analysis, the following formula is used:

Return on Investment

=

Profit Margin

X

Investment Turnover

Net Operating Income

=

Net Operating Income

X

Sales Revenue

Average Invested Assets

Net Revenues

Average Invested Assets

For this assignment we will use the expanded DuPont Method, which is the following formula:

Return on Equity

=

Profit Margin

X

Asset Turnover

X

Financial Leverage

Net Income

=

Net Income

X

Net Revenues

X

Total Assets

Total Shareholder's Equity

Net Revenues

Total Assets

Total Shareholder's Equity

There are two main differences between the expanded model and the simplified model. First, the simplified model uses Return on Investment, or Return on Assets as our Return on Investment measure. In the expanded model, we’ll use Return on Equity. Second, we’ll add a calculation for Financial Leverage. Leverage is a measure of the amount of assets we’re able to control with our equity investment. If a company had no debt, Financial Leverage would equal 1. The more debt a company has, the higher this ratio will be. Leverage is a measure of risk. If a company has high Financial Leverage, returns to shareholder equity investments can be very large. Shareholders can make a lot of money if the company is profitable and lose a lot if it is unprofitable. We’ll also use the term Asset Turnover for the turnover measure. It’s more typical to use average invested assets to calculate this number, but we’re just using a single value for assets for simplicity.

Open the Excel file called DuPont Data.xlsx. You’ll use the DuPont data to demonstrate the analytical skills that you have. You will make use of all the ratios found in the expanded DuPont Method.

The ratios can be understood 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: This represents the amount of Net Income that is generated for each dollar of Net Revenue. It can be interpreted as the percentage of each dollar of revenue that the company retains as earnings. Since Net Income can be negative, this ratio can be either negative or positive.

· Asset Turnover: This represents the amount of Net Revenue that is generated for each dollar of Assets the company owns. This is often interpreted as the efficiency of the company—how much Revenue it can generate given the Assets it owns. Except in very unusual circumstances, this ratio is positive.

· Financial Leverage: This represents the amount of Assets that are financed by shareholders, as opposed to debt holders. Except in very unusual circumstances, this ratio is positive.

Before you begin performing calculations, consider what you expect to see. Which industries would you expect to have the largest and smallest Return on Equity and Profit Margin? Which would you expect to have the highest and lowest Asset Turnover and Financial Leverage?

Requirements:

Work through the instructions below. As you work, you’ll be asked to:

· Follow the steps to perform the required analysis.

· Answer questions, many of which require PivotTables. You will save your answers to these questions in the ‘PivotTable Assignment Answer Sheet’.

· Paste 7 screen clippings into the ‘PivotTable Assignment Answer Sheet.’

The first thing you need to do is calculate the four DuPont ratios:

· In column I calculate Return on Equity. Format the cells as a percentage with 2 decimals.

· In column J calculate Profit Margin. Format the cells as a percentage with 2 decimals.

· In column K calculate Asset Turnover. Format the cells as a number with 2 decimals.

· In column L calculate Financial Leverage. Format the cells as a number with 2 decimals.

Many of the instructions in the DuPont assignment are very similar to those found in the Practice assignment. If you get stuck, you should use the Practice assignment as a resource to help you get un-stuck. Questions 10-30 have a ‘Check Figure’ section below each question. In the ‘Check Figure’ section, there are some answers that you can compare to your work. If you get the same answers as the ‘Check Figure’ answer, it is likely that your answers to the actual question will be correct. Remember to fill out your answers in the ‘PivotTable Assignment Answer Sheet.’ Have fun!

1) How much was American Airlines’ Net Revenues in 2013?
Check Figure:
Apple’s 2015 Net Revenues were 231,300M

2) What was the Return on Equity for Apple in 2015?
Check Figure:
American Airlines’ 2013 Return on Equity was 66.67%

3) Which company had the highest Net Income and in which year? What was the value?
Check Figure:
JP Morgan Chase had the fourth highest Net Income in 2015 with 23,900M.

4) Which company had the lowest Net Income and in which year? What was the value?
Check Figure:
American Airlines had the third lowest Net Income in 2013 with -1,800M.

5) How many unique companies in your sample had Net Losses exceeding one billion dollars? Which companies, and what years?
Check Figure:
There were 4 unique companies that had Net Losses between 100M and 150M dollars. They were:

· Eagle Bulk Shipping in 2015

· Navios Maritime Holdings in 2013 and 2015

· Groupon in 2015

· Teekay Corporation in 2013

6) What was the Sum of the Net Income for all companies in the sample for 2015?
· When creating your PivotTable change the number format to Number with commas and 0 decimals.

Check Figure:
The Sum of Net Income for 2014 was 412,384M

7) Which company had the highest total Net Income over the three-year period? What was the value?
· When creating your PivotTable change the number format to Number with commas and 0 decimals.

Check Figure:
The company with the fourth highest total Net Income was Microsoft with 56,200M

8) Which company had the lowest total Net Income over the three-year period? What was the value?
· When creating your PivotTable change the number format to Number with commas and 0 decimals.

Check Figure:
The company with the third lowest total Net Income was Paragon Shipping with -332M

9) Which Industry had the highest Average Profit Margin over the three-year period? What was the value?
· When creating your PivotTable change the number format to Percentage with 2 decimals.

· When you create this PivotTable, you’ll find errors.

· Follow the instructions for Data Cleaning (explained in greater detail in the Tools section on page 3).

· Note: Remember that you want to delete the formulas in the cells that are getting the errors. Do not delete the base data or you will cause issues with your later answers.

· Refresh your PivotTable data to answer the question.

Check Figure:
Capital Goods had the third highest with 4.02%

10) In which year was the Average Profit Margin the highest for the entire sample? What was the value?
· When creating your PivotTable change the number format to Percentage with 2 decimals.

Check Figure:
The year the Average Profit Margin was the lowest for the entire sample was 2014 at -0.23%

11) For how many companies do you have Profit Margin data in 2013?
Check Figure:
There were 154 companies with Profit Margin ratio data in 2014

12) For what Industry do you have the most Profit Margin data in the sample? What was the value? For that Industry, what year was the highest? What was the value?
Check Figure:
The Industry with the least Profit Margin ratio data was Public Utilities with 71. The year it was highest was 2014 with 25.

DuPont Assignment Screen Clipping 1
· Take a screen clipping of your PivotTable from Q12 and paste below ‘DuPont Assignment Screen Clipping 1’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

13) Which Industry has the highest Average Asset Turnover over the three-year period? What was the value?
· When creating your PivotTable change the number format to Number with 2 decimals.

Check Figure:
The Industry with the lowest Asset Turnover was Finance with 0.13

DuPont Assignment Screen Clipping 2
· Create a PivotChart from the PivotTable in Q14.

· Create a Slicer for the Industry field and revise the chart by unselecting Consumer Services.

· Take a screen clipping of your PivotTable from Q14 and the PivotChart and Slicer you just created and paste below ‘DuPont Assignment Screen Clipping 2’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

14) Which of the remaining Industries has the highest Average Asset Turnover in 2014? What was the value?
· When creating your PivotTable change the number format to Number with 2 decimals.

Check Figure:
The remaining Industry with the third highest Average Asset Turnover in 2014 was Transportation with 0.67

DuPont Assignment Screen Clipping 3
· Create a PivotTable that shows Average Financial Leverage by Industry and Year.

· Use your cursor to highlight the data (don’t include Row or Column totals) and use Color Scale Conditional Formatting to highlight the high and low values as we did with the practice data.

· Take a screen clipping of the PivotTable you just created and paste below ‘DuPont Assignment Screen Clipping 3’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

15) Which Industry has the highest Average Financial Leverage over the three-year period? What was the value?
· When creating your PivotTable change the number format to Number with 2 decimals.

Check Figure:
The Industry with the second lowest Average Financial Leverage was Transportation with 2.35

16) Which Industry has the lowest Average Financial Leverage that does not include negative numbers in any year? What was the value?
· When creating your PivotTable change the number format to Number with 2 decimals.

Check Figure:
The Industry with the second lowest Average Financial Leverage that does not include negative numbers in any year was Capital Goods with 3.02

17) What is the Average Financial Leverage for the Transportation Industry in 2013? Note: The answer is odd. You will have to use Data Cleaning to resolve the issue.
· When creating your PivotTable change the number format to Number with 2 decimals.

· Transportation has a large negative value for Average Financial Leverage ratio in 2013 because three companies had negative Shareholder’s Equity in those years (which is unusual).

· Use Sort & Filter to clean your data (explained in greater detail in the Tools section on pages 3-4). Be sure to find and delete all the negative Financial Leverage ratios (but save the rest of the data).

· Now what is the Average Financial Leverage for Transportation companies in 2013?

Check Figure:
After the adjustments, the Average Financial Leverage for Technology companies in 2013 is 3.78

18) Which Industry has the highest Average Return on Equity over the three-year period, and which company is the highest within that Industry? What are the values?
· When creating your PivotTable change the number format to Percentage with 2 decimals.

Check Figure:
The Industry that has the second highest Average Return on Equity is Consumer Services with 23.73%, and the company that is highest within that Industry is Caesars Entertainment Corporation with 249.11%

DuPont Assignment Screen Clipping 4
· Take a screen clipping of the PivotTable from Q19 and paste below ‘DuPont Assignment Screen Clipping 4’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

19) Which two companies in the Public Utilities Industry have the highest Average Return on Equity during the period? What are the values?
· When creating your PivotTable change the number format to Percentage with 2 decimals.

Check Figure:
The two Public Utilities companies that have the lowest Average Return on Equity are Ocean Power Technologies with

-38.60% and American DG Energy with -42.75%

DuPont Assignment Screen Clipping 5
· Create a PivotTable that shows the Return on Equity by Industry and Year.

· When creating your PivotTable change the number format to Percentage with 2 decimals.

· Now add a Field that calculates the Percentage Change in Return on Equity between 2013 and 2014 and between 2014 and 2015.

· Hide unnecessary columns.

· Take a screen clipping of the PivotTable you just created and paste below ‘DuPont Assignment Screen Clipping 5’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

20) Which Industry had the largest decrease in Average Return on Equity between 2013 and 2014? What was the value?
Check Figure:
The Industry that had the second largest decrease in Average Return on Equity between 2013 and 2014 was Public Utilities with -53.70%

21) Which Industry had the largest increase in Average Return on Equity between 2014 and 2015? What was the value?
Check Figure:
The Industry that had the second largest increase in Average Return on Equity between 2014 and 2015 was Transportation with 136.34%

DuPont Assignment Screen Clippings 6
· Create a Dashboard that uses three of the PivotTable tables explored above.

· When creating your Dashboard make sure all your PivotTables have the correct number formats (consistent to what was used above).

· Follow the process used in the Practice Assignment to build a Dashboard.

· Decide on which PivotTables you think show interesting relationships/trends.

· Create a new version of each PivotTable in a new worksheet.

· Create a PivotChart for each PivotTable (for this type of process Column or Bar charts are probably best).

· Hide the PivotTables.

· Organize the PivotCharts in the best way to show the relationships/trends.

· Create Slicers for the fields involved in the relationships/trends.

· Link the Slicers to all the PivotTables in the Dashboard (and therefore the PivotCharts as well).

· Take a screen clipping of the Dashboard you just created and paste below ‘DuPont Assignment Screen Clipping 6’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

DuPont Assignment Screen Clippings 7
· Take a screen clipping of the Dashboard from Screen Clipping 6 showing the most interesting relationship you found and paste below ‘DuPont Assignment Screen Clipping 7’ in your ‘PivotTable Assignment Answer Sheet’ Word document.

22) Bonus Question 1: How many Industries were in your sample?
23) Bonus Question 2: PivotTables are a useful analytical tool for all business professionals; True or False?
24) Bonus Question 3: Which relationship displayed in the DuPont assignment was most interesting to you? Why?
Yuthas and Finn 2020 13

Based on data from Ernst & Young: Analytics mindset case study DuPont @2016