excel pivot table

profilejjlexie
pivotassignsales_2.docx
Home>Business & Finance homework help>Accounting homework help>excel pivot table

Pivot Table/Chart

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 unit price, the quantity sold, the sale amount, a calculated discount amount and the total sales amount. The discount rate is currently 2% as can be seen in L2 and applies to sales greater than $1000.

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

Makes sure ALL ANSWERS are indicated in the table that I have provided. The answer will be the result of a pivot table.

Pivot Table 1

· Make a Pivot Table named Q1London

· Answer Question 1 in the table below. What is the value of Phone sales(Total Sales Amount) for London in the first quarter

· Use the Value Field settings dialogue box to change the custom heading to Total Sales and change the value in the pivot table to Accounting with 2 decimal points.

Pivot Table 2

· Make a Pivot Table named Q4Tokyo

· Answer Question 2 in the table below. What percent of the total annual sales(Total Sales Amount) for Expeditioner(476775.64) are Tokyo Web sales in the fourth quarter?

Pivot Table 3

· Make a Pivot Table named SydneyPhone

· Answer Question 3 in the table below. What percent of Sydney's annual sales(Total Sales Amount) of 29,628.32 is its Phone sales?

Sydney

Phone

5,421.72

Store

21,492.80

Web

2,713.80

Sydney Total

29,628.32

Pivot Table 4

· Make a Pivot Table named LondonJan

· Answer Question 4 in the table below. What is the value of Phone Total sales(Total Sales Amount) for London in January and give details of the transactions?

· Use the Value Field settings dialogue box to change the custom heading to Total Sales and change the value in the pivot table to Accounting with 2 decimal points.

· Make a new sheet Called Details LondonJan to show details.

Pivot Table 5

· Make a Pivot Table named ParisCamel

· Now go into the Original Data Tab and Change the Discount Rate in L2 to 8%.

· Answer Question 5a in the table below. What is the value of Camel saddle sales for Paris in 2015 by quarter when Discount Rate is 8%.

· Go into the Original Data Tab and Change the Discount Rate in L2 to 2%.

· Answer Question 5b in the table below. What was the value of Camel saddle sales for Paris in 2015 by quarter when Discount Rate is 2%.

· Use the Value Field settings dialogue box to add the custom heading Total Sales and change the value in the pivot table to Accounting with 2 decimal points.

Pivot Table 6

· Make a Pivot Table named ElephantNewYork

· Answer Question 6 in the table below. How many Elephant polo sticks were sold in New York in each month of 2015?

Pivot Table 7

· Make a Pivot Table named BestSelling

· Answer Question 7 in the table below. What were the five best-selling products based on quantity sold in 2015?

Pivot Table 8

· Make a Pivot Table named BottomSalesMonths

· Answer Question 8 in the table below. What are the bottom 5 months that have generated the least total sales?

Pivot Table 9

· Make a Pivot Table named HowSold

· Answer Question 9 in the table below. What percent of total annual sales was Phone, web and Store(How it was Sold)?

Pivot Table 10

· Make a Pivot Table named Hammock

· Answer Question 10 in the table below. What are the running qty totals per quarter for Hammock

Pivot Chart

· Insert a Pivot Chart on a new sheet called Chart

· Create a 3D Column Chart

· Show When and Where on the Axis Fields

· Show How on the Legend Fields

· Show Sum of Total Sales Amount in the Values

· Change the Floor Color

· Change the Back Wall Color or add a Picture

Question

Answer

1

What was the value of Phone sales for London in the first quarter?

$7722.04

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

SalesAmt

Jan

Jan

5

What would the value of Camel saddle sales for Paris in 2015 by quarter at 8% Discount Rate

Qtr1

Qtr2

Qtr3

Qtr4

What was the value of Camel saddle sales for Paris in 2015 by quarter at 2% Discount Rate

Qtr1

Qtr2

Qtr3

Qtr4

6

How many Elephant polo sticks were sold in New York in each month of 2015?

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

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

Example for Pivot 1  Go to the Original Data Worksheet

· Create a Pivot Table on a new worksheet and name the Worksheet Q1London

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

When completed you should have the following worksheets

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