Elsayed Fuyyad (Elsayed Ahmed)

Case study: Exploring different HR metrics, measurement, and analytics

Scenario

A fictitious company’s HR data is used to gain insights from the data to improve and develop.

Analysis Tools

For analysis and visualization purposes, I used Tableau in this project.

Ask

Business task

Determine the most important HR metrics per the dataset such as turnover and performance scores.

Business questions:

· What is the average performance score per department? Which department has the best/worst performance score?

· What is the average performance score per position? Which position has the best/worst performance score?

· What is the average Employee Engagement Score per department? Which department has the best/worst Employee Engagement Score?

· What is the average Employee Engagement Score per position? Which position has the best/worst Employee Engagement Score?

· Is there a relation between Employee Engagement Score and salary/absences?

· To prepare a replacement/succession plan, what is the approaching retirement rate for the company? Turnover rate?

· What is the overall diversity profile of the organization?

· What are our best recruiting sources if we want to ensure a diverse organization?

· Are there areas of the company where pay is not equitable?

· Why do employees leave the company?

Key stakeholders

· Top management

· HR team

· HR managers/director

Prepare

· The data set used in this case study can be found here.

· This data was made available by Dr. Richard A. Huebner, Senior Data Scientist, Author, Professor and Dr. Carla Patalano HR Expert, Consultant, Author, Professor.

· It is a public dataset.

· CC-BY-NC-ND This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License

· The data was last updated 4 years ago

· The data consists of 1 table: HRDataset_v14.

· The table contains 36 columns:

· The data is reliable, original, comprehensive, current, and cited.

Process

Column names and data types

1. Employee Name Employee’s full name Text

2. EmpID Employee ID is unique to each employee Text

3. MarriedID Is the person married (1 or 0 for yes or no) Binary

4. MaritalStatusID Marital status code that matches the text field MaritalDesc Integer

5. GenderID Male: 1, Female:0 Binary

6. EmpStatusID Employment status code that matches text field EmploymentStatus Integer

7. DeptID Department ID code that matches the department the employee works in Integer

8. PerfScoreID Performance Score code that matches the employee’s most recent performance score Integer

9. FromDiversityJobFairID Was the employee sourced from the Diversity job fair? 1 or 0 for yes or no Binary

10. Salary The person’s yearly salary. $ U.S. Dollars Float

11. Termd Has this employee been terminated - 1 or 0 Binary

12. PositionID An integer indicating the person’s position Integer

13. Position The text name/title of the position the person has Text

14. State The state that the person lives in Text

15. Zip The zip code for the employee Text

16. DOB Date of Birth for the employee Date

17. Sex Sex - M or F Text

18. MaritalDesc The marital status of the person (divorced, single, widowed, separated, etc) Text

19. CitizenDesc Label for whether the person is a Citizen or Eligible NonCitizen Text

20. HispanicLatino Yes or No field for whether the employee is Hispanic/Latino Text

21. RaceDesc Description/text of the race the person identifies with Text

22. DateofHire Date the person was hired Date

23. DateofTermination Date the person was terminated, only populated if, in fact, Termd = 1 Date

24. TermReason A text reason / description for why the person was terminated Text

25. EmploymentStatus A description/category of the person’s employment status. Anyone currently working full time = Active Text

26. Department Name of the department that the person works in Text

27. ManagerName The name of the person’s immediate manager Text

28. ManagerID A unique identifier for each manager. Integer

29. RecruitmentSource The name of the recruitment source where the employee was recruited from Text

30. PerformanceScore Performance Score text/category (Fully Meets, Partially Meets, PIP, Exceeds) Text

31. EngagementSurvey Results from the last engagement survey, managed by our external partner Float

32. EmpSatisfaction A basic satisfaction score between 1 and 5, as reported on a recent employee satisfaction survey Integer

33. SpecialProjectsCount The number of special projects that the employee worked on during the last 6 months Integer

34. LastPerformanceReviewDate The most recent date of the person’s last performance review. Date

35. DaysLateLast30 The number of times that the employee was late to work during the last 30 days Integer

36. Absences The number of times the employee was absent from work. Integer

Analyze

· Performance score per department

A. The highest average performance score is the software engineering department with 3.0909 followed by IT/IS department with a score of 3.0600

B. The lowest average performance score is the sales department with 2.8387 followed with production department with a score of 2.9713

· Performance score per position

A. The positions with the highest average performance score are CIO, director of operations, IT director and IT manager-support with a score of 4 followed with data analyst with a score of 3.250

B. The positions with the lowest average performance score are IT manger-infra: 2, network engineer: 2.6 and sales manager: 2.667

· Average Employee Engagement Score per department

A. The most engaged department is executive office with an average score of 4.83 followed by admin offices: 4.393

B. The lowest two departments are software engineering: 4.06 and sales: 3.8

· Average Employee Engagement Score per position

A. Sr. accountant and IT director are the most engaged position with a score of 5, followed by sr. DBA with a score of 4.98.

B. The lowest engaged position is IT manager-infra: 2.39, just after network engineer: 3.66 and both are in the IT/IS department.

· Relation between Employee Engagement Score and salary/absences per department

A. The sales department has the highest average absence days: 11.548, lowest engagement score: 3.8 and the second lowest salary: 69,061 $

B. The admin offices has the lowest average absence days: 8.667, second highest engagement score: 4.4 and the third lowest salary: 71792 $

C. It appears that there is no direct Relation between Employee Engagement Score and salary/absences per department.

· Relation between Employee Engagement Score and salary/absences per position

A. The President & CEO has the highest average salary: 250000$, fifth highest engagement score: 4.83 and the thirteenth lowest average absence days: 10

B. The Administrative Assistant has the lowest average salary: 52280 $, twelfth lowest engagement score: 4.4 and the twelfth lowest average absence days: 8.33

· Employment status:

A. Production: 126 Active, 75 resigned, 8 terminated

B. IT/IS: 40 Active, 6 resigned, 4 terminated

C. Sales: 26 Active, 3 resigned, 2 terminated

D. Software Engineering: 7 Active, 3 resigned, 1 terminated

E. Admin Offices: 7 Active, 1 resigned, 1 terminated

F. Executive Office: 1 Active

· Termination reasons

A. The biggest reasons for people leaving the company are Another position, unhappy and more money with percentage of 19%, 13% and 11% of the total percentage of reasons respectively.

B. The least percentage of reasons for quitting/termination are maternity leave - did not return, learned that he is a gangster, gross misconduct, Fatal attraction with percentage of 2.8%, 0.96%, 0.96% and 0.96% respectively.

· Diversity profile per department

A. The company employs six races in total: American Indian or Alaska Native, Black or African American, White, Asian, Hispanic, Two or more races.

B. The white race is the most dominant in the company: 187

C. The other races are represented with a minimal diversity such as 3 American Indian or Alaska Native and 1 Hispanic only!

D. The most diverse department is production: 6 different races

E. The executive office is the least diverse since it has 1 White employee only

· Recruiting sources and diversity

A. Being the most diverse recruiting website, Indeed recruited 87 individuals from 6 different races with Whites being 62% of the its whole population and Hispanic and American Indian or Alaska Native being 1.14% each.

B. The second most diverse recruiting website is LinkedIn: 76 positions and 5 different races; 61% Whites, while American Indian or Alaska Native 1.31 %

C. The least diverse recruiting source is on-line web application with 1 White employee only recruited.

· Diversity and pay

A. Highest paid races include Whites in Executive Office (one-person department), Sales, Production and IT/IS departments: average percentage of the whole department salaries: 100%, 48%, 63% and 54% respectively.

B. The second most paid race is African American comprising a majority of Software engineering department: 47% and Admin Office: 52% of the whole department salaries.

C. Also, African Americans come second in Sales, Production and IT/IS departments: average percentage of the whole department salaries: 31%, 22% and 35% respectively.

D. The least paid race is Hispanic with only 0.67% of the total pay of only the production department.

· Gender gap and pay per department

A. The executive office has the highest average salary in general and in male category: 250,000$ as it is a one-person department.

B. In production, admin office and IT/IS departments, the female workers get more average salaries than their male counterparts.

C. The opposite is the case for software engineering and sales departments.

· Gender gap and pay per position

A. The total average salary for the company is 3,469,088$.

B. The average salary for all male positions is 1,577,722$.

C. The average salary for all female positions is 1,891,366$.

D. Highest paid positions where male employees get paid more than their female counterparts are president and CEO, CIO, director of sales, data architect and principal data architect: average total of 920,740 $ (all one-person department)

E. Highest paid positions where female employees get paid more than their male counterparts are IT director, director of operations, IT manager-infra, IT manager-DB, IT manager-support and BI director: average total of 899,777 $ (all one-person department, except for IT manager-DB)

F. Lowest paid positions where male employees get paid more than their female counterparts are administrative assistants, production technician I and production technician II: average total of 173,760$

G. Lowest paid positions where female employees get paid more than their male counterparts are network engineer, accountant I, IT support and area sales manager: average total of 262,343$

H. Mid-range positions where male employees get paid more than their female counterparts are sr. accountant, software engineer, BI developer, sr. network engineer, data analyst average total of 482,722$

I. Mid-range positions where female employees get paid more than their male counterparts are database administrator, enterprise architect, sr. DBA, shared service manager, sr. BI developer, software engineering manager, production manager and sales manager: average total of 729,246$.

Share

Data visualization can be found here.

Act

Upon examining the data, one can find that:

· Performance across the company

A. If you are a stakeholder in this company, you might want to invest more in your human capital; you need to introduce more incentives to the employees to increase their performance.

B. Sales and production departments managers need to address the issue of the low performance in their departments. First, they could investigate the issue by administering an anonymous survey for example to know what are the reasons that need to be addressed. Any form of employees’ feedback should be encouraged by the management such as meetings or surveys…etc.,

C. Some other things you need to consider as a stakeholder is limiting distractions in the workplace to increase performance and productivity. Setting clear goals and prioritizing tasks might be helpful as well. Furthermore, ease and efficacy of communication with the management is something that could be helpful here along with introducing some workshops that address issues such as time management and delegation.

D. Delegation is crucial especially for management positions; if you look closely, you will find that the managerial positions of IT Manager – Infra, Sales Manager and Area Sales Manager are of the worst performing positions in the company with Production Technician I and Network Engineer. Another remedy for this matter could be identifying skill gaps and offering opportunities for training for the company’s managers. Also, you could consider incorporating technology in managing employees and effectively creating ways to build trust within the team and with managers as well.

E. Finally, on all levels and with transparent communication, aligning the goals of employees, teams, managers and top management is of utmost importance to increase the productivity and performance of everyone.

· Engagement across the company

A. The sales department needs an immediate intervention plan. Being the least engaged department in the company, stakeholders should create a new initiative to address the low performance and engagement in the sales department and other low engaging departments as well.

B. This initiative could also be extended to production and software engineering departments. You might want to consider including offering professional growth opportunities for the employees, checking employees’ wellness, conducting regular employee engagement surveys, and prioritizing mental and physical health of employees among other things.

C. Positions like IT manager-infra, network engineer and enterprise architect need extreme attention. Being of the least engaged and performing positions in the company, the reasons behind that should addressed. Things like giving autonomy, encouraging interaction, giving recognition and offering more incentives could be remedial in this domain.

· Salary and absence across the company

A. One of the most important factors for being satisfied in a job is the salary. As a stakeholder, you need to give special attention to the sales department one more time. The sales department has the highest average absence days, lowest engagement score and the second lowest salary. If you look closer, you will find that the production department share the same low scores in engagement, big number of absence days as well as low salary. Clearly, salary here is an issue with the sales and production employees.

B. What could be applied to fix this problem, could be applied to the whole company. One thing could be reviewing salary structures. Looking into competitors’ salary scales and creating a transparent salary structure for the whole company is something you should think about.

C. Furthermore, offering bonuses based on performance and engagement is beneficial as well. You should also consider increasing benefits such as social and medical insurances, along with applying best practices in salary increases.

· Employment status and termination

A. You might need to roll up your sleeves and start preparing a succession plan. Around 40% of the employees in the production department have been let go/quit. Numbers include in Software engineering department: 36%, admin office: 22%, IT/IS: 20% and sales: 16%.

B. These numbers necessitate a carefully constructed succession plan; look for internal talents, employee development programs, involve the management in the decision, create a good onboarding program and ensure diversity and inclusion. Moreover, look for the reasons why do such high turnover rate exists in the company.

C. One of the reasons found here is that employees leave the company to find another position. This of course speaks to the working conditions and environment in the company. You could investigate that through exit surveys and interviews.

D. A closer look at the termination reasons listed by employees would give you insights on how to improve work environment in the company. Employees list reasons such as long hours-attendance: think of online/hybrid modes of work, more money: fix the salary scale and structure and relocation out of area: offer a decent relocation package to attract new hires.

· Diversity and inclusion

A. A careful examination of the workforce tapestry in the company will show that the company has one of the least diverse profiles. With the White race comprising 60% of the total employees and Hispanics: 0.32% of the total population, some attention is need here.

B. Start by looking at your executive team (CIO and CEO) and introduce ways to create a more diverse team.

C. One of the reasons why your company is not diverse enough could be discrimination of any sorts; strengthen anti-discriminatory policies and create an environment where every different voice is heard.

D. Another factor not to be overlooked here is eliminating bias in the recruitment and evaluation processes and promotion opportunities. Have a clear and transparent promotion/evaluation process with equal opportunities for all.

E. Make sure that technology is accessible by everyone through accessibility features and applications to ease the work for people of determination onboard (if any).

F. You might want to introduce diversity training and programs to foster a more inclusive workplace.

· Diversity and pay

A. Since the executive office is mainly White dominated, it increases the pay gap among different races. One way to address that is by introducing ways to create a more diverse company.

B. Moreover, in top management positions, women tend to be paid less than men in the company. First you need to review your salary scale. Then provide transparent, equal and clear promotion and pay policies across all positions, especially top management.

C. Increase benefits for female workers such as paid and long maternity leaves, childcare and menstrual leave.

D. Make sure women have equal and clear access to development opportunities in the company in general and in top managerial and executive positions in particular.

E. Encourage salary negotiations and foster an atmosphere where women can negotiate and discuss their viewpoints freely and without reservation.