Results 1 to 7 of 7

How do you guys compute employee turnover rate?

This is a discussion on How do you guys compute employee turnover rate? within the Excel Questions forums, part of the Question Forums category; I assume most, if not all, of us here have encountered a certain key perfomance indicator called employee turnover (or ...

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    1,706

    Default How do you guys compute employee turnover rate?

    I assume most, if not all, of us here have encountered a certain key perfomance indicator called employee turnover (or staff turnover). I have seen it a lot and didn't haven't really given much of a thought until it somehow ended up in one of the excel files I am administering. And now I must devise the formula to compute for it.

    First of all, what is the correct way of computing it? Is it just flat out the # of employees who resigned/fired divided by average # of employees? If this is so, then it is very possible to have more 100% turnover.

    If there are 3 positions in a company, A, B, C and position A has been replaced 3 times in a year, what is the turnover? And will it be different if all 3 position has been replaced but just once?

    What if the company is in a liquidation phase, say at the start of the year 2006 it has 300 staffs, then by end of June 2006 it had none (50 staffs resigned per month). What is the annual staff turnover by end of 2006? And what is the YTD staff turnover in each of the months?

    I have asked several people in the company I worked in and they have differing opinions.

    Would like to hear from you guys.

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,121

    Default

    Turnover is generally measured as the staff at the end of a period compasred to the staff at the beginning of a period.

    Hee's a fairly simple example:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 12.0 : OS =
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    EmployeesDateCumulative TurnoverPeriodic Turnover
    2
    1001/1/20070%
    3
    752/1/2007-25%-25%
    4
    1153/1/200715%53%
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Note that some of your hyotheticals may require you to have a pro-forma approach. I.E. can you really consider it turnover if employees have been retasked to another department?

    And yes, it is entirely possible to have over 100% turnover. Take a look at many newspapers, who routinely turn over 100%+ of their subscriber base in a given year, or industries that rely on seasonal labor.

    Calculating turnover, like any business statistic can be highly subjective...

    HTH,

    Smitty

    EDIT: moved to Excel Questions
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    1,706

    Default

    Turnover is generally measured as the staff at the end of a period compasred to the staff at the beginning of a period.

    Hee's a fairly simple example:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 12.0 : OS =
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    EmployeesDateCumulative TurnoverPeriodic Turnover
    2
    1001/1/20070%
    3
    752/1/2007-25%-25%
    4
    1153/1/200715%53%
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Note that some of your hyotheticals may require you to have a pro-forma approach. I.E. can you really consider it turnover if employees have been retasked to another department?

    And yes, it is entirely possible to have over 100% turnover. Take a look at many newspapers, who routinely turn over 100%+ of their subscriber base in a given year, or industries that rely on seasonal labor.

    Calculating turnover, like any business statistic can be highly subjective...

    HTH,

    Smitty

    EDIT: moved to Excel Questions
    Hi Smitty,

    Hmmn interesting. Seems we do not have the same understanding of the term "employee turnover". From what it is used as a KPI in the company I worked in, it appears that staff turnover is the a measurement of how often staffs resigned and get replaced. If you have a high staff turnover, it only means many staffs resign from the company and as such it is a signal to the HR to review compensation package in order to retain staffs.

    Having more than 100% seems to me means you have replaced more staffs than there is a number of available positions.

  4. #4
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    896

    Default

    If you have only one job position with any changes, but it changes several times, someone probably ought to review the job description and compensation package anyway. Suppose Ann the widget-maker leaves and is replaced by Bob, who leaves after only a month to be replaced by Carl, who then leaves and is replaced by Debbie: even though your year-end and year-start numbers are the same, you still want to take a hard look at the widget-maker's pay and duties to see why you have 300% turnover at that position. What's driving people out?

    To look at it another way: it's just as costly and disruptive to replace the widget-maker three times as it is to replace three different positions once each.
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows XP, Excel 2003
    Work: Windows XP and Windows 7, Excel 2003 and 2007

  5. #5
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,240

    Default

    Hi

    IMO the staff turnover would be the number of resignations for the period divided by the number of positions at the start of the period. So, as pointed out by gardnertoo and pennysaver, the turnover can be > 100%.

    Andrew
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  6. #6
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default

    Hi,

    If it is truly a key performace indicator used by the company I would expect them to provide guidance on how this is calculated to maintain consistency. If this is not the case then this is the basis I use for calculations if it's any help:

    - The numerator is the total number of permanent and temporary employees who have terminated their employment. If an employee has more than one job each job terminated is counted once.

    - The denominator is the total number of employees (permanent and temporary) calculated by a head count (a part time employee counts the same as a full time employee).

    I would be very,very concerned if the figures approached anywhere near 100% although I appreciate it is possible and could occur in certain industries.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  7. #7
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,615

    Default

    I just did quick Google Search, and found this article/discussion regarding tornover claculations. It may also be helpful.

    you still want to take a hard look at the widget-maker's pay and duties to see why you have 300% turnover at that position. What's driving people out?
    Oh man, I just have to share this tidbit from WTF in August... I still have to laugh...
    (XL2010 on Windows 7 Professional SP 1)

    H0mogenized pasteurized milk from confinement animals, and butter, cheeses, and yogurts made from such milk, may look the same and carry the same name, but the inference that they are the same as [raw unpasteurized milk from pasture-fed cows] is part of the insidious deception that has been perpetrated on (us) the consumers of industrialized food.

    -Ron Schmid, "The Untold Story of Milk"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com