Excel 2016 Module 5 - SAM Project

Anonymous
timer Asked: Nov 14th, 2017
account_balance_wallet $20

Question Description

i just need someone to do my project it not long and easy but i dont have time to do its an excel project

Unformatted Attachment Preview

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. 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. New Perspectives Excel 2016 | Module 5: SAM Project 1a 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. 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 abdullah alghamdi 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

Tutor Answer

criss53
School: UT Austin

Hello, here is your document.Have a good day.

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
abdullah alghamdi

edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.

Row Labels
Gold
BGGB-104
BGGB-117
BGGB-147
BGPS-194
Gold Total
Silver
BSPP-144
BSPS-136
BSPS-142
Silver Total
Copper
BCPP-182
BCPS-117
BCPS-132
Copper Total
Grand Total

2018 Sales 2019 Sales 2020 Sales
$
$
$
$
$

890
120
1.180
420
2.610

$
$
$
$
$

480
1.780
920
530
3.710

$
$
$
$
$

1.990
1.490
1.300
1.620
6.400

$
$
$
$

240
70
1.280
1.590

$
$
$
$

470
1.800
1.740
4.010

$
$
$
$

1.250
1.380
1.760
4.390

$
$
$
$
$

1.910
800
1.030
3.740
7.940

$ 1.200
$
590
$ 1.560
$ 3.350
$ 11.070

$ 1.380
$
340
$ 1.650
$ 3.370
$ 14.160

Forge and Polish Jewelry
Sales Records - 2018 - 2020
Product ID
BCPP-182
BCPS-117
BCPS-132
BGGB-147
BGGB-104
BGGB-117
BGPS-194
BSPP-144
BSPS-142
BSPS-136
Total

Type
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
Bracelet
B...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors