New Perspectives Excel 2016 | Module 5: SAM Project 1a Forge and Polish Jewelry Working with Excel TAbles, PivotTables, and PivotCharts GETTING STARTED PROJECT STEPS 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2016 | Module 5: SAM Project 1a

Forge and Polish Jewelry

Working with Excel TAbles, PivotTables, and PivotCharts

GETTING STARTED

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. (VR Note: - Need to click Sort and Filter on the Ribbon and then Custom Sort)

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.) (VR Note: Click in the table then click Design and then click Total Row)

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: (VR Note: Click Insert, Pivot Table, New Sheet)

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.) (VR Note: Right click the Material Heading and select Move)

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.(VR Note: (Right Click the Heading and select Value Field Settings and enter the new title and the new Accounting format)

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.(VR Note: Select range, Design, Table Styles)

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 (VR Note: You can select all the data and then copy and paste special to match destination formatting)

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. (VR Note: Design, Convert to Range)

c.         Insert subtotals into the range A2:G25, with the subtotals appearing at each change in the Material column value. (VR Note: Data, Subtotal, at each change Material and SUM, add subtotal to 2018, 2019, 2010 andinclude summary data)

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. (VR Note: Design, Remove Duplicates Select all)

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.(VR Note: Might need to change to Look in Values)

11.     Filter the table to show only records for products with a Gold Material type and a Pearl Embellishment type.(VR Note: you can use the flter arrows)

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.)(VR Note: Data, Refresh All)

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.)(VR Note: Right Click Size and Properties)

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.