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
20181124230159zhang_e03ch06_grader_h1_shopsales.xlsx
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
20181124230159zhang_e03ch06_grader_h1_shopsales.xlsx
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
20181124230159zhang_e03ch06_grader_h1_shopsales.xlsx
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
20181124230159zhang_e03ch06_grader_h1_shopsales.xlsx
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?
20181124230159zhang_e03ch06_grader_h1_shopsales.xlsx
Office 2016 – myitlab:grader – Instructions
Excel 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.
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
6
7
8
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.
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.
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.
On the Sales worksheet, using the range A15:J212, insert a PivotTable in cell A12 on the
SalesAnalysis worksheet.
Updated: 11/30/2017
1
8
10
8
8
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
9
10
11
12
13
14
15
Excel Project
Points
Possible
Instructions
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
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.
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.
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
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.
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
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
4
4
8
6
8
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
Updated: 11/30/2017
2
0
100
Current_Instruction.docx
Purchase answer to see full
attachment