Hi, this is an excel assignment the instructions are in word document
Documentation New Perspectives Excel 2016 | Module 5: SAM Project 1b The Optical Boutique WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS Author:Mouslam Alideeb 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. Prescription The Optical Boutique Sales Product CodeTypeMaterialShape201820192020 RXPS-101PrescriptionTitaniumRectangular$820$1,530$1,520 RXGB-135PrescriptionPlasticSquare$1,380$1,920$1,400 RXGB-114PrescriptionSSOval$1,090$880$1,990 RXPS-118PrescriptionPlasticRound$2,580$1,540$1,760 RXPS-122PrescriptionSSRound$1,800$1,590$940 RXPP-103PrescriptionSSOval$1,840$2,470$1,250 RXPS-112PrescriptionTitaniumRectangular$1,530$760$1,750 RXPP-182PrescriptionTitaniumSquare$2,910$1,500$1,380 RXPS-136PrescriptionPlasticSquare$170$1,800$1,380 RXGB-117PrescriptionPlasticOval$120$1,780$1,490 Sunglass The Optical Boutique Sales Product CodeTypeMaterialShape201820192020 SGPP-108SunglassTitaniumRound$880$1,120$1,840 SGPP-232SunglassTitaniumOval$780$1,280$1,550 SGGB-157SunglassPlasticOval$840$920$1,420 SGPS-143SunglassSSRectangular$980$1,830$1,860 SGPS-145SunglassSSRound$1,610$1,220$1,190 SGPS-223SunglassTitaniumRectangular$1,710$1,570$1,630 SGPS-171SunglassPlasticSquare$760$1,250$980 SGPS-178SunglassPlasticSquare$1,250$1,120$1,540 SGGB-215SunglassTitaniumRectangular$1,070$800$1,310 SGGB-223SunglassPlasticRound$260$1,900$630 SGGB-171SunglassPlasticOval$340$1,370$1,650 SGGB-196SunglassSSRectangular$980$1,410$1,130 SGPP-177SunglassTitaniumRound$780$260$580 SGPP-119SunglassPlasticOval$1,890$600$1,570 Reading The Optical Boutique Sales Product CodeTypeMaterialShape201820192020 RDPS-157ReadingTitaniumRound$1,390$650$170 RDPP-214ReadingPlasticOval$1,550$1,650$1,240 RDPP-152ReadingSSRectangular$900$760$800 RDPP-245ReadingTitaniumRound$920$610$850 RDPP-241ReadingPlasticSquare$360$800$660 RDPB-177ReadingSSSquare$840$580$1,110 RDPS-155ReadingSSSquare$870$730$900 RDPP-203ReadingTitaniumRectangular$330$930$1,890 RDPS-193ReadingPlasticRound$720$1,770$1,770 RDPP-138ReadingPlasticOval$1,340$1,850$1,940 RDPS-246ReadingTitaniumOval$940$950$120 RDPB-191ReadingTitaniumRound$1,180$600$40 RDPP-126ReadingPlasticRectangular$1,230$1,460$1,390 RDPP-144ReadingSSOval$1,470$690$1,900 RDPS-119ReadingTitaniumRound$280$1,440$1,700 RDPS-189ReadingTitaniumOval$1,520$1,770$560 RDPB-128ReadingPlasticRectangular$1,850$440$1,790 RDPP-227ReadingPlasticRound$210$710$1,100 RDPP-196ReadingSSSquare$820$1,480$850 RDPP-140ReadingTitaniumSquare$1,090$120$370 RDPP-213ReadingPlasticRectangular$1,140$1,930$1,170 RDPP-149ReadingTitaniumRound$870$300$620 Computer The Optical Boutique Sales Product CodeTypeMaterialShape201820192020 COPS-121ComputerTitaniumRound$580$700$430 COGB-210ComputerPlasticOval$590$360$790 COPP-137ComputerSSRectangular$880$650$130 COPP-103ComputerTitaniumRound$980$1,080$1,100 COGB-201ComputerPlasticSquare$920$1,080$600 COPS-228ComputerSSSquare$790$340$270 COPS-151ComputerSSSquare$220$480$1,810 COPS-215ComputerTitaniumRectangular$950$980$1,340 COGB-230ComputerPlasticRound$1,030$1,260$1,430 COGB-224ComputerPlasticOval$1,110$710$810 COPS-161ComputerTitaniumOval$1,300$1,300$1,680 COGB-170ComputerTitaniumRound$1,650$1,190$1,180 COGB-200ComputerPlasticRectangular$1,590$1,360$1,190 COPS-151ComputerSSSquare$220$480$1,810 All Products The Optical Boutique Sales Product CodeTypeMaterialShape201820192020 COPS-121ComputerTitaniumRound$580$700$430 COGB-210ComputerPlasticOval$590$360$790 COPP-137ComputerSSRectangular$880$650$130 COPP-103ComputerTitaniumRound$980$1,080$1,100 COGB-201ComputerPlasticSquare$920$1,080$600 COPS-228ComputerSSSquare$790$340$270 COPS-151ComputerSSSquare$220$480$1,810 COPS-215ComputerTitaniumRectangular$950$980$1,340 COGB-230ComputerPlasticRound$1,030$1,260$1,430 COGB-224ComputerPlasticOval$1,110$710$810 COPS-161ComputerTitaniumOval$1,300$1,300$1,680 COGB-170ComputerTitaniumRound$1,650$1,190$1,180 COGB-200ComputerPlasticRectangular$1,590$1,360$1,190 RDPS-157ReadingTitaniumRound$1,390$650$170 RDPP-214ReadingPlasticOval$1,550$1,650$1,240 RDPP-152ReadingSSRectangular$900$760$800 RDPP-245ReadingTitaniumRound$920$610$850 RDPP-241ReadingPlasticSquare$360$800$660 RDPB-177ReadingSSSquare$840$580$1,110 RDPS-155ReadingSSSquare$870$730$900 RDPP-203ReadingTitaniumRectangular$330$930$1,890 RDPS-193ReadingPlasticRound$720$1,770$1,770 RDPP-138ReadingPlasticOval$1,340$1,420$1,940 RDPS-246ReadingTitaniumOval$940$950$120 RDPB-191ReadingTitaniumRound$1,180$600$40 RDPP-126ReadingPlasticRectangular$1,230$1,460$1,390 RDPP-144ReadingSSOval$1,470$690$1,900 RDPS-119ReadingTitaniumRound$280$1,440$1,700 RDPS-189ReadingTitaniumOval$1,520$1,770$560 RDPB-128ReadingPlasticRectangular$1,850$440$1,790 RDPP-227ReadingPlasticRound$210$710$1,100 RDPP-196ReadingSSSquare$820$1,480$850 RDPP-140ReadingTitaniumSquare$1,090$120$370 RDPP-213ReadingPlasticRectangular$1,140$1,930$1,170 RDPP-149ReadingTitaniumRound$870$300$620 SGPP-108SunglassTitaniumRound$880$1,120$1,840 SGPP-232SunglassTitaniumOval$780$1,280$1,550 SGGB-157SunglassPlasticOval$840$920$1,420 SGPS-143SunglassSSRectangular$980$1,830$1,860 SGPS-145SunglassSSRound$1,610$1,220$1,190 SGPS-223SunglassTitaniumRectangular$1,710$1,570$1,630 SGPS-171SunglassPlasticSquare$760$1,250$980 SGPS-178SunglassPlasticSquare$1,250$1,120$1,540 SGGB-215SunglassTitaniumRectangular$1,070$800$1,310 SGGB-223SunglassPlasticRound$260$1,900$630 SGGB-171SunglassPlasticOval$340$1,370$1,650 SGGB-196SunglassSSRectangular$980$1,410$1,130 SGPP-177SunglassTitaniumRound$780$260$580 SGPP-119SunglassPlasticOval$1,890$600$1,570 RXPS-101PrescriptionTitaniumRectangular$820$1,530$1,520 RXGB-135PrescriptionPlasticSquare$1,380$1,920$1,400 RXGB-114PrescriptionSSOval$1,090$880$1,990 RXPS-118PrescriptionPlasticRound$2,580$1,500$1,760 RXPS-122PrescriptionSSRound$1,800$1,590$940 RXPP-103PrescriptionSSOval$1,840$2,470$1,250 RXPS-112PrescriptionTitaniumRectangular$1,530$760$1,750 RXPP-182PrescriptionTitaniumSquare$2,910$1,500$1,380 RXPS-136PrescriptionPlasticSquare$170$1,800$1,380 RXGB-117PrescriptionPlasticOval$120$1,780$1,490 All Products PT 2018 Sales2019 Sales2020 Sales Computer$ 12,590$ 11,490$ 12,760 COGB-170$ 1,650$ 1,190$ 1,180 COGB-200$ 1,590$ 1,360$ 1,190 COGB-201$ 920$ 1,080$ 600 COGB-210$ 590$ 360$ 790 COGB-224$ 1,110$ 710$ 810 COGB-230$ 1,030$ 1,260$ 1,430 COPP-103$ 980$ 1,080$ 1,100 COPP-137$ 880$ 650$ 130 COPS-121$ 580$ 700$ 430 COPS-151$ 220$ 480$ 1,810 COPS-161$ 1,300$ 1,300$ 1,680 COPS-215$ 950$ 980$ 1,340 COPS-228$ 790$ 340$ 270 Prescription$ 14,240$ 15,730$ 14,860 RXGB-114$ 1,090$ 880$ 1,990 RXGB-117$ 120$ 1,780$ 1,490 RXGB-135$ 1,380$ 1,920$ 1,400 RXPP-103$ 1,840$ 2,470$ 1,250 RXPP-182$ 2,910$ 1,500$ 1,380 RXPS-101$ 820$ 1,530$ 1,520 RXPS-112$ 1,530$ 760$ 1,750 RXPS-118$ 2,580$ 1,500$ 1,760 RXPS-122$ 1,800$ 1,590$ 940 RXPS-136$ 170$ 1,800$ 1,380 Reading$ 21,820$ 21,790$ 22,940 RDPB-128$ 1,850$ 440$ 1,790 RDPB-177$ 840$ 580$ 1,110 RDPB-191$ 1,180$ 600$ 40 RDPP-126$ 1,230$ 1,460$ 1,390 RDPP-138$ 1,340$ 1,420$ 1,940 RDPP-140$ 1,090$ 120$ 370 RDPP-144$ 1,470$ 690$ 1,900 RDPP-149$ 870$ 300$ 620 RDPP-152$ 900$ 760$ 800 RDPP-196$ 820$ 1,480$ 850 RDPP-203$ 330$ 930$ 1,890 RDPP-213$ 1,140$ 1,930$ 1,170 RDPP-214$ 1,550$ 1,650$ 1,240 RDPP-227$ 210$ 710$ 1,100 RDPP-241$ 360$ 800$ 660 RDPP-245$ 920$ 610$ 850 RDPS-119$ 280$ 1,440$ 1,700 RDPS-155$ 870$ 730$ 900 RDPS-157$ 1,390$ 650$ 170 RDPS-189$ 1,520$ 1,770$ 560 RDPS-193$ 720$ 1,770$ 1,770 RDPS-246$ 940$ 950$ 120 Sunglass$ 14,130$ 16,650$ 18,880 SGGB-157$ 840$ 920$ 1,420 SGGB-171$ 340$ 1,370$ 1,650 SGGB-196$ 980$ 1,410$ 1,130 SGGB-215$ 1,070$ 800$ 1,310 SGGB-223$ 260$ 1,900$ 630 SGPP-108$ 880$ 1,120$ 1,840 SGPP-119$ 1,890$ 600$ 1,570 SGPP-177$ 780$ 260$ 580 SGPP-232$ 780$ 1,280$ 1,550 SGPS-143$ 980$ 1,830$ 1,860 SGPS-145$ 1,610$ 1,220$ 1,190 SGPS-171$ 760$ 1,250$ 980 SGPS-178$ 1,250$ 1,120$ 1,540 SGPS-223$ 1,710$ 1,570$ 1,630 Grand Total$ 62,780$ 65,660$ 69,440 Product Material PT 2018 Sales2019 Sales2020 Sales Plastic$ 23,230$ 29,110$ 29,700 Computer$ 5,240$ 4,770$ 4,820 Prescription$ 4,250$ 7,000$ 6,030 Reading$ 8,400$ 10,180$ 11,060 Sunglass$ 5,340$ 7,160$ 7,790 SS$ 15,090$ 15,110$ 16,130 Computer$ 1,890$ 1,470$ 2,210 Prescription$ 4,730$ 4,940$ 4,180 Reading$ 4,900$ 4,240$ 5,560 Sunglass$ 3,570$ 4,460$ 4,180 Titanium$ 24,460$ 21,440$ 23,610 Computer$ 5,460$ 5,250$ 5,730 Prescription$ 5,260$ 3,790$ 4,650 Reading$ 8,520$ 7,370$ 6,320 Sunglass$ 5,220$ 5,030$ 6,910 Grand Total$ 62,780$ 65,660$ 69,440 New Perspectives Excel 2016 | Module 5: SAM Project 1b New Perspectives Excel 2016 | Module 5: SAM Project 1b The Optical Boutique Working with Excel TAbles, PivotTables, and PivotCharts GETTING STARTED Open the file NP_EX16_5b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as NP_EX16_5b_FirstLastName_2.xlsx by changing the “1” to a “2”. · 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_5b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. · If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS Colin Morin is the assistant sales manager for The Optical Boutique. One of his assignments is to format the product sales records from 2018 to 2020 to make it easier to analyze. Switch to the Prescription worksheet. Unfreeze the top row of the worksheet. Sort the data in the PrescriptionSales table first in ascending order by the Material field and then in the ascending order by the Shape field. Insert a Total Row in the PrescriptionSales 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.) Colin decides to create a PivotTable to allow him to better manipulate and filter the PrescriptionSales table data. Create a PivotTable based on the PrescriptionSales table in a new worksheet using Prescription PT 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 Code field (in that order) to the Rows area. (Hint: The order of the materials should be Plastic, SS, and Titanium, 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. Go to the Sunglass worksheet. Format the range A2:G16 as an Excel table with headers using the Table Style Medium 26 table style. (Hint: Depending on your version of Office, the table style may be named Light Yellow, Table Style Medium 26 instead.) Use SunglassSales as the name of the table. Colin notices that the SunglassSales table is missing a record. Add the following record as shown in bold in Table 1 below to the end of the SunglassSales table: Table 1: New Record for the SunglassSales Table Product Code Type Material Shape 2018 2019 2020 SGPP-125 Sunglass Titanium Rectangular $1,100 $990 $1,300 The Optical Boutique offers a large selection of reading glasses. Colin wants to summarize the reading sales data using subtotals to show how the type of Material used influences reading sales. Go to the Reading worksheet and complete the following steps: f. Sort the table by the Material field in ascending order. g. Convert the table to a normal range. h. Insert subtotals into the range A2:G24, with the subtotals appearing at each change in the Material column value. i. 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.) Go to the Computer worksheet and remove the duplicate record associated with the Product Code of COPS-151 from the ComputerSales table. Colin wants to summarize The Optical Boutique’s sales data for all products in a PivotTable. Before doing so, he must first update the data. Go to the All Products worksheet and freeze the top two rows of the worksheet. Use the Find command to find the record with a Product Code of RDPP-138. Edit the record by changing the 2019 field value to $1850. Close the Find dialog box. Filter the table to show only records for products with a Titanium material type and a Rectangular shape. Switch to the All Products PT worksheet. Refresh the PivotTable data. (Hint: After refreshing the PivotTable, the RDPP-138 record in row 34 should now have a 2019 Sales field value of $1,850.) Apply the Pivot Style Medium 26 PivotTable style to the PivotTable. (Hint: Depending on your version of Office, the PivotTable style may be named Light Yellow, Pivot Style Medium 26 instead.) Create a Filter for the PivotTable by adding the Shape field to the Filters area. Filter the table so that only products with an Oval Shape are visible. 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.5” and a width 2”. Move the slicer so that its upper-left corner appears within cell F3 and its lower-right corner appears within cell I10. Finally, use the slicer to filter the PivotTable so that only products made of Plastic are visible. (Hint: Depending on your version of Office, the row order may appear different from Final Figure 7.) Colin 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 PT worksheet. Insert a PivotChart using the Clustered Column chart type. Format the PivotChart as described below: j. Resize and reposition the PivotChart so that the upper-left corner is located within cell F3 and the lower-right corner is