Blog · Board and management reporting
Build a human resources dashboard that compares staffing, turnover and workforce costs by department. A worked example reconciles employee movements, separates headcount from capacity and shows the spreadsheet formulas behind the totals.
A human resources dashboard compares staffing, turnover and workforce costs by department using the same reporting period. Start with a headcount reconciliation, then add capacity and cost measures whose denominators are explicit. Each exception should lead to a staffing question and an accountable manager.
The useful opening question is whether each department has the people and scheduled capacity needed for its workload. Put department rows beside opening headcount, hires, departures and closing headcount. Show workforce cost and scheduled capacity alongside them, with the period and extraction date above the table.
Separate the summary from the detail. The summary identifies departments needing attention; the supporting schedule explains their movements and costs. A gallery of charts cannot replace the staffing check, and the dashboard does not need to reproduce a complete retention study. The employee turnover dashboard owns the deeper comparison of departure cohorts and trends.
Assign HR the employee definitions, department managers the operating explanation, and finance the included cost categories. Record whether contractors, employees on leave and pending hires are inside each measure. A consistent boundary matters more than adding another indicator.
Bring opening and closing employee rosters with employee identifier, department and effective date. Add a movement export containing hires, departures and transfers. Use effective dates rather than the date somebody entered a change.
Bring a separate schedule summary and payroll cost export. Payroll often contains several earning lines for one person, so joining those lines directly to an employee roster can multiply headcount. Reconcile each file at its original level, then summarize each to department and period before combining the summaries.
Keep unassigned departments visible. A missing mapping is a reporting exception, not evidence that the employee or cost belongs outside the company. Use a separate unmapped row until HR and finance agree the assignment.
The workforce management reporting guide covers the hours and coverage review that supports these summaries. Keep its operating detail behind the department view.
A hypothetical US distributor reviews September 2026. Its employee perimeter excludes contractors. Transfers are movements between the departments shown, while departures leave the company.
Enter this table in columns A through G, with headings in row 1:
| A: Department | B: Opening | C: Hires | D: Departures | E: Transfers in | F: Transfers out | G: Closing |
|---|---|---|---|---|---|---|
| Operations | 80 | 5 | 3 | 2 | 0 | 84 |
| Sales | 40 | 4 | 2 | 0 | 2 | 40 |
| Corporate | 30 | 1 | 1 | 0 | 0 | 30 |
| Total | 150 | 10 | 6 | 2 | 2 | 154 |
In G2, use this Excel formula and fill through G4:
=B2+C2-D2+E2-F2
Sum each numeric column in row 5. Company closing headcount must satisfy 150 + 10 - 6 + 2 - 2 = 154 and agree with the closing roster. Transfers in and out cancel at company level. If the roster differs, find the missing movement before interpreting the dashboard.
For this internal report, period turnover uses departures divided by the average of opening and closing headcount. Add H for average headcount and I for turnover:
H2: =(B2+G2)/2
I2: =IF(H2=0,NA(),D2/H2)
| Department | Average headcount | Period turnover calculation | Period turnover |
|---|---|---|---|
| Operations | 82 | 3 / 82 | 3.7% |
| Sales | 40 | 2 / 40 | 5.0% |
| Corporate | 30 | 1 / 30 | 3.3% |
| Total | 152 | 6 / 152 | 3.9% |
Round displayed percentages to one decimal place; keep full precision in cells. The company rate divides total departures by total average headcount. Large movements within a month can make endpoint averages unrepresentative; use daily headcount averages when the decision needs that precision.
People and capacity answer different questions. The BEA definition of full-time equivalent employees converts part-time schedules to a full-time basis. For this company, the September standard is 176 scheduled hours per full-time equivalent, and scheduled hours already reflect time employed during the period.
The cost definition is September wages plus employer benefit costs, including paid leave and overtime pay. Overtime hours are excluded from scheduled capacity. This makes cost per scheduled FTE a cost comparison, not a productivity measure.
On a separate worksheet, use the following layout. Put 176 in H1.
| A: Department | B: Scheduled hours | C: Workforce cost (USD) | D: Scheduled FTE | E: Cost per scheduled FTE (USD) |
|---|---|---|---|---|
| Operations | 13,904 | 480,000 | 79 | 6,075.95 |
| Sales | 6,688 | 300,000 | 38 | 7,894.74 |
| Corporate | 5,104 | 210,000 | 29 | 7,241.38 |
| Total | 25,696 | 990,000 | 146 | 6,780.82 |
D2: =B2/$H$1
E2: =IF(D2=0,NA(),C2/D2)
D5: =SUM(B2:B4)/$H$1
E5: =IF(D5=0,NA(),SUM(C2:C4)/D5)
Fill the department formulas down. Round costs per FTE to cents. The checks are 25,696 / 176 = 146 scheduled FTE and 990,000 / 146 = $6,780.82 per scheduled FTE. Tie the cost total to the included payroll and benefit accounts.
For a larger cost export, suppose Payroll has department in A, period in B and included cost in C. With department in A2 and the actual period date in J1, aggregate using:
=SUMIFS(Payroll!$C$2:$C$10000,Payroll!$A$2:$A$10000,A2,Payroll!$B$2:$B$10000,$J$1)
Microsoft documents SUMIFS as summing values that meet multiple conditions. Ensure both period fields contain matching dates. Covirage is not affiliated with Microsoft.
Sales has the highest departure rate and highest cost per scheduled FTE in these rows. Those facts justify questions about role mix, vacancies, commissions and benefit allocation. They do not establish poor performance or prove that replacing people would lower costs.
Ask each department owner to explain the exception and identify the next check. HR can inspect movement reasons, finance can validate cost assignments, and the manager can compare capacity with demand. Use workforce KPIs for the separate output and hours measures needed to evaluate productivity.
Bring a sample of your own staffing, schedule and payroll exports; Covirage maps the columns, and its tools compute and check the department figures. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business; creating the account takes about a minute.
See capacity planning with Covirage for the supporting business context.
Include opening and closing headcount, hires, departures, internal transfers, scheduled capacity and workforce costs. Add the reporting period, definitions and department owner. Choose additional measures only when they support a specific staffing decision and have reliable source records.
Use a cadence that matches the decision and source availability. Monthly staffing and cost reviews can use closed payroll periods, while a hiring discussion may need more current vacancy information. State the extraction date and flag figures that remain provisional.
Department totals usually answer the management question without names. Keep the working employee identifiers needed for reconciliation within the appropriate review process. A small department may still reveal individual circumstances, so choose the audience and aggregation level deliberately.
Headcount counts people, while full-time equivalents convert schedules to a common full-time basis. Part-time schedules make the measures differ. Label whether capacity uses scheduled or worked hours, and use the same full-time hours standard across the departments being compared.