Using HR Analytics to Diagnose Frontline staff Turnover

This case was developed as part of my CIPD Level 7 and MA in HRM at the University of Westminster, where I applied HR theory and HR analytics learnings to realistic business scenarios through simulated consultancy briefs. This case reflects the approach I would take as an HR practitioner when presented with this situation.

Situation

NetPark is a mid-sized parking operator employing approximately 728 people across multiple operational sites. The majority of employees work in frontline operational roles including parking attendants, shift supervisors, security controllers and cleaning staff. Work is predominantly shift-based and dispersed across units, with a small HR team supporting largely manual, spreadsheet-based processes. The organisation was experiencing a frontline turnover rate of 75% annually and had no structured way of understanding where the problem was most acute or what was driving it.

Task

Presented with this scenario as part of a consultancy brief, my role was to recommend a practical and proportionate approach to using existing workforce data to get a clearer picture of the turnover problem, without requiring new systems or significant investment.

Action

I reviewed what data was already available within the organisation, including employee tenure, absence records, shift patterns, and supervisor assignments, and mapped out how these variables could be used to identify where turnover risk was concentrated across roles, locations, and management structures. I also set out a framework for responsible data use, drawing on data protection principles to ensure staff information was handled transparently and used to improve working conditions rather than to surveil or monitor individuals. I was clear throughout that data points to where to look, not to final answers, and that manager insight and direct employee voice needed to sit alongside any quantitative analysis to ensure findings reflected operational reality rather than artefacts in the data. I recommended exit interview data and pulse surveys as low-cost tools to surface the human story behind the numbers and give employees a meaningful channel to contribute to the diagnosis.

Result

The consultancy brief produced a roadmap to move from reacting to turnover to anticipating it, with a realistic step-by-step approach grounded in the organisation's current capability and focused on building the analytical foundations needed to address a persistent and costly people problem.

Practical Analysis

ATTENTION: The employee data presented in this workbook is entirely simulated, with all names, sites, and personal details fictionalised in compliance with UK GDPR principles, which require the protection of personal data from unnecessary disclosure. While the theoretical foundations of HR analytics were developed through my studies in the MA in HRM, the practical Excel skills demonstrated here were self-taught through independent study, YouTube tutorials, and consistent hands-on practice over time, driven by a genuine interest in bridging HR theory with practical people data capability, supporting ethics, integrity, confidentiality, and confidence in more evidence-based decision-making.

The analysis took place within the tabs:

Tab 1 — Raw Employee Data

The fields included are Employee ID, Full Name, Role, Site, Tenure in months, Shift Pattern, Supervisor, Absence Days, Overtime Hours, Exit Date, Exit Reason, and a Turnover Flag. The Turnover Flag is a derived binary field, marked 1 for employees who left and left blank for those who remained, which is used across all subsequent tabs as the key filter for calculating turnover metrics using COUNTIF and AVERAGEIF formulas.

Employees highlighted in red in the Turnover Flag column are confirmed leavers. The absence of an Exit Date or Exit Reason for the remaining employees indicates they are still active. This distinction is the basis for all risk and retention analysis that follows.

Tab 2 — Turnover Dashboard

This tab acts as a scoreboard, using four key formulas to automatically calculate turnover insights. COUNTA counts the total number of employees in the dataset. COUNTIF identifies how many left by filtering the Turnover Flag column, and calculates how many cited each exit reason. COUNTIFS cross-references two conditions at once, filtering by site or role and turnover flag simultaneously, to produce the breakdowns in Sections B and C. AVERAGEIF calculates the average tenure, absence days, and overtime hours specifically for leavers, revealing that those who left had an average tenure of just 4.1 months and notably higher absence and overtime levels, pointing to workload as a contributing factor alongside the 94% of leavers who cited better pay, poor management, or lack of support as their reason for leaving.

Tab 3 — Turnover Risk Indicator Matrix

This tab scores each leaver across three risk factors using nested IF formulas. An IF formula works like a question with a yes or no answer: is this employee's tenure less than 6 months? If yes, add 1 point. If no, add 0. The same logic is applied to absence over 7 days and overtime over 15 hours, and the three results are added together automatically to produce the Risk Score for each employee.

A second IF formula then reads that score and assigns the RAG rating: if the score is 3, label it HIGH; if it is between 1 and 2, label it MEDIUM; if it is 0, label it LOW. The colour coding makes patterns immediately visible, showing that the majority of leavers scored HIGH. Notably, Ricardo Alves and Lucas Martins appear repeatedly as supervisors across high-risk employees, suggesting that supervisor assignment may be a significant contributing factor worth investigating further.

Tab 4 - Data Audit Log

This tab scores each leaver across three risk factors using nested IF formulas. An IF formula works like a question with a yes or no answer: is this employee's tenure less than 6 months? If yes, add 1 point. If no, add 0. The same logic is applied to absence over 7 days and overtime over 15 hours, and the three results are added together automatically to produce the Risk Score for each employee.

A second IF formula then reads that score and assigns the RAG rating: if the score is 3, label it HIGH; if it is between 1 and 2, label it MEDIUM; if it is 0, label it LOW. The colour coding makes patterns immediately visible, showing that the majority of leavers scored HIGH. Notably, Ricardo Alves and Lucas Martins appear repeatedly as supervisors across high-risk employees, suggesting that supervisor assignment may be a significant contributing factor worth investigating further.

Tab 5 — HR Analytics Maturity Tracker used in the Business Report

This tab maps NetPark's current analytics capability against the four levels of the Bersin Talent Analytics Maturity Model across six capability areas: data collection, reporting, HR role, tools used, decision making, and turnover approach. It provides a clear visual reference for where the organisation currently sits and what each level requires to progress further.

The assessment confirms NetPark is operating at Level 1 across all six areas, relying on manual spreadsheets, ad hoc reporting, and reactive decision making with no structured approach to turnover analysis. This diagnosis directly informed the recommendation to move to Level 2 through Predictive Turnover Analytics, a proportionate and achievable first step beyond basic descriptive reporting that does not require the technical infrastructure or cultural readiness of the more advanced levels.

Final Analysis & Recommendation

The data across all five tabs points consistently to the same conclusion: NetPark's 70% frontline turnover rate is not a coincidence or a collection of individual decisions, but a systemic problem with identifiable patterns.

The Risk Indicator Matrix shows that the majority of leavers scored HIGH across all three risk factors, with short tenure, high absence, and excessive overtime appearing together repeatedly.

The Dashboard confirms that Cleaning Staff experienced 100% turnover and that Site D had the highest attrition rate at 80%, while exit reason data shows that 94% of departures were driven by better pay elsewhere, poor management, or lack of support.

Critically, the same supervisors appear across multiple high-risk employees, suggesting that leadership quality and management capability at site level is a significant and underaddressed driver of exits.

Based on this analysis, the priority recommendation is not simply to improve pay, but to investigate supervisor performance at the highest-risk sites, review workload distribution to address the overtime and absence patterns, and implement structured onboarding and career support to extend tenure beyond the critical first six months.

These interventions, combined with a move from Level 1 to Level 2 analytics maturity, would give HR the visibility needed to shift from reacting to turnover to anticipating and preventing it.