Cases/ Home services/ Job profitability

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.

Job costing Burdened labor Technician performance ServiceTitan / Jobber export Power Query · Power Pivot
Industry Residential HVAC and plumbing contractor · 14 technicians, 11 trucks, two county service area · Service, repair, replacement, maintenance memberships
Driver's seat What the owner opens, which job types were quietly losing money, how technicians are ranked now. The engineering side is behind the toggle in the top right corner.
Under the hood Architecture, the cost measure, overhead allocation and the missed call estimate. To see it through the owner's eyes, use the toggle in the top right corner.
Margin per job every job ▲ was revenue only
Reporting refresh one click ▼ was a manual spreadsheet
Missed call exposure quantified ▲ was invisible
First dashboard 4 weeks from kickoff

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:

The data was all there and none of it met in one place. Three systems, three grains, no shared keys:

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.

FSM exportjobs, invoices, techs
Payrollhours and burden
Accountingparts, overhead
Modeltrue job margin

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.

Labor
$52 / hr

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.

Parts
at cost

What was pulled off the truck for this job, at purchase cost rather than at the marked up line on the invoice.

Overhead
per billable hour

Allocated by the hours the job consumed, not spread evenly across job count. This single choice reorders the whole ranking.

Drive time is where the two county service area shows up. A 45 minute round trip on a 30 minute repair is not free, and it is often the difference between a profitable call and a break even one.

The screen the owner opens first

Job profitability · by type, technician and lead source technicians are masked
Synthetic data · true cost includes burdened labor with drive time, parts at cost and allocated overhead
Trade
Sort by
Job type Jobs Avg ticket Hours incl. drive True cost Profit / job Margin Total GP

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

Data sources
Store
Excel model
FSM · jobs and invoicesjob type, trade, lead source, assigned tech, revenue
FSM · dispatch logonsite and drive minutes per tech per job
Payrollwages, taxes, benefits, workers comp by tech
Accountingparts at cost, overhead accounts by month
Google Sheets (inputs)truck and phone cost per tech · overhead base · membership pricing
Staged CSV exports FSM, payroll and accounting
dropped to one folder
Google Sheets straight
into Power Query
Power Query join on job id and tech id, normalize periods
Power Pivot job, labor, parts facts · burdened rate and overhead rate tables

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] ) )
)
This is the choice that reorders the report. Spread overhead evenly per job and a 19 hour repipe absorbs the same overhead as a 1.2 hour maintenance visit. Allocate per hour and long jobs stop looking artificially profitable while quick high ticket calls stop looking artificially weak.

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] ) )
ParameterLives in
Truck and phone cost per techGoogle Sheets, monthly
Overhead accounts included in the baseGoogle Sheets, account list
Membership pricing and member upliftGoogle Sheets
Callback window, daysGoogle Sheets, default 30

03Timeline and outcome

StageFrom start
Audit: FSM exports, payroll structure, overhead baseWeek 1
Burdened labor rates, parts costing, overhead allocationWeeks 2-3
Job profitability by type, technician and lead sourceWeek 4
Missed call analysis and membership base reportingWeeks 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.

Nothing in this build asks your technicians to do anything differently in the field. The data already exists in your FSM, your payroll and your accounting file. It has just never been in the same place at the same time.

A similar problem on your side?

Tell us and in 30 minutes we will figure out what is possible.

view mode