ACG4401 M5 SAM Project 1A Forge & Polish Jewelry 1.Maureen Meek is doing an internship with Forge and Polish Jewelry. One of her assignments is to format t

ACG4401 M5 SAM Project 1A Forge & Polish Jewelry 1.Maureen Meek is doing an internship with Forge and Polish Jewelry. One of her assignments is to format the product sales records from 2018 to 2020 to make it easier to analyze. Switch to the Bracelet worksheet. Unfreeze the top row of the worksheet. 2.Sort the data in the BraceletSales table first in ascending order by the Material field and then in the ascending order by the Embellishment field. 3.Insert a Total Row in the BraceletSales table, and then use the Total Row to calculate the total of the values in both the 2018 and 2019 fields. (Hint: The Total Row should automatically total the values in the 2020 field.) 4.Maureen decides to create a PivotTable to allow her to better manipulate and filter the BraceletSales table data. Create a PivotTable based on the BraceletSales table in a new worksheet using Bracelet PivotTable as the worksheet name. Update the PivotTable as described below so that it matches Final Figure 1: New Perspectives Excel 2016 | Module 5: SAM Project 1a
Forge and Polish Jewelry
WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS
GETTING STARTED
•
Open the file NP_EX16_5a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as NP_EX16_5a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you automatically.
With the file NP_EX16_5a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
PROJECT STEPS
1.
Maureen Meek is doing an internship with Forge and Polish Jewelry. One of her
assignments is to format the product sales records from 2018 to 2020 to make
it easier to analyze.
Switch to the Bracelet worksheet. Unfreeze the top row of the worksheet.
2.
Sort the data in the BraceletSales table first in ascending order by the
Material field and then in the ascending order by the Embellishment field.
3.
Insert a Total Row in the BraceletSales table, and then use the Total Row to
calculate the total of the values in both the 2018 and 2019 fields. (Hint: The
Total Row should automatically total the values in the 2020 field.)
4.
Maureen decides to create a PivotTable to allow her to better manipulate and
filter the BraceletSales table data.
Create a PivotTable based on the BraceletSales table in a new worksheet using
Bracelet PivotTable as the worksheet name. Update the PivotTable as
described below so that it matches Final Figure 1:
a.
Add the Material field and the Product ID field (in that order) to the
Rows area. (Hint: The order of the materials should be Gold, Silver,
Copper, as shown in Final Figure 1. Sort the PivotTable manually by
dragging or by using the Move command if necessary.)
b.
Add the 2018, 2019, and 2020 fields (in that order) to the Values area.
c.
Update the Sum of 2018 field in the Values area to display the name
2018 Sales with the Accounting number format with 0 decimal places
and $ as the symbol.
New Perspectives Excel 2016 | Module 5: SAM Project 1a
d.
Update the Sum of 2019 field in the Values area to display the name
2019 Sales with the Accounting number format with 0 decimal places
and $ as the symbol.
e.
Update the Sum of 2020 field in the Values area to display the name
2020 Sales with the Accounting number format with 0 decimal places
and $ as the symbol.
5.
Go to the Necklace worksheet. Format the range A2:G16 as an Excel table with
headers using the Table Style Medium 6 table style. (Hint: Depending on
your version of Office, the table style may be named Blue, Table Style
Medium 6 instead.) Use NecklaceSales as the name of the table.
6.
Maureen notices that the NecklaceSales table is missing a record.
Add the following record as shown in bold in Table 1 below to the end of the
NecklaceSales table:
Table 1: New Record for the NecklaceSales Table
Product ID
Type
Material
Embellishment
2018
2019
2020
NGPS-123
Necklace
Gold
Precious
Stones
$1,820
$1,900
$2,160
7.
Forge and Polish Jewelry offerings a large selection of earrings. Maureen wants
to summarize the Earring sales data using subtotals to show how the type of
Material used influences earring sales.
Go to the Earring worksheet and complete the following steps:
a.
Sort the table by the Material field in ascending order.
b.
Convert the table to a normal range.
c.
Insert subtotals into the range A2:G25, with the subtotals appearing at
each change in the Material column value.
d.
The subtotals should use the SUM function and include subtotals for the
2018, 2019, and 2020 fields. (Hint: Make sure to check the summary
below data check box if it is not checked automatically.)
8.
Go to the Ring worksheet and remove the duplicate record associated with the
Product ID of RGGB-200 from the RingSales table.
9.
Maureen wants to summarize Forge and Polish Jewelry’s sales data for all
products in a PivotTable. To do so, she must first update All Products table.
Go to the All Products worksheet and freeze the top two rows of the worksheet.
10.
Use the Find command to find the record with a Product ID of BCPP-182. Edit
the record by changing the 2019 field value to $1200. Close the Find dialog
box.
New Perspectives Excel 2016 | Module 5: SAM Project 1a
11.
Filter the table to show only records for products with a Gold Material type and
a Pearl Embellishment type.
12.
Switch to the All Products PivotTable worksheet. Refresh the PivotTable data.
(Hint: After refreshing the PivotTable, the ECPP-138 record in row 18 should
now have a 2019 Sales field value of $580.)
13.
Apply the Pivot Style Medium 13 PivotTable style to the PivotTable. (Hint:
Depending on your version of Office, the PivotTable style may be named Light
Blue, Pivot Style Medium 13 instead.)
14.
Create a Filter for the PivotTable by adding the Embellishment field to the
Filters area. Filter the table so that only products with a Pearl embellishment
are visible.
15.
Create a Slicer that will filter the PivotTable based on the Material field value.
Resize the slicer so that it has a height of 1.75” and a width 3”. Move the
slicer so that its upper-left corner appears within cell F3 and its lower-right
corner appears within cell J11. Finally, use the slicer to filter the PivotTable so
that only products made of Copper are visible. (Hint: Depending on your
version of Office, the row order may appear different from Final Figure 7.)
16.
Maureen also wants to summarize sales data for all products using a PivotChart
to help determine what product lines should be expanded.
Switch to the Product Material PivotTable worksheet. Insert a PivotChart using
the Clustered Column chart type. Format the PivotChart as described below:
a.
Resize and reposition the PivotChart so that the upper-left corner is
located within cell F3 and the lower-right corner is located within cell O19.
b.
Add the chart title Sales by Material to the PivotChart using the Above
Chart option.
c.
Use the Type axis field button in the PivotChart to filter it so that only the
sales data for bracelets and earrings of each type of material appears in
the chart.
Your workbook should look like the Final Figures on the following pages. Save your
changes, close the workbook, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
New Perspectives Excel 2016 | Module 5: SAM Project 1a
Final Figure 1: Bracelet PivotTable Worksheet
Final Figure 2: Bracelet Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1a
Final Figure 3: Necklace Worksheet
Final Figure 4: Earring Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1a
Final Figure 5: Ring Worksheet
Final Figure 6: All Products Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1a
Final Figure 7: All Products PivotTable Worksheet
Final Figure 8: Product Material PivotTable Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
New Perspectives Excel 2016 | Module 5: SAM Project 1a
Forge and Polish Jewelry
WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS
hamad alshamsi
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Forge and Polish Jewelry
Sales Records – 2018 – 2020
Product ID
BGPS-194
BGGB-147
BGGB-104
BSPS-142
BCPS-117
BSPP-144
BCPS-132
BCPP-182
BSPS-136
BGGB-117
Type
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Material
Gold
Gold
Gold
Silver
Copper
Silver
Copper
Copper
Silver
Gold
Embellishment
Precious Stones
Glass Beads
Glass Beads
Precious Stones
Precious Stones
Pearl
Precious Stones
Pearl
Precious Stones
Glass Beads
2018
$420
$1,180
$890
$1,280
$800
$240
$1,030
$1,910
$70
$120
2019
$530
$920
$480
$1,740
$590
$470
$1,560
$1,200
$1,800
$1,780
2020
$1,620
$1,300
$1,990
$1,760
$340
$1,250
$1,650
$1,380
$1,380
$1,490
Forge and Polish Jewelry
Sales Records – 2018 – 2020
Product ID
NSPP-128
NSPP-242
NSGB-147
NSPS-143
NCPS-145
NCPS-223
NCPS-171
NCPS-178
NCGB-215
NCGB-223
NGGB-171
NSGB-196
NSPP-157
NGPP-119
Type
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Necklace
Material
Silver
Silver
Silver
Silver
Copper
Copper
Copper
Copper
Copper
Copper
Gold
Silver
Silver
Gold
Embellishment
Pearl
Pearl
Glass Beads
Precious Stones
Precious Stones
Precious Stones
Precious Stones
Precious Stones
Glass Beads
Glass Beads
Glass Beads
Glass Beads
Pearl
Pearl
2018
$680
$680
$340
$580
$1,610
$1,710
$760
$1,250
$1,070
$260
$340
$0
$780
$1,890
2019
$20
$280
$420
$1,830
$220
$570
$1,250
$1,120
$800
$1,900
$1,370
$1,410
$260
$600
2020
$1,340
$1,550
$1,420
$860
$90
$1,630
$980
$1,540
$310
$630
$1,650
$1,130
$580
$1,570
Forge and Polish Jewelry
Sales Records – 2018 – 2020
Product ID
ECPS-154
ECPP-234
EGPP-152
ESPP-242
EGPP-241
ECPB-177
ECPS-155
EGPP-203
EGPS-193
ECPP-138
EGPS-246
ECPB-191
EGPP-126
ESPP-144
EGPS-119
ECPS-189
EGPB-128
ECPP-227
ECPS-126
EGPP-196
EGPP-140
ECPP-213
ESPP-149
Type
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Earring
Material
Copper
Copper
Gold
Silver
Gold
Copper
Copper
Gold
Gold
Copper
Gold
Copper
Gold
Silver
Gold
Copper
Gold
Copper
Copper
Gold
Gold
Copper
Silver
Embellishment
Precious Stones
Pearl
Pearl
Pearl
Pearl
Glass Beads
Precious Stones
Pearl
Precious Stones
Pearl
Precious Stones
Glass Beads
Pearl
Pearl
Precious Stones
Precious Stones
Glass Beads
Pearl
Precious Stones
Pearl
Pearl
Pearl
Pearl
2018
$1,290
$1,150
$1,900
$1,920
$1,360
$240
$870
$330
$720
$1,540
$940
$1,180
$1,230
$1,470
$280
$1,520
$1,850
$210
$1,230
$320
$1,090
$1,140
$870
2019
$850
$650
$1,760
$610
$1,800
$480
$730
$930
$1,770
$850
$950
$600
$1,460
$690
$1,440
$1,770
$440
$710
$550
$1,480
$120
$1,930
$300
2020
$70
$240
$800
$850
$660
$1,110
$900
$1,890
$1,770
$1,940
$120
$40
$1,390
$1,900
$1,700
$560
$1,790
$1,100
$560
$850
$370
$1,170
$620
Forge and Polish Jewelry
Sales Records – 2018 – 2020
Product ID
RSPS-126
RGGB-200
RGPP-137
RGPP-103
RCGB-201
RSPS-228
RGPS-151
RGPS-215
RGGB-210
RCGB-224
RSPS-161
RSGB-170
RGGB-200
Type
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Ring
Material
Silver
Gold
Gold
Gold
Copper
Silver
Gold
Gold
Gold
Copper
Silver
Silver
Gold
Embellishment
Precious Stones
Glass Beads
Pearl
Pearl
Glass Beads
Precious Stones
Precious Stones
Precious Stones
Glass Beads
Glass Beads
Precious Stones
Glass Beads
Glass Beads
2018
$1,580
$1,590
$1,080
$980
$920
$1,790
$20
$1,250
$1,230
$1,110
$1,300
$1,650
$1,590
2019
$1,700
$1,360
$1,650
$1,880
$1,080
$340
$480
$1,980
$1,260
$510
$1,300
$1,190
$1,360
2020
$430
$1,190
$130
$100
$600
$270
$1,810
$1,340
$430
$410
$1,680
$80
$1,190
Forge and Polish Jewelry
Sales Records – 2018 – 2020
Product ID
ECPB-191
ECPS-154
RSGB-170
NCPS-145
RGPP-103
EGPS-246
RGPP-137
ECPP-234
RSPS-228
NCGB-215
BCPS-117
EGPP-140
RCGB-224
RSPS-126
RGGB-210
ECPS-189
ECPS-126
NSPP-157
RCGB-201
ESPP-149
NCGB-223
EGPP-241
EGPP-152
EGPP-196
ESPP-242
NSPS-143
ECPS-155
NCPS-171
ECPP-227
ECPB-177
NSGB-196
ECPP-213
RGGB-200
BSPP-144
BGGB-147
RGPS-215
NSPP-128
BSPS-136
BCPP-182
EGPP-126
NSGB-147
BGGB-117
Type
Earring
Earring
Ring
Necklace
Ring
Earring
Ring
Earring
Ring
Necklace
Bracelet
Earring
Ring
Ring
Ring
Earring
Earring
Necklace
Ring
Earring
Necklace
Earring
Earring
Earring
Earring
Necklace
Earring
Necklace
Earring
Earring
Necklace
Earring
Ring
Bracelet
Bracelet
Ring
Necklace
Bracelet
Bracelet
Earring
Necklace
Bracelet
Material
Copper
Copper
Silver
Copper
Gold
Gold
Gold
Copper
Silver
Copper
Copper
Gold
Copper
Silver
Gold
Copper
Copper
Silver
Copper
Silver
Copper
Gold
Gold
Gold
Silver
Silver
Copper
Copper
Copper
Copper
Silver
Copper
Gold
Silver
Gold
Gold
Silver
Silver
Copper
Gold
Silver
Gold
Embellishment
Glass Beads
Precious Stones
Glass Beads
Precious Stones
Pearl
Precious Stones
Pearl
Pearl
Precious Stones
Glass Beads
Precious Stones
Pearl
Glass Beads
Precious Stones
Glass Beads
Precious Stones
Precious Stones
Pearl
Glass Beads
Pearl
Glass Beads
Pearl
Pearl
Pearl
Pearl
Precious Stones
Precious Stones
Precious Stones
Pearl
Glass Beads
Glass Beads
Pearl
Glass Beads
Pearl
Glass Beads
Precious Stones
Pearl
Precious Stones
Pearl
Pearl
Glass Beads
Glass Beads
2018
$1,180
$1,290
$1,650
$1,610
$980
$940
$1,080
$1,150
$1,790
$1,070
$800
$1,090
$1,110
$1,580
$1,230
$1,520
$1,230
$780
$920
$870
$260
$1,360
$1,900
$320
$1,920
$580
$870
$760
$210
$240
$0
$1,140
$1,590
$240
$1,180
$1,250
$680
$70
$1,910
$1,230
$340
$120
2019
$600
$850
$1,190
$220
$1,880
$950
$1,650
$650
$340
$800
$590
$120
$510
$1,700
$1,260
$1,770
$550
$260
$1,080
$300
$1,900
$1,800
$1,760
$1,480
$610
$1,830
$730
$1,250
$710
$480
$1,410
$1,930
$1,360
$470
$920
$1,980
$20
$1,800
$120
$1,460
$420
$1,780
NCPS-178
NSPP-242
NGPP-119
BGPS-194
NCPS-223
BCPS-132
NGGB-171
RSPS-161
EGPS-119
BSPS-142
EGPS-193
EGPB-128
RGPS-151
EGPP-203
ESPP-144
ECPP-138
BGGB-104
NGPS-123
Necklace
Necklace
Necklace
Bracelet
Necklace
Bracelet
Necklace
Ring
Earring
Bracelet
Earring
Earring
Ring
Earring
Earring
Earring
Bracelet
Necklace
Copper
Silver
Gold
Gold
Copper
Copper
Gold
Silver
Gold
Silver
Gold
Gold
Gold
Gold
Silver
Copper
Gold
Gold
Precious Stones
Pearl
Pearl
Precious Stones
Precious Stones
Precious Stones
Glass Beads
Precious Stones
Precious Stones
Precious Stones
Precious Stones
Glass Beads
Precious Stones
Pearl
Pearl
Pearl
Glass Beads
Precious Stones
$1,250
$680
$1,890
$420
$1,710
$1,030
$340
$1,300
$280
$1,280
$720
$1,850
$20
$330
$1,470
$1,540
$890
$1,820
$1,120
$280
$600
$530
$570
$1,560
$1,370
$1,300
$1,440
$1,740
$1,770
$440
$480
$930
$690
$580
$480
$1,900
2020
$40
$70
$80
$90
$100
$120
$130
$240
$270
$310
$340
$370
$410
$430
$430
$560
$560
$580
$600
$620
$630
$660
$800
$850
$850
$860
$900
$980
$1,100
$1,110
$1,130
$1,170
$1,190
$1,250
$1,300
$1,340
$1,340
$1,380
$1,380
$1,390
$1,420
$1,490
$1,540
$1,550
$1,570
$1,620
$1,630
$1,650
$1,650
$1,680
$1,700
$1,760
$1,770
$1,790
$1,810
$1,890
$1,900
$1,940
$1,990
$1,675
Row Labels
2018 Sales 2019 Sales 2020 Sales
Bracelet
$ 7,940 $ 9,990 $ 14,160
BCPP-182 $ 1,910 $
120 $ 1,380
BCPS-117
$
800 $
590 $
340
BCPS-132
$ 1,030 $ 1,560 $ 1,650
BGGB-104 $
890 $
480 $ 1,990
BGGB-117 $
120 $ 1,780 $ 1,490
BGGB-147 $ 1,180 $
920 $ 1,300
BGPS-194 $
420 $
530 $ 1,620
BSPP-144
$
240 $
470 $ 1,250
BSPS-136
$
70 $ 1,800 $ 1,380
BSPS-142
$ 1,280 $ 1,740 $ 1,760
Earring
$ 24,650 $ 22,870 $ 22,400
ECPB-177
$
240 $
480 $ 1,110
ECPB-191
$ 1,180 $
600 $
40
ECPP-138
$ 1,540 $
850 $ 1,940
ECPP-213
$ 1,140 $ 1,930 $ 1,170
ECPP-227
$
210 $
710 $ 1,100
ECPP-234
$ 1,150 $
650 $
240
ECPS-126
$ 1,230 $
550 $
560
ECPS-154
$ 1,290 $
850 $
70
ECPS-155
$
870 $
730 $
900
ECPS-189
$ 1,520 $ 1,770 $
560
EGPB-128 $ 1,850 $
440 $ 1,790
EGPP-126 $ 1,230 $ 1,460 $ 1,390
EGPP-140 $ 1,090 $
120 $
370
EGPP-152 $ 1,900 $ 1,760 $
800
EGPP-196 $
320 $ 1,480 $
850
EGPP-203 $
330 $
930 $ 1,890
EGPP-241 $ 1,360 $ 1,800 $
660
EGPS-119 $
280 $ 1,440 $ 1,700
EGPS-193 $
720 $ 1,770 $ 1,770
EGPS-246 $
940 $
950 $
120
ESPP-144
$ 1,470 $
690 $ 1,900
ESPP-149
$
870 $
300 $
620
ESPP-242
$ 1,920 $
610 $
850
Necklace
$ 13,770 $ 13,950 $ 16,955
NCGB-215 $ 1,070 $
800 $
310
NCGB-223 $
260 $ 1,900 $
630
NCPS-145 $ 1,610 $
220 $
90
NCPS-171 $
760 $ 1,250 $
980
NCPS-178 $ 1,250 $ 1,120 $ 1,540
NCPS-223 $ 1,710 $
570 $ 1,630
NGGB-171 $
340 $ 1,370 $ 1,650
NGPP-119 $ 1,890 $
600 $ 1,570
NSGB-147
NSGB-196
NSPP-128
NSPP-157
NSPP-242
NSPS-143
NGPS-123
Ring
RCGB-201
RCGB-224
RGGB-200
RGGB-210
RGPP-103
RGPP-137
RGPS-151
RGPS-215
RSGB-170
RSPS-126
RSPS-161
RSPS-228
Grand Total
$
340 $
420
$
– $ 1,410
$
680 $
20
$
780 $
260
$
680 $
280
$
580 $ 1,830
$ 1,820 $ 1,900
$ 14,500 $ 14,730
$
920 $ 1,080
$ 1,110 $
510
$ 1,590 $ 1,360
$ 1,230 $ 1,260
$
980 $ 1,880
$ 1,080 $ 1,650
$
20 $
480
$ 1,250 $ 1,980
$ 1,650 $ 1,190
$ 1,580 $ 1,700
$ 1,300 $ 1,300
$ 1,790 $
340
$ 60,860 $ 61,540
$ 1,420
$ 1,130
$ 1,340
$
580
$ 1,550
$
860
$ 1,675
$ 8,470
$
600
$
410
$ 1,190
$
430
$
100
$
130
$ 1,810
$ 1,340
$
80
$
430
$ 1,680
$
270
$ 61,985
Row Labels
Gold
Bracelet
Earring
Necklace
Ring
Silver
Bracelet
Earring
Necklace
Ring
Copper
Bracelet
Earring
Necklace
Ring
Grand Total
2018 Sales 2019 Sales 2020 Sales
$ 2,610 $ 3,710 $ 6,400
$ 10,020 $ 12,150 $ 11,340
$ 4,050 $ 3,870 $ 4,895
$ 6,150 $ 8,610 $ 5,000
$
$
$
$
1,590
4,260
3,060
6,320
$
$
$
$
4,010
1,600
4,220
4,530
$ 3,740 $ 2,270
$ 10,370 $ 9,120
$ 6,660 $ 5,860
$ 2,030 $ 1,590
$ 60,860 $ 61,540
$
$
$
$
4,390
3,370
6,880
2,460
$ 3,370
$ 7,690
$ 5,180
$ 1,010
$ 61,985

Purchase answer to see full
attachment