Collision Shop DRP EFT Reconciliation Guide

Body shop carrier EFT reconciliation is the cash-application workflow that ties each insurance deposit to the carrier remittance advice, one or more repair orders, and the customer-pay deductible portion. A reliable workpaper starts from the bank line, splits batched carrier deposits across ROs, checks the split total back to the bank, and flags short-pays or supplement follow-up before month-end close.

That bank-first view matters because the deposit is the control total. A State Farm, GEICO, Allstate, Progressive, USAA, Liberty Mutual, Farmers, or Nationwide payment might appear as one ACH line even when it covers several repair orders. A CCC Payments or Xplor Pay related deposit may carry a different bank description than the carrier name the bookkeeper sees on the remittance report. The RO system may show the job as ready for payment, but cash application is not complete until the money that actually landed in the account has been tied to the remittance detail and posted against the right RO.

For a collision shop, this is not the same as ordinary bank reconciliation. The bank statement proves cash cleared, but it does not explain which claim, customer, vehicle, or repair order the deposit paid. The carrier remittance advice supplies that detail, but it may arrive as a portal download, an emailed PDF, or a report inside an integrated payment workflow. The repair order ledger supplies the billed amount and customer context. The deductible record shows whether the customer-paid portion has also been collected.

Non-DRP checks and one-off carrier payments can use the same evidence standard, but they should not drive the workflow design. The recurring DRP EFT cycle is where the volume, batching, and month-end pressure usually live.

The difficult cases are the routine ones: one EFT covering five ROs, a carrier payment that arrives before the customer pays the deductible, a short-pay that belongs on a supplement follow-up list, or a bank rule that correctly classifies the payer but cannot split the amount by RO. A useful workpaper brings those facts together without pretending the carrier portal, the bank feed, or QuickBooks can prove the whole story alone.

Build the workpaper from the documents a shop actually has

Start the workpaper with evidence, not with software labels. A collision-shop cash-application file usually pulls from six places: the bank statement or bank feed, the carrier remittance advice, the repair order ledger, the customer-pay or deductible receipt record, the supplement or short-pay tracker, and the accounting system where AR is posted.

The bank side needs enough detail to prove cash. Capture bank date, bank description, deposit amount, carrier or processor name, and ACH or reference number where the bank provides it. Do not clean up the bank description too early. The exact wording, even when messy, can be the clue that ties a CCC Payments, Xplor Pay, Clearent, or direct-carrier deposit back to the remittance record.

The remittance side needs enough detail to prove what the carrier says it paid. Capture remittance date, claim number, RO number, customer, vehicle or VIN where useful, billed amount, carrier-paid amount, adjustment or variance code if present, and the source file and page reference. If the remittance document is unfamiliar, a general guide to what a remittance advice shows is useful background, but the body-shop version has to preserve claim and RO context, not just invoice and payment amount.

The RO side needs the fields that let the bookkeeper close the job cleanly: RO number, customer, carrier, billed amount, expected deductible, customer-pay receipt amount, customer-pay receipt date, supplement status, short-pay variance, posting status, and reviewer notes. Source reference fields are not clerical clutter. They let the month-end reviewer jump from a matched row back to the exact bank line or PDF page without reopening every carrier portal.

Keep this AR-side workpaper separate from AP controls. Matching carrier receipts to repair orders is a different control than body shop parts vendor statement reconciliation, where the shop is checking supplier statements, invoices, credits, and payments on the vendor side. The cost side of the same RO is a third workpaper again: rolling OEM, aftermarket, salvage, glass, P&M, sublet, and labor invoices into a per-RO cost ledger for gross-profit review belongs next to the job, not next to the carrier deposit. Both affect month-end close, but mixing them in one worksheet makes ownership and evidence harder to review.

For shops building the file outside a full integration, Invoice Data Extraction can help prepare the inputs by extracting structured rows from bank statements and carrier remittance PDFs into Excel, CSV, or JSON, with source file and page references for verification. The useful output is not a magic posting decision. It is a cleaner set of rows for the bookkeeper to match, review, and post.

Match bank-up and remittance-down until every RO has evidence

Bank-up matching starts with the deposit that cleared. Take the bank date, amount, description, and reference number, then identify the carrier or payment processor behind the line. From there, locate the matching remittance advice, split the deposit into the ROs listed on that advice, and compare the allocation total back to the bank amount. The bank line remains the control total throughout the process.

This is where exact bank wording matters. A State Farm EFT might be obvious from the payer name. A GEICO, Allstate, Progressive, USAA, Liberty Mutual, Farmers, or Nationwide deposit might use an ACH descriptor that is less readable. A CCC Payments deposit may appear under processor language rather than the carrier name the shop expected. Preserve the original descriptor, then add a normalized carrier field beside it so searches and pivot tables still work.

Carrier remittance formats vary, but the workpaper should not. One carrier may emphasize claim number, another may show RO reference more clearly, and an integrated payment report may group payments under processor or workfile language. Normalize those documents into the same fields: carrier, bank reference, remittance date, claim number, RO number, customer, paid amount, variance, and source reference. If a carrier-specific field is useful for lookup, keep it in an extra column, but do not let every carrier become a separate reconciliation method.

Remittance-down matching runs the other direction. Start with the carrier advice, find the claim number, RO number, customer, vehicle, remittance date, and paid amount, then locate the bank deposit that funded those rows. This route is useful when the shop has downloaded a carrier report before the bank feed has been reviewed, or when a batched deposit contains several claims and the remittance detail is the only clear way to split it.

A match is complete only when three facts agree: the cash reached the bank, the carrier remittance explains what the payment covers, and the RO ledger shows the receivable that should be relieved. The general bank statement reconciliation process proves the bank balance, but carrier cash application adds a second layer: the shop has to prove which repair orders the deposit closed.

Do not treat payer recognition as RO matching. A QuickBooks bank feed can identify a recurring carrier deposit, and a shop-management export can show open receivables, but neither one alone proves the remittance allocation. The workpaper should make the evidence visible row by row: bank line at the top, remittance detail in the middle, RO posting status at the end.

Treat multi-RO deposits and split-payer receipts as normal

One carrier EFT paying several repair orders should be modeled as a normal case. Put the bank deposit on one header row or control line, then create allocation rows for each RO covered by the remittance advice. Each allocation row should carry its own RO number, claim number, customer, paid amount, variance, and posting status. The allocation total must equal the bank deposit amount before the deposit is marked reconciled.

If the allocations do not add back to the deposit, leave the residual open. Do not hide it in the nearest RO, a miscellaneous income account, or a rounding adjustment unless the evidence supports that treatment. A residual might be a missing RO, a carrier adjustment, a fee, a duplicate remittance row, or a payment that belongs to a different location. The workpaper should show that the difference exists and who owns the follow-up.

Split-payer receipts need the same discipline. In collision repair, the carrier and the customer often pay different portions of the same job. A PYMNTS interview on collision repair cash-flow complexity describes the receivable pattern: the insurer typically pays the shop less the deductible, while the customer pays the deductible separately, and those payments can arrive at different times and through different methods such as insurer ACH and customer credit card.

That means the carrier-paid amount is not supposed to equal the full RO total when a deductible is outstanding. The workpaper needs separate fields for expected deductible, deductible received, receipt method, receipt date, and remaining customer-pay balance. A carrier EFT can be fully matched and still leave the RO open if the customer portion has not been collected.

Keep deductible gaps separate from short-pays. If the carrier EFT is lower than the carrier-approved amount, the difference belongs in a short-pay or supplement review. If the carrier paid the approved carrier portion and the customer has not paid the deductible, the issue is customer-pay collection. Treating both as one generic variance makes AR aging harder to read and weakens carrier follow-up.

Use QuickBooks and CCC Payments for what they can prove

Integrated payments can reduce the amount of manual evidence-gathering a shop has to do. If the shop uses CCC Payments through the CCC environment, payment processing and repair-order context may sit closer together than they do in a fully manual workflow. That helps, but it does not eliminate the accounting control. The bookkeeper still needs to know whether the bank deposit, remittance detail, RO balance, and accounting posting agree.

QuickBooks bank rules solve a narrower problem. They can classify a recurring carrier deposit by description, payer name, or amount pattern. That is useful for consistent coding, especially when the same carriers pay every week. But a bank rule does not read the carrier remittance advice, split one EFT across multiple ROs, identify which deductible is still unpaid, or explain why the carrier payment is lower than the billed amount.

A practical posting pattern keeps those jobs separate. First, match the bank deposit to remittance detail and RO allocations in the workpaper. Then post the AR offset against the correct customer, job, invoice, or repair-order structure used by the shop's accounting process. For customer-pay receipts, undeposited funds may be part of the card or cash deposit workflow, but that is a deposit-clearing step, not proof that the carrier side of the RO is paid.

For shops without full payment integration, the extraction-augmented spreadsheet is often the workable middle ground. The bookkeeper can extract carrier remittance PDFs and bank statements to Excel, then use the spreadsheet to compare bank rows, carrier remittance rows, and RO ledger fields side by side. Invoice Data Extraction supports that input-building job through a prompt-based workflow: upload the financial documents, describe the fields needed, and download structured Excel, CSV, or JSON output with source file and page references.

The matching decision still belongs to the bookkeeper. Extraction reduces retyping and makes the evidence easier to compare, but it should not be treated as carrier-portal access, a CCC ONE replacement, or an accounting-system posting engine.

Keep exceptions visible instead of burying them in the deposit total

The value of the workpaper is clearest when something does not match. Common exception buckets include a bank deposit with no remittance found, a remittance advice with no matching bank deposit, a carrier payment below the billed or approved amount, a multi-RO deposit with an unidentified residual, an unpaid customer deductible, a duplicate posting, and stale unapplied cash.

Each exception needs a reason field and an owner. A short carrier payment should go to the supplement and short-pay reconciliation workflow when the variance is not explained by a deductible, approved write-off, fee, or documented adjustment. A remittance row with no bank deposit should stay open until the cash clears or the carrier confirms the payment was voided, reissued, or sent through another channel.

Unmatched residuals should not be hidden inside the nearest repair order. If a ten-RO carrier EFT is short by a few hundred dollars, forcing the difference into one RO may get the deposit total to tie, but it corrupts the job record and weakens follow-up. The better treatment is to leave the residual as an open item with the source file, page reference, suspected cause, and next action.

Customer-pay exceptions need their own review path. A deductible may be collected by card, cash, check, or online payment on a different day from the carrier EFT. If the card batch is unclear, the issue may overlap with merchant deposit review, but the carrier cash-application workpaper should still show whether the deductible expected on the RO has been received.

Use the same discipline for payment reconciliation exceptions that you would use in any cash-control process: classify the difference, keep the evidence attached, and avoid clearing a row until the business reason is known. The point is not to make the spreadsheet look clean. The point is to make unresolved cash visible enough that it gets resolved.

The finished workpaper should close AR and show carrier behavior

A completed carrier EFT reconciliation should produce more than a tied bank deposit. It should show posted AR offsets by RO, deductible status by job, unresolved short-pay or supplement follow-up items, and a clean list of unapplied cash. Each closed RO should have three forms of evidence: bank cash received, carrier remittance support, and accounting posting status.

That structure also makes review faster. A manager, controller, or outsourced bookkeeper can filter the workpaper by carrier, location, deposit date, RO status, or exception owner. Open items no longer sit inside a general AR aging report with no explanation. They carry a reason: waiting on customer deductible, carrier short-pay, missing remittance, unapplied deposit, supplement pending, or posting review needed.

Over time, the same rows become useful carrier intelligence. The shop can see which DRP carriers pay quickly, which ones create frequent short-pay follow-up, and which deposits require the most manual allocation. That can feed carrier-level AR aging and DSO review without turning the cash-application file into a full benchmarking model.

The extraction layer helps because it reduces the amount of retyping between bank statements, remittance PDFs, and RO-related exports. The bookkeeping judgment remains human: deciding whether two records truly match, whether a variance is deductible-related or carrier-related, and whether the accounting posting closes the right receivable.

For month-end, the standard is simple to audit: every closed RO has evidence, every open item has a reason, and every carrier deposit ties back to the bank.