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, direct materials, direct labor, variable overhead, 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 are total direct material costs?

How much are total direct labor costs?

How much are total overhead costs?

How much is total contribution margin?

2. Add a sheet and rename "Pivot table 2". On the sheet which is now called Pivot table 2 , 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

Answer the following:

Which sales person generated the highest sales revenue?

Which sales person generated the highest contribution margin?

Which sales person generated the lowest sales revenue?

Which sales person generated the lowest contribution margin? 3. Add a sheet and rename it "Pivot table 3". On the sheet which is now called Pivot table 3 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "Job #" to the Row Labels box and "amount" to the Values s box. On the table select the data in the sum of amounts column and sort from highest to lowest.

Answer the following:

Which job number generated the highest contribution margin?

Which job number generated the lowest contribution margin?

4. Add a sheet and rename "Pivot table 4". On the sheet which is now called Pivot table 4 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "customer" to the Row Labels box and "amount" to the Values box. On the table select the data in the sum of amounts column and sort from highest to lowest.

Which customer generated the highest contribution margin?

Which customer generated the lowest contribution margin?

5. Rename sheet 4 "Pivot table 5". On the sheet which is now called Pivot table 5 insert a pivot table at cell A1 using the data range provided on the data sheet (including the heading lines). Drag "location" to the Row Labels box and "amount" to the Values box. Which location generated the highest contribution margin? Which location generated the lowest contribution margin?

Submit your Excel worksheet and answers to the above.

9 s # * ARER : # # # * g # % # #*# #* - E - $ E *#* # # # **E # # # # 222 ? + # # * *2 , ##*# 9 s # * ARER : # # # * g # % # #*# #* - E - $ E *#* # # # **E # # # # 222 ? + # # * *2 , ##*#