Hi, I have a report that has the termination date and hiring date of employees, and what I need to get is the attrition forecast for the coming 3 months. Are there any suggestions? I have attached sample data and hope someone here can help.
Thank you!
From United States, Frisco
Thank you!
From United States, Frisco
Dear Zina A,
Thanks for coming up with a proper HR query. Most of the queries on this forum are on notice period, PF/ESI etc. These are not the real HR queries as such.
I have worked on the Excel sheet that you have provided. Following are the observations on the Excel sheet:
a) You have given the details of the date of exit (not termination) and date of joining of 135 employees. However, this is consolidated data for the whole organisation. There was no break up as per the department. Therefore, the department-wise analysis could not be done.
b) For the analysis purposes, I have converted the Excel sheet into Google Sheet. You may click here to have a look at it.
The following emerges from the Google Sheet:
c) The highest employee attrition (19%) happens between the _____ year slab.
d) For the three duration slabs i.e. ____ years, ____ years and ____ years, the attrition is 50%
Corrective action for (c) and (d): - Start conducting stay interviews as the employee completes _____ years of stay. Prepare a career plan and show the employees that there are growth opportunities in the company.
e) Employee attrition for _________ slab is 12% This could be because of the inability of the employees to align with the work environment or the work itself.
Corrective action for (e): - You may improve the recruitment practices.
f) ____% employee attritions is for >10 years slab. Probably this is because employees stay till their retirement. Though they are long-serving employees, if they are not adding value, then no need to regret their exit. Possibly you could hire their replacement with a lower salary to offset their exit.
I hope you are satisfied with the analysis.
Explanation on Forecasting: - Your query is on forecasting the attrition. However, the given data is inadequate to do a forecast of any kind. Furthermore, if the average length of stay of the employee is 1,618 days and if the Standard Deviation (SD) is 1,468 days, what forecasting anyone can do?
Other senior members may go through the Google sheet and provide their inputs.
Thanks,
Dinesh Divekar
From India, Bangalore
7884Thanks for coming up with a proper HR query. Most of the queries on this forum are on notice period, PF/ESI etc. These are not the real HR queries as such.
I have worked on the Excel sheet that you have provided. Following are the observations on the Excel sheet:
a) You have given the details of the date of exit (not termination) and date of joining of 135 employees. However, this is consolidated data for the whole organisation. There was no break up as per the department. Therefore, the department-wise analysis could not be done.
b) For the analysis purposes, I have converted the Excel sheet into Google Sheet. You may click here to have a look at it.
The following emerges from the Google Sheet:
c) The highest employee attrition (19%) happens between the _____ year slab.
d) For the three duration slabs i.e. ____ years, ____ years and ____ years, the attrition is 50%
Corrective action for (c) and (d): - Start conducting stay interviews as the employee completes _____ years of stay. Prepare a career plan and show the employees that there are growth opportunities in the company.
e) Employee attrition for _________ slab is 12% This could be because of the inability of the employees to align with the work environment or the work itself.
Corrective action for (e): - You may improve the recruitment practices.
f) ____% employee attritions is for >10 years slab. Probably this is because employees stay till their retirement. Though they are long-serving employees, if they are not adding value, then no need to regret their exit. Possibly you could hire their replacement with a lower salary to offset their exit.
I hope you are satisfied with the analysis.
Explanation on Forecasting: - Your query is on forecasting the attrition. However, the given data is inadequate to do a forecast of any kind. Furthermore, if the average length of stay of the employee is 1,618 days and if the Standard Deviation (SD) is 1,468 days, what forecasting anyone can do?
Other senior members may go through the Google sheet and provide their inputs.
Thanks,
Dinesh Divekar
From India, Bangalore
Dear all,
Now I have added one more sheet titled Month-wise Analysis to find out whether there is a seasonality factor in the data. The following are the findings:
a) Maximum attrition happens in the month of _____, _____, _____, _____ & May. These five constitute ___% of employee attrition.
b) From December to May, the attrition drops in February. Why this happens merits investigation.
c) From ____ to _____, these five months are slack. Just ____% of exits happened during these months.
d) Though I have not added a separate sheet, I have studied the recruitment pattern. There are no slack months in the recruitment. In the month of February, June & October a fewer number of employees joined compared with other months.
e) Slight flaw: - Though the data is for 136 employees, when I did a month-wise break-up, the analysis is coming for 134 employees only. Why the count reduces by 2 is not understood.
Thanks,
Dinesh Divekar
From India, Bangalore
Now I have added one more sheet titled Month-wise Analysis to find out whether there is a seasonality factor in the data. The following are the findings:
a) Maximum attrition happens in the month of _____, _____, _____, _____ & May. These five constitute ___% of employee attrition.
b) From December to May, the attrition drops in February. Why this happens merits investigation.
c) From ____ to _____, these five months are slack. Just ____% of exits happened during these months.
d) Though I have not added a separate sheet, I have studied the recruitment pattern. There are no slack months in the recruitment. In the month of February, June & October a fewer number of employees joined compared with other months.
e) Slight flaw: - Though the data is for 136 employees, when I did a month-wise break-up, the analysis is coming for 134 employees only. Why the count reduces by 2 is not understood.
Thanks,
Dinesh Divekar
From India, Bangalore
Hi all, my query is that I want to create awareness on early attrition i.e (0-3) months among the manages via email. Can some one help me with the content of the email on staffing yield. Regards MG
From India, Delhi
From India, Delhi
Hello Dinesh,
Thank you so much for your valuable and informative input, appreciate that!
Two things I want from you:
- What are the missing data/demographics you want me to provide to build the module
- Access to the Google sheet you created to download and get to know what formulas you used
Thank you,
Zina
From United States, Frisco
Thank you so much for your valuable and informative input, appreciate that!
Two things I want from you:
- What are the missing data/demographics you want me to provide to build the module
- Access to the Google sheet you created to download and get to know what formulas you used
Thank you,
Zina
From United States, Frisco
Dear Zina,
Earlier, I have given an exhaustive reply on "Attrition Analysis". You may click the following link to refer to it:
https://www.citehr.com/519562-have-w...ml#post2211229
You create the data based on the points given in my above reply.
Secondly, whoever has a link, can download the Google Sheet. A few members and my other friends have downloaded the Google sheet that I created. Therefore, downloading the sheet should not be a problem for you.
For further discussion, feel free to call me at +91-9900155394.
Thanks,
Dinesh Divekar
From India, Bangalore
Earlier, I have given an exhaustive reply on "Attrition Analysis". You may click the following link to refer to it:
https://www.citehr.com/519562-have-w...ml#post2211229
You create the data based on the points given in my above reply.
Secondly, whoever has a link, can download the Google Sheet. A few members and my other friends have downloaded the Google sheet that I created. Therefore, downloading the sheet should not be a problem for you.
For further discussion, feel free to call me at +91-9900155394.
Thanks,
Dinesh Divekar
From India, Bangalore
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
Employee Engagement – Scaling Down Attrition.pdf
SAMPLE Employee Attrition Calculation sheet for FY 09-10 .xls