Thousands of rows of benefits data, and a dashboard that reads them for you

A while back, the HR director at a Georgia restaurant group came to me with a problem she had every month and no good way to solve. To see what the company was spending on employee benefits — and to answer ordinary questions like which locations were carrying which coverage — she had to open a report exported straight from the payroll system. Thousands of rows. Every employee, every plan, every line of coverage, in whatever order the system decided to hand them over. The answer she needed was in there. It just wasn’t in a shape a human being could read.

Here’s the part that surprises people: the export didn’t actually contain the answers. It contained the ingredients. A payroll system exports what’s easy for the system to produce, not what’s useful for a person to read, and the gap between those two things is where the real work lives. Closing it took two steps, and only one of them is the part most people picture.

The first step is the one nobody sees coming. The report doesn’t tell you who is eligible for what — it tells you hire dates and a column of classification codes. So the first thing the spreadsheet does is derive two facts about each person: how long they’ve been with the company, and which eligibility class they fall into — manager, salaried, hourly, and so on — decoded from those codes. Those two facts together determine which plans a person can actually be on. That’s not data cleanup; it’s business logic. The source data holds the codes. The spreadsheet holds the rules that turn codes into eligibility, so the right person lands in the right category without anyone deciding it by hand.

The second step is the money. With eligibility settled, the spreadsheet brings in a second dataset — the carrier’s plans, costs, and coverage values — and matches each person to what they’re enrolled in and what it costs. Then it rolls everything up into the dashboard, which is the part she actually opens. Each location’s spend is laid out by type of coverage — health, life, accidental death — and by who’s covered, from employee-only up through employee and spouse, employee and child, and full family, in both coverage volume and premium dollars. The thing she used to hunt for across thousands of rows is now a single view she reads in a few seconds: this location, this coverage, this much.

I later built her a second tool on the same bones, for paid time off. It takes the same raw export, works out from each person’s tenure and title how many vacation and sick days they’re eligible for, and then costs that out per location on the assumption that everyone uses every day they’re owed — a worst-case number a manager can actually plan a budget around. Same move as the first one: raw codes in, a decision-ready number out.

I’ll tell you the part that means the most to me, because it’s the only review of this kind of work that counts. She still uses it. Years later, several times a month, without anyone prompting her. Plenty of spreadsheets get built, used twice, and abandoned because they were really just a snapshot dressed up as a tool. The test of a real one is whether someone still opens it long after you’ve stopped thinking about it — and whether it still gives them the answer when they do. This one does, which tells me the logic underneath was built right, not just built quickly.

A word on the data, since this is exactly the kind most people are right to be careful with: everything here runs on payroll and benefits records, so the specifics stay out. You’ll notice this piece names no company, no employee, and not a single real figure — that’s deliberate, and it’s how we handle work like this whether or not it ever becomes a story. The method is worth sharing. The data isn’t ours to share.

If you run a business and the answer to “what are we actually spending, and where” lives inside a system export you dread opening — a payroll dump, a point-of-sale report, a benefits file — the fix isn’t to read it more carefully or to dread it more efficiently. It’s to build a layer that sits on top and reads it for you, the same way every time. You work out the logic once. After that, the messy report just feeds it, and the answer comes out the other end in a shape you can use.

The numbers were all there the whole time. They just weren’t in a shape a person could use. Turning a system’s idea of a report into a human’s idea of an answer — that’s most of what this work actually is.