Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 Jewelrys 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 Jewelry Sales 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
Forge and Polish Jewelry Sales Records - 2018 - 2020
Product ID Type Material Embellishment 2018 2019 2020
BGPS-194 Bracelet Gold Precious Stones $420 $530 $1,620
BGGB-147 Bracelet Gold Glass Beads $1,180 $920 $1,300
BGGB-104 Bracelet Gold Glass Beads $890 $480 $1,990
BSPS-142 Bracelet Silver Precious Stones $1,280 $1,740 $1,760
BCPS-117 Bracelet Copper Precious Stones $800 $590 $340
BSPP-144 Bracelet Silver Pearl $240 $470 $1,250
BCPS-132 Bracelet Copper Precious Stones $1,030 $1,560 $1,650
BCPP-182 Bracelet Copper Pearl $1,910 $1,200 $1,380
BSPS-136 Bracelet Silver Precious Stones $70 $1,800 $1,380
BGGB-117 Bracelet Gold Glass Beads $120 $1,780 $1,490
Forge and Polish Jewelry Sales Records - 2018 - 2020
Product ID Type Material Embellishment 2018 2019 2020
ECPS-154 Earring Copper Precious Stones $1,290 $850 $70
ECPP-234 Earring Copper Pearl $1,150 $650 $240
EGPP-152 Earring Gold Pearl $1,900 $1,760 $800
ESPP-242 Earring Silver Pearl $1,920 $610 $850
EGPP-241 Earring Gold Pearl $1,360 $1,800 $660
ECPB-177 Earring Copper Glass Beads $240 $480 $1,110
ECPS-155 Earring Copper Precious Stones $870 $730 $900
EGPP-203 Earring Gold Pearl $330 $930 $1,890
EGPS-193 Earring Gold Precious Stones $720 $1,770 $1,770
ECPP-138 Earring Copper Pearl $1,540 $850 $1,940
EGPS-246 Earring Gold Precious Stones $940 $950 $120
ECPB-191 Earring Copper Glass Beads $1,180 $600 $40
EGPP-126 Earring Gold Pearl $1,230 $1,460 $1,390
ESPP-144 Earring Silver Pearl $1,470 $690 $1,900
EGPS-119 Earring Gold Precious Stones $280 $1,440 $1,700
ECPS-189 Earring Copper Precious Stones $1,520 $1,770 $560
EGPB-128 Earring Gold Glass Beads $1,850 $440 $1,790
ECPP-227 Earring Copper Pearl $210 $710 $1,100
ECPS-126 Earring Copper Precious Stones $1,230 $550 $560
EGPP-196 Earring Gold Pearl $320 $1,480 $850
EGPP-140 Earring Gold Pearl $1,090 $120 $370
ECPP-213 Earring Copper Pearl $1,140 $1,930 $1,170
ESPP-149 Earring Silver Pearl $870 $300 $620
Forge and Polish Jewelry Sales Records - 2018 - 2020
Product ID Type Material Embellishment 2018 2019 2020
ECPB-191 Earring Copper Glass Beads $1,180 $600 $40
ECPS-154 Earring Copper Precious Stones $1,290 $850 $70
RSGB-170 Ring Silver Glass Beads $1,650 $1,190 $80
NCPS-145 Necklace Copper Precious Stones $1,610 $220 $90
RGPP-103 Ring Gold Pearl $980 $1,880 $100
EGPS-246 Earring Gold Precious Stones $940 $950 $120
RGPP-137 Ring Gold Pearl $1,080 $1,650 $130
ECPP-234 Earring Copper Pearl $1,150 $650 $240
RSPS-228 Ring Silver Precious Stones $1,790 $340 $270
NCGB-215 Necklace Copper Glass Beads $1,070 $800 $310
BCPS-117 Bracelet Copper Precious Stones $800 $590 $340
EGPP-140 Earring Gold Pearl $1,090 $120 $370
RCGB-224 Ring Copper Glass Beads $1,110 $510 $410
RSPS-126 Ring Silver Precious Stones $1,580 $1,700 $430
RGGB-210 Ring Gold Glass Beads $1,230 $1,260 $430
ECPS-189 Earring Copper Precious Stones $1,520 $1,770 $560
ECPS-126 Earring Copper Precious Stones $1,230 $550 $560
NSPP-157 Necklace Silver Pearl $780 $260 $580
RCGB-201 Ring Copper Glass Beads $920 $1,080 $600
ESPP-149 Earring Silver Pearl $870 $300 $620
NCGB-223 Necklace Copper Glass Beads $260 $1,900 $630
EGPP-241 Earring Gold Pearl $1,360 $1,800 $660
EGPP-152 Earring Gold Pearl $1,900 $1,760 $800
EGPP-196 Earring Gold Pearl $320 $1,480 $850
ESPP-242 Earring Silver Pearl $1,920 $610 $850
NSPS-143 Necklace Silver Precious Stones $580 $1,830 $860
ECPS-155 Earring Copper Precious Stones $870 $730 $900
NCPS-171 Necklace Copper Precious Stones $760 $1,250 $980
ECPP-227 Earring Copper Pearl $210 $710 $1,100
ECPB-177 Earring Copper Glass Beads $240 $480 $1,110
NSGB-196 Necklace Silver Glass Beads $0 $1,410 $1,130
ECPP-213 Earring Copper Pearl $1,140 $1,930 $1,170
RGGB-200 Ring Gold Glass Beads $1,590 $1,360 $1,190
BSPP-144 Bracelet Silver Pearl $240 $470 $1,250
BGGB-147 Bracelet Gold Glass Beads $1,180 $920 $1,300
RGPS-215 Ring Gold Precious Stones $1,250 $1,980 $1,340
NSPP-128 Necklace Silver Pearl $680 $20 $1,340
BSPS-136 Bracelet Silver Precious Stones $70 $1,800 $1,380
BCPP-182 Bracelet Copper Pearl $1,910 $120 $1,380
EGPP-126 Earring Gold Pearl $1,230 $1,460 $1,390
NSGB-147 Necklace Silver Glass Beads $340 $420 $1,420
BGGB-117 Bracelet Gold Glass Beads $120 $1,780 $1,490
NCPS-178 Necklace Copper Precious Stones $1,250 $1,120 $1,540
NSPP-242 Necklace Silver Pearl $680 $280 $1,550
NGPP-119 Necklace Gold Pearl $1,890 $600 $1,570
BGPS-194 Bracelet Gold Precious Stones $420 $530 $1,620
NCPS-223 Necklace Copper Precious Stones $1,710 $570 $1,630
BCPS-132 Bracelet Copper Precious Stones $1,030 $1,560 $1,650
NGGB-171 Necklace Gold Glass Beads $340 $1,370 $1,650
RSPS-161 Ring Silver Precious Stones $1,300 $1,300 $1,680
EGPS-119 Earring Gold Precious Stones $280 $1,440 $1,700
BSPS-142 Bracelet Silver Precious Stones $1,280 $1,740 $1,760
EGPS-193 Earring Gold Precious Stones $720 $1,770 $1,770
EGPB-128 Earring Gold Glass Beads $1,850 $440 $1,790
RGPS-151 Ring Gold Precious Stones $20 $480 $1,810
EGPP-203 Earring Gold Pearl $330 $930 $1,890
ESPP-144 Earring Silver Pearl $1,470 $690 $1,900
ECPP-138 Earring Copper Pearl $1,540 $580 $1,940
BGGB-104 Bracelet Gold Glass Beads $890 $480 $1,990
NGPS-123 Necklace Gold Precious Stones $1,820 $1,900 $1,675

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Applications Of Statistical Sampling To Auditing

Authors: Alvin A. Arens, James K. Loebbecke

1st Edition

0130391565, 978-0130391568

More Books

Students also viewed these Accounting questions

Question

Compare Jung and Adlers theories to Freuds psychoanalysis.

Answered: 1 week ago

Question

13.1 Explain the strategic role of employee benefits.

Answered: 1 week ago