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!