Stories Case study: The HR department of a Fortune 100 technology company
When paychecks depend on Excel formulas
A regulated payment's path from a spreadsheet to a dedicated system, with an audit trail and five times the throughput
The HR department of a Fortune 100 technology company calculated a regulated payment to employees in one spreadsheet. Fewer than five people used it, and every change in the regulation meant editing formulas by hand in a rush. Sometimes the only way to the right number was to override a cell.
I was the architect and back-end developer on the small team that replaced it with a web application. The rules were in one place, with an audit trail, an approval flow, and employee data pulled in instead of typed in. HR estimated they reviewed about five times more cases a day, and a regulation change stopped meaning hand-edited formulas.
- Users
- fewer than five people
- First version
- a few months, in 2020
- Throughput afterwards
- about five times more cases a day
The challenge
This client was a department inside a large company, not a small standalone business. The spreadsheet, though, had the exact characteristics I see at small companies: a handful of people around one file, with the rules being partly in formulas and partly in someone's head.
The calculation was complicated for good reasons. It had many inputs and many exceptions, and a regulation stood behind every one of them. When the regulation changed, the formulas had to change with it, by hand, before the next case. When the formulas couldn't express a case, someone overrode the value. Every one of those edits was a compliance risk, and a shared folder gave no way to tell who had changed what, or to stop someone from changing what they shouldn't.
What we did
- 1
Speed up the process that already worked
The process was fine. The tool had reached its limit. So the first version did exactly what the spreadsheet did, faster and safer, and nothing more. Employee data came from the central database instead of being typed in, and the people who used it told us what we got wrong before it cost anything.
- 2
Put the rules where they can be checked
The rules were in one place, handling exceptional cases, too. A regulation change became a dated rule, introduced ahead of time and applied from the day it took effect. The system refused overrides that weren't allowed and logged every change with who and when. A change could be reverted, and a four-eyes rule became an approval flow: one person enters the case, another approves it. Roles decided who could see or edit which fields.
- 3
More, once the basics worked
Once HR saw it worked, they wanted more. The system started sending email notifications from templates and generating the official documents that had been produced by hand. An Excel export stayed, because Excel is still the right tool for the analysis they did on the side. The first version took a few months. The extras, and finding out which extras mattered, took the rest of a year.
What changed
HR estimated they reviewed about five times more cases a day. The time went from typing data in to checking that the data was right.
Regulation changes were introduced ahead of time and automatically applied from the day they took effect, so nothing had to be rushed any more. Every number had a trail, with who entered it, who approved it and the previous value. That's a much easier thing to show an auditor than a spreadsheet in a shared folder.
What we learned from it
We only replace a process that already works. We sped this one up first, and added new things only once the transformed process ran correctly. The other way round, we would have automated the confusion.
We don't write software for things that rarely happen. Five minutes once a week isn't worth automating, unless a regulation says it must be traceable. Then it is.
In 2020 the first version took a few months because people wrote every line of code. Today AI does the typing and an engineer does the deciding, and the same first version takes weeks. What doesn't shrink is finding out what the department actually needs, which is why the first step is that we spend half a day with the people who do the work.
The department isn't named, and the calculation is described only as a regulated HR payment. On a call I can say more.
More stories
More for your business
Earlier work 2017
Check, please
Thousands of payment notices with pre-filled checks, so the back office stopped fixing payments
Thousands of payment notices went out with a blank check attached, and some payers filled it in wrong. What changed, what didn't, and the lesson.
ReadEarlier work 2017
Current affairs
Eight meters logged every ten seconds, and a weekly report that gives the final consumption per client without the Excel magic
A facility billed its users for electricity based on logs too large for anybody to effectively work with. What replaced the Excel afternoons, why trust came fast, and the lesson.
ReadEarlier work 2015
Lost in translation
A project management system built in full and approved at every demo, when a to-do list was the need
Built in full, approved at every demo, and wrong at the end. What the client asked for, what they needed, why nobody noticed, and what I do differently.
ReadDoes compliance depend on a spreadsheet?
Bring the file and the rules behind it. You leave with a straight answer on whether it's worth fixing, which may be no.