excel question 3,1

Sales Orders-Instructions

INSTRUCTIONS

Create the Following:

Pivot Tables

Pivot Table to show Total Sales by Item Region; format in outline form and show Region Total as % of Grand Total and Item Total as % of Parent Total

Pivot Table to show Distribution of Total Sales, grouped by $100 increments

Pivot Table to show Total Sales by Rep by Region; move subtotals to the bottom of each Region and sort in descending order by Total Sales

Pivot Table to show Total Sales by Rep with Region as a Filter

Charts

Create Clustered Column Chart to show Sales by Region by Item

Create a Column Chart to show Sales by Month with Average Sales as Trend Line (hint: may have to copy/paste data from Pivot Table and crete chart that way)

Create a Pie Chart to show Total Sales by Rep for just the East Region

Create a Clustered Column chart- what would be a good subset of the data to use?

Sales Orders- Data

Order Date	Region	Rep Name	Item Name	Quantity	Price per Unit	Total Sales
9/1/18	Central	Smith	Desk	2	$ 125.00	$ 250.00
8/24/19	West	Sorvino	Desk	3	$ 275.00	$ 825.00
2/18/19	East	Jones	Binder	4	$ 4.99	$ 19.96
6/17/19	Central	Kivell	Desk	5	$ 125.00	$ 625.00
3/7/19	West	Sorvino	Binder	7	$ 19.99	$ 139.93
9/10/19	Central	Gill	Pencil	7	$ 1.29	$ 9.03
11/17/19	Central	Jardine	Binder	11	$ 4.99	$ 54.89
10/31/19	Central	Andrews	Pencil	14	$ 1.29	$ 18.06
11/8/18	East	Parent	Pen	15	$ 19.99	$ 299.85
9/18/18	East	Jones	Pen Set	16	$ 15.99	$ 255.84
2/26/18	Central	Gill	Pen	27	$ 19.99	$ 539.73
10/5/18	Central	Morgan	Binder	28	$ 8.99	$ 251.72
12/21/19	Central	Andrews	Binder	28	$ 4.99	$ 139.72
7/12/18	East	Howard	Binder	29	$ 1.99	$ 57.71
5/22/18	West	Thompson	Pencil	32	$ 1.99	$ 63.68
8/15/18	East	Jones	Pencil	35	$ 4.99	$ 174.65
2/9/18	Central	Jardine	Pencil	36	$ 4.99	$ 179.64
8/7/19	Central	Kivell	Pen Set	42	$ 23.95	$ 1,005.90
1/15/19	Central	Gill	Binder	46	$ 8.99	$ 413.54
1/23/18	Central	Kivell	Binder	50	$ 19.99	$ 999.50
3/24/19	Central	Jardine	Pen Set	50	$ 4.99	$ 249.50
5/14/19	Central	Gill	Pencil	53	$ 1.29	$ 68.37
7/21/19	Central	Morgan	Pen Set	55	$ 12.49	$ 686.95
3/15/18	West	Sorvino	Pencil	56	$ 2.99	$ 167.44
10/14/19	West	Thompson	Binder	57	$ 19.99	$ 1,139.43
4/1/18	East	Jones	Binder	60	$ 4.99	$ 299.40
6/8/18	East	Jones	Binder	60	$ 8.99	$ 539.40
7/4/19	East	Jones	Pen Set	62	$ 4.99	$ 309.38
10/22/18	East	Jones	Pen	64	$ 8.99	$ 575.36
4/10/19	Central	Andrews	Pencil	66	$ 1.99	$ 131.34
12/12/18	Central	Smith	Pencil	67	$ 1.29	$ 86.43
12/29/18	East	Parent	Pen Set	74	$ 15.99	$ 1,183.26
4/18/18	Central	Andrews	Pencil	75	$ 1.99	$ 149.25
9/27/19	West	Sorvino	Pen	76	$ 1.99	$ 151.24
5/31/19	Central	Gill	Binder	80	$ 8.99	$ 719.20
7/29/18	East	Parent	Binder	81	$ 19.99	$ 1,619.19
2/1/19	Central	Smith	Binder	87	$ 15.00	$ 1,305.00
5/5/18	Central	Jardine	Pencil	90	$ 4.99	$ 449.10
6/25/18	Central	Morgan	Pencil	90	$ 4.99	$ 449.10
12/4/19	Central	Jardine	Binder	94	$ 19.99	$ 1,879.06
1/6/18	East	Jones	Pencil	95	$ 1.99	$ 189.05
4/27/19	East	Howard	Pen	96	$ 4.99	$ 479.04
11/25/18	Central	Kivell	Pen Set	96	$ 4.99	$ 479.04