How a Restoration Company Replaced Color-Coded Spreadsheets With One Automated Bonus Plan

Two Regional Offices, One Color-Coded Spreadsheet Nightmare

Picture a restoration company handling water, fire, and mold jobs across two regional markets within the same state. Real crews, real gross profit margins to track, and a quarterly bonus structure that on paper looked perfectly reasonable: project manager bonuses tied to gross profit margin, and inspector spot bonuses for specific behaviors worth rewarding, weekend inspections, fast emergency-call response, same-day collections.

In practice, all of it lived in a spreadsheet. Not one spreadsheet, either. Each of the two regional offices kept its own version, color-coded by hand to track which technicians qualified for which bonus tier that quarter. The color-coding was not a minor stylistic choice. It was the actual system of record for who got paid what, and it was built and maintained separately by each office with its own conventions for what a given color or tab meant.

That kind of manual, regionally-fragmented process works, in the sense that people eventually get paid, right up until it does not. Two spreadsheets with inconsistent formatting cannot be automatically reconciled, compared, or audited against each other. Every quarter closeout meant someone manually re-deriving which technicians in which office hit which tier, cross-checking gross profit numbers by hand, and hoping nothing had shifted between when a job was color-coded and when the bonus actually got calculated.

It is worth being clear about why the spreadsheets existed in the first place. Nobody set out to build a fragile system. A single spreadsheet per office is usually the fastest way to get an incentive plan running at all, and for a while it works fine because volume is low enough and staff turnover is slow enough that the same one or two people who built the original tabs are still the ones closing out every quarter. The system does not fail because the initial design was careless. It fails because it was never rebuilt as the company grew past the point where informal, tribal-knowledge processes can keep up with two offices, several technicians per office, and a bonus structure with enough tiers and exceptions that no one person can hold the whole thing in their head anymore.

What a Manual Process Actually Costs You

The real cost of a system like that rarely shows up as an obvious dollar figure. It shows up as risk sitting quietly in the background until the day it does not. For this company, that day arrived when a national account, a job that should have been excluded from the local office's bonus pool entirely under the company's own rules, got miscounted into a regional bonus calculation. It was caught before anyone got paid incorrectly, but only because someone happened to double check the numbers that particular quarter. There was no structural reason it had to be caught. A slightly busier closeout, a slightly less careful review, and a technician could have been paid a bonus off revenue that was never supposed to count toward it in the first place, or a technician who should have qualified could have been shorted.

That is the quiet failure mode of almost every manual incentive process, in restoration or in any trade running multi-location incentive pay by hand. It is not that the spreadsheet is wrong every quarter. It is that nothing in the process actually prevents it from being wrong, and the only safeguard is a person's attention on a given afternoon. A system built on individual vigilance instead of structural rules will eventually have a bad quarter, and the company will not know which quarter that is until well after the damage is done.

Building One System Out of Two Regional Data Sources

The rebuild's first job was unglamorous but essential: pulling both regional offices' data into a single automated system instead of two independently maintained spreadsheets. That alone eliminates the core problem, two sources of truth that could drift apart from each other without anyone noticing until closeout. With one system, a job like the national account that should be excluded from local bonus pools can be flagged and excluded by a consistent rule applied everywhere, not by whichever office happens to remember the exclusion that quarter.

On top of the unified data, the company kept the incentive structure it already believed in, tiered project manager bonuses by gross profit margin, starting around $75 at the $2,500 to $5,000 margin band and scaling upward from there, plus the existing inspector spot bonuses: $75 for a Sunday inspection, $50 for an emergency-call check, $50 for a same-day collection. None of that logic needed to change. What needed to change was where it lived and how reliably it got calculated.

Separate Plans for Lead and Senior Technicians

The rebuild also separated lead technician and senior technician incentive plans, which had previously been blended together in ways that made the color-coded spreadsheets even harder to maintain. A lead technician and a senior technician are not doing the same job, and paying them off the same undifferentiated bonus structure tends to under-reward the person actually running point on a job while over-rewarding tenure that is not necessarily reflected in that quarter's output. Splitting the two roles into distinct, clearly defined plans meant each group's bonus now reflects what that specific role is actually responsible for on a job site, not a generic blended average.

Every plan, for project managers, inspectors, lead technicians, and senior technicians, is now reviewable and exportable directly to payroll, with a per-technician drill-down that shows exactly which jobs, margins, and spot-bonus triggers fed into that person's number for the quarter. That drill-down is the piece that a color-coded spreadsheet could never really provide. Color tells you a technician qualified. It does not tell you, at a glance, which three jobs and which margin calculations got them there, which is exactly the kind of question that comes up the moment a technician asks why their bonus looks different from what they expected.

Two Quarters In: What Automation Actually Changed

The new system has now run two consecutive live quarters, Q1 and Q2 of 2026, with bonus calculation and payout finalization fully automated across both regional markets. That is the headline result, and it is worth being direct about what kind of result it is. This is not a story about a dramatic revenue jump or a specific dollar figure the company saved. It is a reliability story: two full quarters where the process that used to depend on careful manual cross-checking across two independently color-coded spreadsheets instead ran on consistent, auditable rules applied the same way in both offices.

The system already proved that reliability matters in a concrete way. During the automated process, it caught a misclassified national-account job before it could incorrectly trigger a local bonus payout, the same category of error that had previously slipped through under the manual process and only gotten caught by luck. This time it got caught by the structure of the system itself, not by whether someone happened to be paying extra-close attention that particular week.

That is the real argument for automating a multi-location bonus process even when the underlying incentive logic, the margin tiers, the spot bonuses, is not changing at all. The value is not always a bigger number. Sometimes it is the same numbers, calculated the same correct way, every single quarter, without depending on any one person's memory of which color meant what in which office. For a restoration or construction company running more than one location on a bonus plan held together by spreadsheets and institutional knowledge, that kind of quiet, structural reliability is often worth more than it looks like on paper, especially the first time it quietly catches a mistake before a technician ever sees the wrong number in their paycheck.

If your own multi-location incentive plan still lives in a spreadsheet, color-coded or otherwise, the question worth asking is not whether the plan's logic is fair. It might be. The question is whether that logic gets applied the same way, every time, regardless of which office is running it and how careful the person closing out that quarter happens to be.

The Warning Signs a Manual Bonus Process Is About to Fail

A few patterns tend to show up right before a manual, spreadsheet-driven incentive process produces a real error, and they are worth watching for in any multi-location trades business. One is exactly what happened here: an exception case, a national account, a one-off job type, a special pricing arrangement, that the spreadsheet was never actually built to handle, and that depends on someone remembering to manually carve it out every single quarter. The more offices and the more technicians a plan covers, the less realistic it becomes to trust that memory indefinitely.

A second warning sign is formatting drift between locations. The moment two offices doing the same job start keeping their own versions of the same spreadsheet, with their own color conventions and their own tab structures, you no longer have one incentive plan. You have two plans that happen to share the same intended logic, run by different people, with no structural way to confirm they actually agree. That drift is often invisible until a technician transfers between offices, or a company decides to compare performance across locations, and the numbers simply do not reconcile.

The third sign is the absence of a drill-down. If a technician asks why their bonus was a specific number this quarter and the honest answer requires someone to reconstruct the calculation by hand from a color-coded tab, the plan has already outgrown the tool it is running on. A trustworthy incentive plan should be able to show its work automatically, for every technician, every quarter, without anyone needing to remember how a particular spreadsheet was built eighteen months ago.

Consistency Is the Actual Product

None of the incentive logic in this story is unusual. Gross-profit-margin tiers for project managers and spot bonuses for inspectors are common structures across the restoration industry and beyond. What this company actually bought by automating the process was not a smarter bonus formula. It was the guarantee that the formula it already believed in would be applied identically, every quarter, in both offices, with a record showing exactly how each technician's number was calculated. For a multi-location business built on trust between the office and the field, that consistency is very often the entire product.