Body Shop Parts Statement Reconciliation Guide

Body shop parts statement reconciliation compares each vendor's monthly statement with the shop's invoices, return slips, core-charge records, credit memos, and repair-order references. The goal is to catch missing credits, duplicate charges, wrong pricing, unreturned cores, and cutoff issues before the statement balance is approved for payment or the month is closed.

For a collision shop, that vendor side is rarely tidy. One statement may come from LKQ, another from Keystone or NPW, several from local OEM franchise dealers, another from a salvage or dismantler account, and another from the paint and materials jobber. Each one summarizes what the vendor believes the shop owes for the statement period. The shop's internal records show a different view: parts ordered for specific repair orders, invoices captured in AP, returns sitting in a folder, cores waiting to be sent back, and credit memos that may not post until the next statement.

The reconciliation workpaper sits between those two views. It asks a practical question for every statement line: does the vendor's record agree with the shop's record, and if not, what has to happen before the balance is payable?

That makes the control different from a generic vendor statement review. The body shop bookkeeper is not only confirming that invoice 12345 appears on both sides. They are also checking whether a bumper cover returned last month has been credited, whether a recycled part carried a core charge that is still open, whether a dealer invoice was posted to the wrong RO, whether freight or restocking fees explain a variance, and whether a paint jobber's monthly statement has been allocated to the right refinish work.

The control is worth doing monthly because small mismatches compound. Withum's dealership parts-reconciliation guidance treats a 2% to 5% accounting-to-parts variance as a normal tolerance range, while warning that a variance that grows every month is a red flag for a deeper process problem in parts reconciliation.

The Parts Pad, accounting system, repair-order file, and vendor statement all describe the same cost flow from different angles. Reconciliation is how the shop turns those separate records into one close-ready parts-cost control.

Start With the Vendor Mix and Statement Sources

The first pass is not matching. It is intake. A body shop needs every source document for the statement period saved by vendor, account, and month before the workpaper can be trusted.

For LKQ and Keystone, the source may be a portal export, a statement PDF, and separate invoices or credit memos downloaded from the account. NPW, Diamond Standard, and other aftermarket suppliers may send emailed PDFs or account statements with invoice and credit lines mixed together. OEM franchise dealer parts departments are less consistent. One Ford dealer may send a clean monthly statement; a Toyota dealer may send printed copies; a GM dealer may apply credits under a different reference than the original invoice. Salvage and dismantler accounts add recycled or used parts, Hollander interchange references, and core-related notes that may not look like the aftermarket supplier statements.

Paint and materials jobbers need their own folder in the intake file. PPG, BASF, Sherwin-Williams, Axalta, and local jobbers may bill primer, clearcoat, hardener, masking materials, sandpaper, mixing supplies, spray-out cards, and shop consumables purchased across the month. Those statements do not always map cleanly to one repair order, so they should be marked as P&M source documents from the start rather than forced into a normal part-by-part match.

Use the same discipline a general vendor statement reconciliation process would use, but add body-shop fields at intake. Save the statement, invoice copies, credit memos, return slips, portal exports, and any scan of a mailed document. Name the files so the vendor, account number, and statement period are obvious. If a statement has no account number, preserve the vendor location or dealer name because that may be the only way to separate two local accounts later.

This intake step also protects the audit trail. If a vendor rep later says a credit was applied under a different invoice number, the bookkeeper can return to the original statement package instead of searching email, portal downloads, and paper folders again.

Build One Normalized Row Schema for Every Vendor

A useful reconciliation sheet has one row per vendor statement line, with enough shop-side fields beside it to prove whether the line is cleared, open, or disputed. Do not build one tab for LKQ, another for Keystone, and another for dealer statements if each tab uses different columns. Different vendors can keep their own source files, but the workpaper needs one normalized row schema.

At minimum, each row should carry:

• Vendor name and account number
• Statement period and statement date
• Statement line type: invoice, credit, return, core charge, freight, tax, restocking fee, adjustment, or payment
• Vendor invoice number, credit memo number, and invoice date
• RO number, part number, and part description from the shop-side record
• Statement amount, tax, freight, restocking fee, and total line amount
• Return slip number and return slip date
• Core flag, core amount, core due date, and core status
• Days outstanding for open credits or cores
• Variance reason, follow-up owner, follow-up date, and resolution notes

The statement line type matters because not every dollar on the statement is a normal part purchase. A freight charge may be valid but allocated differently. A restocking fee may explain why the credit does not equal the original invoice. A core charge is not the same as an ordinary expense if the core is still returnable. A vendor adjustment with no invoice reference should not be cleared just because the total statement balance looks reasonable.

RO number and part number are the bridge back to the shop. Vendor statements often carry invoice numbers and amounts, but they may not show the repair order that created the purchase. The bookkeeper usually has to pull that from the captured invoice, the Parts Pad, the shop management export, or an AP entry. Once the RO is added to the row, the statement line can be tested against both the vendor balance and the job-cost record.

The same schema works for portal exports, statement PDFs, scans, and copied invoice details. The point is not to make every source document look identical. The point is to extract parts vendor statements to Excel in a shape where formulas, filters, and pivot tables can compare them without a manual rebuild each month.

Match Invoices, Returns, Credits, and Cores Line by Line

Once the rows are normalized, reconciliation becomes a sequence of matches. Start with the easiest one: statement-to-invoice. Each vendor invoice on the statement should tie to a captured shop invoice by vendor, invoice number, invoice date, and amount. If the statement shows an invoice that AP never captured, mark it as missing invoice. If AP captured an invoice that is absent from the statement, hold it open until the next vendor cycle or confirm whether it belongs to a different account.

The second match is invoice-to-RO. A part can be valid on the vendor side and still be wrong in the shop's records if it was posted to the wrong repair order, posted without an RO, or assigned to a closed job. This is where LKQ, Keystone, OEM dealer, and salvage invoices need the shop-side part number, RO number, and cost detail beside the vendor statement line.

Returns and credits need their own chain. A returned part should have a return slip, the original invoice line, and a later vendor credit memo. The credit memo should be matched back to the original invoice line, not merely treated as a negative amount somewhere on the statement. That prevents two common errors: clearing a credit that belongs to a different return, or missing a credit because it posted in the next statement period.

Price and duplicate-charge checks need more than invoice-number matching. Compare the statement amount against the captured invoice total, the original vendor invoice or quote, tax, freight, restocking fees, and any return credit tied to that line. Then scan for repeated invoice numbers, repeated part numbers on the same RO, same-dollar charges posted under different references, and credit memos that only partially offset the original invoice. Unexplained differences should become variance reasons, not manual adjustments buried in the sheet.

Cores are stricter because the clock matters. The core tracker should show the original invoice line, core amount, component type, due date, return slip date, vendor credit memo date, days outstanding, and open or cleared status. According to the LKQ return and core-charge policy, returned parts must include the invoice, returns after 30 days from the invoice date may be subject to a 20% handling or restocking fee, no returns are accepted after 45 days, and core charges are refundable when the appropriate core is returned within 30 days of the invoice date. Treat that as an LKQ example, not a rule for every supplier. It shows why invoice date, return date, core due date, core status, and credit memo date belong in the workpaper.

Period bridging keeps the file honest. If a part is returned on January 29 and the vendor credit appears on the February statement, January should not pretend the issue is solved. The return becomes an aged follow-up item with expected credit, vendor contact, and next review date. When the credit posts, the February workpaper clears it against the January return record.

The cleanest workpapers separate match status from variance reason. Match status answers whether the row is cleared, open, disputed, or waiting for the next statement. Variance reason explains why: missing credit, partial credit, duplicate charge, wrong amount, freight difference, tax difference, restocking fee, missing invoice, wrong RO, open core, or vendor adjustment. Keeping those fields separate makes the final review faster because the owner can filter for unresolved dollars without losing the accounting cause behind each line.

Allocate Paint and Materials Jobber Statements Without Losing the RO Trail

Paint and materials statements need a different control from parts invoices. A jobber statement may include clearcoat, primer, hardener, reducers, masking materials, sandpaper, mixing supplies, spray-out cards, and shop consumables purchased across the month. Some lines relate to a specific repair order. Others support the refinish department as a whole.

The reconciliation question is still the same: does the PPG, BASF, Sherwin-Williams, Axalta, or local jobber statement agree with what the shop received, returned, and owes? The allocation question is separate: how should the month's P&M cost be assigned across repair orders or cost pools?

Keep those two questions in different columns. First reconcile the statement lines against invoices, credits, returns, freight, tax, and adjustments. Then apply the shop's existing allocation basis, such as a percentage of refinish labor hours, dollars per refinish hour, a materials calculator output, or another rule the shop already uses consistently. If a line is tied directly to an RO, preserve that RO. If it belongs to the monthly pool, mark the allocation basis rather than inventing a job link.

A paint jobber reconciliation row should include the vendor, statement period, invoice or credit memo number, purchase date, material category, amount, return or credit status, allocation basis, allocated RO or pool, and variance notes. That gives the bookkeeper a way to answer both sides of the question: whether the vendor balance is payable, and where the cost belongs in the month.

Do not turn this step into a markup review. Parts margin, paint caps, insurer reimbursement, and refinish labor assumptions may all matter to the business, but the vendor-statement workpaper is narrower. It proves that the shop captured the jobber charges, recorded the credits, applied the allocation rule consistently, and did not let freight, special-order materials, or returns disappear inside a monthly total.

---

Use Extraction to Turn Mixed Statements Into Matchable Rows

Shop-management reports and QuickBooks exports help with the shop-side records, but they do not solve the vendor side by themselves. The month-end file still has LKQ or Keystone statements, dealer PDFs, scanned jobber statements, emailed credit memos, return slips, and invoice copies from different sources. The workpaper cannot match those documents until their line details are in a consistent table.

That is where structured extraction fits. The bookkeeper defines the fields needed for the reconciliation sheet, such as vendor, statement period, invoice number, invoice date, statement line type, part number, amount, tax, freight, credit memo number, return slip number, core flag, and due date. Each source document is converted into rows that can be filtered, matched, and reviewed instead of retyped.

For the document-conversion part of the workflow, a deeper guide on how to convert vendor statements to Excel is useful because the same extraction problem appears outside body shops as well: vendor statements arrive in layouts that accounting software did not design.

Invoice Data Extraction can sit in that extraction layer for body shop AP teams that want the spreadsheet route. Users upload PDFs or image files, including JPG and PNG scans, describe the fields and formatting they need in a prompt, and download structured Excel, CSV, or JSON output. The product handles invoices, credit notes, vendor statements, receipts, bills, bank statements, and other financial documents, so the same prompt-driven workflow can be used to extract vendor statement and invoice data into spreadsheets before the matching work starts.

The prompt should reflect the reconciliation schema, not a generic invoice capture. A useful instruction might ask for one row per statement line, separate invoice lines from credit lines, capture core-charge indicators, preserve source file and page references, format dates as year-month-day, and show credit notes as negative amounts. If the vendor statement does not show the RO number, leave the RO column blank for shop-side enrichment instead of forcing a guess.

Dedicated auto-repair AP and reconciliation tools, including systems such as WickedFile or Auto Care Software, may be the right fit for shops that want a vertical workflow. For a shop building its own month-end control, extraction plus a disciplined workpaper is the lower-friction path: get the source documents into rows, match them against shop records, and investigate the exceptions.

Close the Month With a Variance List, Not a Pile of Statements

The month-end output should be a decision file. Cleared lines support the statement balance approved for payment. Open items become a variance list with reason, owner, vendor contact, expected resolution, and carry-forward date.

Use variance reasons that tell AP what to do next:

• Missing vendor credit
• Duplicate charge
• Price mismatch
• Freight, tax, or restocking fee difference
• Invoice posted to the wrong RO
• Vendor invoice missing from AP
• Return accepted but not credited
• Credit applied to a later statement period
• Core not returned inside the vendor window
• Core returned but credit still open

Each reason should lead to an action. A missing credit goes to the vendor rep with the return slip and original invoice attached. A duplicate charge is held from payment or disputed before the statement is cleared. A wrong-RO posting goes back to the bookkeeper or parts manager for correction. An open core gets an owner because the shop may still be able to return it before the vendor window closes.

Unresolved returns and cores should roll forward as an aging schedule. That schedule is not a side note. It is the proof that the shop is watching refundable dollars until the credit appears. Aging by days outstanding also keeps a small issue from becoming invisible when statements change month to month.

This vendor-statement control validates the vendor side of parts cost. The shop's repair-order records, Parts Pad, and AP entries validate where those costs belong. Rolling those captured invoices into a per-RO ledger that splits OEM, aftermarket, salvage, glass, P&M, sublet, and labor into a six-bucket P&L is the next control once the vendor side is reconciled. Cash receipts need a separate trail for how carrier EFT deposits match repair orders, remittance advice, and deductibles. Insurer-side exceptions need a separate DRP supplement short-pay reconciliation that compares approved supplements, EORs, actual RO costs, and carrier payments. Adjacent automotive businesses face similar document flow, and a dealership AP workflow for parts invoices and vendor statements has some overlap, but collision shops need their own controls for cores, returns, salvage parts, dealer heterogeneity, and paint jobber allocation.

When the workpaper is complete, the bookkeeper should be able to separate payable balance from follow-up balance. Pay what is supported, dispute what is not, and carry unresolved credits and cores into the next month with enough detail that they can be cleared without reconstructing the whole file.

That final file also gives the owner or controller a better review than a stack of statements can. They can see which vendors create repeated exceptions, which accounts have aging credits, whether core returns are being handled quickly enough, and whether the same variance reasons appear every month. The purpose is not to create a perfect spreadsheet for its own sake. It is to keep parts cost from drifting because no one had a clean view of the vendor side.