Get to know employee’s annual leave
Background:
Owing to Taiwan’s labor law amendment effective from the year of 2017, employers are required to cash-out all the un-used Annual Leave (AL) to employees at the end of each year.
Purpose:
The purpose of this analysis is to analyze which employee segmentation requires more attention either from the senior management team or the human resources department.
*The source file for this analysis is dummy data.
Source:
This story is created for message delivery. For original python codes, please refer to my Github repository:
Variable Explanations:
Entitlement: Number of AL days employees are entitled to in the year. The days are granted based on seniority but not in a proportional way. In general, senior employees have more AL days.
Remaining: Number of AL days employees remain unused at the end of the year.
Monthly Salary: The amount of salary employees are paid every month.
Cash-out: Amount employers are obliged to pay employees for un-used AL days. Formula: Remaining * Monthly Salary/ 30 (monthly salary is divided by 30 in order to convert it to daily salary)
Ratio: Ratio of AL usage. Formula: (Entitlement — Remaining)/Entitlement
Years of Service: Number of years that employees work in the company.
YOS: Employees are divided by each five-year based on their Years of Service and then allocated to different YOS groups.
Summary of the findings
Before starting all the paragraphs, let’s first jump into the findings we discovered after the whole analysis,
1. Consulting Service Department is with the lowest AL usage ratio and the most Cash-out amount. Considering its average entitlement and monthly salary are not the highest among all the other departments, the department is relatively not taking equivalent Annual Leave which leads to a huge Cash-out amount.
2. Job Title ‘Service Delivery Manager’, ‘Account Sales Executive’ and ‘Project Executive’ have the lowest AL usage ratio throughout the whole company.
3. In the contrary to the gut feeling, Job Grade is more decisive than Years of Service when it comes to Cash-out amount. Thus, they shall be the target if the company wants to reduce the cost.
Now, let’s see how we discover these findings.
Read/ Load Data
First, we need to read the source file and add some useful columns for the analysis.
After the data is loaded, a summary table can help to capture the whole picture of the status.
Analysis
Finding 1 — Consulting Service Department is not taking equivalent Annual Leave. (Group By Department)
Through codes below, we can group employees by department. It’s easy to see from the graphs that the Business Service Department has the lowest AL usage ratio, the most remaining AL days, and the most Cash-out amount.
Ratio_by_BU = data.groupby(['Department']).mean()['Ratio']
Ratio_by_BU = data.groupby(['Department']).mean()['Entitlement']
Ratio_by_BU = data.groupby(['Department']).mean()['Remaining']
Ratio_by_BU = data.groupby(['Department']).mean()['Cash-out']
Ratio_by_BU = data.groupby(['Department']).mean()['Monthly Salary']
Since the department is not the one which has the most average days of AL entitlement or the highest average Monthly Salary, we will say that Business Service Department is not taking equivalent AL compared to other departments. Sales Department has a similar issue as the Business Service Department.
Finding 2 — Job Title ‘Service Delivery Manager’, ‘Account Sales Executive’ and ‘Project Executive’ have the lowest AL usage rate. (Group by Job Title)
Through the codes below, we can find out the AL usage ratio by job title. Among the whole company, Service Delivery Manager, Account Sales Executive, Project Executive are the three job titles with the lowest AL usage rate.
data.groupby(['Job Title']).mean(['Ratio'].sort_values().dropna().head(3))What’s more, below function can be used to lookup three job titles with the lowest AL usage ratio within one specific department.
Finding 3 — Years of Service is not the decisive factor for cashing-out. (Group by YOS)
The number of AL days are granted to employees based on the Years of Service. Therefore, there is a saying —
“AL days granted to senior employees are too many. The number of days should be reduced as long as it’s unrealistic to ask them to use up all the AL days. Those un-used days are the root cause for the unbearable cash-out amount.”
However, after grouping employees by YOS, we can see that there is no direct relationship between AL usage ratio and YOS.
We further analyzed correlations between different variables:
- The Monthly Salary is positively correlated to Job Grade (0.88), less positively correlated to Years of Service (0.58).
- Years of Service is positively correlated to Entitlement (0.93), less positively correlated to Job Grade (0.53).
- Remaining doesn’t have a clear relationship with Years of Service (0.31).
As the formula for the cash-out amount is Remaining * Monthly Salary/ 30, and Monthly Salary is highly correlated to Job Grade, we’ll say Job Grade is more decisive than the Year of Service when it comes to Cash-out amount. It’s likely that senior employees have higher job grades, which lead to higher salary and higher cash-out amount. The saying is inaccurately interpreting the situation.
Finding 4 — Employees with higher Job Grade is not taking equivalent leaves. (Group by Job Grade)
When we group employees by Job Grade, we can see that employees with higher Job Grade tend to have lower AL usage ratios.
Therefore, since employees have higher Job Grade tend to have higher Monthly Salary and lower AL usage ratio, if the company would like to ameliorate the current situation, employees with higher Job Grade should be considered as the target group.
Recommendations
In order to reduce the cash-out amount, we can propose a promotion campaign targeting the below groups based on our findings.
1. Employees in the Consulting Service Department.
2. Employees with Job Title ‘Service Delivery Manager’, ‘Account Sales Executive’ and ‘Project Executive’
3. Employees with higher Job Grade instead of longer YOS.
