YO16_XL_CH06_GRADER_PS1_HW - Gift Shop Sales Review 1.8

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Open the downloaded Excel file named e03ch06_grader_h1_ShopSales.xlsx. Save the file with the name e03ch06_grader_h1_ShopSales_LastFirst, using your last and first name.

0

2

Create a copy of the Sales worksheet, and then place it at the end of the workbook. Rename the Sales (2) worksheet as SalesBackup

6

3

On the Sales worksheet, insert a table with headers that uses the range $A$15:$I$212.
With the data table selected, create named ranges using the top row as the names.

16

4

Copy range A15:I15, and then paste the range in cell A1. In cell G2, type Receptionist. In cell I2, type Card

4

5

Create an advanced filter using the data in range A1:I2 as the Criteria range and A15:I212 as the List range. 
Filter the list in-place to display the filtered data on the Sales worksheet. Insert a new sheet, and then rename the new sheet Filter. Copy the filtered data from the table on the Sales worksheet, and then place it in cell A1 on the Filter worksheet. Resize the columns so all the data is visible.
 

8

6

On the Sales worksheet, in cell J15, type Subtotal.
In cell J16, enter a formula using structured references that multiplies Units and Retail_Price.
Format the Subtotal column as Accounting Number Format.
Create a named range for the Subtotal column that uses the column heading as the name.

Note, Mac users, copy the formula down through the column, if necessary.

10

7

Use the SUBTOTAL function to complete the following:
In cell H7, insert a formula that counts the number of cells in the Category field that are not empty.
In cell H8, insert a formula that sums the cells in the Units field.
In cell H9, insert a formula that sums the cells in the Subtotal field.
In cell H10, insert a formula that averages the cells in the Retail_Price field.

8

8

On the Sales worksheet, using the range A15:J212, insert a PivotTable in cell A12 on the SalesAnalysis worksheet.
 

8

9

Configure the PivotTable using the following.
Add Units, Staff, and Staff Category to the PivotTable.
Move Staff Category to the COLUMNS area.
Using the Column Labels arrow, uncheck Manager.
Right-click cell E14, point to Sort, and then sort the data in ascending order.
 

4

10

View the data in the PivotTable to determine the employee with the highest number of units sold.
In cell A2, click the drop-down arrow, and then select the name of the employee with the highest number of units sold.
In cell A3, click the drop-down arrow, and then select the name of the employee with the second highest number of units sold.

4

11

View the PivotTable data to determine the employee with the lowest number of units sold.
Click cell A5, click the drop-down arrow, and then select the name of the employee with the lowest number of units sold.
Click cell A6, click the drop-down arrow, and then select the name of the employee who sold 30 units to answer question 2.

4

12

Modify the PivotTable as follows:
Add the Category field to the PivotTable.
Move Category to the FILTERS area.
Add the Hotel_Guest field to the PivotTable in the ROWS area under Staff.
In cell A15, replace No with Non-hotel Guest
Resize column A so Non-hotel Guest is visible.
In cell A16, replace Yes with Hotel Guest

8

13

On the Design tab, modify the grand totals so they display for just the columns.
Modify the subtotals so they display at the bottom of the group.

View the PivotTable data to determine the percentage of revenue for the massage therapists (% of Grand Total). Click cell A8, click the drop-down arrow, and then select the percentage of revenue of massage therapists.

6

14

On the SalesAnalysis worksheet, insert a slicer for the Trans_Type field. Select both cash and check in the Trans_Type slicer. Drag the Trans_Type slicer so the upper left corner is in the top left corner of G12. Drag the bottom edge of the slicer to adjust the height so the extra white space is no longer visible. Replace the Trans_Type caption with Payment Type

8

15

Using the PivotTable, create a Clustered Column PivotChart.
Move the PivotChart to a new sheet named SalesChart
Add chart title above the chart with the text Percent Revenue Contribution

Note, Mac users, select the range A13:D40, then insert a Clustered Column chart. Move the chart and complete the steps as specified.

6

16

Save the workbook, exit Excel, and then submit your files as directed by your instructor.

0

Total Points

100