Exp22_Excel_Ch05_Cumulative_Merchandise 1.1

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp22_Excel_Ch05_Cumulative_Merchandise.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

Your first task is to sort the dataset on the Apparel worksheet. Ensure the Apparel worksheet is active. Sort the data by Week in alphabetical order and further sort it by Category in alphabetical order.

3

3

You want to focus on apparel sales within one month. You will insert subtotal rows to display total sales by week (1 and 2) and by category (Hoodie and T-shirt). Use the Subtotal feature to insert subtotal rows by Week to calculate the totals for Qty Sold and Gross Revenue. Add a second subtotal (without removing the first subtotal by Category to calculate the totals for Qty Sold and Gross Revenue.

5

4

To focus on the Gross Revenue, you will apply an outline and collapse columns. Create an automatic outline and collapse the outline above Gross Revenue.

3

5

Next, you want to create a blank PivotTable and then name it. Display the Qtr1 worksheet and create a blank PivotTable on a new worksheet. Do not add data to the data model. Name the PivotTable Qtr1 Sales. Change the name of the worksheet to Sales PivotTable.

5

6

The PivotTable should determine total number of items sold and the total gross revenue by category for the first quarter. Place the Category field in rows. Place the Gross Revenue and Qty Sold fields as values.

5

7

You will format the values in the PivotTable to look more professional and change the custom names that display as column headings. Modify the Sum of Gross Revenue field with the custom name Total Gross Revenue and apply Accounting Number Format with 0 decimal places.

3

8

Modify the Sum of Qty Sold field with the custom name Total Qty Sold, apply Number format with 0 decimal places, and select the Use 1000 Separator (,) option

4

9

Now you want to insert a timeline so that you can filter the PivotTable by month. Insert a timeline for the Month field. Click or select March to filter the PivotTable to display only March data. Cut the timeline and paste it in cell A9. Set the timeline width to 3.3 inches.

4

10

You want to create a PivotChart to display percentages in a pie chart. Insert a PivotChart pie chart that depicts the categories and gross revenue. Cut the chart and paste it in cell D1.

5

11

Next, you want to remove the legend and display data labels. Remove the legend. Display data labels in the best fit position with only the Category Name and Percentage labels.

4

12

You are ready to finish the PivotChart by adding a title and changing the width. Change the title to Gross Revenue. Change the chart width to 3.9 inches.

3

13

Extracting a value from the PivotTable and displaying it on the main worksheet is helpful as you look through the dataset. Click or select cell H1 in the Qtr1 worksheet. Insert the GETPIVOTDATA function to retrieve the grand total in the Total Gross Revenue column in the filtered PivotTable.

5

14

Next, you want to create a recommended PivotTable to analyze product sales in the Apparel category. Use the data on the Qtr1 worksheet to create a recommended PivotTable using the Sum of Retail Price by Category. Name the PivotTable Sale Prices. Change the name of the worksheet to Sale Prices.

4

15

Move the Category field from the Rows area to Filters area. Add the Product field to the Rows area. Modify the Sum of Retail Price field with the custom name Item Retail Price and apply Accounting format with zero decimal places.

6

16

Now you want to focus on only the Souvenirs data. Set a filter to display on the Souvenirs category.

5

17

You will create a calculated field to determine product retail prices if you offer a 15% sale, which is 85% of the current retail price. Create a calculated field using the default name Field1 to multiply the Retail Price by .85. Modify the calculated field with the custom name Sale Price and apply Accounting Number Format with zero decimal places.

3

18

Next, you will apply a different PivotTable style to have a similar color scheme as the dataset. Apply Light Yellow, Pivot Style Light 19 to the Sale Prices PivotTable.

4

19

You are ready to insert and customize a slicer to provide another way to filter the categories. Insert a slicer for Category. Move it so that the top-left corner is in cell E1.

5

20

After inserting the slicer, you want to change the dimensions and appearance of it. Change the button width to 1.5 inches. Change the slicer height to 1.4 inches. Apply Light Yellow, Slicer Style Light 4.

5

21

The Employee worksheet contains two tables. The NAMES table contains a list of the four part-time souvenir shop employees and their IDs. The HOURS table contains a list of the employees’ hours per week for one month. You will create a relationship between the tables. Create a relationship between the HOURS table using the ID field and the NAMES table using the ID field.

5

22

Now that you built a relationship between the tables, you can create a PivotTable using fields from both tables. Create a blank PivotTable in cell E1 of the Employee worksheet and add the data to the data model. Name the PivotTable as Monthly Hours.

5

23

Select the LastName field from the NAMES table for rows. Select the Hours field from the HOURS table as values. Change the Sum of Hours custom name to Monthly Hours. Type Names in cell E1.

5

24

You realize a value needs to be changed in the main worksheet. Type 20 in cell C8 in the Employee worksheet and then refresh the PivotTable.

4

25

Save and close Exp22_Excel_Ch05_Cumulative_Merchandise.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100