IL_EX19_8a_RubyRawls_1.xlsx

Documentation

	Illustrated Excel 2019 \| Module 8: SAM Project 1a
	M&K Merchants Bank
	ANALYZE DATA WITH PIVOTTABLES
Author:	Ruby Rawls
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Orders

Cooking Utensil Orders
Customer	Region	Order Number	Date	Product	Quantity	Unit Price	Discount	Discounted Price	Total	Quantity per Region
CS1	Pacific	1101	1/1/21	Ladles	13	$19.25	5.0%	$18.29	$237.74	Midwest	87
CS5	Pacific	1742	1/10/21	Spatulas	15	$12.50	5.0%	$11.88	$178.13	Atlantic	63
CS2	Atlantic	1123	2/1/21	Skimmers	16	$19.75	7.5%	$18.27	$292.30	Pacific	48
CS2	Atlantic	1764	2/14/21	Spatulas	10	$12.50	7.5%	$11.56	$115.63	Southeast
CS3	Southeast	1145	3/1/21	Spatulas	15	$12.50	4.0%	$12.00	$180.00	Southwest	45
CS7	Southwest	1786	3/15/21	Skimmers	15	$19.75	5.0%	$18.76	$281.44
CS4	Midwest	1167	4/3/21	Skimmers	10	$19.75	10.0%	$17.78	$177.75
CS12	Midwest	1308	4/7/21	Wooden Spoons	18	$18.50	5.0%	$17.58	$316.35
CS5	Pacific	1189	5/6/21	Whisks	10	$11.75	4.0%	$11.28	$112.80
CS5	Pacific	1320	5/19/21	Skimmers	5	$19.75	4.0%	$18.96	$94.80
CS6	Atlantic	1102	5/20/21	Spatulas	5	$12.50	5.0%	$11.88	$59.38
CS6	Atlantic	1353	5/24/21	Tongs	15	$17.50	5.0%	$16.63	$249.38
CS9	Atlantic	1344	7/2/21	Tongs	2	$17.50	2.5%	$17.06	$34.13
CS10	Midwest	1365	7/5/21	Tongs	9	$17.50	4.0%	$16.80	$151.20
CS7	Southeast	1156	7/18/21	Spatulas	10	$12.50	5.0%	$11.88	$118.75
CS3	Southwest	1377	8/2/21	Tongs	15	$17.50	4.0%	$16.80	$252.00
CS4	Midwest	1148	9/1/21	Ladles	15	$19.25	10.0%	$17.33	$259.88
CS12	Midwest	1399	9/10/21	Wooden Spoons	20	$18.50	5.0%	$17.58	$351.50
CS4	Southwest	1380	9/20/21	Wooden Spoons	15	$18.50	10.0%	$16.65	$249.75
CS1	Pacific	1161	9/25/21	Ladles	5	$19.25	5.0%	$18.29	$91.44
CS2	Atlantic	1182	10/17/21	Skimmers	15	$19.75	7.5%	$18.27	$274.03
CS1	Pacific	1402	10/18/21	Wooden Spoons	10	$18.50	10.0%	$16.65	$166.50
CS2	Atlantic	1403	10/19/21	Tongs	10	$17.50	4.0%	$16.80	$168.00
CS5	Pacific	1404	10/20/21	Spatulas	12	$12.50	5.0%	$11.88	$142.50
CS14	Pacific	1405	10/21/21	Whisks	10	$11.75	4.0%	$11.28	$112.80
CS11	Southeast	1203	11/12/21	Skimmers	10	$19.75	2.5%	$19.26	$192.56
CS8	Midwest	1524	12/16/21	Spatulas	15	$12.50	2.5%	$12.19	$182.81

Products

Product	Region	Customer	Average Quantity	Sum of Discount	Average Discounted Price
Ladles	Midwest	CS4	15	0.1	17.325
	Pacific	CS1	9	0.1	18.2875
Spatulas	Midwest	CS8	15	0.025	12.1875
	Southeast	CS3	15	0.04	12
		CS7	10	0.05	11.875
	Pacific	CS5	15	0.05	11.875
	Atlantic	CS2	10	0.075	11.5625
		CS6	5	0.05	11.875
Skimmers	Midwest	CS4	10	0.1	17.775
	Southwest	CS7	15	0.05	18.7625
	Southeast	CS11	10	0.025	19.25625
	Pacific	CS5	5	0.04	18.96
	Atlantic	CS2	16	0.15	18.26875
Wooden Spoons	Midwest	CS12	19	0.1	17.575
	Southwest	CS4	15	0.1	16.65
Whisks	Pacific	CS5	10	0.04	11.28
Tongs	Midwest	CS10	9	0.04	16.8
	Southwest	CS3	15	0.04	16.8
	Atlantic	CS6	15	0.05	16.625
		CS9	2	0.025	17.0625
Grand Total			12	1.25	15.9536413043

Monthly Sales

Sum of Quantity
	Midwest	Southwest	Southeast	Pacific	Atlantic	Grand Total
Jan				28		28
Feb					26	26
Mar		15	15			30
Apr	28					28
May				15		15
Jun	9				22	31
Jul		15	10			25
Aug	35	15				50
Sep				5		5
Oct					15	15
Nov			10			10
Dec	15					15
Grand Total	87	45	35	48	63	278

Region PivotChart

Product Orders per Region

Total

SkimmersWhisksPacific94.800000000000011112.8

Regions

Date	May
	Total Order Amounts
Pacific	$ 207.60
Skimmers	$ 94.80
Whisks	$ 112.80
Grand Total	$ 207.60

Discounted Price

Average of Discounted Price	Column Labels
Row Labels	Ladles	Spatulas	Skimmers	Wooden Spoons	Whisks	Tongs	Grand Total
1101	18.29						18.29
CS1	18.29						18.29
1742		11.88					11.88
CS5		11.88					11.88
1123			18.27				18.27
CS2			18.27				18.27
1764		11.56					11.56
CS2		11.56					11.56
1145		12.00					12.00
CS3		12.00					12.00
1786			18.76				18.76
CS7			18.76				18.76
1167			17.78				17.78
CS4			17.78				17.78
1308				17.58			17.58
CS12				17.58			17.58
1189					11.28		11.28
CS5					11.28		11.28
1320			18.96				18.96
CS5			18.96				18.96
1102		11.88					11.88
CS6		11.88					11.88
1353						16.63	16.63
CS6						16.63	16.63
1344						17.06	17.06
CS9						17.06	17.06
1365						16.80	16.80
CS10						16.80	16.80
1156		11.88					11.88
CS7		11.88					11.88
1377						16.80	16.80
CS3						16.80	16.80
1148	17.33						17.33
CS4	17.33						17.33
1399				17.58			17.58
CS12				17.58			17.58
1380				16.65			16.65
CS4				16.65			16.65
1161	18.29						18.29
CS1	18.29						18.29
1182			18.27				18.27
CS2			18.27				18.27
1203			19.26				19.26
CS11			19.26				19.26
1524		12.19					12.19
CS8		12.19					12.19
Grand Total	17.97	11.90	18.55	17.27	11.28	16.82	15.95

Product Pricing

Product Pricing PivotTable
Row Labels	Sum of Quantity	Average of Unit Price
Ladles	33	$ 19.25
CS1	18	$ 19.25
CS4	15	$ 19.25
Spatulas	70	$ 12.50
CS5	15	$ 12.50
CS2	10	$ 12.50
CS3	15	$ 12.50
CS7	10	$ 12.50
CS6	5	$ 12.50
CS8	15	$ 12.50
Skimmers	71	$ 19.75
CS5	5	$ 19.75
CS2	31	$ 19.75
CS7	15	$ 19.75
CS4	10	$ 19.75
CS11	10	$ 19.75
Wooden Spoons	53	$ 18.50
CS4	15	$ 18.50
CS12	38	$ 18.50
Whisks	10	$ 11.75
CS5	10	$ 11.75
Tongs	41	$ 17.50
CS3	15	$ 17.50
CS6	15	$ 17.50
CS9	2	$ 17.50
CS10	9	$ 17.50
Grand Total	278	$ 16.89

IL_EX19_8a_RubyRawls_1.xlsx

IL_EX19_8a_RubyRawls_1.xlsx

Documentation

Orders

Products

Monthly Sales

Region PivotChart

Regions

Discounted Price

Product Pricing

Related posts

HW