INSTRUCTIONS:
1. Add a sheet to the worksheet and rename sheet Pivot table 1. On sheet which is now called Pivot table 1, go to
cell A1 then “insert” “pivot table” using the data range provided on the data sheet (including the heading lines).
Drag “account” to the row labels and “amount” to the ∑Values. You should now see the dollar amounts for sales
revenue, cost of goods sold, commission expense, insurance expense, salary expense, depreciation expense, and
Grand Total (contribution margin). If you don’t see the dollar amounts click the arrow next to amounts in the
∑Values box and choose “Sum of amount”. Next, answer the following:
How much are total sales revenues?
How much is cost of goods sold?
How much is commission expense?
How much is insurance expense?
How much is salary expense?
How much is depreciation expense?
How much is total net income?
2. Add a sheet and rename “Pivot table 2”. On the Pivot table 2 sheet, insert a pivot table in cell A1 using the data
range provided on the data sheet (including the heading lines). Drag “account” to the Row Labels box, drag
“location” to the Column Labels box, and drag “amount” to the ∑Values box.
Answer the following:
Highest sales revenues were generated in which state?
Lowest sales revenues were generated in which state?
Highest net income was generated in which state?
Lowest net income was generated in which state?
3. Add a sheet and rename “Pivot table 3”. On the Pivot table 3 sheet, insert a pivot table in cell A1 using the data
range provided on the data sheet (including the heading lines). Drag “sales person” to the Row Labels box, drag
“account” to the Column Labels box, and drag “amount” to the ∑Values box. Next sort the data by sales revenue by
clicking on any amount in the “sales revenue” column, click “data”, then “sort”, then choose “smallest to largest”
and “top to bottom”.
Answer the following:
Which sales person generated the highest sales revenue?
Which sales person generated the lowest sales revenue?
Now sort the data in the “grand total” net income column:
Which sales person generated the highest net income?
Which sales person generated the lowest net income?
4. Add a sheet and rename “Pivot table 4”. On the Pivot table 4 sheet, insert a pivot table in cell A1 using the data
range provided on the data sheet (including the heading lines). Drag “date” to the Row Labels box, and drag
“amount” to the ∑Values box. Sort the amounts from lowest to highest.
Answer the following:
Lowest net income was earned on which date?
Highest net income was earned on which date?
Submit your Excel worksheet and answers to the above.
