Temple University Excel Worksheet

User Generated

xunaulj

Business Finance

Temple University

Description

Unformatted Attachment Preview

Transaction count Units sold Subtotal sum Retail average Date Units Product 5/1/2018 1 Spaaaaah - Hair Wrap 5/1/2018 2 Sandalwood Massage Oil 5/1/2018 2 Vanilla Bean Massage Oil 5/1/2018 2 Water - rejuvenating, flavored, 12 oz bottle 5/1/2018 2 Geranium Rose Bath Crystals 5/1/2018 1 Vanilla Bean reed oil 5/1/2018 1 Vanilla Bean Massage Oil 5/1/2018 1 Nail repair 5/2/2018 2 Nail repair 5/2/2018 4 Geranium Rose Bath Crystals 5/2/2018 1 Nail repair 5/2/2018 1 Sandalwood reed oil 5/2/2018 1 Vanilla Bean reed oil 5/2/2018 3 Healthy Hands - extra dry 5/2/2018 1 Nail repair 5/2/2018 1 Turquoise Oasis Exfoliating Sponge 5/3/2018 2 Nail repair 5/3/2018 1 Water - rejuvenating, flavored, 12 oz bottle 5/3/2018 1 Bergamot Massage Oil 5/3/2018 3 Water - rejuvenating, flavored, 12 oz bottle 5/3/2018 2 Ylang Ylang Massage Oil 5/3/2018 2 Ginseng Tea 5/4/2018 1 Spaaaaah - Hair Wrap 5/4/2018 1 Vanilla Bean Massage Oil 5/4/2018 2 Healthy Hands - extra dry 5/4/2018 1 Water - rejuvenating, flavored, 12 oz bottle 5/4/2018 2 Nail repair 5/4/2018 1 Cordial glasses set of 2 5/4/2018 1 Orange reed oil 5/4/2018 1 Bathrobe - Persian Silk 5/4/2018 1 Nail protector 5/5/2018 1 Vanilla Bean Massage Oil 5/5/2018 1 Skin Toner 5/5/2018 2 Geranium Rose Bath Crystals 5/5/2018 2 Bergamot Massage Oil 5/5/2018 1 Ginseng Tea 5/5/2018 1 Vanilla Bean Massage Oil 5/5/2018 1 Healthy Hands - rejuvenating 5/5/2018 3 Water - mineral, 16 oz bottle 5/5/2018 1 Spaaaaah - Bath Towel 36x96 5/6/2018 1 Healthy Hands - rejuvenating 5/7/2018 1 Healthy Hands - rejuvenating 5/7/2018 1 Vanilla Bean Massage Oil 5/7/2018 1 Healthy Hands - extra dry 5/7/2018 1 Healthy Hands - dry 5/7/2018 2 Spaaaaah - Bath Towel 36x96 5/7/2018 2 Sandalwood Massage Oil 5/7/2018 1 Orange reed oil 5/7/2018 1 Nail repair 5/7/2018 1 Sandalwood reed oil 5/8/2018 1 Ginseng Tea 5/8/2018 2 Nail repair 5/8/2018 1 Healthy Hands - dry 5/8/2018 1 Sandalwood reed oil 20200221191128fang_e03ch06_grader_h1_shopsales Category Clothing Massage Oil Massage Oil Food Skincare Scents Massage Oil Hand Care Hand Care Skincare Hand Care Scents Scents Hand Care Hand Care 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 Massage Oil Food Massage Oil Hand Care Food Clothing Hand Care Hand Care Massage Oil Hand Care Hand Care Clothing Massage Oil Scents Hand Care Scents Food Hand Care Hand Care Scents 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.5 2.45 6.99 9.95 4.19 19.95 4.19 19.95 2.45 17.95 19.95 8.5 4.19 9.95 13.95 6.95 73.49 9.95 19.95 9.95 6.99 19.95 2.45 19.95 9.25 3.95 21.99 9.25 9.25 19.95 8.5 8.5 21.99 19.95 6.95 9.95 6.95 2.45 9.95 8.5 6.95 Staff Kendra Kelly Kelly Pat Kendra Irene Kendra Jason Christy Susan Jason Jason Pat Christy Leslie Kendra Pat Kendra Leslie Kendra Kendra Kendra Kendra Irene Kendra Susan Kendra Irene Christy Kendra Kendra Christy Kendra Meda Kendra Leslie Pat Kelly Irene Irene Kendra Kendra Kendra Meda Kelly Meda Susan Pat Christy Meda Kendra Kelly Kendra Kendra Staff_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 Massage Therapist Nail Technician Massage Therapist Receptionist Manager Manager Massage Therapist Massage Therapist Massage Therapist Manager Receptionist Manager Nail Technician Massage Therapist Massage Therapist Manager Massage Therapist Receptionist Massage Therapist Massage Therapist 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 Yes No No Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes 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 card card cash card card card check cash check card card card card card cash card check card card card 5/8/2018 5/8/2018 5/8/2018 5/8/2018 5/8/2018 5/8/2018 5/9/2018 5/9/2018 5/9/2018 5/10/2018 5/10/2018 5/11/2018 5/11/2018 5/11/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/12/2018 5/13/2018 5/13/2018 5/13/2018 5/13/2018 5/13/2018 5/13/2018 5/14/2018 5/14/2018 5/14/2018 5/14/2018 5/14/2018 5/14/2018 5/15/2018 5/15/2018 5/15/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/16/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/17/2018 5/18/2018 1 1 1 1 1 2 1 1 4 2 1 1 1 1 1 1 2 1 1 3 1 1 1 1 2 1 1 1 2 1 1 1 1 1 2 2 2 2 1 1 2 1 1 1 1 2 1 1 4 1 4 1 1 2 1 2 1 2 1 2 1 1 1 1 1 3 2 1 1 Ginseng Tea Ginseng Tea Healthy Hands - rejuvenating Nail repair Healthy Hands - extra dry Healthy Hands - dry Water - mineral, 16 oz bottle Healthy Hands - rejuvenating Healthy Hands - dry Water - mineral, 16 oz bottle Nail repair Healthy Hands - dry Ylang Ylang Massage Oil Orange reed oil Nail protector Nail strengthener Healthy Hands - dry Ginseng Tea Healthy Hands - extra dry Nail strengthener Vanilla Bean Massage Oil Healthy Hands - extra dry Sandalwood Massage Oil Vanilla Bean Massage Oil Turquoise Travel Mug - 16oz Nail repair Ginseng Tea Water - mineral, 16 oz bottle Spaaaaah - Hair Wrap Healthy Hands - rejuvenating Ginseng Tea Nail protector Nail strengthener Ginseng Tea Ginseng Tea Sandalwood reed oil Orange reed oil Healthy Hands - dry Healthy Hands - rejuvenating Water - rejuvenating, flavored, 12 oz bottle Geranium Rose Bath Crystals Ylang Ylang Massage Oil Healthy Hands - extra dry Spaaaaah - Bath Towel 36x96 Water - mineral, 16 oz bottle Vanilla Bean reed oil Ylang Ylang Massage Oil Water - mineral, 16 oz bottle Vanilla Bean Massage Oil Nail protector Healthy Hands - dry Vanilla Bean reed oil Sage reed oil Sage reed oil Orange reed oil Orange reed oil Vanilla Bean Massage Oil Skin Toner Orange reed oil Nail repair Nail repair Sage reed oil Ylang Ylang Massage Oil Nail protector Healthy Hands - extra dry Water - mineral, 16 oz bottle Nail strengthener Water - mineral, 16 oz bottle Skin Toner 20200221191128fang_e03ch06_grader_h1_shopsales Food Food Hand Care Hand Care Hand Care Hand Care Food Hand Care Hand Care Food Hand Care Hand Care Massage Oil Scents Hand Care Hand Care Hand Care Food Hand Care Hand Care Massage Oil Hand Care Massage Oil Massage Oil Housewares Hand Care Food Food Clothing Hand Care Food Hand Care Hand Care Food Food Scents Scents Hand Care Hand Care Food Skincare Massage Oil Hand Care Clothing Food Scents Massage Oil Food Massage Oil Hand Care Hand Care Scents Scents Scents Scents Scents Massage Oil Skincare Scents Hand Care Hand Care Scents Massage Oil Hand Care Hand Care Food Hand Care Food Skincare 2.45 2.45 9.25 9.95 8.5 8.5 3.95 9.25 8.5 3.95 9.95 8.5 19.95 6.95 9.95 9.95 8.5 2.45 8.5 9.95 19.95 8.5 19.95 19.95 15.99 9.95 2.45 3.95 17.95 9.25 2.45 9.95 9.95 2.45 2.45 6.95 6.95 8.5 9.25 4.19 6.99 19.95 8.5 21.99 3.95 6.95 19.95 3.95 19.95 9.95 8.5 6.95 6.95 6.95 6.95 6.95 19.95 9.95 6.95 9.95 9.95 6.95 19.95 9.95 8.5 3.95 9.95 3.95 9.95 Irene Susan Christy Jason Irene Kendra Christy Jason Leslie Irene Kendra Pat Irene Jason Kelly Kendra Kendra Irene Susan Irene Kendra Kendra Leslie Leslie Christy Irene Leslie Pat Irene Jason Kendra Meda Christy Jason Kendra Kendra Irene Leslie Kendra Christy Kelly Kendra Christy Christy Kendra Jason Susan Pat Jason Leslie Christy Kendra Pat Kendra Kendra Christy Kelly Irene Christy Susan Irene Susan Kendra Kelly Kendra Irene Leslie Pat Jason Manager Nail Technician Massage Therapist Massage Therapist Manager Massage Therapist Massage Therapist Massage Therapist Nail Technician Manager Massage Therapist Massage Therapist Manager Massage Therapist Receptionist Massage Therapist Massage Therapist Manager Nail Technician Manager Massage Therapist Massage Therapist Nail Technician Nail Technician Massage Therapist Manager Nail Technician Massage Therapist Manager Massage Therapist Massage Therapist Manager Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Nail Technician Massage Therapist Massage Therapist Receptionist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Nail Technician Massage Therapist Massage Therapist Nail Technician Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Receptionist Manager Massage Therapist Nail Technician Manager Nail Technician Massage Therapist Receptionist Massage Therapist Manager Nail Technician Massage Therapist Massage Therapist Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes No No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes No Yes Yes No Yes Yes No No No No Yes No Yes No No No Yes No Yes Yes check check card card cash card card card cash card card card card card card card card cash cash cash card check check cash cash card check card card card card card card card card card card cash card check card cash cash card card card card check card card cash cash card check card card check card card card card cash card cash cash card card card check 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/18/2018 5/19/2018 5/19/2018 5/19/2018 5/19/2018 5/19/2018 5/20/2018 5/20/2018 5/20/2018 5/20/2018 5/20/2018 5/20/2018 5/21/2018 5/21/2018 5/21/2018 5/21/2018 5/21/2018 5/21/2018 5/21/2018 5/21/2018 5/22/2018 5/22/2018 5/22/2018 5/22/2018 5/22/2018 5/22/2018 5/22/2018 5/22/2018 5/23/2018 5/23/2018 5/23/2018 5/23/2018 5/24/2018 5/24/2018 5/24/2018 5/24/2018 5/24/2018 5/24/2018 5/24/2018 5/24/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/25/2018 5/26/2018 5/26/2018 5/26/2018 5/27/2018 5/27/2018 5/27/2018 5/27/2018 5/27/2018 5/27/2018 5/28/2018 5/28/2018 1 1 1 1 2 1 2 1 1 3 1 2 1 2 1 2 1 1 2 3 1 3 1 2 1 1 1 1 1 1 2 2 1 1 2 1 1 2 1 2 1 1 1 1 1 1 1 2 1 1 1 3 2 1 1 1 1 1 3 1 2 1 1 1 3 1 4 2 2 Turquoise Ceramic mug -15oz Non-allergenic scent free Massage Oil Water - mineral, 16 oz bottle Bergamot reed oil Water - rejuvenating, flavored, 12 oz bottle Avocado Foot Care Cream Water - rejuvenating, flavored, 12 oz bottle Spaaaaah - Hair Wrap Water - rejuvenating, flavored, 12 oz bottle Nail protector Nail strengthener Sage reed oil Sandalwood reed oil Nail repair Sandalwood reed oil Geranium Rose Bath Crystals Vanilla Bean reed oil Skin Toner Bergamot Massage Oil Ginseng Tea Healthy Hands - dry Sage reed oil Sandalwood Massage Oil Geranium Rose Bath Crystals Spaaaaah - Bath Towel 36x96 Water - mineral, 16 oz bottle Orange reed oil Geranium Rose Bath Crystals Vanilla Bean Massage Oil Water - mineral, 16 oz bottle Vanilla Bean Massage Oil Nail strengthener Water - mineral, 16 oz bottle Sage reed oil Skin Toner Sandalwood reed oil Orange reed oil Healthy Hands - extra dry Sand Brush - dry skin exfoliator Water - rejuvenating, flavored, 12 oz bottle Sand Brush - dry skin exfoliator Cilantro Foot Bath Crystals Sand Brush - dry skin exfoliator Nail repair Ginseng Tea Sandalwood Massage Oil Geranium Rose Bath Crystals Water - mineral, 16 oz bottle Skin Toner Spaaaaah - Hair Wrap Turquoise Travel Mug - 16oz Vanilla Bean Massage Oil Ginseng Tea Water - rejuvenating, flavored, 12 oz bottle Water - rejuvenating, flavored, 12 oz bottle Nail repair Cordial glasses set of 2 Nail protector Bathrobe - Persian Silk Cilantro Foot Bath Crystals Nail repair Healthy Hands - dry Ylang Ylang Massage Oil Cilantro Foot Bath Crystals Turquoise Oasis Moisturizing Cream Healthy Hands - extra dry Turquoise Oasis Exfoliating Sponge Ylang Ylang Massage Oil Healthy Hands - extra dry 20200221191128fang_e03ch06_grader_h1_shopsales Housewares Massage Oil Food Scents Food Foot Care Food Clothing Food Hand Care Hand Care Scents Scents Hand Care Scents Skincare Scents Skincare Massage Oil Food Hand Care Scents Massage Oil Skincare Clothing Food Scents Skincare Massage Oil Food Massage Oil Hand Care Food Scents Skincare Scents Scents Hand Care Skincare Food Skincare Skincare Skincare Hand Care Food Massage Oil Skincare Food Skincare Clothing Housewares Massage Oil Food Food Food Hand Care Housewares Hand Care Clothing Skincare Hand Care Hand Care Massage Oil Skincare Skincare Hand Care Skincare Massage Oil Hand Care 11.29 22.95 3.95 6.95 4.19 4.95 4.19 17.95 4.19 9.95 9.95 6.95 6.95 9.95 6.95 6.99 6.95 9.95 19.95 2.45 8.5 6.95 19.95 6.99 21.99 3.95 6.95 6.99 19.95 3.95 19.95 9.95 3.95 6.95 9.95 6.95 6.95 8.5 10.99 4.19 10.99 6.99 10.99 9.95 2.45 19.95 6.99 3.95 9.95 17.95 15.99 19.95 2.45 4.19 4.19 9.95 13.95 9.95 73.49 6.99 9.95 8.5 19.95 6.99 13.99 8.5 6.99 19.95 8.5 Meda Kendra Kelly Jason Susan Pat Kendra Kelly Pat Leslie Kendra Kelly Kendra Pat Kendra Christy Jason Meda Kelly Kelly Jason Susan Kendra Christy Jason Kendra Pat Susan Irene Kendra Kendra Pat Christy Kelly Meda Kendra Kendra Leslie Pat Leslie Christy Pat Christy Kendra Kendra Irene Pat Pat Kendra Pat Kendra Kendra Irene Kendra Susan Jason Christy Kelly Pat Kendra Irene Irene Jason Pat Leslie Pat Jason Irene Susan Manager Massage Therapist Receptionist Massage Therapist Nail Technician Massage Therapist Massage Therapist Receptionist Massage Therapist Nail Technician Massage Therapist Receptionist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Receptionist Receptionist Massage Therapist Nail Technician Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Nail Technician Manager Massage Therapist Massage Therapist Massage Therapist Massage Therapist Receptionist Manager Massage Therapist Massage Therapist Nail Technician Massage Therapist Nail Technician Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Massage Therapist Manager Massage Therapist Nail Technician Massage Therapist Massage Therapist Receptionist Massage Therapist Massage Therapist Manager Manager Massage Therapist Massage Therapist Nail Technician Massage Therapist Massage Therapist Manager Nail Technician Yes Yes Yes No Yes No No Yes Yes Yes Yes No Yes Yes Yes Yes No Yes Yes Yes Yes Yes No No No Yes No Yes Yes No Yes No Yes Yes Yes No Yes Yes Yes Yes No Yes Yes No Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes check card card card check card card card card check card card cash cash card cash card card card card cash cash card card card card check card card card card card cash card card card card card cash check card cash card cash cash check cash card card card card card card card cash card card card card cash card card card card card card cash cash card 5/28/2018 5/28/2018 5/28/2018 5/28/2018 5/28/2018 1 2 1 1 1 Nail strengthener Nail repair Orange reed oil Geranium Rose Bath Crystals Ginseng Tea 20200221191128fang_e03ch06_grader_h1_shopsales Hand Care Hand Care Scents Skincare Food 9.95 9.95 6.95 6.99 2.45 Susan Kelly Jason Leslie Susan Nail Technician Receptionist Massage Therapist Nail Technician Nail Technician Yes Yes No No Yes card card card cash card 1. Who were the top two sales people in terms of units sold (non-managers)? 2. Who was the bottom sales person in terms of units sold (non-managers) and the sales person with 30 Units? 3. What percentage of revenue came from the massage therapists? 20200221191128fang_e03ch06_grader_h1_shopsales Grader - Instructions Excel 2016 Project YO16_XL_CH06_GRADER_PS1_HW - Gift Shop Sales Review 1.8 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 members have finished a sales training program. You have been given the data for May 2018 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. Steps to Perform: 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. 10 Note, Mac users, copy the formula down through the column, if necessary. 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 Created On: 07/05/2019 1 YO16_XL_CH06_GRADER_PS1_HW - Gift Shop Sales Review 1.8 Grader - Instructions Step Excel 2016 Project Points Possible Instructions 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. 6 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. 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 6 Note, Mac users, select the range A13:D40, then insert a Clustered Column chart. Move the chart and complete the steps as specified. 16 Save the workbook, exit Excel, and then submit your files as directed by your instructor. Total Points Created On: 07/05/2019 2 0 100 YO16_XL_CH06_GRADER_PS1_HW - Gift Shop Sales Review 1.8
Purchase answer to see full attachment
Explanation & Answer:
NA
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi 😀 i did ityou have to rename it to include your name (LastFirst)Anything u need just let me knowgood luck!

Date

Units

Product

Date
Units
Product
5/5/2018
1 Healthy Hands - rejuvenating
5/7/2018
1 Healthy Hands - dry
5/8/2018
2 Nail repair
5/12/2018
1 Nail protector
5/15/2018
2 Geranium Rose Bath Crystals
5/18/2018
1 Water - mineral, 16 oz bottle
5/18/2018
1 Spaaaaah - Hair Wrap
5/19/2018
2 Sage reed oil
5/20/2018
2 Bergamot Massage Oil
5/20/2018
3 Ginseng Tea
5/22/2018
1 Sage reed oil
5/25/2018
1 Nail protector
5/28/2018
2 Nail repair

20200221220841e03ch06_grader_h1_shopsales_lastfirst

Category

Category
Hand Care
Hand Care
Hand Care
Hand Care
Skincare
Food
Clothing
Scents
Massage Oil
Food
Scents
Hand Care
Hand Care

Retail_Price

Retail_Price
9,25
8,5
9,95
9,95
6,99
3,95
17,95
6,95
19,95
2,45
6,95
9,95
9,95

Staff

Staff
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly

Staff_Category
Receptionist

Hotel_Guest

Transaction count
Units sold
Subtotal sum
Retail average

$
$

Staff_Category
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist

Hotel_Guest
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes

Trans_Type
Card

13
20
181,43
9,44

Trans_Type
card
card
card
card
card
card
card
card
card
card
card
card
card

Subtotal
$ 9,25
$ 8,50
$ 19,90
$ 9,95
$ 13,98
$ 3,95
$ 17,95
$ 13,90
$ 39,90
$ 7,35
$ 6,95
$ 9,95
$ 19,90

Date
Units Product
5/5/2018
1 Healthy Hands - rejuvenating
5/7/2018
1 Healthy Hands - dry
5/8/2018
2 Nail repair
5/12/2018
1 Nail protector
5/15/2018
2 Geranium Rose Bath Crystals
5/18/2018
1 Water - mineral, 16 oz bottle
5/18/2018
1 Spaaaaah - Hair Wrap
5/19/2018
2 Sage reed oil
5/20/2018
2 Bergamot Massage Oil
5/20/2018
3 Ginseng Tea
5/22/2018
1 Sage reed oil
5/25/2018
1 Nail protector
5/28/2018
2 Nail repair

Category
Hand Care
Hand Care
Hand Care
Hand Care
Skincare
Food
Clothing
Scents
Massage Oil
Food
Scents
Hand Care
Hand Care

Retail_Price
9,25
8,5
9,95
9,95
6,99
3,95
17,95
6,95
19,95
2,45
6,95
9,95
9,95

Staff
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly
Kelly

Staff_Category
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist
Receptionist

Hotel_Guest
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes

Trans_Type
card
card
card
card
card
card
card
card
card
card
card
card
card

Category
Sum of Units

Percent Revenue Contribution
25

20

15

Staff_Category
Massage Therapist
Nail Technician

10

Receptionist

5

0
Non-hotel
Guest

Hotel
Guest

Kelly

Staff

Hotel_Guest

Hotel
Guest
Jason

Non-hotel
Guest
Pat

Hotel
Guest

Non-hotel
Guest

Hotel
Guest

Susan

Non-hotel
Guest

Hotel
Guest

Christy

Non-hotel
Guest
Leslie

Hotel
Guest

Non-hotel
Guest

Hotel
Guest

Kendra

1. Who were the top two sales people in terms of units sold (non-managers)?
Kendra
Christy
2. Who was the bottom sales person in terms of units sold (non-managers) and the sales person with 30 Units?
Susan
Pat
3. What percentage of revenue came from the massage therapists?
67,37
Category

(All)

Sum of Units
Row Labels
Kelly
Non-hotel Guest
Hotel Guest
Kelly Total
Jason
Hotel Guest
Jason Total
Pat
Non-hotel Guest
Hotel Guest
Pat Total
Susan
Non-hotel Guest
Hotel Guest
Susan Total
Christy
Non-hotel Guest
Hotel Guest
Christy Total
Leslie
Non-hotel Guest
Hotel Guest
Leslie Total
Kendra
Non-hotel Guest
Hotel Guest
Kendra Total
Grand Total

Column Labels
Massage Therapist

Nail Technician

Receptionist
4
2
6

7
7
2
9
11
1
13
14
2
15
17
2
15
17
2
20
22
57

20200221220841e03ch06_grader_h1_shopsales_lastfirst

31

6

Payment Type
card
cash
check

20200221220841e03ch06_grader_h1_shopsales_lastfirst

Transaction count
Units sold
Subtotal sum
Retail average

Date
Units Product
5/1/2018
1 Spaaaaah - Hair Wrap
5/1/2018
2 ...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags