Documentation
New Perspectives Excel 2016 | Module 5: SAM Project 1
The Optical Boutique
WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS
Author: Mouslam Alidee
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 Code Type Material Shape 2018 2019 2020
RXPS-101 Prescription Titanium Rectangular $820 $1,530 $1,520
RXGB-135 Prescription Plastic Square $1,380 $1,920 $1,400
RXGB-114 Prescription SS Oval $1,090 $880 $1,990
RXPS-118 Prescription Plastic Round $2,580 $1,540 $1,760
RXPS-122 Prescription SS Round $1,800 $1,590 $940
RXPP-103 Prescription SS Oval $1,840 $2,470 $1,250
RXPS-112 Prescription Titanium Rectangular $1,530 $760 $1,750
RXPP-182 Prescription Titanium Square $2,910 $1,500 $1,380
RXPS-136 Prescription Plastic Square $170 $1,800 $1,380
RXGB-117 Prescription Plastic Oval $120 $1,780 $1,490
Sunglass
The Optical Boutique
Sales
Product Code Type Material Shape 2018 2019 2020
SGPP-108 Sunglass Titanium Round $880 $1,120 $1,840
SGPP-232 Sunglass Titanium Oval $780 $1,280 $1,550
SGGB-157 Sunglass Plastic Oval $840 $920 $1,420
SGPS-143 Sunglass SS Rectangular $980 $1,830 $1,860
SGPS-145 Sunglass SS Round $1,610 $1,220 $1,190
SGPS-223 Sunglass Titanium Rectangular $1,710 $1,570 $1,630
SGPS-171 Sunglass Plastic Square $760 $1,250 $980
SGPS-178 Sunglass Plastic Square $1,250 $1,120 $1,540
SGGB-215 Sunglass Titanium Rectangular $1,070 $800 $1,310
SGGB-223 Sunglass Plastic Round $260 $1,900 $630
SGGB-171 Sunglass Plastic Oval $340 $1,370 $1,650
SGGB-196 Sunglass SS Rectangular $980 $1,410 $1,130
SGPP-177 Sunglass Titanium Round $780 $260 $580
SGPP-119 Sunglass Plastic Oval $1,890 $600 $1,570
Reading
The Optical Boutique
Sales
Product Code Type Material Shape 2018 2019 2020
RDPS-157 Reading Titanium Round $1,390 $650 $170
RDPP-214 Reading Plastic Oval $1,550 $1,650 $1,240
RDPP-152 Reading SS Rectangular $900 $760 $800
RDPP-245 Reading Titanium Round $920 $610 $850
RDPP-241 Reading Plastic Square $360 $800 $660
RDPB-177 Reading SS Square $840 $580 $1,110
RDPS-155 Reading SS Square $870 $730 $900
RDPP-203 Reading Titanium Rectangular $330 $930 $1,890
RDPS-193 Reading Plastic Round $720 $1,770 $1,770
RDPP-138 Reading Plastic Oval $1,340 $1,850 $1,940
RDPS-246 Reading Titanium Oval $940 $950 $120
RDPB-191 Reading Titanium Round $1,180 $600 $40
RDPP-126 Reading Plastic Rectangular $1,230 $1,460 $1,390
RDPP-144 Reading SS Oval $1,470 $690 $1,900
RDPS-119 Reading Titanium Round $280 $1,440 $1,700
RDPS-189 Reading Titanium Oval $1,520 $1,770 $560
RDPB-128 Reading Plastic Rectangular $1,850 $440 $1,790
RDPP-227 Reading Plastic Round $210 $710 $1,100
RDPP-196 Reading SS Square $820 $1,480 $850
RDPP-140 Reading Titanium Square $1,090 $120 $370
RDPP-213 Reading Plastic Rectangular $1,140 $1,930 $1,170
RDPP-149 Reading Titanium Round $870 $300 $620
Compute
The Optical Boutique
Sales
Product Code Type Material Shape 2018 2019 2020
COPS-121 Computer Titanium Round $580 $700 $430
COGB-210 Computer Plastic Oval $590 $360 $790
COPP-137 Computer SS Rectangular $880 $650 $130
COPP-103 Computer Titanium Round $980 $1,080 $1,100
COGB-201 Computer Plastic Square $920 $1,080 $600
COPS-228 Computer SS Square $790 $340 $270
COPS-151 Computer SS Square $220 $480 $1,810
COPS-215 Computer Titanium Rectangular $950 $980 $1,340
COGB-230 Computer Plastic Round $1,030 $1,260 $1,430
COGB-224 Computer Plastic Oval $1,110 $710 $810
COPS-161 Computer Titanium Oval $1,300 $1,300 $1,680
COGB-170 Computer Titanium Round $1,650 $1,190 $1,180
COGB-200 Computer Plastic Rectangular $1,590 $1,360 $1,190
COPS-151 Computer SS Square $220 $480 $1,810
All Products
The Optical Boutique
Sales
Product Code Type Material Shape 2018 2019 2020
COPS-121 Computer Titanium Round $580 $700 $430
COGB-210 Computer Plastic Oval $590 $360 $790
COPP-137 Computer SS Rectangular $880 $650 $130
COPP-103 Computer Titanium Round $980 $1,080 $1,100
COGB-201 Computer Plastic Square $920 $1,080 $600
COPS-228 Computer SS Square $790 $340 $270
COPS-151 Computer SS Square $220 $480 $1,810
COPS-215 Computer Titanium Rectangular $950 $980 $1,340
COGB-230 Computer Plastic Round $1,030 $1,260 $1,430
COGB-224 Computer Plastic Oval $1,110 $710 $810
COPS-161 Computer Titanium Oval $1,300 $1,300 $1,680
COGB-170 Computer Titanium Round $1,650 $1,190 $1,180
COGB-200 Computer Plastic Rectangular $1,590 $1,360 $1,190
RDPS-157 Reading Titanium Round $1,390 $650 $170
RDPP-214 Reading Plastic Oval $1,550 $1,650 $1,240
RDPP-152 Reading SS Rectangular $900 $760 $800
RDPP-245 Reading Titanium Round $920 $610 $850
RDPP-241 Reading Plastic Square $360 $800 $660
RDPB-177 Reading SS Square $840 $580 $1,110
RDPS-155 Reading SS Square $870 $730 $900
RDPP-203 Reading Titanium Rectangular $330 $930 $1,890
RDPS-193 Reading Plastic Round $720 $1,770 $1,770
RDPP-138 Reading Plastic Oval $1,340 $1,420 $1,940
RDPS-246 Reading Titanium Oval $940 $950 $120
RDPB-191 Reading Titanium Round $1,180 $600 $40
RDPP-126 Reading Plastic Rectangular $1,230 $1,460 $1,390
RDPP-144 Reading SS Oval $1,470 $690 $1,900
RDPS-119 Reading Titanium Round $280 $1,440 $1,700
RDPS-189 Reading Titanium Oval $1,520 $1,770 $560
RDPB-128 Reading Plastic Rectangular $1,850 $440 $1,790
RDPP-227 Reading Plastic Round $210 $710 $1,100
RDPP-196 Reading SS Square $820 $1,480 $850
RDPP-140 Reading Titanium Square $1,090 $120 $370
RDPP-213 Reading Plastic Rectangular $1,140 $1,930 $1,170
RDPP-149 Reading Titanium Round $870 $300 $620
SGPP-108 Sunglass Titanium Round $880 $1,120 $1,840
SGPP-232 Sunglass Titanium Oval $780 $1,280 $1,550
SGGB-157 Sunglass Plastic Oval $840 $920 $1,420
SGPS-143 Sunglass SS Rectangular $980 $1,830 $1,860
SGPS-145 Sunglass SS Round $1,610 $1,220 $1,190
SGPS-223 Sunglass Titanium Rectangular $1,710 $1,570 $1,630
SGPS-171 Sunglass Plastic Square $760 $1,250 $980
SGPS-178 Sunglass Plastic Square $1,250 $1,120 $1,540
SGGB-215 Sunglass Titanium Rectangular $1,070 $800 $1,310
SGGB-223 Sunglass Plastic Round $260 $1,900 $630
SGGB-171 Sunglass Plastic Oval $340 $1,370 $1,650
SGGB-196 Sunglass SS Rectangular $980 $1,410 $1,130
SGPP-177 Sunglass Titanium Round $780 $260 $580
SGPP-119 Sunglass Plastic Oval $1,890 $600 $1,570
RXPS-101 Prescription Titanium Rectangular $820 $1,530 $1,520
RXGB-135 Prescription Plastic Square $1,380 $1,920 $1,400
RXGB-114 Prescription SS Oval $1,090 $880 $1,990
RXPS-118 Prescription Plastic Round $2,580 $1,500 $1,760
RXPS-122 Prescription SS Round $1,800 $1,590 $940
RXPP-103 Prescription SS Oval $1,840 $2,470 $1,250
RXPS-112 Prescription Titanium Rectangular $1,530 $760 $1,750
RXPP-182 Prescription Titanium Square $2,910 $1,500 $1,380
RXPS-136 Prescription Plastic Square $170 $1,800 $1,380
RXGB-117 Prescription Plastic Oval $120 $1,780 $1,490
All Products PT
2018 Sales 2019 Sales 2020 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 Sales 2019 Sales 2020 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 1
New Perspectives Excel 2016 | Module 5: SAM Project 1
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.)
. 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
Rectangula
$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