Maureen Meek is doing an internship with Forge and Polish Jewelry. One of her as
ID: 2818203 • Letter: M
Question
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.
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.
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.
all product pivot pilot
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.
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.
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.
all product pivot pilot
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 $ 340 $ 420 $ 1,420 NSGB-196 $ - $ 1,410 $ 1,130 NSPP-128 $ 680 $ 20 $ 1,340 NSPP-157 $ 780 $ 260 $ 580 NSPP-242 $ 680 $ 280 $ 1,550 NSPS-143 $ 580 $ 1,830 $ 860 NGPS-123 $ 1,820 $ 1,900 $ 1,675 Ring $ 14,500 $ 14,730 $ 8,470 RCGB-201 $ 920 $ 1,080 $ 600 RCGB-224 $ 1,110 $ 510 $ 410 RGGB-200 $ 1,590 $ 1,360 $ 1,190 RGGB-210 $ 1,230 $ 1,260 $ 430 RGPP-103 $ 980 $ 1,880 $ 100 RGPP-137 $ 1,080 $ 1,650 $ 130 RGPS-151 $ 20 $ 480 $ 1,810 RGPS-215 $ 1,250 $ 1,980 $ 1,340 RSGB-170 $ 1,650 $ 1,190 $ 80 RSPS-126 $ 1,580 $ 1,700 $ 430 RSPS-161 $ 1,300 $ 1,300 $ 1,680 RSPS-228 $ 1,790 $ 340 $ 270 Grand Total $ 60,860 $ 61,540 $ 61,985 Row Labels 2018 Sales 2019 Sales 2020 Sales Gold Bracelet $ 2,610 $ 3,710 $ 6,400 Earring $ 10,020 $ 12,150 $ 11,340 Necklace $ 4,050 $ 3,870 $ 4,895 Ring $ 6,150 $ 8,610 $ 5,000 Silver Bracelet $ 1,590 $ 4,010 $ 4,390 Earring $ 4,260 $ 1,600 $ 3,370 Necklace $ 3,060 $ 4,220 $ 6,880 Ring $ 6,320 $ 4,530 $ 2,460 Copper Bracelet $ 3,740 $ 2,270 $ 3,370 Earring $ 10,370 $ 9,120 $ 7,690 Necklace $ 6,660 $ 5,860 $ 5,180 Ring $ 2,030 $ 1,590 $ 1,010 Grand Total $ 60,860 $ 61,540 $ 61,985 Forge and Polish JewelrySales Records - 2018 - 2020 Product ID Type Material Embellishment 2018 2019 2020 RSPS-126 Ring Silver Precious Stones $1,580 $1,700 $430 RGGB-200 Ring Gold Glass Beads $1,590 $1,360 $1,190 RGPP-137 Ring Gold Pearl $1,080 $1,650 $130 RGPP-103 Ring Gold Pearl $980 $1,880 $100 RCGB-201 Ring Copper Glass Beads $920 $1,080 $600 RSPS-228 Ring Silver Precious Stones $1,790 $340 $270 RGPS-151 Ring Gold Precious Stones $20 $480 $1,810 RGPS-215 Ring Gold Precious Stones $1,250 $1,980 $1,340 RGGB-210 Ring Gold Glass Beads $1,230 $1,260 $430 RCGB-224 Ring Copper Glass Beads $1,110 $510 $410 RSPS-161 Ring Silver Precious Stones $1,300 $1,300 $1,680 RSGB-170 Ring Silver Glass Beads $1,650 $1,190 $80 RGGB-200 Ring Gold Glass Beads $1,590 $1,360 $1,190 Forge and Polish Jewelry
Sales Records - 2018 - 2020 Product ID Type Material Embellishment 2018 2019 2020 NSPP-128 Necklace Silver Pearl $680 $20 $1,340 NSPP-242 Necklace Silver Pearl $680 $280 $1,550 NSGB-147 Necklace Silver Glass Beads $340 $420 $1,420 NSPS-143 Necklace Silver Precious Stones $580 $1,830 $860 NCPS-145 Necklace Copper Precious Stones $1,610 $220 $90 NCPS-223 Necklace Copper Precious Stones $1,710 $570 $1,630 NCPS-171 Necklace Copper Precious Stones $760 $1,250 $980 NCPS-178 Necklace Copper Precious Stones $1,250 $1,120 $1,540 NCGB-215 Necklace Copper Glass Beads $1,070 $800 $310 NCGB-223 Necklace Copper Glass Beads $260 $1,900 $630 NGGB-171 Necklace Gold Glass Beads $340 $1,370 $1,650 NSGB-196 Necklace Silver Glass Beads $0 $1,410 $1,130 NSPP-157 Necklace Silver Pearl $780 $260 $580 NGPP-119 Necklace Gold Pearl $1,890 $600 $1,570
Explanation / Answer
For 2 & 3
Use the sum formula in the total row
4. Insert>> Pivot Table>> Select the data in the above table>> Select the location where you want to insert the pivot table>> Add Material and Product ID to the Rows section>> Add the Years 2018 to 2020 to the values section.
Right click the field "Sum of 2018" >> Select value field settings>> Change the name to 2018 Sales>> Under value field settings select number format>> Select accounting>> Change the symbol to $ and decimal places to 0
Repeat these steps for the years 2019 and 2020, after which the pivot table should look as below:
For 5 & 6
For 7