ISM 280: Information Systems in Organizations

Business Finance

ISM 280

Question Description

Your Office Excel Chapter 6 Problem Solve 1. This question requires more knowledge of solving office excel problems.

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 20200428023219e03ch06_grader_h1_shopsales_mugheriwilbart 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 20200428023219e03ch06_grader_h1_shopsales_mugheriwilbart 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 20200428023219e03ch06_grader_h1_shopsales_mugheriwilbart 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 20200428023219e03ch06_grader_h1_shopsales_mugheriwilbart 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? 20200428023219e03ch06_grader_h1_shopsales_mugheriwilbart Grader - Instructions Excel 2016 Project YO_Excel16_Ch06_PS1_v2 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 Points Possible Instructions 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 YO_Excel16_Ch06_PS1_v2 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 t ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.
Tags: ISM 280

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags