ACC 381 - Accounting Information Systems Lab

Excel Assignment 4

Pivot Tables

Management has asked you to provide some information concerning retail sales patterns the company has experienced last year. Management has provided data in a workbook that is available on Exprep. They would like the following questions answered:

Using the data in the Sales worksheet in the Excel workbook you downloaded from Exprep, for each question below, you will create one PivotTable, each in the shaded area in its own separate worksheet, e.g.,

salesperson. number of units sold

1 38

2 47

3 37

5 55

6 148

8 75

(blank)

Total units sold 400.00

(this is just for illustrative purposes)

Ensure all columns are wide enough for the results to be read (no ###).

Questions

1. How many units did each salesperson sell in the year (also show the grand total)?

2. How many units of each product were sold during the year (also show the grand total)?

3. What were total dollar value of sales for each salesperson for the year (also show the grand total)?

4. What was the average number of units of each product per sale in each month (to 2 decimal places), along with average for each product, average total of all units sold for each month, and overall monthly average across all products? This should be done in ONE PivotTable. Hint: this will be a matrix with several columns, rather than just two.

5. How many units in total were sold in each month of the year (also show the grand total)?

6. How many units were sold in total in each region (also show the grand total)?

7. How many units were sold in total in each region by each sales person? Also show an annual total for each salesperson and region, as well as a grand total.

8. How many units were sold each day between Jan. 9 and Dec. 11? Also show the grand total.