Employee Attrition

Attrition is the loss of human resources in an organization. A high attrition rate is a cause of concern for organization. It is a reduction in the manpower strength of an organization. Attrition can be voluntary or involuntary.

Voluntary attrition is due to resignation by the employee due to personal reasons, growth prospects, health problems etc.

Involuntary attrition includes retrenchment, layoff, terminations etc.

Steps for calculating attrition rate

E.g. : Attrition Rate for 2016 . (We are considering calendar year 01 Jan 2016 to 31 Dec 2016)

Calculate the below

  1. Opening Count: This is the number of employee who are active at the start of your assessment period. Your opening count will be employees who were active as on 01 Jan 2016.
  2. Newly Joined: These are the employee who have joined in your assessment period. ( 01 Jan 2016 to 31 Dec 2016)
  3. Resigned: These are the employee who have left in your assessment period. ( 01 Jan 2016 to 31 Dec 2016)
  4. Closing Count: (Opening Count + Newly Joined) – Resigned.These are the employees who remain active at the last day of the assessment period.

The closing count for 2016 will become the opening count for 2017.





Opening Count was 365 . New Joined was 78. So now we have a total of 443 employees. The attrition percent is 20.32%. So ( 443 * 20.32%) = 90. Hence it matched our resigned employees.

The alternative formula generally used in calculating attrition is as under:

(( Resigned / Average Head Count))*100

Here average head count is (( Opening Count + Closing Count )/2)

However there will be a slight difference in the attrition value as we average our total employee count.( As per second formula the attrition value comes to 22.6 %)

As a personal preference ,I always use the first formula.

You can further use the above formula and bifurcate attrition percentage across departments, locations, Gender, Grades etc.

Excel Charts for Attrition

The Joinee , Resigned and Attrition data can be plotted in different charts formats.

Example 1

Example 2

Example 3

Example 4

Attrition Data Monthwise.

The Data Set is as below:

The above data can be represented in various chart formats as under:

Example 1

Example 2

Example 3

Attrition % in a Line Chart

There are two variants of this chart

  • Highlighting the year with maximum attrition
  • Highlighting the selected year.

The user can adopt this chart and create more variations like years having above attrition highlighted or separating a yearly attrition across departments /locations and then highlighting the department/location with highest attrition.







Waterfall Chart – Employee Head Count.

A waterfall chart is generally used to display how the starting position and an ending point which are connected by a series of values (either increases or decreases). By a series of changes from the start value and through all the connecting intermediate increasing/decreasing values we arrive at the end point.

A waterfall chart is also called a bridge chart since the floating in between columns between the start value and the end value form a pseudo bridge.

We display the employee headcount in a monthly basis (increase /decrease) basis the new joinee and resignees in a month.

We start with the opening count of the year. The opening count are those employees who are on active payroll of the company at the start of the assessment year.

For E.g : Assessment year ( 01 Jan 2016 to 31 Dec 2016)

The opening count for 01 Jan 2016 will be the closing count as on 31 Dec 2015.

Then we show the net employee count for each month basis the employee joining or leaving . This is shown till Dec and then we display the closing count for Dec 2016.

In below example the opening count at the start of the year is 500. Throughout the year in each month we have certain number of joinees and resignees. Due to the same we may have an increase or a dercrease in the net employee count . At the end of the year the closing employee count is 509.We can see that in Jan there was a employee count decrease of 2 . So it means number of employees reduced from 500 to 498.

In Feb then there was a increase of 4. So employee count increased from 498 to 502. Continuing the calculations at the end of the year we have a closing count of 509.

Opening Count + Monthly Increase/Decrease) = Closing Count

500 + (-2+4+4+3+2+3-3-1-1-4-1+5) = 509

Download Waterfall Chart – Employee Headcount



Employee Tenure in a Sorted Bar Chart

We have created various tenure bands like 0-3 years, 3-5 years, 5-7 years and 7+ years and then grouped employees accordingly.

The user selects the required department. The Bar Chart will dynamically update in descending order. The tenure band having the highest number of employees will be displayed at the top. The respective employee count and percentage will also be shown.

 Download Employee Tenure

Infographics using Male Female Icons

Infographics is the use of  images/icons to represent your data. They help to capture the users attention and also give a creative feel to your data.Infographics in Excel Charts make it visually appealing .

For e.g : We have a data based on an employee engagement survey.

The employee engagement % values for men and women are shown.

The chart is good. It gives you the required information. But imagine a dashboard wherein you have multiple column charts and bar charts. This chart would be just another chart to the mix.

So to enhance your data representation you need to apply infographics.

Now what is infographics. It is showing of information via images.

So the same chart above using infographics is shown below.

The image fill amount is determined by the respective % values.

As the percent changes the image fill for the respective image as changes. See below

Download Template _Infographics_Employee Engagement

Attrition % in a Bullet Chart

Bullet Charts is a variation of the bar chart developed by Stephen Few.

They are emerging as a strong alternative to Gauge Charts. It is used for analyzing whether a parameter score is poor, average, good or excellent. One can use it for determine employee engagement score, actual vs target, HR audit score etc.

For e.g

  • 0 – 30% = Poor
  • 31% -50% = Average
  • 51%-70% = Good
  • 71%-100% = Excellent

One must base the scales depending on requirement.

If you are measuring a performance score then a 75 % score is excellent. However if you measuring employee attrition then a score of 75% is very negative. Here your scales will be reversed where 0-30% will be a fair score. Therefore user must determine how the parameter would be measured.

Bullet charts in addition to the scales have an actual achievement and a target parameter associated with it.

However in our attrition chart we are using only the actual parameter to see in which scale our attrition percent lies.

Download -Bullet Chart

Attrition – Using Gauge Chart

In our example, we have the data for attrition percent for certain years.

The chart has a combo box which has all our available years in a drop down.

In our Gauge chart we have three sections denoting three levels of attrition, Low, Moderate, and High.

Upon selecting the required year, the attrition % for that year is denoted by an indicator. The indicator points to the appropriate section of the Gauge Chart.











Download Chart Attrition_Gauge Chart


Introduction – Variance Analysis

Variance analysis refers to a difference between two values. The deviation may favorable or unfavorable as under:

Variance is generally calculated as per below parameters

The time period for measuring deviation can be as under:

  • Current Month vs Previous Month
  • Current Year vs Previous Year
  • First Quarter vs Second Quarter

Variance Reports are made to find out the % increase or decrease for Profit, Revenue, Cost Expenses, Attrition, Manpower Count, Salary, Recruitment Cost etc.

Examples of Variance Analysis charts