The trucks are booked solid. The bank account disagrees
True job cost: burdened labor, drive time, allocated overhead
Fourteen technicians, eleven trucks, a two county service area. Revenue was up and the money left at month end was not. The model now reports profit per job, and it turns out two job types were losing money on every single call.
FSM, payroll and accounting exports into Power Query, model in Power Pivot. One core measure: burdened labor including drive time, parts at cost, and overhead allocated per billable hour rather than spread evenly across jobs.
01How it worked before us
This company was not disorganized. They ran a real field service platform, dispatched properly, invoiced same day and collected well. The gap was between operational data and financial truth.
Before our work:
- Revenue per job was known, profit per job was not. The software reported what was invoiced. It did not net out the technician's fully burdened hours, the drive time, the parts pulled off the truck, or the overhead that job consumed.
- Every job type looked the same on the board. A $480 diagnostic and a $480 water heater swap sit next to each other in dispatch and read as equivalent. One carries 70% margin and the other barely covers the tech's time.
- Technicians were judged by revenue. Whoever had the highest ticket average was assumed to be the best. Nobody had checked callback rates or hours per job.
- Missed calls disappeared without a trace. Calls that rang out after hours were simply gone, with no number attached and therefore no reason to fix it.
- Marketing spend and job source were disconnected. They could say which channel produced the most calls, not which produced profitable work.
The data was all there and none of it met in one place. Three systems, three grains, no shared keys:
- The FSM reports invoices, not costs. Job records carry revenue, assigned tech and duration. Labor cost, parts cost and overhead are in two other systems.
- Payroll knows hours, not jobs. Hours arrive as timesheet totals per pay period, so tying a technician hour to the job it was spent on needs the dispatch log as the bridge.
- Drive time was recorded and never costed. The platform tracked it. No report multiplied it by anything.
- The hourly rate used everywhere was the pay stub rate. Payroll taxes, benefits, workers comp, the truck, fuel and the phone were absorbed into overhead and never landed on a job.
- Overhead was spread evenly per job. That single choice systematically flattered long jobs and punished quick high ticket calls, which is the opposite of the truth.
02What we built
No platform migration. They kept their FSM, their payroll provider and their accounting file. We took the exports those systems already produce and built a model on top that refreshes on one click.
What a job actually costs
The core of the build is one number: what the job consumed. Three components, none of which the field software reports on its own.
Not the $28 on the pay stub. Wages plus taxes, benefits, workers comp, the truck, fuel and the phone. And it starts when the truck starts, so drive time counts.
What was pulled off the truck for this job, at purchase cost rather than at the marked up line on the invoice.
Allocated by the hours the job consumed, not spread evenly across job count. This single choice reorders the whole ranking.
The screen the owner opens first
| Job type | Jobs | Avg ticket | Hours incl. drive | True cost | Profit / job | Margin | Total GP |
|---|
| Technician | Jobs | Revenue | Avg ticket | Gross profit | Margin | Callback rate | Memberships sold |
|---|
* Callback is a return visit on the same issue within 30 days.
| Lead source | Leads | Booked | Booking rate | Spend | Revenue | Gross profit | Return |
|---|
* Return is gross profit per dollar of marketing spend, not revenue per dollar.
The job type tab is where the conversation starts. Diagnostics and maintenance visits look thin on their own, and that is fine, because their job is to create replacement opportunities rather than to carry the company. What matters is whether they convert. Once the model links a diagnostic call to the replacement that followed it, a "low margin" job type often turns out to be the most valuable one in the business.
The technician tab changes how people are managed. Ranking techs by revenue rewards whoever gets sent to the biggest jobs. Ranking them by margin, callback rate and hours per job shows something different: the quiet tech with a lower ticket average who never goes back twice is frequently the most profitable person on the roster.
A number on the calls you never took
We pull the call log, match it against booked jobs, isolate calls that never became anything, and apply the company's own average job value and booking rate. The output is not an exact figure and is not presented as one. It is an order of magnitude, and once an owner sees a five figure annual number attached to after hours calls, the decision about an answering service stops being a debating point.
Membership base as an asset
The last piece treats the maintenance base as what it is: recurring revenue with a renewal curve. Active agreements, renewals due in the next 60 days, lapse rate, and the number owners rarely have, which is how much more a member household spends per year than a non member. That last comparison is what justifies paying technicians for conversions.
Architecture
dropped to one folder Google Sheets straight
into Power Query
The burdened rate is a table, not a constant
Each technician gets a rate per period: wages plus employer taxes, benefits, workers comp, and the vehicle and phone cost from the input sheet, divided by paid hours. It changes when someone gets a raise or a new truck, so it is a per period row rather than a hardcoded number.
-- Burdened hourly rate per technician, per period VAR DirectPay = SUM( 'Payroll'[Wages] ) + SUM( 'Payroll'[EmployerTaxes] ) + SUM( 'Payroll'[Benefits] ) + SUM( 'Payroll'[WorkersComp] ) VAR Vehicle = SUM( 'TechCosts'[TruckAndPhone] ) VAR PaidHours = SUM( 'Payroll'[PaidHours] ) RETURN DIVIDE( DirectPay + Vehicle, PaidHours )
True job cost
The measure that everything else reads from. Note that onsite and drive hours are summed together before being multiplied by the rate:
-- True job cost = burdened labor + parts + allocated overhead VAR LaborCost = SUMX( 'Job Labor', ( 'Job Labor'[OnsiteHours] + 'Job Labor'[DriveHours] ) * RELATED( 'Technicians'[BurdenedHourlyRate] ) ) VAR PartsCost = CALCULATE( SUM( 'Job Parts'[Cost] ) ) VAR OverheadAlloc = [Billable hours on job] * [Overhead rate per billable hour] RETURN LaborCost + PartsCost + OverheadAlloc
Overhead per billable hour, and why it matters
The overhead rate is total overhead for the period divided by total billable hours in the same period, so it moves with utilization rather than with job count:
-- Overhead rate per billable hour, current period DIVIDE( CALCULATE( SUM( 'Overhead'[Amount] ) ), CALCULATE( SUM( 'Job Labor'[OnsiteHours] ) ) )
Margin, and the grain it is computed at
Profit per job and margin are computed at job grain and then aggregated, never the other way round. Aggregating revenue and cost first and dividing at the end hides the losing job types inside a healthy average:
-- Gross profit, computed per job then summed SUMX( VALUES( 'Jobs'[JobId] ), [Job revenue] - [True job cost] )
Missed call exposure
Deliberately a rough estimate, built from the company's own numbers rather than an industry benchmark:
-- Estimated revenue lost on unconverted inbound calls VAR UnbookedCalls = [Inbound calls] - [Calls converted to jobs] VAR AvgJobValue = DIVIDE( [Total revenue], [Job count] ) VAR HistoricalClose = [Booking rate] RETURN UnbookedCalls * AvgJobValue * HistoricalClose
Presented with the assumptions visible, because the value of the number is the order of magnitude and not the decimals.
Lead source return, on profit rather than revenue
The last measure is one line and it changes marketing budgets. Return per channel is gross profit over spend, not revenue over spend, so a channel that books cheap low margin work stops looking like a winner:
-- Return per dollar of marketing spend DIVIDE( [Gross profit], SUM( 'Marketing'[Spend] ) )
| Parameter | Lives in |
|---|---|
Truck and phone cost per tech | Google Sheets, monthly |
Overhead accounts included in the base | Google Sheets, account list |
Membership pricing and member uplift | Google Sheets |
Callback window, days | Google Sheets, default 30 |
03Timeline and outcome
| Stage | From start |
|---|---|
| Audit: FSM exports, payroll structure, overhead base | Week 1 |
| Burdened labor rates, parts costing, overhead allocation | Weeks 2-3 |
| Job profitability by type, technician and lead source | Week 4 |
| Missed call analysis and membership base reporting | Weeks 5-6 |
Margin improvement depends on how your pricebook is currently structured and how overhead is absorbed. We size it during the audit, before any work starts.
A similar problem on your side?
Tell us and in 30 minutes we will figure out what is possible.
