What analysis would be good for Employee Retention

[Aman Semwal]

I am sharing a sample which consist of 3 worksheet were 1. Current means Current Year 2. 17-18 means - Year 2017 -2018 & 3. 16-17 means - Year 2016-2017
This is a sample sheet were employee may be or may not be familiar on all the sheet.

So what analysis would be good to get whom should we retain ?

 

[vletm]

Aman Semwal
Your data shows:
- none has worked 2019
- emp A has worked from 2016-2018, 2020-2021
- emps E & F do not work any more
- some values are huge
Seem that emps B, C & D will stop working after this year and A will keep on working.
Next year, there will be three new employees.
There should be more data.

 

[Aman Semwal]

Please find my reply on the below :

Your data shows:
- none has worked 2019 - "During COVID situation data was not collated by team. Therefore I have only data of 2018 till 2016 & current April 20- till sep 21"
- emp A has worked from 2016-2018, 2020-2021 - Yes and employee may be same for all the year or have worked only 2016 till 2018
- emps E & F do not work any more
- some values are huge - The Average are huge since the department is of sales and total sales done by the employee have crossed the 100%
-Seem that emps B, C & D will stop working after this year and A will keep on working. - So I need an analysis whom should I retain and check the retention rate analysis and the employee which are not reflecting on next YTD may be they left the organization or move internally.
Next year, there will be three new employees. - This is only an example but my data consist employee more than 200+
There should be more data

 

[vletm]

Aman Semwal
Your This is only an example but my data consist employee more than 200+
Your sample data should be more realistic.

 

[Aman Semwal]

Please refer the sample

 

[p45cal]

In the attached, sheet Append1, there's a pivot table based on consolidated data from your 3 sheets, BUT, the source data for the pivot only includes Em nos who have values in 2021 Q2, that is, people who were working at that time.
The Average column on the right is the average of the visible results, you can filter the YQ field for say just the last 4 quarters.
The table is sorted by the highest to lowest average.

 

[Aman Semwal]

Need 4 Analysis with the given data :
1. Average performer vs current performer
2. Productivity(Target at previous year vs current year)
3. Percentile Retention curve
4. Consistency ratio

 

[p45cal]

Good luck.