Since well be changing data in this tab, you may wish to make a duplicate. 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.

o If you make a copy name it Original Practice Data.

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

This will add dropdown menus to your field headings.

Find the Debt to Assets ratio for Sunworks, highlight it in Red, and delete it

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: Now what is the year Average Debt to Assets ratio for Public Utilities?

ANSWER:

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, and why.

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 decimal places.

Question: Which Industry has the highest Gross Margin

ANSWER: Finance

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: Which Technology Company has the highest Average Gross Margin

ANSWER: Facebook

Add a calculated field to a PivotTable.

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

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

Click on the new Field and open the Value Field Settings menu and follow these instructions:

o Choose Average from the Summarize fields by tab

o Change the Custom Name to Change in Total Assets M

o Click on the Show Values As tab

o Click on the Show Values as dropdown menu the default is No Calculation and select Difference From.

o In the Base field area, select Year

o In the Base item area, select previous

Click OK to create the table.

The first column of percentages shows the change from to and the second shows the change from to

We dont need columns C H and I.

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: What was the Average Change in the value of Total Assets between and for the Transportation Industry?