PROJECT 17Intermediate

HR Analytics: Attrition and Workforce

Business scenario

The HR director believes attrition in Sales and Customer Service is costing the company heavily and wants evidence before proposing a retention programme.

Problem statement

Measure headcount, attrition and recruitment performance, identify attrition drivers, and estimate the cost of turnover.

Learning objectives

Compute headcount and attrition rates correctly
Analyse drivers (rating, pay position, tenure, department)
Measure recruitment funnel and time-to-fill

Instructions

Annual attrition rate = leavers in year ÷ average headcount in year. Respect privacy: report aggregates only, suppress groups with fewer than 5 employees.

Tasks

Headcount at each year-end and average headcount.
Attrition by department, level, rating and tenure band.
Voluntary vs involuntary split.
Recruitment funnel by source and department.
Turnover cost estimate with stated assumptions.
Dashboard and summary.

Deliverables

SQL
Dashboard
Summary

Evaluation rubric

Criterion	Weight	Excellent	Proficient	Developing	Insufficient
Data preparation & validation	20%	Correct filters and grain; validation checks documented and totals reconciled.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Metric correctness	25%	Attrition and headcount calculated correctly.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Driver analysis & privacy	15%	Drivers analysed; small groups suppressed.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Interpretation & recommendations	25%	Clear, quantified conclusions tied to the business question, with limitations stated.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Presentation	15%	Concise, well-labelled outputs a manager can act on without explanation.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.