Posts

Illustrated Excel 2016 | Module 12: assignment Help

Illustrated Excel 2016 | Module 12: assignment Help

Documentation

Illustrated Excel 2016 | Module 12: SAM Project 1a
The Spice Market
ANALYZING DATA WITH PIVOTTABLES
Author: Latrice Reaves
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

Spice Pack Orders
Customer Region Order Number Date Product Quantity Unit Price Discount Discounted Price Total Quantity per Region
SP1 Northwest 500 1/1/18 Mediterranean 30 $19.25 5.0% $18.29 $548.63 Midwest 227
SP5 Northwest 554 1/10/18 Starter Set 85 $12.50 5.0% $11.88 $1,009.38 Northeast 213
SP2 Northeast 502 2/1/18 Grill Set 36 $19.75 7.5% $18.27 $657.68 Northwest 212
SP2 Northeast 556 2/14/18 Starter Set 30 $12.50 7.5% $11.56 $346.88 Southeast 75
SP3 Southeast 504 3/1/18 Starter Set 45 $12.50 4.0% $12.00 $540.00 Southwest
SP7 Southwest 558 3/15/18 Grill Set 25 $19.75 5.0% $18.76 $469.06
SP4 Midwest 506 4/3/18 Grill Set 60 $19.75 10.0% $17.78 $1,066.50
SP12 Midwest 530 4/7/18 Curry Set 28 $18.50 5.0% $17.58 $492.10
SP5 Northwest 508 5/6/18 Holiday 40 $11.75 4.0% $11.28 $451.20
SP5 Northwest 532 5/19/18 Grill Set 32 $19.75 4.0% $18.96 $606.72
SP6 Northeast 510 6/1/18 Starter Set 25 $12.50 5.0% $11.88 $296.88
SP6 Northeast 535 6/14/18 Chile Set 45 $17.50 5.0% $16.63 $748.13
SP9 Northeast 534 6/20/18 Chile Set 52 $17.50 2.5% $17.06 $887.25
SP10 Midwest 536 6/25/18 Chile Set 19 $17.50 4.0% $16.80 $319.20
SP7 Southeast 515 7/18/18 Starter Set 10 $12.50 5.0% $11.88 $118.75
SP3 Southwest 537 7/22/18 Chile Set 35 $17.50 4.0% $16.80 $588.00
SP4 Midwest 514 8/1/18 Mediterranean 45 $19.25 10.0% $17.33 $779.63
SP12 Midwest 539 8/10/18 Curry Set 40 $18.50 5.0% $17.58 $703.00
SP4 Southwest 538 8/30/18 Curry Set 55 $18.50 10.0% $16.65 $915.75
SP1 Northwest 516 9/5/18 Mediterranean 25 $19.25 5.0% $18.29 $457.19
SP2 Northeast 518 10/7/18 Grill Set 25 $19.75 7.5% $18.27 $456.72
SP11 Southeast 520 11/12/18 Grill Set 20 $19.75 2.5% $19.26 $385.13
SP8 Midwest 552 12/16/18 Starter Set 35 $12.50 2.5% $12.19 $426.56

Products

Product Region Average Quantity Sum of Discount Average Discounted Price
Mediterranean Midwest 45 0.1 17.325
Northwest 28 0.1 18.2875
Starter Set Midwest 35 0.025 12.1875
Northwest 85 0.05 11.875
Northeast 28 0.125 11.71875
Southeast 28 0.09 11.9375
Grill Set Midwest 60 0.1 17.775
Southwest 25 0.05 18.7625
Northwest 32 0.04 18.96
Northeast 31 0.15 18.26875
Southeast 20 0.025 19.25625
Curry Set Midwest 34 0.1 17.575
Southwest 55 0.1 16.65
Holiday Northwest 40 0.04 11.28
Chile Set Midwest 19 0.04 16.8
Southwest 35 0.04 16.8
Northeast 49 0.075 16.84375
Grand Total 37 1.25 15.9536413043

Monthly Sales

Product (All)
Sum of Quantity
Midwest Southwest Northwest Northeast Southeast Grand Total
Jan 115 115
Feb 66 66
Mar 25 45 70
Apr 88 88
May 72 72
Jun 122 122
Jul 19 35 10 64
Aug 85 55 140
Sep 25 25
Oct 25 25
Nov 20 20
Dec 35 35
Grand Total 227 115 212 213 75 842

Region PivotChart

Product Order Amounts per Region

Starter Set Southwest Southeast 540 Grill Set Southwest Southeast 469.0625

 

 

 

 

Regions

Date Mar
Total Order Amounts
Starter Set Grill Set Grand Total
Southwest $ 469.06 $ 469.06
Southeast $ 540.00 $ 540.00
Grand Total $ 540.00 $ 469.06 $ 1,009.06

Discounted Price

Average of Discounted Price Column Labels
Row Labels Chile Set Curry Set Grill Set Holiday Mediterranean Starter Set Grand Total
500 18.29 18.29
SP1 18.29 18.29
502 18.27 18.27
SP2 18.27 18.27
504 12.00 12.00
SP3 12.00 12.00
506 17.78 17.78
SP4 17.78 17.78
508 11.28 11.28
SP5 11.28 11.28
510 11.88 11.88
SP6 11.88 11.88
514 17.33 17.33
SP4 17.33 17.33
515 11.88 11.88
SP7 11.88 11.88
516 18.29 18.29
SP1 18.29 18.29
518 18.27 18.27
SP2 18.27 18.27
520 19.26 19.26
SP11 19.26 19.26
530 17.58 17.58
SP12 17.58 17.58
532 18.96 18.96
SP5 18.96 18.96
534 17.06 17.06
SP9 17.06 17.06
535 16.63 16.63
SP6 16.63 16.63
536 16.80 16.80
SP10 16.80 16.80
537 16.80 16.80
SP3 16.80 16.80
538 16.65 16.65
SP4 16.65 16.65
539 17.58 17.58
SP12 17.58 17.58
552 12.19 12.19
SP8 12.19 12.19
554 11.88 11.88
SP5 11.88 11.88
556 11.56 11.56
SP2 11.56 11.56
558 18.76 18.76
SP7 18.76 18.76
Grand Total 16.82 17.27 18.55 11.28 17.97 11.90 15.95

Product Pricing

Product Pricing PivotTable
Row Labels Sum of Quantity Average of Unit Price
Mediterranean 100 $ 19.25
SP1 55 $ 19.25
SP4 45 $ 19.25
Starter Set 230 $ 12.50
SP5 85 $ 12.50
SP2 30 $ 12.50
SP3 45 $ 12.50
SP7 10 $ 12.50
SP6 25 $ 12.50
SP8 35 $ 12.50
Grill Set 198 $ 19.75
SP5 32 $ 19.75
SP2 61 $ 19.75
SP7 25 $ 19.75
SP4 60 $ 19.75
SP11 20 $ 19.75
Curry Set 123 $ 18.50
SP4 55 $ 18.50
SP12 68 $ 18.50
Holiday 40 $ 11.75
SP5 40 $ 11.75
Chile Set 151 $ 17.50
SP3 35 $ 17.50
SP6 45 $ 17.50
SP9 52 $ 17.50
SP10 19 $ 17.50
Grand Total 842 $ 16.89
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!