Creating Voluntary Turnover

Reports
Introduction
There is a lot of flexibility in creating snapshots of the workforce in understanding why our employees
are leaving. The key to remember is that there isn’t only one way of viewing this information.

For the purposes of examining the case in class we will focus on voluntary turnover by occupation.
However, this is only one slice to gather critical data to determine if we have a problem and if so, what
occupations are an issue and then to prioritize action plans.

There are many ways to slice the data but here are a few other examples of different ways to examine
voluntary turnover which can include viewing the data by:

 Gender
 Age group
 Generation
 Region
 Department
 Manager / Director
 Salary level
 Tenure / Years of Service
 Time under current manager
 Compa-ratio
 Performance rating

We can then combine the above elements to do a deeper dive and gain further insights. Perhaps we are
trying to create predictive analysis, and determine which employees are most at risk for leaving. For
example, we might find that employees with less than 3 years of service, are under the average salary
level of their peers, are less than 28 years old and are in the top 2% of performance ratings have a 75%
greater chance of quitting than the average employee. Since these employees could be seen as “up and
comers” we could take pre-emptive action to implement plans to retain and develop this essential
talent.

Another consideration to take into account with predictive analytics is the idea of lagging and leading
indicators. Voluntary turnover is a lagging indicator meaning that it reports what has already happened.
When we consider what are the early signs that will eventually contribute to voluntary turnover these
are called leading indicators. Some of these include employee satisfaction, absenteeism, and
engagement.

Overview
This guide will focus on answering a simple question, “Which occupations have higher than average
turnover each year?” Then we will do a deeper dive and ask, “Which occupations have higher than

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 1 of 13

average turnover across all four years?” If an occupation has higher than average turnover year after
year, then this should be seen as high priority in trying reduce turnover. However, this will depend on
numerous factors: does the voluntary turnover help with the executive’s strategic plan on reducing the
workforce?; do the employees leaving have a critical skill set that is unique and difficult to replace?; can
the occupations with higher turnover be outsourced or replaced with contractors?; etc.

Here is the report we will work to complete.

Files Needed to Complete a Voluntary Turnover Report

To figure out voluntary turnover by occupation we need two sets of numbers to complete the formula:

Voluntary Turnover ∈Year x

X 100=Year x Voluntary Turnover %
Headcount ∈Year x

For example, in 2018 Accounting had 14 people leave and total headcount of 284. The voluntary
turnover rate would be 4.9%. Please note that if you change the cell format to Percent in Excel, you do
not have to multiply the result by 100.

14
=0.049=4.9 %
284
We need this information because we cannot go across years to determine turnover. In other words, if
you wanted to calculate 2018 turnover, you must use the voluntary turnover in 2018 and divide by the
headcount in 2018. You cannot divide 2018 turnover by 2021 headcount.

The files we will need are:

 Headcount History by Job and Gender  for all the denominators through the years
 Termination Database  for the numerators through the years
 A blank spreadsheet file  for the calculations and to make your tables/reports

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 2 of 13

We’ll first start creating our report by finding the bottom number. The Headcount History by Gender
and Job file contains all occupations by year and gender. We do not need the gender data for this
report.

Copy the data and paste into new sheet. It is always a good idea to do this so that the source data is not
affected by accident.

For our purposes we do not need the split between gender and we do not need the data for 2022
because, as you will see later, we do not have turnover data for 2022. Delete the gender data and 2022
data.

Once you have eliminated the unnecessary data, relabel

the headings and create a group title.

This data shows us the total number of employees

who worked with us each year.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 3 of 13

Now, open the Terminated Employee Database. It will open to a pre-created pivot table.

Click on the one of the areas inside

the pivot table to bring up the
available fields.

Once done, put Occupations in Rows, Termination Category in Filters, and Gender in Values. Also, don’t
forget, since this is a Voluntary Turnover report, make sure you choose “Voluntary” from the dropdown
menu in filters. Often students forget this step and create Total Attrition reports which is not what we
are looking for.

Highlight the data inside your pivot table, including the occupations but excluding the far-right column
“Grand Total” (this column adds up all the terminations from the years 2018 to 2021 and is not very
useful to us for analysis), right click, and copy the data.

You’ll see in a moment that we copy the Occupation names to help us align data but will remove it to
clean up the report.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 4 of 13

Go to the spreadsheet where you already have the headcount history information and paste the data
(AS VALUES) alongside the Headcount History data you pasted previously so that “Accountant” is on the
same line for both lists (shown below in yellow):

Unfortunately, as the screenshot shows, your two lists are not the same size. This is because there are
many occupational groups at Generesta that have had NO voluntary turnover since 2017. In order to line
up your numerators (voluntary terminations, on the right) and your denominators (headcount history,
on the left), insert blank lines or cut and paste the list so that the occupational groups line up properly.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 5 of 13

Once the data is now properly aligned, we can remove the Occupation titles in column F since we
already have them in column A. Afterwards, properly name the data in columns F to I.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 6 of 13

 Once your two
lists are lined up
as shown below,
you’ll need to
create new
columns on the far
right to calculate
your voluntary
turnover rates.

In this company,
terminations
through the
calendar year are
divided against
the headcount at
the start of the year. So, for 2018 turnover, we’re dividing the 2018 terminations against the 2018
headcount, as shown to the right.

After writing the formula for

2018 Accounting, you can
fill across to 2021. Then
highlight your formula for
Accounting from 2018 –
2021, and fill down to the
Grand Total. (#DIV/0!
Appears because you’re
dividing against zero. Only
in 2022 did the company
bring in co-op students).

Replace the DIV/0!’s with

zeros, and then convert all
those decimals to %. Add 1
decimal place for turnover
%’s.

Applying Conditional Formatting

The objective of turnover segmentation is to highlight the number of terminations within one
occupation, and divide them against the number of people in that same occupation. Overall, a
company’s turnover rates could appear low at 5%, but certain occupations, such as managers, could be
much higher, at 9% per year. Highlight all the %’s for 2007, except the Grand Total %.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 7 of 13

Click on “Conditional Formatting” button at the top of your screen and select “Highlight Cells,” then
“Greater Than.” The cell for you to select is the 2018 Grand Total %.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 8 of 13

The cell for you to select is the 2018 Grand Total %.

Repeat the previous steps for 2019 through 2021. For each turnover year, the column of turnover rates
will highlight if they are higher than the overall company turnover rate at the bottom of your table. This
removes the “noise” and lets your easily target which occupational groups at your company are higher
than average.

Cells highlighted in red are

automatically highlighted because
they are higher than the overall
company turnover rate for 2018.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 9 of 13

Once done it is easy to find the occupations that are problematic across all four years because the
conditional formatting will be applied for 2018 to 2021.

We can also dive a little deeper and determine the occupation that had the largest increase in voluntary
turnover from 2018 to 2021. For this we will use the percent change formula and it must be applied to
the raw data and not the percentages.

The formula is for rate of change is:

( Voluntary Turnover T −Voluntary Turnover T− x )

Voluntary Turnover T− x
Where T is the most recent time period, and T-x is the furthest time period from the most recent that
you are including in your analysis. Remember it can be for any time period. Perhaps you are looking at
change from last year to this year, of change from last Q4 last year to Q4 the year prior, or change from
2009 to 2019, etc.

We will begin with Accounting. The formula will be:

( 2021Voluntary Turnover−2018 Voluntary Turnover ) ( 5−14 )

= =−0.643=−64.3 %
2018 Voluntary Turnover 14

Afterwards copy the formula down and convert all those decimals to % and add 1 decimal place.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 10 of 13

You’ll notice that many of the results have the error code #DIV/0!. To get rid of this we’ll alter the
original formula to replace any error codes with a 0%.

To do this, wrap the original formula in the =IFERROR formula. This formula checks to see if the result is
an error and if so, replaces it with whatever you choose to display.

In the first line we’ll make the change and then copy it down.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 11 of 13

Now to find the occupation with the highest increase in
turnover we can manually look for it, which will turn out to
be HR at 1000%. This is a small list so it is easy to do.
Alternatively, we can use conditional formatting to show us
the cell with the highest number.

Select the data from cell O5:O30 and select Conditional

Formatting. Then select “Top 10 Items…”.

From here change the number from 10 to 1.

This will show you the highest percent change from 2018 to 2021.

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 12 of 13

HR Metrics – Voluntary Turnover © 2022 Michael Pitkanen Page 13 of 13