Pivot Table | Course Paper

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

PIVOT TABLE ASSIGNMENT ANSWER SHEET

1) For all of the questions below, please type in your answer to the questions and include a

screenshot of the table that shows you this answer. The answers for Question 1 and 2 are given

below as examples.

2) Each question is worth 2 marks, 1 mark for the answer, 1 mark for the screenshot, except for the

discussion questions which are worth 3 marks each.

Marking Rubric:

Data Questions – 23 questions worth 2 marks each – Total 46 Marks

Discussion Questions – 1 question worth 6 marks – Total 6 marks

Total Marks – 52

(EXAMPLE) Question 1: What was the overall revenue? Which year had the highest revenue

and what was the revenue during that year?

Overall Revenue – $285,250,872.77

Year with Highest Revenue – 2007, $60,715,831.69

(EXAMPLE) Question 2: What is the Revenue for E-Bike in 2010?

E-Bike revenue in 2010 – $2,024,435.69

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

Question 3: What is the Revenue for E-Bike in 2009?

No revenue

Question 4: What is the Sales Quantity of T-shirts for Airport Bikes in 2007?

Sales Quantity of T-shirts in 2007: 807

Question 5: What year had the highest Sales Quantity?

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

2007 had the highest sales quantity

Question 6: What year had the lowest Revenue?

2009 had the lowest revenue

Question 7: In the year with the lowest Revenue, which Material had the lowest revenue?

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

In 2009, Fixed Gear Bike Plus as material has the lowest revenue.

Question 8: Which material had the lowest revenue overall?

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

Water Bottle: Total: $83,314 as lowest revenue

Question 9: In the year that had the highest Net Sales, what division had the highest Net

Sales?

BI division had the highest net sales in 2007 (year that had highest net sales)

Question 10: For the division in Question 9, which customer had the highest Net Sales?

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

Professional Road Bike (Shimano) had the highest net sales

Question 11: In the year with the lowest Revenue, what division had the highest sales

revenue?

Abhayjot Singh Chawla (8688596)

Microsoft Excel Pivot Tables

BI division had the highest revenue