IBM HR ATTRITION ANALYSIS WITH EXCEL
IBM HR ATTRITION ANALYSIS WITH EXCEL
INTRODUCTION: IBM HR Attrition Analysis is a comprehensive examination of the factors influencing employee attrition within IBM, a global technology and consulting company. Attrition is the voluntary and involuntary departure of employees from an organization, which can have profound effects on the company’s performance, productivity, and overall workforce stability.
This analysis delves into the various aspects of HR attrition within IBM, aiming to provide valuable insights into the underlying causes, patterns, and trends associated with employee turnover. By examining factors such as job satisfaction, career development opportunities, work-life balance, compensation and benefits, and stock option opportunity, this analysis seeks to shed light on the reasons why employees choose to leave IBM and how the company can address these issues effectively.
The goal of the IBM HR Attrition Analysis is to provide actionable insights that enable IBM’s human resources department and management to make informed decisions and implement effective retention strategies. By identifying the root causes of attrition and implementing targeted interventions, IBM can reduce turnover, enhance employee satisfaction, and cultivate a strong and resilient workforce capable of driving innovation and achieving organizational objectives.
IMPLEMENTATION OUTLINE
Analytics process: The tool I will use for this case study is MS Excel 2021. The data analytics process will follow the PMAVD (Prepare, Model, Analyze, Visualize and Dashboard) process. Data Source: Kaggle
https://www.kaggle.com/datasets/pavansubhasht/ibm-hr-analytics-attrition-dataset
PS: This is a fictional dataset created by IBM data scientists
a. Preparation: I deployed the OMG-C method in the data preparation step. Objectives, Measures, Get and Clean the data.
Objective:
- Calculate the necessary KPI
- Using an interactive visual, explore the top five reasons why employees leave the organization
b. Measures: The dataset for this task has thirty-six columns which contains Age,Attrition,BusinessTraveL,DailyRate,Department,DistanceFromHome,Education,EducationField,EmployeeCount,EmployeeNumber,EnvironmentSatisfaction,Gender,HourlyRate,JobInvolvement,JobLevel,JobRole,JobSatisfaction,MaritalStatus,MonthlyIncome,MonthlyRate,NumCompaniesWorkedOver18OverTime,PercentSalaryHike,PerformanceRating,RelationshipSatisfaction,StandardHours,StockOptionLevel,TotalWorkingYears,TrainingTimesLastYear,WorkLifeBalance,YearsAtCompany,YearsInCurrentRole,YearsSinceLastPromotion,YearsWithCurrManager. Total of 1,470 rows
1. DATA PREPARATION
I deployed the OMG-C method in the data preparation step. Objectives, Measures, Get and Clean the data.
Objective:
- Calculate the necessary KPI
- Using an interactive visual, explore the top five reasons why employees leave the organization
Get and Clean Data
In IBM HR sheet I removed some columns that is not needed from the objectives.
2. Exploratory Data Analysis (EDA)
EDA are a set of steps used to explore and understand the data better before cleaning and transformation.
a. Cleaning and Transformation
- Convert dataset into table: Ctrl A, Ctrl T, “OK”
- Export to power query: Go to “Data”, Select “From table”.
- Check for number of rows: Go to “Transform”, Select “Count Rows”
Columns: 36 Rows: 1470
this picture above is a blank worked that enabled me o get the dataset from my file location
The picture above is when I located the dataset from my file location(ETL PROCESS)
BELOW IS THE DATASET BEFORE CLEANING 1
Figure 3BEFORE CLEANING 2
Figure 4REMOVING COLUMNS THAT I DONT WANT TO USE FOR THE ANALYSIS
- Rearrange columns and use the column “Employee Number” as identifier. Change its datatype to text.
- The column “Education” contains numbers (1–5) only. From the dataset source, the represent the following
1 = High school
2 = Associate
3 = Bachelor’s degree
4 = Master’s degree
5 = Doctorate degree
Environment Satisfaction
1 = Low
2 = Medium
3 = High
4 = Very High
Job satisfaction
1 = very dissatisfied
2 = dissatisfied
3 = satisfied
4 = Very satisfied
performance status
1 = satisfied
2 = moderate
Age group
18–27
28–37
38–47
48–57
58–67
I replaced the numbers with their true values. Then, change datatype to “Text” with the help of conditional column
Created a conditional column and replace all numerical values with the appropriate educational values.
steps in creating conditional columns
Click on “Add Column”, Select “Conditional Column”, type in the new column name (Educational Level) and define the new values for the new columns.
I added some some columns like performance status, education level etc with help of conditional column by Following the same steps i took to creating condition column for the “Educational Level” column and create for the columns listed above.
Figure 6AFTER CLEANING
AFTER THE WHOLE CLEANING PROCESS, I CLOSED AND LOAD THE DATA BACK TO EXCEL FOR ME TO CARRY OUT AN ANALYSIS KNOWN AS DATA MODELING WITH THE HELP OF A TOOL CALLED PIVOT TABLE
DATA MODELING USING PIVOT TABLE
From the pivot table I generated all the necessary KPI like
Total employee
Active employee
Inactive employee(ATTRITION)
Attrition rate etc.
Also the reasons why employees leave the organization with the help of charts and graph where gotten from this pivot table analysis as shown below
Attrition By Gender On the new interface (Pivot Chart Field), drag “Attrition” to Filter, “Gender” to row and “Employee Number” to Value/Count.
For subsequent analysis, drag “Attrition” and “Employee Number” to Filters and Values respectively on the PivotChart Fields and click on the filter icon for attrition on the table and select “Yes”.
VISSUALS AND DASHBOARD
INSIGHTS: After the whole analysis, visualization creation ,and reports to track the attrition key performance indicator 9KPI0:
Employee count =1470
Attrition count=234
Attrition rate=16.12%
Avg age=37
Avg service years=11
Analyzing historical data
Attrition by age group: age group 28–37 with the highest number of attrition (113)
Attrition by education level: master’s degree holder has the highest attrition of 99
Attrition by job role: laboratory technicians have the highest attrition of 62
Attrition by gender: male has the highest attrition of 150
Attrition by job satisfaction: employee that are satisfied left the organization most (73)
Attrition by performance status: people with moderate performance left most (200)
Attrition by environmental satisfaction: people with low environmental satisfaction left most (72)
FILTERING
Business travel: employee that travels frequently have attrition rate of 24.91%, non-travelers have attrition rate of 8.00% and travel rarely have 14.96%
Marital status: employee that are married have attrition rate of 12.48%, single have attrition rate of 25.53% and divorced have 10.09%
Department: employee from HR department have attrition rate of 19.05%, R&D department have attrition rate of 13.84% and SL department have 20.63%
RECOMMENDATION
Attrition Analysis:
Identify reasons for the high attrition rate among inactive employees. Conduct exit interviews or surveys to understand their dissatisfaction and address any underlying issues.
Monitor attrition trends by gender, education level, job role, and environmental satisfaction to pinpoint areas for improvement and implement targeted retention strategies.
Employee Engagement:
Focus on improving job satisfaction and environmental satisfaction for better employee retention.
Provide opportunities for career development and growth to increase employee engagement and loyalty.
Work-Life Balance:
Assess the impact of business travel on attrition rates. Consider implementing flexible work arrangements or travel policies to support employees’ work-life balance.
Performance Management:
Review performance status across departments to identify any correlation with attrition rates. Offer additional support or training to underperforming employees to improve job satisfaction and retention.
Diversity and Inclusion:
Ensure equal opportunities and a supportive work environment for employees of all genders, marital statuses, and age groups to foster inclusivity and reduce attrition.
Departmental Analysis:
Analyze attrition rates by department and address any disparities or challenges specific to each department to improve overall retention.
Age Group Analysis:
Consider tailoring retention strategies based on different age groups’ needs and preferences to effectively retain talent across all demographics.
Continuous Monitoring:
Regularly review and update the attrition dashboard to track progress and adjust strategies as needed to maintain a healthy retention rate.
By implementing these recommendations, you can effectively address attrition issues and improve employee retention within the organization.