How to Calculate Annualized Turnover
Summary
How to Annualize TO %Content
Hi
We are interested in adding annualized TO % to our current TO reporting. I have written a formula that returns the correct annualized TO % for January, current year, however I am unsure how to create this formula for each month going forward.
This is an example of our report
| 2018 Q 1 | 2018 Q 2 | 2018 Q 3 | 2018 Q 4 | 2018 Q 1 Total | 2018 Q 2 Total | 2018 Q 3 Total | 2018 Q 4 Total | Grand Total | |||||||||
| 2018 / 01 | 2018 / 02 | 2018 / 03 | 2018 / 04 | 2018 / 05 | 2018 / 06 | 2018 / 07 | 2018 / 08 | 2018 / 09 | 2018 / 10 | 2018 / 11 | 2018 / 12 | ||||||
| Start HC | 4,524. | 4,514. | 4,507. | 4,504. | 4,504. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,524. | 4,504. | 4,503. | 4,503. | 4,524. |
| Terminations | 10. | 8. | 3. | 0. | 1. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 21. | 1. | 0. | 0. | 22. |
| Hires | 0. | 1. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 1. | 0. | 0. | 0. | 1. |
| Rehires | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. | 0. |
| NET Transfer In/Out | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| End HC | 4,514. | 4,507. | 4,504. | 4,504. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,503. | 4,504. | 4,503. | 4,503. | 4,503. | 4,503. |
| Turnover % | 0.22% | 0.18% | 0.07% | 0.00% | 0.02% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.46% | 0.02% | 0.00% | 0.00% | 0.49% |
| Annualized Turnover % | 2.7% | 2.1% | 0.8% | 0.0% | 0.3% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 5.6% | 0.3% | 0.0% | 0.0% | 5.8% |
Below is the formula used to calculate the annualized TO % for January
(CASE WHEN "Assignment Event"."Termination Headcount" IS NOT NULL THEN "Assignment Event"."Termination Headcount" ELSE 0 END/("Workforce Management - Workforce Trend Real Time"."Workforce Trend"."Period Start Active Headcount"+CASE WHEN "Assignment Event"."Hire Headcount" IS NOT NULL THEN "Assignment Event"."Hire Headcount" ELSE 0 END+CASE WHEN "Assignment Event"."Rehire Headcount" IS NOT NULL THEN "Assignment Event"."Rehire Headcount" ELSE 0 END+("Workforce Management - Workforce Trend Real Time"."Workforce Trend"."Period End Active Headcount"-"Workforce Management - Workforce Trend Real Time"."Workforce Trend"."Period Start Active Headcount")-((CASE WHEN "Assignment Event"."Rehire Headcount" IS NOT NULL THEN "Assignment Event"."Rehire Headcount" ELSE 0 END)+(CASE WHEN "Assignment Event"."Hire Headcount" IS NOT NULL THEN "Assignment Event"."Hire Headcount" ELSE 0