Easy Excel Pivot Table Assignment

Pivot Table

Download the file sales.xlsx and open it with MS Excel. This file is a sample of 1000 sales transactions for the Expeditioner. For each sale, there is a row recording when it was sold, where it was sold, what was sold, how it was sold, the quantity sold, and the sales revenue.

There will be a sheet called Original Data. This is the data to use for all the pivot tables.

Continue to make Pivot Tables to answer the following questions: One pivot table for each question.

	Question	Answer
1	What was the value of Phone sales for London in the first quarter?
2	What percent of the total annual sales were Tokyo Web sales in the fourth quarter?
3	What percent of Sydney's annual sales was its Phone sales?
4	What was the value of Phone sales for London in January and give details of the transactions?	When Where What How Qty Revenue Jan London Jan London
5	What was the value of Camel saddle sales for Paris in 2012 by quarter?	Qtr1 Qtr2 Qtr3 Qtr4
6	How many Elephant polo sticks were sold in New York in each month of 2012?	Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
7	What are the five best-selling products based on quantity sold in 2012?
8	What are bottom 5 months that have generated the least sales?
9	What percent of total annual sales was Phone, Web and Store(How it was Sold)?	Phone Store Web
10	What are the running qty totals per quarter for Hammock?	Qtr1 Qtr2 Qtr3 Qtr4

1. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot1

b. What was the value of phone sales for London in the first quarter?

2. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot2

b. What percent of the total annual sales were Tokyo Web sales in the fourth quarter?

3. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot3

b. What percent of Sydney's annual sales was its Phone sales?

4. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot4

b. What was the value of phone sales for London in January and give details of the transactions?

5. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot5

b. What was the value of Camel saddle sales for Paris in 2012 by quarter?

6. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot6

b. How many Elephant polo sticks were sold in New York in each month of 2012?

7. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot7

b. What are the five best-selling products based on quantity sold in 2012?

8. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot8

b. What are bottom 5 months that have generated the least sales?

9. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot9

b. What percent of total annual sales was Phone, Web and Store(How it was Sold)?

10. Go to the Original Data Worksheet

a. Create a Pivot Table on a new worksheet and name the Worksheet Pivot10

b. What are the running qty totals per quarter for Hammock?

11. When finished you should have the following worksheets.

SUBMIT THE SPREADSHEET AND THIS DOCUMENT WITH THE ANSWERS FILLED IN TO THE EXCEL ASSIGNMENT DROPBOX.