Hiring process Analytics
Introduction: Hiring human resources is a crucial Process in the organization. To make this
decision the hiring team needs some crucial information like how many vacancies are
available, number of interviews conducted, departments, gender of the candidate, salary etc.
These factors are analyzed beforehand to hire people.
In this Project we have a dataset of a company where the details about people who registered
for a particular post in a department of this company are given. We have to implement
statistical techniques to analyze this dataset.
Objectives
In the project we have to determine following metrics:
• Hiring: Process of intaking of people into an organization for different kinds of
positions. Number of males and females hired.
• Average Salary: What is the average salary offered in this company.
• Class Intervals: The class interval is the difference between the upper-class limit and
the lower-class limit. Draw the class intervals for salary in the company.
• Charts and Plots: Draw Pie Chart / Bar Graph to show proportion of people
working different department.
• Charts: Use different charts and graphs to perform the task representing the data.
Represent different post tiers using chart/graph.
Tech-stack used: For this Project I have used MS Excel as it is one of the amazing
applications which is so easy to use and provides lots of tools and functions for working with
the data.
Approach: To begin with, Firstly I conducted an Exploratory Data Analysis to get the feel
of the data and understand it properly. I have gone through my dataset, attributes given for
each row entry, I have checked for the missing value and outliers in the dataset, and used
descriptive statistics to summarize the data. We have used Sort and filter function, Pivot table
and pivot table analyze, descriptive statistics etc. to solve the case.
Exploratory Data Analysis
• Understanding data columns and data: We have entries of 7168 applicants in
our dataset. The details comprise of application Id, date of the interview, status
(hired or rejected), candidate gender, department name, post name and salary
offered.
• Checking for missing data: In Excel it is very easy to find the missing values by
using sort and filter function. We have 15 missing values on event_name which is
the gender column and 1 missing value in the post name for which application id
is ‘289907’. There is one missing value in salary column for the application Id
‘114584’.
• Checking for outliers: In our dataset the only quantitative variable we have is the
‘offered salary’ column. There are so many methods to find the outliers in the
dataset. I have used Z Score to find it. So, the Z Score which are beyond 3 were
considered as outliers. So, we have 3 outliers in our dataset with applicant ID
‘649039’, ‘795330’ and ‘874368’.
• Drawing Data Summary: Data summary can be obtained by using descriptive
statistics in case of quantitative variable. Descriptive statistics tool is present
under Data analysis tool pack in Excel.
Offered Salary
Mean 49983.02902
Standard Error 340.8317054
Median 49625
Mode 72843
Standard
Deviation 28854.17689
Sample Variance 832563524
Kurtosis 2.610052003
Skewness 0.361578537
Range 399900
Minimum 100
Maximum 400000
Sum 358228369
Count 7167
In the above table, we can see the average salary, minimum salary, maximum salary and
many other statistics.
As for the categorical variables, we have 2675 female applicants, 4085 Male applicants and
268 candidates have not disclosed their gender. Out of the total interviews conducted 2471
candidates being rejected and 4697 are hired. The dataset we have here is for interviews
conducted between May 2014 to August 2014. The departments for which hiring has been
conducted is finance, General Management, HR, Marketing, Operations, Production,
Purchase, sales and Service.
HIRING: How many males and females are Hired.
1856 Females and 2563 Males are hired.
3000
2563
2500
1856
2000
1500
Total
1000
500
0
Female Male
AVERAGE SALARY
Average salary is 49983.
CLASS INTERVALS: Draw the class intervals for salary in the company.
Lower Limit: Mean-Z_Score*Standard Deviation/SQRT(n)
Upper Limit: Mean+ Z_Score*Standard Deviation/SQRT(n)
Let’s assume Z_Score is 1.96. Mean salary is 49983 and standard deviation is 28854. By
using the above formula, we get lower limit as 49315 and upper limit as 50651. Class Interval
is difference between lower limit and upper limit. Class interval for salary is 1336.
CHARTS AND PLOTS: Draw Pie Chart / Bar Graph to show proportion of people working
in different department
Service Department 1332
Sales Department 485
Purchase Department 230
Production Department 246
Operations Department 1843
Marketing Department 202
Human Resource Department 70
General Management 113
Finance Department 176
0 500 1000 1500 2000
We can see maximum number of employees is working in Operations department. And then
into the service department. Least number of employees are employed in Human Resource
Department.
CHARTS AND VISUALIZATIONS
Box And whisker Chart of Salary: It is a Five-point summary of salary column which
shows minimum salary, maximum salary, average salary, median, Quartile1 and Quartile 3.
We can also see we have three outlier values in the salary.
Column chart for candidates hired and rejected by gender: it is showing how many males
and females are rejected and hired by the company. Hiring proportion is more than that of
rejection. More Male candidates are hired.
Column Chart for Hiring in Different Months: It shows hiring is on an upward trajectory.
Number of candidates hired have increased over the months.
Histogram of salary: It shows that salary has a normal distribution curve. That means salary
of most employees are centered around average salary.
Following table shows how many candidates are hired by the companies in different
departments for various posts. Maximum number of hiring has been done for post c5 and c9.
Least hiring is for m6 and n6.
Department Post Name b9 c-10 c5 c8 c9 i1 i4 i5 i6 i7 m6 n6
Finance Department 8 2 34 3 81 4 24 5 15
General Management 2 7 18 4 24 1 1 23 6 27
Human Resource Department 1 15 5 4 2 30 5 8
Marketing Department 16 7 54 19 37 10 1 17 10 31
Operations Department 97 44 455 51 502 65 15 194 186 233 1
Production Department 29 5 48 3 60 16 1 26 19 39
Purchase Department 18 2 83 3 51 1 19 16 36 1
Sales Department 1 15 8 142 36 126 2 3 53 21 78
Service Department 122 30 333 69 354 51 10 125 69 168 1
Grand Total 1 308 105 1182 193 1239 151 32 511 337 635 2 1
The table below provides the statistical summary for salary column.
Offered Salary
Mean 49983.02902
Standard Error 340.8317054
Median 49625
Mode 72843
Standard Deviation 28854.17689
Sample Variance 832563524
Kurtosis 2.610052003
Skewness 0.361578537
Range 399900
Minimum 100
Maximum 400000
Sum 358228369
Count 7167
Insights And Result
In this Project, I have learned many statistical concepts like central tendency, skewness, five-
point summary, outliers, how to calculate a Z score, performing EDA on MS Excel and using
Data analysis add in tool to summarize and analyze the data. I have used pivot table analyze
for charts and visualization to represent the summary of the dataset. This project helped me in
gaining more clarity of the statistical concepts and learning data analysis in MD Excel.
References
https://docs.google.com/spreadsheets/d/1gncGhS1sDCi_n8ZPZQR-
_bLrwzkZB4xP/edit?usp=sharing&ouid=114414534387652139803&rtpof=true&sd=true
