Needs excel for this assignment :

E3 125% v E 33L m (B m (E1 Lu [a 69 99 (9 View Zoom Add Category Pivot Table insert Table Chart Text Shape Media Comment Collaborate Format Organize + KeyBoard Shortcuts Activity to Complete Actiwty Data ActiVity Code Sheet Pivot Table Tutorial Data Descriptive 81 > Cell Text Arrange 15' A B C D E ii i H H i Activity 3 Questions Instructions: You are asked to report some descriptive information about your organilation. The stakeholders want this 2 information in a series of tables, and you've now gathered the data to create those tables. Go through the following prompts m and answer each question. Scenario: Your data for the activity is located under the "Activnty Data" tab. However, even though the data is all merged, you Table Styles first need to clean the data and check for any errors. Use one or more of the data cleaning methods from the data cleaning . 3 slides to clean this data and identify any input that need to be changed. If needed, remove or recode data and note the issues in the rst table below. To get more information about each column, refer to the "Activity Code Sheet" which Table Options describes each of the columns in the Activity Data. Title 4 Questions Responses Caption 1. Using any of the methods of data cleaning from the tutorial slides Headers 8' Footer identify as many errors/inconsistencies you can find in each column 0 a I. o a II o a of the "Activity Data" Sheet. Which method(s) did you decide to use 5 and why? Note, some data cleaning methods will work best for categorical or text entries. Other data validation methods will work Rows 60 A better for continuous data. Feel free to use multiple approaches to v see which works best for you. Columns 11 C 2. Complete the table below indicating how many errors/ Table Font Size A A 6 inconsistencies you found for each column (some columns may not have any errors). 7 Column Name Number of Errors/Inconsistencies & Description Table Outline 7 3 1 t " 8 Emp oyee ID p V 9 \"\"3\"\" Outline Table Title 10 Sex 11 Engagement 12 Job Satisfaction Gridlines i3 Customer Service 14 Performance is Sales Revenue 6 3. After documenting the errors, adjust the sex column so that the Alt t' R C I 7 1 formatting is consistent for that column. erna mg ow 0 or 17 Request 1. You've been asked to calculate the headcount (number of employees) at each of the 4 locations operated by your RW 8' Column Size is . . . . . .. .. n - n organization. The locations are llsted In the data sheet under Location . Height 87 pt 3 Fit Scenario: Your data for the activity is located under the l'Activity Data" tab. However, even though the data is all merged, you first need to clean the data and check for any errors. Use on > Width 316 pr 3 Fit ' l 125% v E 33,, View Zoom Add Category Pivot Table KeyBoard Shortcuts Activity to Complete A 7 Lgllgg a _ 1, What kind of data is "location" (between binary, nominal, ordinal, 20 interval, ratio)? -m _ 2. Enter the appropriate Excel formulas (=COU NTIF) in the "Employee 21 headcount" colum of the table below to get the employee headcount for each department. 22 Location Employee Headcount Table Styles 23 Hayward . 24 Portland 25 Seattle Table Options 25 Austin Title Caption 3. Did you notice anything strange about the formula for the Austin location? Hint, there is an extra space after TX, which can lead your 27 formula to being slightly off. Try adding a space to the end of TX: This Headers & Footer is why ensuring our data are clean, free of errors, and in the desired format is so important for being able to actually use our data. 0 Q i. O Q " O Q Rows 60 C 4. In addition to the overall headcount for each location, you've been A 28 asked to report headcount of men and women for each location, Columns 11 v Note, I'm only requesting Male/Female because the data only have those two groups reported. 29 5. Use the =COUNT|FS formulas to calculate the headcounts for each Table Font 5'29 A A location by sex. 30 Employee Headcount ' 3i \"cam" _W_-E_ we "\"'"9 32 Hayward, CA a 7 1 pt 3 33 Portland, OR 34 593mg, WA Outline Table Title 35 Austin, TX 36 Gridliries Request 2: In addition to headcounts, you've been asked to summarize several KPIs at the company-wide level. The KPls include: Engagement, 37 job satisfaction, customer satisfaction, job performance, and sales revenue. Engagement: . 1. What kind of data are each of these KPIs (Binary, nominal, ordinal, Job Satisfaction: Alternating ROW Color 7 3a interval, ratio)? Refer to the Activity Code Sheet Tab for information Customer Satisfaction: about how each KPI is measured. Job Performance: 53155 Revenue: Row & Column Size Height 87 pt 3 Fit iexi Scenario: Your data for the activity is located under the l'Activity Data" tab. However, even though the data is all merged, you first need to clean the data and check for any errors. Use on > Width 316 pr 3 Fit ml (9 iii Q] to insert Table Chart Text Shape Media Activity Data Activity Code Sheet Pivot Table , , , , , , i B C La 69 99 (9 Comment Collaborate Format Organize Tutorial Data Descriptive Si > Table Cell Text Arrange D E 1 View 125% v Zoom Add Category Act ty to Complete KeyBoa rd Shortcuts A Z.What formula should you use to summarize these KPls? , Fill in the Pivot Table Activity Data ml CB lnsert Table Chart Activity Code Sheet B iii Q] to Text Shape Media Pivot Table C [EJ Comment Tutorial Data D Collaborate Descriptive 81 E > Cell Text 39 (9 Format Organize Arrange 39 first empty row of the table below "Company-Wide" for each KPI using the appropriate formula. - 3. In addition to the overall ratings, you've been asked to report these numbers based on each location, How should you sumarize the KPls 40 for each location (e,g., Sums, counts, averages, standard deviations, Table Styles etc)? , 41 4.Which formula should you use to calculate this information? Table Options 42 5. Complete the following table using the appropriate formulas Title 43 Summary Data Captlon 44 Location Engagement Customer Service Sa es Revenue 45 Company-WI - e A6 Hayward Headers & Footer 47 Portland H n 0 a 45 Seattle 49 Austin 50 Rows 60 C 51 Request 3: The third request you've been asked to for is to identify problematic performance issues. Follow the instructions below. Columns 11 C 1. Employees on a PIP plan are those with a 1 rating fortheirjob Table Font Size A A 52 performance. Fill in the table below with the formulas that will indicate how many employees are on a PIP plan at each location. Table Outline 2. In addition to location and employees on a PIP plan, what other _ Q 7 1 pt A 53 information would you want to know to better understand how v employees are performing at each location? Outline Table Title 54 Summary Data 55 Location PIP Plan Gridlines 55 CompanyWide 57 Hayward === 58 Portland 59 Seattle 60 Austin Alternating Row Color 7 Row & Column Size Height 37 pt 3 Fit lexl Scenario: Your data for the activity is located under the l'Activity Data" tab. However, even though the data is all merged, you first need to clean the data and check for any errors. Use on > Width 316 pr 3 Fit : l 125% v ti :1; Q] W LEJ $9 69 Sheet Sheet Name The employee ID for this employee. Each should start Employee ID with EP Location The location of the employee works at. There are Background 7 four different possible locations The sex of the employee. This should be entered as Sex "F" for female, "M" for Male, or other with a listed . , _ Duplicate Sheet description. Measured with 9 questions, each scored on a 1-5 scale. Delete Sheet Engagement Employee's engagement score is the average of their responses to the 9 questions. Measured with 5 questions, each scored on a 1-5 scale. Job Satisfaction Employee's job satisfaction score is the average of their responses to the 9 questions. After each sale, customers are surveyed and asked to rate their experience using a star system with options from 1 Customer Service to 5 stars (no half stars). Score is the average customer service rating that employee has received over the past 3 months. Managers rate their employees on a 5 point scale 1 = employee will be put on a performance improvement plan "PIP" plan. 5 = Top performer and the employee is eligible for the highest raise available. Job Performance Monthly sales revenue averaged over the past 3 months. Revenue represented in 3. Typical sales revenue for a Sales Revenue salesperson should range from approximately 30,000 to 100,000. Values outside of that range should be agged for review. 125% A Add Category Pivot Table Insert Table Chart Text Shape Media Comment Collaborate Format Organize View Zoom Activity to Complete Activity Data Activity Code Sheet Pivot Table Tutorial Data Descriptive S1 Sheet + KeyBoard Shortcuts Sheet Name PivotTable2 Pivot Table Facility Facility (Count Al Charlote, NC Charlotte Background Charlotte, NC Indianapolis, IN AHUWH Omaha Duplicate Sheet Omaha, NE Grand Total Delete Sheet Total 5 3.75 Grand Total Facility (Count (All)) 2.5 1.25 0 Charlote, NC Charlotte, NC Omaha125% A Add Category Pivot Table Insert Table Chart Text Shape Media Comment Collaborate Format Organize View Zoom + KeyBoard Shortcuts Activity to Complete Activity Data Activity Code Sheet Pivot Table Tutorial Data Descriptive S1 Sheet Sheet Name Onboarding Performance Tutorial Data Employee ID Last Name First Name Job Level Facility Hire Date Start Date Completed Score Kenar Omaha, NE 1/28/17 2/1/17 Yes 9.5 EA4411 Linkous 7.5 EA4412 Robertson James Omaha, NE 9/1/1 9/11/13 Charlotte, NC 2/9/13 2/10/13 8 Background EA4413 Zuckerman Mandie EA4414 Swartout Shayna Charlotte 7/10/15 7/18/15 5.5 EA4415 Jimenez Derek Indianapolis, IN July 16 2015 July 18 2015 9.5 Charlotte, NC 2/21/18 3/2/18 8.5 EA4416 Tyler Chur EA4417 Omaha, NE 9/15/13 9/29/13 7.25 Duplicate Sheet Ray Tyson 2.5 EA4418 Jensen John Charlote, NO May 10th 5/12/16 No W W N U A U U W W AA UW Delete Sheet EA4419 Keagle Fatima Indianapolis, IN 11/9/18 11/12/18 Yes 9.25 EA4420 Horace Indianapolis, IN 3/25/14 4/1/14 8.25 Mckay Omaha, NE N EA4421 Lafountain Floyd 6/1/17 June 6th 7.5 EA4422 Kris Indianapolis, IN 5/20/13 5/22/13 3.25 Ward Connor Johan Charlotte, NC 4/29/18 5/2/18 Yes 7.5 EA4423 EA4424 Rolf Indianapolis, IN 8/15/15 8/16/15 6.5 Hernandez EA4425 Bailey Courtney Omaha, NE 10/10/13 10/16/13 9.75125% A View Zoom Add Category Pivot Table Insert Table Chart Text Shape Media Comment Collaborate Format Organize + cuts Activity to Complete Activity Data Activity Code Sheet Pivot Table Tutorial Data Descriptive Statistics Tutorial