Brainteaser: formula to calculate annual rate of attrition...
Posted by rm on February 15, 2002 11:50 AM
Hello,
Can anyone come up with a formula to calculate a company's monthly and annual accumulated turnover rate percentage (and help solve a difference of opinion between myself and my boss)? For example:
in January 2001, a company had 60 employees and
lost 0; in February, the company had 63 employees
and lost 2, etc...
What's the formula for monthly turnover%, then the accumulated annual percentage up to that month? I am using " =M5/N5 " to calculate that month's turnover%, (where M5 is terminated employees and N5 is the total number of employees). Then to calculate the annualized turnover rate% up to that month, I am using " =AVERAGE('array') " (where 'array' is the range of each month's turnover% up to that month--e.g. Jan-May). My boss says that this second formula is not correct and to use " =(M5/N5)*12 " for every month and also at to figure end of the year%(?!?)
I really think this is wrong, but please correct me if I am in the wrong...I appreciate any insight and help...thank you in advance...rm
Posted by John on February 15, 2002 12:19 PM
The first formula (for the monthly rate) appears correct. For the second part:
If you want Year to Date, just add the monthly percentages.
If you want the result annualized, use =AVERAGE(range)*12 {(range) is the M5/N5 cells}
If you have a month with no turnover, be sure to enter a zero, so the AVERAGE formula will work properly.
And don't argue too much with your boss, or you might be part of the "turnover".
Posted by rm on February 15, 2002 5:18 PM
...good call!
Hi John,
Thanks for the advice (the latter as well)! We really aren't arguing, though, just differing in opinion...(I do remember rules #1 and #2 if the boss is wrong!) ;P
