3PL Billing: AI Reconciles Storage and Pick Fees Against WMS Data

A 3PL can reconcile fees by exporting the WMS activity for the billing period, putting each client's rate card into a clean fee table, having a spreadsheet recompute every charge, and asking AI to draft the formulas and explain the variances. Anything outside an agreed tolerance goes to a person before the invoice leaves.

The AI never produces the money figure. Spreadsheet formulas do. What AI is good at is the dull middle: reading a messy rate-card PDF, mapping WMS charge codes to fee lines, writing the variance formulas, and turning 300 flagged rows into six sentences a client can follow. Billing mistakes are usually mapping mistakes, not arithmetic mistakes.

Follow me on Instagram@sagnikteaches

Why storage and pick fees drift away from the WMS

Invoices are built from three sources that rarely agree: the contract (rate card and any side letters), the WMS (what physically happened) and the billing module or spreadsheet (what someone keyed or configured). Drift starts when any one of them changes and the others do not.

Connect on LinkedInSagnik Bhattacharya

Typical causes in a small 3PL:

Subscribe on YouTube@sagnikteaches

• Rate changes not loaded. A client agrees a lower pallet rate from 1 March, but the billing sheet still carries the old one until someone notices in May.
• Charge codes that mean two things. The WMS has a "PICK" code used for both each-picks and case-picks. The rate card prices them differently.
• Storage snapshots on the wrong day. Pallet positions counted on the first of the month while a large inbound arrived on the second give a different bill from a month-end count.
• Minimums and tiers. A minimum monthly storage charge, or a tier that drops the pick rate after 5,000 lines, is easy to leave out of a hand-built sheet.
• Manual adds. Relabelling, kitting and returns handling are often keyed from a whiteboard, not the WMS.

Undercharging is quiet; overcharging produces a dispute. A reconciliation catches both, and the second kind protects the relationship. If you quote on the same fee structure, the 3PL pricing calculator tutorial covers the rate-card side.

The main example: one client's month at a 12-client 3PL

Illustrative only. Take a 3PL running one site with 12 clients, a WMS that exports to CSV, and a billing clerk who spends about two days each month building invoices in a spreadsheet. The largest client, a home-goods seller, has this rate card:

For the month, the WMS export shows 6,310 orders shipped for this client, four weekly storage snapshots and a few hundred rows of "other" activity. The clerk's sheet billed this client $16,962. The reconciliation will recompute the total independently and compare line by line.

Step 1: turn the rate card into a fee table

Rate cards live in PDFs, Word contracts and emails. Paste the relevant clauses (not the whole contract) into an AI assistant and ask for a structured table. Use a prompt like this:

Below are the pricing clauses from a 3PL client contract. Turn them into a table
with these columns: fee_code, description, unit_of_measure, rate_usd,
effective_from, effective_to, tier_from_qty, tier_to_qty, minimum_charge_usd,
and source_clause (quote the clause number). If anything is ambiguous, do not
guess: add a row to a separate list called QUESTIONS with the exact wording
that is unclear.

[paste clauses]

Illustrative output for the home-goods client: eight rows, plus two questions. One question might be "Clause 4.2 says handling is charged 'per order' but clause 7.1 says 'per shipment'. Is a split shipment one charge or two?" That is a real contract gap, and finding it before billing is worth the whole exercise. Settle it with the client in writing, then record the answer in the fee table.

Check the table against the original yourself. Read every rate against the PDF once. This is the one place a wrong number repeats on every invoice, so it deserves ten minutes of human eyes.

Step 2: map WMS charge codes to fee lines

Export the period's activity from the WMS. Most systems can produce a transaction or billing-activity report as CSV; if yours cannot, ask the vendor which table holds picks, receipts and adjustments. Keep only the columns you need: client code, date, transaction type, SKU, quantity, unit of measure, location type, order number.

Then send the AI the distinct transaction types and ask it to propose a mapping:

Here are the distinct transaction_type and uom values from a WMS export, with a
row count for each, and the fee table for one client. Propose a mapping from
each transaction_type + uom combination to a fee_code. Mark any combination
where more than one fee_code could apply as AMBIGUOUS and say what extra
column would settle it. Do not map anything you cannot justify.

Illustrative output: "PICK + EA maps to EACH_PICK_FIRST or EACH_PICK_EXTRA depending on whether it is the first unit on the order line; add order_line_id and a running unit count. PICK + CS maps to CASE_PICK. ADJ + EA is not billable unless the reason code is RELABEL." The mapping is the real work product. Save it as a tab in the workbook so it is reused next month and reviewed only when the WMS configuration changes.

Step 3: let formulas, not the AI, recompute the charges

Ask the AI for formulas, then test them. For the each-pick split above, a typical request is:

I have a sheet called Picks with columns: client, order_line_id, qty.
I have a sheet called Rates with fee_code in column A and rate_usd in column B.
Write Excel formulas that, per order line, charge the first unit at
EACH_PICK_FIRST and the remaining units at EACH_PICK_EXTRA, and then total by
client. Explain each formula in one sentence.

The assistant will typically return something like a per-line charge of =Rates!B2 + MAX(0, C2-1)*Rates!B3 and a SUMIFS total. Test it on lines where you know the answer. A line with quantity 1 should cost $0.60; quantity 4 should cost $0.60 + 3 x $0.20 = $1.20. If those match, fill down. For a gentler introduction to prompting for formulas, see the ChatGPT and Excel guide.

Apply the tier and minimum rules in a separate summary step, after the line charges, so you can see them. A minimum charge is a MAX of the calculated total and the minimum, plus a visible line saying "minimum applied: $X top-up". Hidden minimums are what clients dispute first.

Step 4: the storage snapshot check

Storage is where money hides. Compare three numbers per client per week: pallet positions or bins in the WMS snapshot, the count billed, and the count in the previous week. The AI is useful for the comparison commentary:

Here are four weekly storage snapshots and the billed quantities for one client.
List any week where billed quantity differs from the snapshot, any week where
the snapshot moved by more than 15% from the previous week, and for each one
suggest the most likely operational reason (large inbound, return to stock,
stock moved to another client, snapshot taken part-way through a putaway).
Label these as hypotheses.

Illustrative output for the home-goods client: weeks one and two show 184 pallet positions; week three shows 211 and week four 209. The sheet billed 184 for all four. The tool flags weeks three and four as under-billed by 27 and 25 positions, and suggests a large inbound in week three. Check the receipts for that week. If a 27-pallet receipt arrived, the under-billing is $4.50 x (27 + 25) = $234. Small for one client, but across 12 clients it adds up.

The variance report you actually send to a person

Once recomputed charges sit beside billed charges, build one report per client with a fixed layout. A reviewer should see the answer in ten seconds:

All figures here are made up for illustration. Add the columns and the recomputed lines come to $17,700 against $16,962 billed: the client was under-billed by $738 for the month, about 4% of the invoice. The pattern to read from it: three lines outside tolerance, each with a reason code the clerk picks from a short list (RATE for wrong rate loaded, MAP for wrong charge-code mapping, QTY for wrong quantity, MANUAL for a hand-keyed item, CONTRACT for a clause that needs a decision). After a quarter, a count of reason codes tells you where to fix the process, not just the invoice.

Ask the AI to draft the commentary under each flagged line, with the row-level evidence listed beneath:

Write two sentences per flagged line for an internal reviewer. State the
variance, the most likely cause from the reason code, and the first three
order numbers to check. Do not state a cause as fact if it is only a
hypothesis.

Look at the handling variance of +$328 across 6,310 orders. That is about $0.052 an order, so a rate difference is more likely than a count error. Try it: 6,310 x $1.20 = $7,572, close to the $7,560 billed. Someone loaded $1.20 instead of $1.25, and the remaining $12 is ten orders at $1.20 that were never charged at all. Two small causes, not one, and the line needs both fixed.

Where the AI misleads you, and how to catch it

Three failures recur in this work.

Confident wrong mappings. The assistant will happily map an unfamiliar transaction code to a plausible fee. Keep the "AMBIGUOUS" instruction in the prompt and look at every row it marks, plus a random sample of ten it did not mark.

Silent row loss. Pasting a large CSV into a chat window can truncate it without warning. Always ask the assistant to report the row count it received and compare it with the file. Better, do the heavy recompute in the spreadsheet and send the AI only summaries and flagged rows.

Plausible explanations treated as findings. "Likely a large inbound" is a hypothesis. The finding is the receipt document that proves it. Make "check the source record" a required column before a variance is closed.

Dispute handling: turning a client's query into a short evidence pack

When a client questions a line, speed and clarity win. A good response contains the clause, the WMS rows, the calculation and the offer. Build that from a template so every dispute looks the same:

Client query: [paste the client's email, with names removed]
Rate card clause: [paste clause]
WMS rows for the disputed period: [paste up to 30 rows, redacted]
Recomputed charge: [figure from the sheet]

Draft a reply to the client in a calm, factual tone. Structure: what we
charged, the clause that sets it, the rows that support it, our calculation
in three lines, and either "we believe the charge is correct" or "we found an
error of $X and will credit it on the next invoice". Do not apologise unless
we found an error. Address them as [first name].

Illustrative case: a client says they were charged for 40 case picks they believe were each picks. The rows show 40 PICK + CS transactions with a case quantity of 12 units each, which matches the case-pick clause. The AI drafts the reply and attaches three example order numbers. The reviewer checks the three orders in the WMS, sends it, and the query closes the same day. The version that does not work is a long defensive email with no rows in it.

If you find the client is right, credit it quickly and tag it. Slow credits cost more goodwill than the sum itself.

Disputes that are really contract gaps

Some disputes are not errors at all; the contract is silent or contradictory. Split shipments, returns put away to a different location type, and storage of dead stock are the common ones. Keep a running list of these with the agreed interpretation, and ask the AI each quarter to draft a short amendment letter that records them. Get the client to sign it. This turns repeated arguments into a rule.

Receiving, accessorials, returns and lot-controlled stock

Receiving and putaway

Receiving is often charged per pallet or per carton received, with a surcharge for mixed pallets. A reconciliation compares advance shipping notice lines with received lines. Ask the AI to list receipts where the received pallet count differs from the notice, since the billed figure should follow what was actually received. In an illustrative month, 3 of 41 receipts show differences; two were correct re-pallets and one was a keying error that over-billed by four pallets.

Accessorial and manual items

Relabelling, kitting and special projects are the easiest items to lose. Export the manual-adds list and ask the AI to compare it with the labour sheet: any project with more than, say, two labour hours logged and no billed line is a candidate for a missed charge. It is a prompt for a conversation, not proof, because some projects are free by agreement.

Returns processing

Returns handling usually has a per-unit fee plus a disposition fee (restock, scrap, refurbish). Ask the AI to cross-check the returns report against the disposition codes: a "scrap" unit should carry the scrap fee, not the restock fee. This two-column check is quick to build and rarely done by hand.

A dental-supply client with expiry-date stock

One client sells consumables with expiry dates and needs lot-controlled storage at a premium rate. The mapping question is whether every location holding lot-controlled stock was billed at the premium rate. Export locations by type and ask the AI for a formula that flags any lot-controlled SKU stored in a standard bin. The fix is often a location flag in the WMS, not an invoice change.

A realistic mistake: the rate that was right for the wrong client

One failure is worth showing because the variance report cannot see it. Say a busy onboarding month leaves two clients whose trading names differ by a single word, sitting next to each other in the rate sheet. The billing clerk copies the first client's pallet rate of $4.50 into the second client's row, but the second client's contract says $3.90. Every recompute that reads from the clerk's own rate sheet agrees with the invoice, so the variance is zero and the error survives for six months.

The fix is to make the fee table the only source of rates and to build it from the contracts, not from the billing sheet. Then add one control that does not depend on the sheet: once a quarter, ask the AI to compare the contract-derived rate for each client and fee line against the rate actually billed in the last invoice, listing every mismatch. In the illustration that single check shows 26 weeks of storage on 40 pallet positions at a $0.60 difference, or $624 over-billed. The credit note is awkward, but it is far cheaper to send it yourself than to have the client find it.

The wider lesson: a reconciliation is only as independent as its inputs. Where the check and the invoice read the same rate cell, they will always agree, right or wrong.

Making the reconciliation a monthly routine

Run it in this order each billing cycle. Expect the first run to take most of a day, the second half a day, and later runs about two hours once the mapping is stable:

1. Export WMS activity and the four weekly storage snapshots.
2. Refresh the Picks, Storage and Receipts tabs; check row counts against the export.
3. Confirm the fee table is current: any rate changes, new clauses or side letters since last month.
4. Recompute charges and build the variance report per client.
5. Review every line outside tolerance; assign reason codes; correct the invoice or the mapping.
6. Spot-check five lines inside tolerance for each of your three largest clients.
7. Send invoices with a one-page supporting schedule for the big clients.
8. Log disputes and credits with reason codes; review the counts quarterly.

Send the supporting schedule. Clients who can see the pick count and storage positions behind a number tend to ask fewer questions, and your reconciliation becomes a selling point. If you also send a weekly performance report, the 3PL client KPI report tutorial uses the same WMS exports.

What to measure to know it is working

• Credit notes per month. The count and value should fall after two or three cycles.
• Days to produce invoices. From two days towards a few hours.
• Reason-code mix. If MAP dominates, fix the mapping; if RATE dominates, fix how rate changes are loaded.
• Disputes closed within a day. Evidence packs should shift this up.
• Recovered revenue. Add up the under-billed items you corrected before sending. Track it, but do not promise a number to anyone in advance.

Pair the billing check with good stock-count discipline: the cycle count planning tutorial keeps the quantities your storage fees depend on trustworthy. For wider context on where billing sits among other jobs, see six AI jobs ranked by payback for a small 3PL.

Privacy and client data when using AI on billing files

Billing files contain client SKUs, volumes and sometimes consignee details. Remove consignee names, addresses and phone numbers before anything goes into an AI tool; billing checks do not need them. Use a business-tier assistant whose terms say it does not train on your content by default, and read your client contracts for restrictions on third-party processing. Where a client's contract is strict, run the recompute in the spreadsheet only and use the AI for the formulas and the wording, with made-up sample rows.

Keep the fee table, the mapping and the reason codes in a shared workbook with version history. When the clerk is away, the checks should still run.

Questions from 3PL finance and account teams

Should the AI calculate the invoice itself?

No. Let the AI draft the checks, the formulas and the variance commentary, but let a spreadsheet or your billing module produce the numbers. Language models are unreliable at long arithmetic across thousands of rows. Ask the AI to write the formula, then confirm it on five lines you work out by hand.

Is it safe to paste client order data into an AI tool?

Strip what you do not need first. Billing checks need SKUs, quantities, dates and locations, not consignee names or addresses. Use a business plan that does not train on your content by default, and check your client contracts for rules on where their data may be processed.

How often should we reconcile?

Run the pick and handling check on every billing run, and the storage check on the same day your storage snapshot is taken. A monthly invoice reconciled only at month end hides weeks of drift. A weekly mini-run on the largest two or three clients catches most problems early.

What if the WMS export and the invoice never match exactly?

A small residual is normal. Agree a tolerance, for example under 0.5% of the invoice or under $25 a line, and investigate only what falls outside it. Record every adjustment with a reason code so the pattern shows up over a quarter.

Further reads

• 3PL Quote: What 3PLs Charge and How to Compare Every Fee With AI — See every fee type a client expects before they sign.
• AI Tools for Warehousing: Which Suit a Small Warehouse or 3PL? — Compare AI tools that suit a small warehouse or 3PL.
• Questions to Ask a 3PL About Its AI and Automation: A Checklist — Questions to put to a 3PL about its own automation.
• 3PL Client Onboarding Checklist: AI-Drafted SOPs and SLAs — The 3PL's own onboarding checklist for a new client: what to collect, how AI drafts the client SOP and SLA, and who signs what before the first pallet arrives.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: general 3PL billing practice (storage, handling, receiving and accessorial fee structures); client rate cards are supplied by the reader.