Guide
Parts matrix template
A seven-band parts matrix you can copy, the spreadsheet formula that applies it, a worked diesel parts list and the band-boundary bug that costs $29.98.
A parts matrix template is a table of cost bands, each with a multiplier that turns cost into selling price. Small parts carry a high multiplier because handling one costs the same whether it is a washer or a turbo. Below is a seven-band matrix you can copy, the spreadsheet formula that applies it, a worked diesel parts list with the blended margin computed, and the boundary bug that quietly sells a $200.01 part for less than a $200.00 one.
Key takeaways
- Seven bands. Replace every number with your own, then test it on a real parts list.
- Markup is on cost, margin is on the sale. A 1.70 multiplier is a 41.2% margin.
- Add a floor rule or the band boundary prices a dearer part cheaper.
- Cores and freight sit outside the matrix, for opposite reasons.
What does a parts matrix look like?
The multiplier column is the one you change. Margin is derived from it, so it moves when the multiplier does.
| Cost band | Multiplier | Resulting margin |
|---|---|---|
| $0.01 to $5.00 | 3.00 | 66.7% |
| $5.01 to $25.00 | 2.40 | 58.3% |
| $25.01 to $75.00 | 1.95 | 48.7% |
| $75.01 to $200.00 | 1.70 | 41.2% |
| $200.01 to $500.00 | 1.55 | 35.5% |
| $500.01 to $1,500.00 | 1.42 | 29.6% |
| Over $1,500.00 | 1.32 | 24.2% |
The margin column is one divided by the multiplier, subtracted from one. At 1.70 that is 1 minus 0.588, which is 41.2%. Do that arithmetic once for your own table and pin it next to the counter, because a multiplier is what you type and a margin is what you get asked about.
One rule has to sit under the table or two people will read it two ways. The band comes from the unit cost, and the extension is unit price times quantity. Eight injectors costing $412.00 each land in the $200.01 to $500.00 band, not in the top band on their combined $3,296.00. Anything sold by volume is the exception: coolant, gear oil, transmission fluid, sealant and shop consumables band on the landed total of the line, because the counter handled one line whether it held four quarts or eighteen. Every worked example in this reference set applies both halves of that rule, so the arithmetic on one page agrees with the arithmetic on the next.
How do you build the matrix in a spreadsheet?
Two ranges and one lookup. Put the bands on their own sheet so the multipliers can be edited without touching a single parts line.
band_floor,band_ceiling,multiplier,margin
0.01,5.00,3.00,0.667
5.01,25.00,2.40,0.583
25.01,75.00,1.95,0.487
75.01,200.00,1.70,0.412
200.01,500.00,1.55,0.355
500.01,1500.00,1.42,0.296
1500.01,999999.00,1.32,0.242
On the parts sheet, with landed cost in column D, the lookup is an approximate-match VLOOKUP against the band floor:
Multiplier =VLOOKUP(D2, Bands!$A$2:$C$8, 3, TRUE)
Raw price =ROUND(D2 * E2, 2)
Floored =MAX(F2, Bands!prev_ceiling * Bands!prev_multiplier)
Sell price =MAX(G2, D2 + MinGrossProfit)
Margin =(H2 - D2) / H2
The last two rows are the ones every free template leaves out. The floor stops the boundary inversion described below. The minimum gross profit stops a $0.40 clip selling for $1.20 when handling it costs you three minutes of a counter person's day.
What happens at the band boundary?
A part that costs a penny more sells for thirty dollars less. Run it:
| Part cost | Band | Multiplier | Sell price |
|---|---|---|---|
| $200.00 | $75.01 to $200.00 | 1.70 | $340.00 |
| $200.01 | $200.01 to $500.00 | 1.55 | $310.02 |
One cent of extra cost removes $29.98 of selling price. Two suppliers quoting the same part either side of the line produce two invoices that look like a pricing error, because they are one.
The fix is a floor rule, and it is one column in the sheet. No band may sell a part for less than the top of the previous band would have sold it for. Applied here, the $200.01 part sells at $340.00 until cost reaches $219.36, which is $340.00 divided by 1.55 and rounded up. Above that the matrix takes over again and the curve is continuous.
What does the matrix do to a real parts list?
The parts for a turbocharger replacement on a 2015 Ram 2500 with the 6.7L Cummins. Costs are illustrative supplier costs, landed. Every price is cost times the multiplier for its band.
| Part | Cost | Band multiplier | Price |
|---|---|---|---|
| Copper sealing washers, four | $3.20 | 3.00 | $9.60 |
| V-band clamp | $18.40 | 2.40 | $44.16 |
| Turbo gasket set | $46.20 | 1.95 | $90.09 |
| Engine oil and filter | $62.00 | 1.95 | $120.90 |
| Oil feed line | $88.40 | 1.70 | $150.28 |
| Turbocharger, remanufactured | $1,180.00 | 1.42 | $1,675.60 |
| Measure | Working | Result |
|---|---|---|
| Total cost | Six lines summed | $1,398.20 |
| Total price | Six lines summed | $2,090.63 |
| Gross profit | $2,090.63 less $1,398.20 | $692.43 |
| Blended margin | $692.43 divided by $2,090.63 | 33.1% |
| Blended markup | $2,090.63 divided by $1,398.20 | 1.50 |
Look at what the turbo does. Five of the six lines run at margins between 41% and 67%, and the blended result still lands at 33.1%, because one line is 84% of the cost. That is the argument for a banded matrix in one number.
What does a flat multiplier do to the same list?
Price the identical parts at a single 1.60 on everything and compare.
| Part | Banded price | Flat 1.60 price | Difference |
|---|---|---|---|
| Copper sealing washers | $9.60 | $5.12 | You lose $4.48 |
| V-band clamp | $44.16 | $29.44 | You lose $14.72 |
| Turbocharger, remanufactured | $1,675.60 | $1,888.00 | You quote $212.40 high |
| Whole list | $2,090.63 | $2,237.12 |
The flat rate loses money on the hardware and loses the job on the turbo. A customer calling three shops is comparing the turbo price, because that is the only line big enough to compare, and $212.40 is enough to move the work. Meanwhile the washers that pay for the counter person's time are being sold at $5.12.
What does the law say about how parts appear?
Nothing about your multiplier. What it does govern is how the result gets shown. California Business and Professions Code 9884.8 requires the invoice to separate service work from parts and to subtotal each:
"Service work and parts shall be listed separately on the invoice, which shall also state separately the subtotal prices for service work and for parts, not including sales tax, and shall state separately the sales tax, if any, applicable to each."
The customer sees a price per part. Your cost and your multiplier stay in your own columns, which is exactly where they belong.
Checking whether the matrix is doing what you think takes one division. Total parts gross profit for a quarter, divided by total parts sales for the same quarter, is your measured margin. Set it next to the margin your bands predict. If the measured number sits below the predicted one, the gap is usually freight nobody added to cost. Returns nobody credited back come second, then parts bought retail on a Saturday at a cost the matrix was never designed for.
Freight belongs in the cost cell before the lookup runs, and the IRS books it the same way. Publication 334 puts freight-in on "merchandise you purchase for sale" inside cost of goods sold, which is where your own accountant will find it at year end. A band applied to a supplier price with the delivery stripped out prices the line off a number that was never your cost. The FTC's Auto Repair Basics is a reminder of the other constraint on that column: your state may require a shop to disclose when non-original equipment is used, so the condition of a part travels with its price.
Where do parts matrices go wrong?
| Failure | What it costs | The fix |
|---|---|---|
| One multiplier on everything | Margin on hardware, jobs on big parts | Bands, enough that a clip and a turbo never share one |
| No floor at the band boundary | A dearer part sells for less | A floor column in the sheet |
| Freight left out of cost | Margin you never earned | Land the cost before the lookup |
| Cores run through the matrix | You bill for money you will refund | Cores outside the matrix, at cost |
| Matrix never re-tested | Supplier cost moves, your margin drifts | Quarterly, against actual sales |
| Retail-counter buys priced at account cost | Silent losses on rush jobs | Price from what you paid, per line |
| No minimum gross profit | A $0.40 clip sells for $1.20 | A floor in dollars, not just a multiplier |
| Special-order parts at standard bands | Handling and return risk unpriced | A flag that lifts the multiplier |
The quarterly re-test is the one that matters most and gets done least. A matrix written three years ago is pricing against three-year-old costs, and the drift is invisible because every individual line still looks reasonable.
How do you test a new matrix before switching to it?
Back-test it. Export ninety days of parts lines with their costs, apply the candidate matrix in a spare column, then compare the totals to what you billed at the time. The whole exercise runs off one export and it tells you three things nothing else will.
| Column | What it holds |
|---|---|
| Part cost | Landed, as of the date it was billed |
| Price billed | What the customer paid at the time |
| Candidate price | Cost run through the new bands |
| Difference | Candidate less billed |
| Job total impact | Difference summed per repair order |
The first thing it tells you is the aggregate move. A matrix that lifts parts revenue 6% across ninety days is a real number rather than a hope. The second is the distribution, and it is the one that matters. If the whole lift comes from four large parts on three tickets, the new matrix is a bet on those tickets rather than a pricing policy.
The third is the ceiling test. Sort the difference column descending and look at the top twenty rows. Those are the lines a customer would have noticed. A turbocharger moving $180.00 is a quote you might lose, and a matrix that produces its gain there instead of across the hardware is a matrix that costs you jobs to win margin.
| Test | Pass condition |
|---|---|
| Aggregate lift | Meets the gross profit target you set before you started |
| Distribution | The lift survives when you delete your ten largest lines |
| Ceiling | No single line moves more than your competitive tolerance |
| Floor | No line loses margin against what you billed |
Run the same export again ninety days after the switch and compare measured against predicted. If measured comes in lower, the gap is almost always freight, retail-counter buys or credits that never came back, which is the same list the quarterly division above surfaces from the other end.
One caveat on the back-test. Ninety days of history prices at ninety days of costs, and supplier costs moved during that window. Where a part appears several times at different costs, use the most recent one, otherwise the candidate matrix gets credit for a price change it had nothing to do with.
Who should touch this table?
One person. A parts matrix that three people can edit is a matrix nobody trusts, and the version history is what you need when margin moves and nobody knows why. Keep the current table and the one before it, with the date each took effect.
That table is also the thing an automated estimate has to respect. Mechanica quotes parts at the shop's own suppliers and applies the shop's own markup rules, so a drafted line reads at the price the shop would have written by hand. Every line shows where its cost came from, which is what lets an advisor answer the question about the turbo without opening a catalog. What a draft does not touch is the rest of the totals block: cores, sublet, hazmat and shop supplies stay exactly where your shop sets them, on the lines your shop already writes.
The estimate template is where these prices land and the invoice template is where the customer reads them. Labor is the other half of your pricing, and it lives on the labor rate sheet template. For the definitions, see parts matrix, parts margin, markup and margin and core charge. Every form is listed on the shop forms roundup and the guides hub.
Sources
Questions shops ask
- What is a parts matrix?
- A table that turns a part's cost into its selling price using a multiplier that changes with the cost band. Cheap parts carry a high multiplier because the handling cost is the same whether the part is a washer or a turbocharger. Expensive parts carry a low one because the market prices them and the customer can check.
- How many bands should a parts matrix have?
- Sort your last ninety days of parts lines by cost and look at where the pile sits. You need enough bands that a $4.00 clip and a $700.00 sensor are never on the same multiplier, and few enough that you can hold the table in your head, because a band nobody can recite is a band nobody notices is wrong. The seven-band table on this page is a starting point you replace with your own numbers.
- What multiplier should I use on parts?
- Whatever holds your gross profit after freight, returns and the counter time each part costs you. There is no published correct number, and any page that gives you one without seeing your costs is guessing. What this page does give you is the arithmetic, so you can test a candidate multiplier against your own parts list before you commit.
- What is the difference between markup and margin?
- Markup is measured against cost, margin against the selling price. A 1.70 multiplier is a 70% markup and a 41.2% margin, because 1 divided by 1.70 is 0.588 and the remainder is what you keep. Quote the multiplier to your parts person and the margin to your accountant, and check once a quarter that the second one still follows from the first.
- What is the band-boundary problem?
- A part costing one cent more can sell for less. At a 1.70 multiplier a $200.00 part sells for $340.00, while at 1.55 a $200.01 part sells for $310.02. The fix is a floor rule: no band may sell a part for less than the previous band's ceiling would have. It is one extra column in the sheet, and without it the inversion stays invisible until a customer compares two invoices.
- Does the multiplier come off the unit cost or the line total?
- The unit cost, then the extension is unit price times quantity. Eight injectors at $412.00 each band at $412.00, not at $3,296.00. The exception is anything sold by volume, so coolant, gear oil, transmission fluid and sealant band on the landed total of the line, because you handled one line either way. Write down which rule you use, because the two produce different invoices.
- Should freight go into the matrix?
- Put it in the cost before the multiplier applies, not as a separate line. A hot-shot delivery on a Saturday afternoon is part of what the part cost you, and a matrix applied to an invoice price that ignores it prices the job at a margin you never earned.
- How do cores fit into a parts matrix?
- They do not. A core charge passes through at cost and comes back when the old unit does, so running it through a multiplier bills a customer for money you are about to refund. Keep the core on its own line at cost, and exclude it from the matrix lookup.
- Does automated estimating use my matrix?
- Yes. Mechanica prices parts at the shop's own suppliers and applies the shop's own markup rules, so a drafted estimate reads at the prices the shop would have quoted by hand. The matrix stays yours. What changes is that nobody has to look the cost up first.