Excel 2016+ workbook for HR and delivery reporting on a fixed roster of 3,499 employees. Eight task sheets cover validation, lookups, headcount and salary summaries, a pivot view, period staffing counts, location KPIs, and a two-year project P&L. A second file lists the task brief and checks you can use when reviewing results.
| File | Use |
|---|---|
| workforce-analytics-excel.xlsx | Main workbook (Tasks 1–8) — download |
| problem-statement-and-analysis-metrics.xlsx | Task wording and expected outputs — download |
| Releases | Same .xlsx files packaged by version |
GitHub does not open .xlsx in the browser; download the file or use a release asset.
- Download
workforce-analytics-excel.xlsx(or the latest release). - Open in Excel 2016 or later (Windows or Mac).
- Press Ctrl + Alt + F9 for a full recalculation.
- Work through Instruction and score and Task 1 through Task 8. Use the metrics file to confirm figures where needed.
| Sheet | Contents |
|---|---|
| Instruction and score | Task weights; score cells for Tasks 1–8 |
| Raw Data | Source data (rows 2–3500); Tasks 1–3 (dropdowns in M2/N2, column K, conditional format on column C) |
| Task 1-3 | Task instructions (logic lives on Raw Data) |
| Task 4 | INDEX/MATCH lookups for a fixed employee list |
| Task 5 | Headcount by location and seniority; average salary growth by location |
| Task 6 | PivotTable WorkforcePivot (H4) and a formula grid for the same breakdown |
| Task 7 | Half-year headcount by location; specialization count, average days employed, terminations |
| Task 8 | Hourly cost and bill rates (Kyiv, Munich); two-year P&L |
| _lists | Lists for dependent validation in N2 |
| _spec_helper | Helper calculations for Task 7 distinct counts |
| Column | Field |
|---|---|
| A | ID |
| B | Location |
| C | Name, Surname |
| D | Role |
| E | Seniority (Intern, Junior, Regular, Lead, Senior) |
| F | Specialization |
| G | Hire Date |
| H | Termination date |
| I | Start salary |
| J | Current salary |
Columns K–N are used for Tasks 1–3. Locations in the file are global site names; summary tables on Tasks 5–7 use named locations (e.g. Munich, Kyiv, Detroit, Shanghai).
Tasks 1–3 (Raw Data)
- M2 / N2: List validation on
SeniorityorLocation; N2 uses=INDIRECT($M$2)with lists on_lists. - Column K: Name if the employee is active (no termination, or termination after today) and salary growth is under 26%; otherwise Surname, parsed from column C.
- Column C format: Red fill when
($J-$I)/$I > 25%(rule onC2:C3500).
Task 4
Lookups on Name, Surname (column B) against Raw Data column C:
=IFERROR(INDEX('Raw Data'!$B$2:$B$3500, MATCH($B4,'Raw Data'!$C$2:$C$3500,0)),"")
(Same pattern for specialization and termination date.)
Task 5
- Headcount:
SUMPRODUCTwithSEARCHon location text and a match on seniority. - Growth: average
(Current − Start) / Startby location.
Task 6
PivotTable on Raw Data!A1:J3500 (rows = Location, columns = Seniority, values = count of ID). Formula table at B5:F9 is a cross-check, not a substitute for the pivot.
Task 7
Half-year counts use period start/end dates on the sheet. Location KPIs use COUNTIFS, date arithmetic, and helper columns where a distinct count is required.
Task 8
- Cost per hour:
(gross salary × (1 + payroll tax) + monthly costs) / hours per month(168 Kyiv, 172 Munich). - Bill rate:
cost / (1 − margin)(30% Kyiv, 40% Munich). - P&L: two-year revenue, pass-through, cost, and tax lines; EBIT and net income on the sheet.
Use INDEX/MATCH, COUNTIFS, SUMIFS, AVERAGEIFS, SUMPRODUCT, and IFERROR. Do not rely on XLOOKUP, dynamic arrays, FILTER, or UNIQUE if the file must run on Office 2016.
- Headcount and seniority mix by site for staffing plans
- Salary change review (growth flags and averages by location)
- Roster lookups for audits or ad hoc lists
- Period staffing for capacity (half-year buckets)
- Site KPIs (tenure, terminations, specialization spread)
- Internal cost and bill rates; simple project P&L for bid or delivery review
| Area | Editable |
|---|---|
| Raw Data M2, N2 | Dropdown inputs |
| Task 8 assumption cells | Salaries, costs, hours, tax, margin, P&L inputs |
| Formula cells, Raw Data A–J body | Do not change (Tasks 4–7 constraint) |
After changing inputs, recalculate with Ctrl + Alt + F9.
| Issue | What to check |
|---|---|
#N/A / #REF! |
Raw Data ranges still 2:3500; full recalc |
| Pivot or tables stale | Refresh pivot; confirm no rows inserted inside the data block |
| No red fill on names | Rule on Raw Data!C2:C3500, formula =($J2-$I2)/$I2>0.25 |
| P&L off | Task 8 input cells and location-specific rates |
Large files: set calculation to Manual under Formulas → Calculation Options, then recalc when finished editing.
| Version | Date | Notes |
|---|---|---|
| 1.0.1 | 2026-05-23 | Pivot on Task 6; README and release layout |
| 1.0.0 | 2026-05-08 | First GitHub release |
Updated 2026-05-23 · Repository · Releases