IL_EX19_8a_RubyRawls_1.xlsx

by

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

Leave a Comment

Dont compromise on quality.
Our Writers will service all your academic needs perfectly.

order now