Turnover is the number of employees leaving the organization expressed as a percentage of the total number of employees. While some turnover is expected and healthy, excessive turnover is very costly and a hinderance to efficient operations. This measure will track turnover by major employee group (Police, Fire, General, and Overall) and department and allow comparison to other organizations, as well as identification of trends that may be indicative of other issues such as management effectiveness, compensation concerns, and other workplace processes or conditions not conducive to a high-performing environment.

Step-By-Step Data Gathering Process

To Obtain # of Separations:

1. Access Query Viewer from ERP HR & Payroll (Main Menu>Reporting Tools>Query>Query Viewer)
2. Run the query, Z_TERMINATED_EMP_RPT2 (Termination Report) using the date range of 9/30/xx to 10/01/xx and save to Excel
3. Make a copy of the report in a separate worksheet/tab and name it “Working Copy”.
4. Delete all records with a “T” in the Reg/Temp column.
5. Delete all records with a Term Date outside of the date range, 10/01/xx to 9/30/xx.
6. Insert a Pivot Table, using Job Family as the Row and a count of “ID” for the values.
7. # Separations (General) will be the total of all Job Families except Fire Civil Service and Police Civil Service.
8. # Separations (Police) will be the count for the Job Family, Police Civil Service.
9. # Separations (Fire) will be the count for the Job Family, Fire Civil Service.

To Obtain Avg # Employees:

1. Access Query Viewer from ERP HR & Payroll (Main Menu>Reporting Tools>Query>Query Viewer)
2. Run the query, Z_HR_EMPLOYEE_JOB_MASTER3 (HR Employee JOB Master WRS) as of the beginning of each quarter, and the end of the fourth quarter (five reports) and save to Excel.
3. Make a copy of the report in a separate worksheet/tab and name it “Working Copy”.
4. Delete all records with a “T” in the Reg/Temp column.
5. Insert a Pivot Table, using “Job Family” as the Row and a count of “ID” for the values.
6. Total the number of employees for all Job Families except Fire Civil Service and Police Civil Service for the # Employees (General).
7. The # Employees (Police) will be the count for Police Civil Service.
8. The # Employees (Fire) will be the count for Fire Civil Service.
9. In a separate Excel worksheet named Average Headcount Calculations, for each employee group, obtain an average number of employees for each quarter of the year, then average those four values for the average number of employees for the year. (Calculation: the number of employees as of 10/01/xx, plus the number of employees as of 1/01/xx, divided by two, equals the average number of employees for the first quarter; with like calculations for quarters two, three, and four).

All files are currently stored on Bill Sawyer’s personal drive in the folder, Turnover>Turnover KPI.

ClearPoint Data Series Descriptions & Formulas

1. # Separations (General) – Regular status (no Temporaries) General employees, both FT and PT that separate from employment for any reason during the fiscal year.
2. Avg # Employees (General) – Average number of Regular status (no Temporaries) General employees, both FT and PT employed during the fiscal year. (see formula above)
3. # Separations (Police) – Police Civil Service employees that separate from employment for any reason during the fiscal year.
4. Avg # Employees (Police) – Average number of Police Civil Service employees employed during the fiscal year. (see formula above)
5. # Separations (Fire) – Fire Civil Service employees that separate from employment for any reason during the fiscal year.
6. Avg # Employees (Fire) – Average number of Fire Civil Service employees employed during the fiscal year. (see formula above)

Target Calculations: N/A

Evaluated Series: N/A