Move through the data set.

$$ Use Ctrl $+$ the arrow keys to move quickly around the Practice data.

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

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

ANSWER: $516$

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,300$M$.$

Question: How much were Apple$$s assets in $2015?$

ANSWER: $$290,500$M

Calculate the Debt to Assets ratio.

$$ In cell I$1($Column I, Row $1),$ type Debt to Assets.

$$ In cell I$2,$ type $=$F$2/$E$2$ and hit return. Your answer should be $0\mathrm{.}486997636,$ which means the company$$s loans 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: What was the Debt to Assets ratio for Apple Inc. in $2013?$

ANSWER: $8\%$

Calculate the Gross Margin ratio.

$$ In cell J$1,$ type Gross Margin $\%.$ In cell J$2,$ type $=($G$2-$H$2)/$G$2$ and hit return.

$$ Your answer should be approximately $0\mathrm{.}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: What was Arrow Financial$$s Gross Margin ratio for $2015?$

ANSWER: $100\%$

Create a PivotTable.

$$ Move your cursor to cell A$1.$

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

$$ Accept the default values 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: What is the last Field in the list of PivotTable Fields?

ANSWER: Gross Margin $\%$

Build your PivotTable.

$$ Add values and rows to the table.

$$ 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: What number shows for Sum of Net Revenues in $2015?$

ANSWER: $4188205\mathrm{.}199$

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: Now what number shows for Sum of Net Revenues in $2015?$

ANSWER: $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.$

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

$$ 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?

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

Question: Which Company had the highest Total Net Revenues?

ANSWER: Wall$-$Mart Stores Inc.

Change PivotTable calculations.

$$ Let$$s change the Grand Total to an average of the three years.

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

$$ Select Value