e02 grader hw Raises

Anonymous
timer Asked: Apr 29th, 2019
account_balance_wallet $10

Question Description

Project Description:

Painted Paradise Resort & Spa evaluates employee performance yearly and determines raises. You have been asked to assist with compiling and analyzing the data. Upper management also started the workbook. You are to finish the workbook and keep the data confidential.

Unformatted Attachment Preview

Addresses Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson StreetAddress 4215 Lupita Rd 17324 Camino Dr 1542E Abeja Ct 1200 Three Trees Rd 5 Park Rd 923 Clark St 622 E. High St 4476 Wilson Blvd City Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe State NM NM NM NM NM NM NM NM ZipCode 87505 87501 87509 87508 87500 87501 87511 87513 - RevisedZip Phone 5055551812 5055554231 5055557810 5055556110 5055552837 5055555215 5055558343 5055557421 RevisedPhone Confidential Internal Evaluations Customer Team Manager Reliable Innovation Service Collaborator Yes 3 4 5 4 No 3 3 3 4 No 4 3 4 4 Yes 4 5 4 4 No 3 3 5 4 Yes 4 3 3 3 No 4 3 2 2 No 2 4 4 3 Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson Title Spa Manager Therapist Massage Therapist Coffee Shop Manager Barista Gift Shop Manager Sales Associate Sales Associate Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson Manager Raise Recommendations and Notes High Raise. Very high customer ratings on comment cards. Great at appeasing an upset customer. Standard Raise. Standard employee who works with the team well. Standard Raise. Fills in last minute for other employees and has a large satisfied client following. High Raise. Envisioned and lead a redesign of the Terra Cotta Brew that lead to increased sales. Standard Raise. Average employee who has a good rapport with the customers. Low Raise. Average manager who has had some challenging employees. Low Raise. Has received some customer complaints and seems reluctant to get along with other employee Standard Raise. Good at her job. However, she misses work a lot and never fills in for others who need off. Leadership Rank Average Raise 4 5 3 ng an upset customer. fied client following. d to increased sales. mers. get along with other employees. fills in for others who need off. Scale 5 = Far Exceeds Expectations 4 = Exceeds Expectations 3 = Meets Expectations 2 = Below Expectations 1 = Well Below Expectations Today's Date High Bonus Low Deduct Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson HireDate 3/14/2010 8/12/2009 8/12/2009 2/1/2011 9/30/2007 5/15/2010 3/16/2007 6/4/2005 Years Employed 0 2 5 7 Standard Raise % 2.50% 3.00% 3.50% 4.00% JobTitle Spa Manager Therapist Massage Therapist Coffee Shop Manager Barista Gift Shop Manager Sales Associate Sales Associate Salary $ 61,500 $ 42,100 $ 45,800 $ 35,000 $ 26,900 $ 37,200 $ 28,100 $ 28,700 Years Employed $ $ 750 500 Raise Recommend Net Payroll Increase Average Raise % Standard Raise % Low Final % New High Bonus Deduction Raise Amt Increase Salary Create Date By Whom dd/mm/yyyy Firstname Lastname Mod. Date By Whom Description Mod. Description Last Version Backup Name Create Date Creator Purpose Sheet Name Workbook Name 20190429012043alfarj_e02_grader_hw_raises.xlsx Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_BU02_GRADER_PS3_AS - Raises 1.3 Project Description: Painted Paradise Resort & Spa evaluates employee performance yearly and determines raises. You have been asked to assist with compiling and analyzing the data. Upper management also started the workbook. You are to finish the workbook and keep the data confidential. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step 1 2 3 4 Points Possible Instructions Open the downloaded document e02_grader_hw_Raises.xlsx. Save the file as e02_grader_hw_Raises_LastFirst using your last and first name. 0.000 The Addresses worksheet includes data from the payroll database. You need to cleanse the data so this worksheet can be used later in a mail merge to create salary notice letters. Add the following. In column E, extra spaces and a dash were left in the data by the database in case the zip code had an extension. In cells F7:F14, enter a LEFT function to return the ZipCode without the unnecessary “–”. In column G, the database did not export the formatting for the phone numbers. In cells H7:H14, use Flash Fill to add formatting back to the phone number. In cell H7, type (505) 555-1812. There is only one space between the “)” and the 5. Note, Mac users, in cells H7:H8 type the phone numbers using the format (505) 555-1812. The Evaluations worksheet includes evaluation data collated from various managers. Managers rated employees both quantitatively—numerically—and qualitatively—written notes. The number scores and notes support the raise recommendation made by the manager. Add the following. In cells I3:I10, create an IF function to calculate the average numerical ratings for each employee. Only managers get evaluated by the Leadership rank in column H. Column C indicates whether the employee is a manager. Thus, the average calculation should only include the values in column H if the employee is a manager. Even if a rating is given to a non-manager employee in column H, that number should not be included in the average in column I. In cells B13:B20, imported data from the rating system combines the manager’s raise recommendation of High, Standard, or Low followed by the manager’s notes. In cells J3:J10, use a combination of text functions to return just the manager’s raise recommendation of High, Standard, or Low. The values returned should not have extra spaces before or after the word. The names in A3:A10 are in the same order as A13:A20. Notice the text pattern is always the recommendation followed by a space. Be sure to Trim any extra spaces. 12.000 8.000 8.000 5 On the Evaluations worksheet, create the named range Evaluations for the cell range A3:J10. 4.000 6 On the Payroll worksheet, there is an analysis for raises you need to finish. First, you need to get all the applicable data for making the raise determination on this worksheet. Add the following. In cell F1, enter a formula to always return the current date. 4.000 7 In cells E6:E13, add a DATEDIF function that will calculate the years employed based on today’s date in cell F1 and the HireDate in column B. Remember you are looking for years and not days. 4.000 Updated: 04/24/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 8 In cells F6:F13, add a VLOOKUP function that returns the raise recommendation—High, Standard, or Low—for each employee by exactly matching the Name in column A in the range you named Evaluations. Be sure to use exact match criteria. 8.000 9 In cells G6:G13, add a VLOOKUP function that returns the starting raise percentage based on the ranges in the Standard Raise table in cells A16:B19 and the number of years employed in column E. 8.000 10 11 12 13 14 15 Next, you need to begin determining the employee’s final raise. Add the following. In cells H6:H13, add an IF function that will award anyone with a Raise Recommend of High an extra bonus listed in cell F2. All other employees get zero bonus. In cells I6:I13, add an IF function that will give anyone with a Raise Recommend of Low a deduction listed in cell F3. All other employees get zero deduction. On the Payroll worksheet, in cells J6:J13, add a calculation that returns the final raise amount with the value—not just formatted—rounded to the hundreds position (-2). For example, a final raise of $1,625 rounded to the hundreds returns a value of $1,600. The final raise amount is the salary multiplied by the Standard Raise % and then adding any bonus and subtracting any deduction. On the Payroll worksheet, in cells K6:K13, add a calculation that returns the Final % Increase. The final increase is determined by dividing the Raise Amt by the Salary. In cells L6:L13, add a calculation that returns the New Salary by adding the Salary and Raise Amt. You now need to add a few summary measures for upper management. Add the following on the Payroll worksheet. In cell J1, add a function that calculates the total Net Payroll Increase based on the Raise Amt in cells J6:J13. In cell J2, add a function that calculates the Average Raise % based on the Final % Increase in cells K6:K13. On the Payroll worksheet, based on the data in cells A5:A13 and K5:K13, add a 3-D Clustered Column. Under chart styles, set the chart to Style 3. Change the vertical axis to start at a lower bound of 0.01—for 1%. Then, change the title to read Final % Increase Comparison. Move and resize the chart so the upper-left corner is in cell F14 and the lower-right corner is in cell L22. Set the chart title to 11 pt font. If necessary, set the vertical and horizontal axis labels to 9 pt font. Click Save, close Excel, and then submit the file as directed by your instructor. 2 6.000 12.000 4.000 10.000 0.000 Total Points Updated: 04/24/2017 12.000 100.000 Current_Instruction.docx ...
Purchase answer to see full attachment

Tutor Answer

Ace_Tutor
School: University of Maryland

attached is my answer

1) The file is saved as the required name as follow:

2) The cells F7:F14 can be completed as follow:

For the cells H7:H14, the flash fill for the cell phone number can be completed as follow:

3) The cells I3:I10 can be completed as follow:

4) The cells J3:J10 can be completed as follow:

5) The named range Evaluations was created for the cell range A3:J10 as follow:

6) A formula to return the current date in the cell F1 is shown below:

7) A DATEDIF function was added for the cells E6:E13 as follow:

8) The cells F5:F13 can be completed as follow:

9) The cells G6:G13 can be completed as follow:

10) The cells H6:H13 can be completed as follow:

The cells I6:I13 can be completed as follow:

11) The cells J6:J13 can be completed as follow:

12) The cells K6:K13 can be completed as follow:

The cells L6:L13 can be completed as follow:

13) The total net payroll in cell J1 can be completed as follow:

The average raise % can be calculated in the cell J2 as follow:

14) The required 3-D clustered column is shown below:...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors