CASE STUDY
Home care payroll calculation system
Care worker pay is calculated from service item codes and actual service hours, with bulk import for service records, replacing a hand-maintained Excel workbook. The rules live in the system, so every calculation uses the same basis and settlement no longer means checking each worker by hand.
RESULTS
What this project changed
- -80%
- Payroll settlement time
- 0
- Manual calculation errors
The problem
Pay in home care is complex: different service item codes carry different payment rates, and time of day, hours and travel all have to be taken into account. The old method was to copy service records into Excel and apply formulas maintained by hand.
- There were so many service records that transcribing them into the workbook took up most of the settlement period.
- The formulas were spread across several Excel files, and editing one did not update the others.
- A single wrong code meant recalculating a whole batch, and the error usually surfaced after payday.
What we did
We moved the payment rules out of Excel and into the system, so service records arrive as data that can be calculated.
- Service item codes and payment rules are held as a lookup table maintained in the back office, so a rule change needs no formula editing.
- Service records can be imported in bulk, with codes, hours and required fields validated at import and errors flagged straight away.
- The system calculates each care worker's pay from codes and hours and produces a statement that can be checked.
- Every result keeps its basis, so a supervisor can expand any line and see how the amount was reached.
Results
Payroll settlement changed from a heavy monthly push into a predictable routine.
- Payroll settlement time fell by about 80%, freeing administrative staff for work related to care quality.
- Calculation is done by the system alone, and manual calculation errors dropped to zero.
- Bulk import with validation catches data problems before they reach the calculation.
CONSTRAINTS
Constraints
The conditions that were fixed from the start. The trade-offs behind the approach only make sense once you know them.
Payment rates in home care are set externally, and the system can only calculate to those rules. That was the largest condition on the whole project.
- Service item codes and payment rates follow the rules set by the authorities and by the organization, and could not be simplified to make development easier.
- Those rules change, so they had to become settings the organization maintains itself rather than values written into code and waiting for a release.
- Pay affects how much care workers trust the organization, so every amount has to expand line by line to show how it was calculated, not just a total.
- Existing service records were never going to be reorganized for a new system, so the system had to accept them in their original file format.
HOW IT WORKS
Technology and integrations
What the system connects to, how the data moves and why those choices were made — written so that no technical background is needed.
We moved the formulas out of Excel and into the system, so a service record is ready to calculate the moment it arrives.
- Codes and payment rules are stored as a lookup maintained in the back office, so a rule change means editing settings, not the program.
- Codes, hours and required fields are validated at the moment of import, so data problems are caught there rather than after the calculation.
- Every pay line keeps its basis and can be expanded by a supervisor, so checking never means going back to the original records.