# The HR Director: companion files

Julian R. Sterling

## What is here

- HRD_1_Headcount_and_Turnover.xlsx (Chapters 2 and 12; Headcount and Turnover workbook of Appendix A): sheets Inputs, Positions, Flow, Turnover, then Checks. Authorized positions against filled positions month by month, the headcount bridge by site and year (513 employees on 1 January 2024, 507 on 31 December 2025), average headcount and turnover by group (44.1 then 50.2 percent for production operators).
- HRD_2_Labor_Cost.xlsx (Chapters 3 and 8; Labor Cost workbook): sheets Inputs, Total Labor Cost, Load Factor, Overtime, then Checks. The five components of total labor cost by group ($38,317,157 then $40,976,857 for the company), the load factor of each hourly group (1.1185 for production), and overtime hours and cost by month.
- HRD_3_Pay_Structure_and_Merit.xlsx (Chapters 6 and 7; Pay Structure and Merit workbook): sheets Inputs, Structure, Compa-Ratio, Merit Matrix, then Checks. Grades and ranges, compa-ratios by group (0.89 then 0.92 for production operators), the merit matrix and the merit budgets.
- HRD_4_Benefits.xlsx (Chapters 9, 10 and 11; Benefits workbook): sheets Inputs, Tiers, Renewal, Funding, Match, then Checks. Medical tiers, the 2025 renewal, the employee contribution, full insurance ($8,473,511 quoted for 2026) against self-funding with stop-loss ($7,653,192 expected, $9,394,692 in the worst case), and the 401(k) match by group.
- HRD_5_Hiring.xlsx (Chapter 13; Hiring workbook): sheets Inputs, Funnel, Days to Fill, Cost per Hire, then Checks. The funnel, hires by group and site, days to fill, offer acceptance and cost per hire ($2,239.87 then $2,391.80).
- HRD_6_Decisions.xlsx (Chapters 10, 14 and 16; Decisions workbook): sheets Inputs, Plant Options, Health Funding, then Checks. Option A, a market adjustment of production rates ($3,377,730 over 2026 and 2027), option B, retention bonuses ($3,519,954), option C, the status quo with overtime ($3,523,990), and the health funding comparison. All projections, never results.
- HRD_HR_Director_Checklists.docx: the 8 check-lists of Appendix B, editable (the first 90 days of an HR director, reading the numbers; the headcount plan and the labor cost budget; the annual pay review; the benefits renewal; hiring one position; an employee's departure and the exit interview; annual compliance (conditional, counsel decides); the monthly dashboard to the executive team).

## How to use the workbooks

Each workbook opens on an Inputs sheet. Every count, rate, premium and cost of the case is typed there once, in blue on a light blue fill, under a short Conventions block; the other sheets only read from it. Black type is a formula, green type a link to another sheet. Every division returns a blank and a flag, never an error, when its divisor is zero or empty, and the calculated cells are protected against overtyping, with no password. Rebuild the case first: the Checks sheet of each workbook puts the live cells beside the figures printed in the book and reads ALL CHECKS OK; below the comparisons it counts the flags Appendix A names. Each of the 12 exercises of Appendix A is done in the workbook it names.

The arithmetic is plain: add, subtract, multiply, divide and round. Turnover divides by the average headcount rounded to one decimal, as the book prints it.

Two inputs to try first:

1. In HRD_1_Headcount_and_Turnover.xlsx, on Inputs, add one departure to production operators in any month of 2025: the turnover rate moves, and the Flow sheet flags the bridge until the month-end headcount is corrected.
2. In HRD_6_Decisions.xlsx, on Inputs, change the projected turnover of option A: departures, replacement cost, the two-year cost and the comparison with option C move at once.

To use a workbook on your own company, replace the blue cells of the Inputs sheet with your payroll register, headcount reports, plan documents and quotes, and keep a copy with the case for comparison. Date every input. The workbooks are separate files with no link between them: when the Decisions workbook needs the expected cost of self-funding, copy the figure across from the Benefits workbook.

## A word of caution

Corlethby Building Products, Ashwickham, Torbelling, Quenridge, the company's officers and staff, and Vendrassel Benefits Group are fictional. Pay rates, salaries, premiums, contribution rates, fees and costs are teaching assumptions, not market norms, targets or recommendations, and nothing here promises lower turnover or lower costs. Where state law sets a rule, ask counsel. No real insurer, broker, consulting firm, survey publisher, staffing agency or software is described or recommended. Nothing here is legal, tax or benefits advice.
