please help me understand the assignment by completing the attached excel file based on the instructions in the word document

Transaction count Units sold Subtotal sum Retail average Date Units Product 5/1/2014 1 Spaaaaah - Hair Wrap 5/1/2014 2 Sandalwood Massage Oil 5/1/2014 2 Vanilla Bean Massage Oil 5/1/2014 2 Water - rejuvenating, flavored, 12 oz bottle 5/1/2014 2 Geranium Rose Bath Crystals 5/1/2014 1 Vanilla Bean reed oil 5/1/2014 1 Vanilla Bean Massage Oil 5/1/2014 1 Nail repair 5/2/2014 2 Nail repair 5/2/2014 4 Geranium Rose Bath Crystals 5/2/2014 1 Nail repair 5/2/2014 1 Sandalwood reed oil 5/2/2014 1 Vanilla Bean reed oil 5/2/2014 3 Healthy Hands - extra dry 5/2/2014 1 Ginseng Tea 5/2/2014 1 Turquoise Oasis Exfoliating Sponge 5/3/2014 2 Nail repair 5/3/2014 1 Water - rejuvenating, flavored, 12 oz bottle 5/3/2014 1 Bergamot Massage Oil 5/3/2014 3 Water - rejuvenating, flavored, 12 oz bottle 5/3/2014 2 Ylang Ylang Massage Oil 5/3/2014 2 Ginseng Tea 5/4/2014 1 Spaaaaah - Hair Wrap 5/4/2014 1 Vanilla Bean Massage Oil 5/4/2014 2 Healthy Hands - extra dry 5/4/2014 1 Water - rejuvenating, flavored, 12 oz bottle 5/4/2014 2 Nail repair 5/4/2014 1 Cordial glasses set of 2 5/4/2014 1 Orange reed oil 5/4/2014 1 Bathrobe - Persian Silk 5/4/2014 1 Nail protector 5/5/2014 1 Vanilla Bean Massage Oil 5/5/2014 1 Skin Toner 5/5/2014 2 Geranium Rose Bath Crystals Category Clothing Massage Oil Massage Oil Food Skincare Scents Massage Oil Hand Care Hand Care Skincare Hand Care Scents Scents Hand Care Food Skincare Hand Care Food Massage Oil Food Massage Oil Food Clothing Massage Oil Hand Care Food Hand Care Housewares Scents Clothing Hand Care Massage Oil Skincare Skincare Retail_Price $ 17.95 $ 19.95 $ 19.95 $ 4.19 $ 6.99 $ 6.95 $ 19.95 $ 9.95 $ 9.95 $ 6.99 $ 9.95 $ 6.95 $ 6.95 $ 8.50 $ 2.45 $ 6.99 $ 9.95 $ 4.19 $ 19.95 $ 4.19 $ 19.95 $ 2.45 $ 17.95 $ 19.95 $ 8.50 $ 4.19 $ 9.95 $ 13.95 $ 6.95 $ 73.49 $ 9.95 $ 19.95 $ 9.95 $ 6.99 Staf Kendra Kelly Kelly Pat Kendra Irene Kendra Jason Christy Susan Jason Leslie Pat Christy Leslie Kendra Pat Kendra Leslie Kendra Kendra Kendra Kendra Irene Kendra Susan Kendra Irene Christy Kendra Kendra Christy Kendra Meda Staf_Category Massage Therapist Receptionist Receptionist Massage Therapist Massage Therapist Manager Massage Therapist Massage Therapist Massage Therapist Nail Technician Massage Therapist Nail Technician Massage Therapist Massage Therapist Nail Technician Massage Therapist Massage Therapist Massage Therapist Nail Technician Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Massage Therapist Nail Technician Massage Therapist Manager Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Hotel_Guest Yes Yes No No Yes No Yes Yes No Yes Yes Yes Yes Yes Yes No Yes Yes No Yes Yes No Yes Yes Yes No Yes Yes Yes Yes Yes Yes No Yes Trans_Type cash check check card card cash card cash check cash card card cash check cash card cash cash cash cash card card card card check cash card check card card cash card card cash 1. Who were the top two sales people in terms of units sold (non-managers)? 2. Who were the bottom two sales people in terms of units sold (non-managers)? 3. What percentage of revenue came from the massage therapists? Office 2013 - myitlab:grader - Instructions Excel Project YOF EWS06 A1 - Painted Treasures Gift Shop Review 1.1 Project Description: The sales team at the Painted Treasures Gift Shop would like to review its staff's sales after some of the new staff has finished a sales training program. You have been given the data for May 2014 and more data will be added in the future. The sales team wants you to review the data, perform some data analyses, and answer some specific questions. This will help the sales team determine how well the staff is performing, and who needs further training. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named e03ws06_grader_a1.xlsx. Save the file with the name e03ws06ShopSales_LastFirst replacing LastFirst with your name. Enable content if necessary. 0.000 2 On the Sales worksheet, insert a table with headers that uses the range $A$9:$I$43. With the data table selected, create named ranges using the top row as the names. 5.000 3 Copy range A9:I9, and then paste the range in cell A1. In cell G2, type Receptionist. In cell I2, type Check. 5.000 4 Create an advanced filter using the data in range A1:I2. Filter the list in-place to display the filtered data on the Sales worksheet. Copy the filtered data, including the header row, and paste it in cell A1 of the Filtered worksheet. 5.000 5 On the Sales worksheet, remove the filter from the table. In cell J9, type Subtotal. In cell J10, enter a formula that multiplies Units and Retail_Price. Format the Subtotal column as accounting number format. 9.000 6 Create a named range for the Subtotal column that uses the column heading as the name. 5.000 7 Use the SUBTOTAL function to complete the following. In cell H4, insert a formula that counts the number of cells in the Category field that are not empty. In cell H5, insert a formula that sums the cells in the Units field. In cell H6, insert a formula that sums the cells in the Subtotal field. In cell H7, insert a formula that averages the cells in the Retail_Price field. 5.000 8 On the Sales worksheet, using the range $A$9:$J$43, insert a PivotTable in cell A12 on the Sales Analysis worksheet. 9 On the Sales Analysis worksheet, add Units, Staff, and Staff Category to the PivotTable. Move Staff Category to the COLUMNS area. Filter the data in the PivotTable so that those categorized as Manager are not included in the results. Sort the Grand Total column in ascending order. 10.000 6.000 View the data in the PivotTable to answer question 1. Enter the employee's name with the highest units sold in cell A2, and then enter the employee's name with the second highest units sold in cell B2. Updated: 03/11/2015 1 Current_Instruction.docx Office 2013 - myitlab:grader - Instructions Step Excel Project Points Possible Instructions 10 View the data in the PivotTable to answer question 2. Enter the employee's name with the lowest units sold in cell A5, and then enter the employee's name with the second lowest units sold in cell B5. 10.000 11 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, above Staff. In cell A14, replace No with Non-hotel Guest. Resize column A so Non-hotel Guest is visible. In cell A21, replace Yes with Hotel Guest. 10.000 12 Modify the PivotTable data to show the percent of the grand total. 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 data in the PivotTable to answer question 3. Enter the percentage in cell A8. Remove the Staff field from the PivotTable. 13 On the Sales Analysis 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 E12. Change the height of the slicer to 1.3\". Replace the Trans_Type heading caption with Payment Type. 10.000 14 Using the PivotTable, create a Clustered Column PivotChart. Move the PivotChart to a new sheet named Sales Chart. Add chart title above the chart with the text Percent Revenue Contribution. 15.000 15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales, Filtered, Sales Chart, and Sales Analysis. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. Total Points Updated: 03/11/2015 2 5.000 0.000 100.000 Current_Instruction.docx