Workforce planning report · case-study data

Replacement Hiring & Attrition Scenarios at OptiGrowth Enterprises

How many roles each department will need to refill if attrition holds, where that demand comes from, and what a realistic improvement in retention would change.

Organisation: OptiGrowth Enterprises (case study)Population: 300 employeesTools: Excel · Power BI · DAX

Executive summary

  • 58roles to refill at current rates
  • 19.3%attrition across the workforce
  • $4.26Msalary attached to leavers
  • 12hires avoided at a 20% reduction

One in five employees left. Demand for replacement hiring sits in Sales, Logistics and Operations, and in coordinator, analyst and manager roles. Long hours are common everywhere, including the lowest-attrition team, so workload alone does not explain who leaves.

Recommendation: plan capacity for about 58 hires, target retention at the three highest-attrition roles, and set a 20% retention goal, which would cut the hiring load to 46.

01

Business context

Leadership needed to size next year's recruitment effort and asked three questions:

  • How many roles will need refilling if current attrition continues?
  • Which departments and roles drive that demand?
  • How much would a realistic improvement in retention reduce the hiring load and the salary at stake?
02

Data and preparation

The source is a single Excel sheet with one row per employee.

FieldTypeUse in the analysis
Employee_IDWhole numberRow identifier
DepartmentText, 6 valuesMain breakdown
Job_RoleText, 5 valuesSecond breakdown
Salary ($)NumberSalary of leavers
Work_HoursWhole number, 30 to 60Workload indicator
Productivity_ScoreDecimal, 0.46 to 1.00Output indicator
Attrition_Flag0 or 11 means the employee left

Checks before modelling

  • No blank values in any field.
  • No duplicate Employee_ID values, so each row is one person.
  • Ranges were plausible: hours 30 to 60, productivity 0 to 1, salaries $30,136 to $210,848.

The sheet loaded cleanly, so the only modelling step was renaming the table to Workforce so the measures read clearly. The data has no dates, which limits what can be claimed (section 06).

03

DAX measures explained

Every figure on the dashboard comes from an explicit measure, so each number has one definition that anyone can check.

Headcount

Headcount = COUNTROWS(Workforce)

Counts rows. Each row is one employee, so this returns headcount for whatever the visual is filtered to: a department, a role or the whole company.

Leavers

Leavers =
CALCULATE(COUNTROWS(Workforce), Workforce[Attrition_Flag] = 1) + 0

CALCULATE adds a filter for employees flagged as leavers. The + 0 shows zero rather than a blank for any group with no leavers.

Attrition Rate

Attrition Rate = DIVIDE([Leavers], [Headcount])

DIVIDE returns blank instead of an error if headcount is zero, which a plain / would not. Formatted as a percentage to one decimal place.

Avg Weekly Hours and Share Over 45 Hours

Avg Weekly Hours = AVERAGE(Workforce[Work_Hours])

Share Over 45 Hours =
DIVIDE(
    CALCULATE(COUNTROWS(Workforce), Workforce[Work_Hours] > 45),
    [Headcount]
)

An average hides the spread, so the share of people above 45 hours shows how widespread long weeks are.

Avg Productivity

Avg Productivity = AVERAGE(Workforce[Productivity_Score])

Placed next to attrition and hours so a reader can see whether high-attrition teams are also lower-output teams.

Salary of Leavers

Salary Base of Leavers =
CALCULATE(SUM(Workforce[Salary ($)]), Workforce[Attrition_Flag] = 1) + 0

The annual salary attached to vacated roles. It shows what is at stake. It is not a turnover cost.

04

The scenario slider

A numeric what-if parameter, Attrition Reduction Pct, runs from 0 to 50 in steps of 5. Power BI creates a one-column table and a measure that returns the slider position. Three measures respond to it.

Hires Needed (Scenario)

Hires Needed (Scenario) =
ROUND(
    [Leavers] * (1 - 'Attrition Reduction Pct'[Attrition Reduction Pct Value] / 100),
    0
)

Leavers reduced by the chosen percentage and rounded to whole people, because a plan cannot hire part of a person.

Hires Avoided

Hires Avoided = [Leavers] - [Hires Needed (Scenario)]

Calculated from the rounded figure so the two cards always add back to the leaver total.

Salary Retained (Scenario)

Salary Base Retained (Scenario) =
[Salary Base of Leavers] * 'Attrition Reduction Pct'[Attrition Reduction Pct Value] / 100

The salary attached to the departures the scenario avoids.

What the slider assumes

  • One-for-one replacementEvery leaver is replaced. In practice some roles would be removed or merged.
  • Average salaryAvoided leavers are assumed to earn the average leaver salary. Retaining higher-paid roles would raise the figure.
  • Salary, not savingsSalary retained is not money saved. The saving is avoided recruitment, onboarding and lost output, which this data cannot measure.
  • Even improvementThe reduction applies equally to every department and role, although targeted retention would not work that way.
05

Dashboard and findings

OptiGrowth workforce planning dashboard in Power BI with KPI cards, attrition by department and job role, headcount and leavers by department, an attrition reduction slider and a department summary table.
The Power BI dashboard with the scenario slider set to 20%. The top row answers "how big is the problem", the middle row "where is it", and the bottom row "what happens if we act".
DepartmentStaffLeftAttritionAvg hrsOver 45 hrsProductivity
Sales501122.0%45.552%71.9%
Logistics521121.2%46.252%72.7%
Operations491020.4%45.551%76.5%
HR551120.0%46.960%75.6%
IT47919.1%45.549%72.1%
Marketing47612.8%46.755%72.4%
Total3005819.3%46.153%73.6%

Five of six departments lose around one in five people. Marketing is the exception, yet its staff work hours as long as anyone's.

  • By role, coordinators (23.2%), analysts (22.4%) and managers (22.4%) leave far more often than engineers (14.0%) and executives (12.2%).
  • 53% of staff work over 45 hours a week, rising to 60% in HR.
  • Productivity varies only modestly between departments, from 71.9% to 76.5%, and does not track attrition.
06

Limitations

  • No dates. The data is a single snapshot, so 19.3% is the share of this population that left, not an annual rate. Treating it as a one-year rate is a working assumption to replace with dated leaver data before a budget is set.
  • Correlation, not cause. Hours, role and attrition are shown side by side. The analysis does not show that any one causes another.
  • Case-study data. Figures describe a fictional company and are not results from an employer.
07

Recommendations

  1. Plan recruitment capacity for about 58 replacement hires at current rates, weighted towards Sales, Logistics and Operations.
  2. Focus retention on coordinator, analyst and manager roles. Start with stay interviews and a review of progression routes, since these roles carry the highest attrition.
  3. Review workload in HR, where 60% of staff exceed 45 hours a week, before it appears as attrition.
  4. Set a retention target with the scenario. A 20% reduction leaves 46 hires instead of 58 and keeps about $852K of salary in roles that stay filled.
  5. Add hire and exit dates to the data so attrition can be measured per year and tracked monthly on the same dashboard.
08

How to reproduce it

Download the Power BI file (OptiGrowth_Workforce_Planning.pbix). It opens in Power BI Desktop with the data model and every measure described here.

  1. Load the Excel sheet into Power BI and rename the table Workforce.
  2. Create the measures in sections 03 and 04 and set formats: whole numbers, percentages to one decimal place, dollars with no decimals.
  3. Add the parameter from Modeling > New parameter > Numeric range: 0 to 50, increment 5, default 0.
  4. Build the visuals in three rows as described in section 05, with one accent colour.
Prepared by Olajumoke O. Medunoye