Collision Shop DRP EFT Reconciliation Guide

For a body shop, carrier EFT reconciliation means tying each insurance deposit to the remittance advice, the related repair order or 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.

The bank deposit is the control total. A carrier or processor payment may arrive as one ACH line covering several repair orders, and the bank description may not match the carrier name on the remittance report. Cash application is complete only when the cleared deposit, remittance detail, and RO posting agree.

Ordinary bank reconciliation proves cash cleared; it does not show which claim, vehicle, customer, or RO was paid. The remittance advice, RO ledger, and deductible record supply that detail.

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 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.

Integrated payments and bank rules still need the same control. CCC Payments may put processing and repair-order context closer together, and QuickBooks can classify recurring carrier deposits by description, payer, or amount pattern. Neither one proves which ROs the carrier paid, whether the deductible is still open, or why the carrier payment is lower than the billed amount.

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.

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.

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

For month-end review, the workpaper should show three things: every closed RO has bank, remittance, and posting evidence; every open item has a reason and owner; and every carrier deposit ties back to the bank.

Once that standard is met, the same rows can support carrier-level AR review. 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 without turning the cash-application file into a separate benchmarking model.