Please follow the instructions.Colin Morin is the assistant sales manager for The Optical Boutique. One of hisassignments is to format the product sales records from 2018 to 2020 to make it easier to analyze. 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.Switch to the Prescription worksheet. Unfreeze the top row of the worksheet. 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:>>>>>>>>>>>>> The rest of the instructions are on the word file.
instructions_np_ex16_5b.docx
np_ex16_5b_myname_1.xlsx
Unformatted Attachment Preview
PROJECT STEPS
1.
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.
2.
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.
3.
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.)
4.
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.
5.
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.
6.
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
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Product
Code
Type
Material
Shape
2018
2019
2020
SGPP-125
Sunglass
Titanium
Rectangular
$1,100
$990
$1,300
7.
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:
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:G24, 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 Computer worksheet and remove the duplicate record associated with
the Product Code of COPS-151 from the ComputerSales table.
9.
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.
10.
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.
11.
Filter the table to show only records for products with a Titanium material type
and a Rectangular shape.
12.
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.)
13.
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.)
14.
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.
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.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.)
16.
Colin also wants to summarize sales data for all products using a PivotChart to
help determine what product lines should be expanded.
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Switch to the Product Material PT 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 Frames to the PivotChart using the Above
Chart option.
c.
Use the Type axis field button to filter the PivotChart so that only the
sales data for Prescription and Reading glasses 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.
Final Figure 1: Prescription PT Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Final Figure 2: Prescription Worksheet
Final Figure 3: Sunglass Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Final Figure 4: Reading Worksheet
Final Figure 5: Computer Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Final Figure 6: All Products Worksheet
Final Figure 7: All Products PT Worksheet
New Perspectives Excel 2016 | Module 5: SAM Project 1b
Final Figure 8: Product Material PT 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 1b
The Optical Boutique
WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS
My name
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
The Optical Boutique
Sales
Product Code
RXPS-101
RXGB-135
RXGB-114
RXPS-118
RXPS-122
RXPP-103
RXPS-112
RXPP-182
RXPS-136
RXGB-117
Type
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Material
Titanium
Plastic
SS
Plastic
SS
SS
Titanium
Titanium
Plastic
Plastic
Shape
Rectangular
Square
Oval
Round
Round
Oval
Rectangular
Square
Square
Oval
2018
$820
$1,380
$1,090
$2,580
$1,800
$1,840
$1,530
$2,910
$170
$120
2019
$1,530
$1,920
$880
$1,540
$1,590
$2,470
$760
$1,500
$1,800
$1,780
2020
$1,520
$1,400
$1,990
$1,760
$940
$1,250
$1,750
$1,380
$1,380
$1,490
The Optical Boutique
Sales
Product Code
SGPP-108
SGPP-232
SGGB-157
SGPS-143
SGPS-145
SGPS-223
SGPS-171
SGPS-178
SGGB-215
SGGB-223
SGGB-171
SGGB-196
SGPP-177
SGPP-119
Type
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Material
Titanium
Titanium
Plastic
SS
SS
Titanium
Plastic
Plastic
Titanium
Plastic
Plastic
SS
Titanium
Plastic
Shape
Round
Oval
Oval
Rectangular
Round
Rectangular
Square
Square
Rectangular
Round
Oval
Rectangular
Round
Oval
2018
$880
$780
$840
$980
$1,610
$1,710
$760
$1,250
$1,070
$260
$340
$980
$780
$1,890
2019
$1,120
$1,280
$920
$1,830
$1,220
$1,570
$1,250
$1,120
$800
$1,900
$1,370
$1,410
$260
$600
2020
$1,840
$1,550
$1,420
$1,860
$1,190
$1,630
$980
$1,540
$1,310
$630
$1,650
$1,130
$580
$1,570
The Optical Boutique
Sales
Product Code
RDPS-157
RDPP-214
RDPP-152
RDPP-245
RDPP-241
RDPB-177
RDPS-155
RDPP-203
RDPS-193
RDPP-138
RDPS-246
RDPB-191
RDPP-126
RDPP-144
RDPS-119
RDPS-189
RDPB-128
RDPP-227
RDPP-196
RDPP-140
RDPP-213
RDPP-149
Type
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Material
Titanium
Plastic
SS
Titanium
Plastic
SS
SS
Titanium
Plastic
Plastic
Titanium
Titanium
Plastic
SS
Titanium
Titanium
Plastic
Plastic
SS
Titanium
Plastic
Titanium
Shape
Round
Oval
Rectangular
Round
Square
Square
Square
Rectangular
Round
Oval
Oval
Round
Rectangular
Oval
Round
Oval
Rectangular
Round
Square
Square
Rectangular
Round
2018
$1,390
$1,550
$900
$920
$360
$840
$870
$330
$720
$1,340
$940
$1,180
$1,230
$1,470
$280
$1,520
$1,850
$210
$820
$1,090
$1,140
$870
2019
$650
$1,650
$760
$610
$800
$580
$730
$930
$1,770
$1,850
$950
$600
$1,460
$690
$1,440
$1,770
$440
$710
$1,480
$120
$1,930
$300
2020
$170
$1,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
$850
$370
$1,170
$620
The Optical Boutique
Sales
Product Code
COPS-121
COGB-210
COPP-137
COPP-103
COGB-201
COPS-228
COPS-151
COPS-215
COGB-230
COGB-224
COPS-161
COGB-170
COGB-200
COPS-151
Type
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Material
Titanium
Plastic
SS
Titanium
Plastic
SS
SS
Titanium
Plastic
Plastic
Titanium
Titanium
Plastic
SS
Shape
Round
Oval
Rectangular
Round
Square
Square
Square
Rectangular
Round
Oval
Oval
Round
Rectangular
Square
2018
$580
$590
$880
$980
$920
$790
$220
$950
$1,030
$1,110
$1,300
$1,650
$1,590
$220
2019
$700
$360
$650
$1,080
$1,080
$340
$480
$980
$1,260
$710
$1,300
$1,190
$1,360
$480
2020
$430
$790
$130
$1,100
$600
$270
$1,810
$1,340
$1,430
$810
$1,680
$1,180
$1,190
$1,810
The Optical Boutique
Sales
Product Code
COPS-121
COGB-210
COPP-137
COPP-103
COGB-201
COPS-228
COPS-151
COPS-215
COGB-230
COGB-224
COPS-161
COGB-170
COGB-200
RDPS-157
RDPP-214
RDPP-152
RDPP-245
RDPP-241
RDPB-177
RDPS-155
RDPP-203
RDPS-193
RDPP-138
RDPS-246
RDPB-191
RDPP-126
RDPP-144
RDPS-119
RDPS-189
RDPB-128
RDPP-227
RDPP-196
RDPP-140
RDPP-213
RDPP-149
SGPP-108
SGPP-232
SGGB-157
SGPS-143
SGPS-145
SGPS-223
SGPS-171
Type
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Computer
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Reading
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Material
Titanium
Plastic
SS
Titanium
Plastic
SS
SS
Titanium
Plastic
Plastic
Titanium
Titanium
Plastic
Titanium
Plastic
SS
Titanium
Plastic
SS
SS
Titanium
Plastic
Plastic
Titanium
Titanium
Plastic
SS
Titanium
Titanium
Plastic
Plastic
SS
Titanium
Plastic
Titanium
Titanium
Titanium
Plastic
SS
SS
Titanium
Plastic
Shape
Round
Oval
Rectangular
Round
Square
Square
Square
Rectangular
Round
Oval
Oval
Round
Rectangular
Round
Oval
Rectangular
Round
Square
Square
Square
Rectangular
Round
Oval
Oval
Round
Rectangular
Oval
Round
Oval
Rectangular
Round
Square
Square
Rectangular
Round
Round
Oval
Oval
Rectangular
Round
Rectangular
Square
2018
$580
$590
$880
$980
$920
$790
$220
$950
$1,030
$1,110
$1,300
$1,650
$1,590
$1,390
$1,550
$900
$920
$360
$840
$870
$330
$720
$1,340
$940
$1,180
$1,230
$1,470
$280
$1,520
$1,850
$210
$820
$1,090
$1,140
$870
$880
$780
$840
$980
$1,610
$1,710
$760
2019
$700
$360
$650
$1,080
$1,080
$340
$480
$980
$1,260
$710
$1,300
$1,190
$1,360
$650
$1,650
$760
$610
$800
$580
$730
$930
$1,770
$1,420
$950
$600
$1,460
$690
$1,440
$1,770
$440
$710
$1,480
$120
$1,930
$300
$1,120
$1,280
$920
$1,830
$1,220
$1,570
$1,250
SGPS-178
SGGB-215
SGGB-223
SGGB-171
SGGB-196
SGPP-177
SGPP-119
RXPS-101
RXGB-135
RXGB-114
RXPS-118
RXPS-122
RXPP-103
RXPS-112
RXPP-182
RXPS-136
RXGB-117
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Sunglass
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Prescription
Plastic
Titanium
Plastic
Plastic
SS
Titanium
Plastic
Titanium
Plastic
SS
Plastic
SS
SS
Titanium
Titanium
Plastic
Plastic
Square
Rectangular
Round
Oval
Rectangular
Round
Oval
Rectangular
Square
Oval
Round
Round
Oval
Rectangular
Square
Square
Oval
$1,250
$1,070
$260
$340
$980
$780
$1,890
$820
$1,380
$1,090
$2,580
$1,800
$1,840
$1,530
$2,910
$170
$120
$1,120
$800
$1,900
$1,370
$1,410
$260
$600
$1,530
$1,920
$880
$1,500
$1,590
$2,470
$760
$1,500
$1,800
$1,780
2020
$430
$790
$130
$1,100
$600
$270
$1,810
$1,340
$1,430
$810
$1,680
$1,180
$1,190
$170
$1,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
$850
$370
$1,170
$620
$1,840
$1,550
$1,420
$1,860
$1,190
$1,630
$980
$1,540
$1,310
$630
$1,650
$1,130
$580
$1,570
$1,520
$1,400
$1,990
$1,760
$940
$1,250
$1,750
$1,380
$1,380
$1,490
Computer
COGB-170
COGB-200
COGB-201
COGB-210
COGB-224
COGB-230
COPP-103
COPP-137
COPS-121
COPS-151
COPS-161
COPS-215
COPS-228
Prescription
RXGB-114
RXGB-117
RXGB-135
RXPP-103
RXPP-182
RXPS-101
RXPS-112
RXPS-118
RXPS-122
RXPS-136
Reading
RDPB-128
RDPB-177
RDPB-191
RDPP-126
RDPP-138
RDPP-140
RDPP-144
RDPP-149
RDPP-152
RDPP-196
RDPP-203
RDPP-213
RDPP-214
RDPP-227
RDPP-241
RDPP-245
RDPS-119
RDPS-155
2018 Sales
$ 12,590
$ 1,650
$ 1,590
$
920
$
590
$ 1,110
$ 1,030
$
980
$
880
$
580
$
220
$ 1,300
$
950
$
790
$ 14,240
$ 1,090
$
120
$ 1,380
$ 1,840
$ 2,910
$
820
$ 1,530
$ 2,580
$ 1,800
$
170
$ 21,820
$ 1,850
$
840
$ 1,180
$ 1,230
$ 1,340
$ 1,090
$ 1,470
$
870
$
900
$
820
$
330
$ 1,140
$ 1,550
$
210
$
360
$
920
$
280
$
870
2019 Sales
$ 11,490
$ 1,190
$ 1,360
$ 1,080
$
360
$
710
$ 1,260
$ 1,080
$
650
$
700
$
480
$ 1,300
$
980
$
340
$ 15,730
$
880
$ 1,780
$ 1,920
$ 2,470
$ 1,500
$ 1,530
$
760
$ 1,500
$ 1,590
$ 1,800
$ 21,790
$
440
$
580
$
600
$ 1,460
$ 1,420
$
120
$
690
$
300
$
760
$ 1,480
$
930
$ 1,930
$ 1,650
$
710
$
800
$
610
$ 1,440
$
730
2020 Sales
$ 12,760
$ 1,180
$ 1,190
$
600
$
790
$
810
$ 1,430
$ 1,100
$
130
$
430
$ 1,810
$ 1,680
$ 1,340
$
270
$ 14,860
$ 1,990
$ 1,490
$ 1,400
$ 1,250
$ 1,380
$ 1,520
$ 1,750
$ 1,760
$
940
$ 1,380
$ 22,940
$ 1,790
$ 1,110
$
40
$ 1,390
$ 1,940
$
370
$ 1,900
$
620
$
800
$
850
$ 1,890
$ 1,170
$ 1,240
$ 1,100
$
660
$
850
$ 1,700
$
900
RDPS-157
RDPS-189
RDPS-193
RDPS-246
Sunglass
SGGB-157
SGGB-171
SGGB-196
SGGB-215
SGGB-223
SGPP-108
SGPP-119
SGPP-177
SGPP-232
SGPS-143
SGPS-145
SGPS-171
SGPS-178
SGPS-223
Grand Total
$ 1,390 $
650
$ 1,520 $ 1,770
$
720 $ 1,770
$
940 $
950
$ 14,130 $ 16,650
$
840 $
920
$
340 $ 1,370
$
980 $ 1,410
$ 1,070 $
800
$
260 $ 1,900
$
880 $ 1,120
$ 1,890 $
600
$
780 $
260
$
780 $ 1,280
$
980 $ 1,830
$ 1,610 $ 1,220
$
760 $ 1,250
$ 1,250 $ 1,120
$ 1,710 $ 1,570
$ 62,780 $ 65,660
$
170
$
560
$ 1,770
$
120
$ 18,880
$ 1,420
$ 1,650
$ 1,130
$ 1,310
$
630
$ 1,840
$ 1,570
$
580
$ 1,550
$ 1,860
$ 1,190
$
980
$ 1,540
$ 1,630
$ 69,440
Plastic
Computer
Prescription
Reading
Sunglass
SS
Computer
Prescription
Reading
Sunglass
Titanium
Computer
Prescription
Reading
Sunglass
Grand Total
2018 Sales
$ 23,230
$ 5,240
$ 4,250
$ 8,400
$ 5,340
$ 15,090
$ 1,890
$ 4,730
$ 4,900
$ 3,570
$ 24,460
$ 5,460
$ 5,260
$ 8,520
$ 5,220
$ 62,780
2019 Sales
$ 29,110
$ 4,770
$ 7,000
$ 10,180
$ 7,160
$ 15,110
$ 1,470
$ 4,940
$ 4,240
$ 4,460
$ 21,440
$ 5,250
$ 3,790
$ 7,370
$ 5,030
$ 65,660
2020 Sales
$ 29,700
$ 4,820
$ 6,030
$ 11,060
$ 7,790
$ 16,130
$ 2,210
$ 4,180
$ 5,560
$ 4,180
$ 23,610
$ 5,730
$ 4,650
$ 6,320
$ 6,910
$ 69,440
…
Purchase answer to see full
attachment
