Auto Repair Shop Spreadsheet Template: Tracking Parts and Labor Profit Separately

Your accountant hands back a set of books that says the shop ran a 59.3% gross margin last quarter. It is a fine number. It is also, as a management tool, almost completely useless — because there is nothing you can do on Monday morning that acts on “59.3%.”

Split the same quarter in two and it becomes a decision:

Two figures, thirty-three points apart, averaged into one that describes neither. (The two rows do not add to the third: sublet and shop supplies account for the remaining $600 of revenue, and they ran at a small loss over this period — which is its own conversation, and another one the blended number buries.) And they are not two views of the same problem. Parts margin is a pricing decision you make at the counter with a matrix. Labor margin is a rate-and-efficiency decision you make on the schedule board. Different levers, different conversations, different people. The blended figure asks for neither.

Every figure on this page comes from one fully worked sample shop — 53 closed repair orders, five still in the bay — pre-filled in the tracker described at the end. The dollars are not the point. The structure is.

What a Repair Order Actually Has to Record

Most shops track jobs at the level of “ticket total” and “roughly what the parts cost.” That is enough to invoice and nowhere near enough to manage. A repair order needs seven things on one line, because the moment they sit on separate lines — or in separate systems — nobody ever reconciles them again.

1. Parts sold and parts cost, as two columns, not one net figure
2. Labor hours sold and the rate they were sold at
3. Sublet — the transmission you sent out, the alignment the tire shop did — with its cost
4. Shop supplies, usually a percentage of labor
5. Travel, if it was a mobile job: miles and the call-out fee
6. Discount given, as its own column and not baked into a lower price
7. Rework, posted back against the original job, not opened as a new one

The two that get left off are the ones that matter most. Discount, hidden inside a reduced ticket, makes your labor rate look fine while your bank balance disagrees. Rework opened as a fresh ticket turns a failure into apparent new business — the second-best-looking job of the week can be the same car, twice.

Here is one repair order written out fully. The figures are my own, chosen to be typical of a mid-size ticket at the $145 posted rate used throughout this page — the arithmetic is what matters, not the parts:

Parts margin on this job: 44.9%. Labor margin: 74.5%. One job, two answers, and both of them actionable — which is exactly what the single 59% never is.

The Number Almost No Shop Has Calculated

Your posted rate is $145.00 an hour. Ask what you collected per hour last month and most owners will say $145.00, because that is what it says on the wall and on every invoice.

In the worked shop, the answer is $138.91.

The calculation is not complicated. Total labor billed, minus every discount given, divided by every hour sold. What makes it disappear is that the erosion arrives in pieces small enough to be invisible: half an hour of diagnostic comped to keep a customer sweet, a ticket rounded down to a flat $600, a fleet account billed at $128, a job quoted at 2.5 hours that took 3.1 and got billed at 2.5 anyway.

None of those appear as an expense. There is no line in any set of books called “rate I did not charge.” It simply never arrives.

$145.00 − $138.91 = $6.09 an hour. Across 141.3 hours sold, that is $861. On a full year at that volume it is a technician’s worth of tooling budget, and it is entirely invisible unless one column does the division for you.

This is worth its own walkthrough — see how to calculate your effective labor rate, including the five leaks that cause most of the gap and why a rate increase on the wall often moves the effective rate by less than half as much.

Parts: Why a Flat Markup Is Always Wrong Somewhere

Parts margin is decided once, at the counter, by whatever rule you price with — and in most small shops that rule is a single flat markup applied to everything. A flat rule cannot be right across a range that runs from a two-dollar clip to a five-hundred-dollar alternator, because what the number has to cover changes completely between the two ends. At the cheap end it has to cover counter time. At the expensive end it has to survive a customer with a phone.

A tiered matrix prices by cost band instead: heavy multiples where sourcing labor dominates, thinner multiples as the part gets expensive and the customer’s ability to price-check goes up. In the worked shop the matrix returns 44.9% blended parts margin, with a floor set at 35% and nine lines flagged automatically for sitting under it.

The flag matters more than the average. A blended 44.9% is comfortable enough that nobody investigates, while individual lines sold below cost sit inside it unnoticed. The full comparison — including the four-tier matrix used here and the markup-versus-margin arithmetic that trips up most shops — is in parts matrix pricing versus flat markup.

Labor: Busy Is Not the Same as Productive

Labor margin came in at 77.6% after discounts, which sounds like the healthy half of the business, and mostly is. But that figure is produced by people, and people vary more than parts do.

Every one of these people worked a full set of hours. Tomas was in the building for 15.5 hours and sold 14 of them. Ray was in for 59 and sold 61.6 — he beat book time on enough jobs to clear 100%.

The gap between hours worked and hours billed is the single largest controllable number in a repair shop, and it is the one that requires you to record something no system can infer: the clock. Hours worked has to be typed in. Everything else can be calculated. This is the distinction between efficiency and productivity, and shops routinely fix the wrong one — the difference, and which one your numbers are actually telling you to work on.

The Revenue You Already Sold and Never Collected

Every shop generates a second business it does not run. The customer came in for brakes, you found a leaking rear shock and a cracked serpentine belt, you quoted both, they took the brakes and said not today to the rest.

In the worked shop, declined work totalled $16,960. Of that, $4,310 was later recovered by calling people back — a 25.4% recovery rate. And $9,940 is still sitting open, including $3,740 of it flagged as a safety item.

That is the cheapest revenue in the building. It needs no marketing, the diagnosis is already done, the customer already knows and has already been quoted. It requires exactly one thing: writing it down with a date on it, and then ringing on that date. Almost nobody does, because a declined job is a small disappointment and small disappointments do not get logged.

The process — what to record, how to set the follow-up interval by urgency, and what the call sounds like when you have the vehicle’s own history in front of you — is in how to track declined repair work and follow up.

Three Things That Quietly Distort Every Shop Report

Open jobs counted as revenue. A car in the bay has most of its hours logged and only some of its parts invoiced. Count it and the month flatters itself. In the worked shop, five open jobs hold $5,274.23 — reported separately as work in progress, kept out of revenue, margin, car count and the effective labor rate until they close.

Comebacks opened as new tickets. Two comebacks against 53 closed jobs is a 3.8% comeback rate, and the $392 of rework belongs on the original repair orders, where it turns two apparently good jobs into two mediocre ones. Opened as fresh tickets instead, the same $392 reads as revenue and the comeback rate reads as zero.

Mobile work compared to shop work without the drive. Mobile jobs in the worked shop returned 66.3% gross margin against 58.9% for shop jobs — which looks like an argument for going mobile, right up until the travel is costed. Put the call-out fee in as labor revenue and the mileage in as a labor cost and the two become comparable. Leave the drive out and mobile wins every time, on paper.

Setting This Up Without Rebuilding Last Year

You do not need history. Four steps, and the first month is enough to see the shape.

1. Put your real numbers in Settings once — posted rate, fleet rate, target gross margin, the lowest parts margin you will accept, shop supplies percentage and cost per mile. Fifteen minutes, and every calculation downstream reads from it.
2. Log the next twenty repair orders completely. Parts sold and parts cost as separate columns. Discount as its own column. Hours sold at the rate they were actually sold at.
3. Type in hours worked for each technician, weekly. This is the only number nothing can work out for you, and it is the one that unlocks efficiency.
4. Write down every declined job with a follow-up date starting with the next customer who says “not today.”

After twenty jobs you will have a defensible parts margin, a labor margin, an effective labor rate and a declined-work list worth more than most shops’ monthly advertising spend. After sixty, the service-type breakdown starts telling you which work to chase — in the worked shop, Oil & Maintenance ran 76.3% margin on small tickets while Transmission & Driveline ran 48.7% on big ones, which is a scheduling conversation nobody has without the data.

The Thing Worth Remembering

A repair shop is two businesses sharing a building. One sells parts and is won or lost at the counter with a pricing matrix. The other sells hours and is won or lost on the schedule board with rate and efficiency.

Report them together and you get a number that is technically true and operationally useless. Report them apart and every week hands you a specific thing to fix: a part priced under the floor, a rate that is quietly $6 light, a technician selling 90% of the clock, $9,940 of work the customer already agreed they needed.

Split the two. The rest follows.

The rest of this series

• How to calculate effective labor rate for an auto repair shop — the formula, a worked month, and the five leaks that put a posted rate and a collected rate $6 apart.
• Parts matrix markup vs flat markup — a four-tier matrix against a flat 80%, and the markup-versus-margin arithmetic that underprices whole shops.
• Technician efficiency vs productivity — three measures, three different fixes, and why ranking techs on efficiency gets the order backwards.
• How to track declined repair work and follow up — five fields, follow-up intervals by urgency, and what the call sounds like when you wrote the measurement down.

Featured on ReadySheetGo

Auto Repair Shop & Mobile Mechanic Job Tracker — $16.99

Eleven linked tabs and 11,763 working formulas — the shop above, pre-filled across 53 closed repair orders and five still in the bay, so you can see it running before you type anything.

Settings holds your posted labor rate, fleet rate, target gross margin, minimum acceptable parts margin, shop supplies percentage, cost per mile and your own parts pricing matrix, and drives every other tab. Repair Orders is the master — 200 rows with parts, labor, sublet, supplies, travel, discount and rework all on one line, returning parts margin and labor margin separately on every job. Shop Stats works out your effective labor rate and multiplies the gap against your posted rate by all your hours, so the leak appears in dollars.

Then: Declined Work, 120 rows with a follow-up date calculated from the day you quoted it, safety items flagged and recovered work counted; Technicians, setting hours billed against hours actually worked to return efficiency and the labor gross profit each person produced after their own pay; Parts Inventory, 120 lines priced by your matrix, valued at cost and at retail, flagged for reorder and flagged again under your minimum margin; Vehicles, 100 VINs with visits, lifetime spend, last mileage, next service due and the declined work still open on that exact car; Monthly & Trends across twelve rolling months then split by service type and shop against mobile; and a Dashboard returning twelve figures including revenue, gross profit, average repair order, hours sold, effective labor rate, parts and labor margin separately, comeback rate, open declined work and the value sitting in the bay.

Open jobs are held out of revenue, margin, car count and the effective labor rate as work in progress. A Row Check column catches a date out before the date in, hours with no rate, parts sold under cost, a discount larger than the ticket, travel miles on a shop job and a technician no longer on your list.

Works in Excel and Google Sheets. No macros, no add-ons.

Get the Auto Repair Shop & Mobile Mechanic Job Tracker →