Close

Practical guide

How to reconcile manufacturer commission statements with AI

Every principal sends a different commission statement. How rep agencies can tie them out with Excel, rep software or AI, and catch short pays.

You carry a dozen or more lines. Every month each manufacturer sends a commission statement in its own format: a PDF, an Excel file, a portal export, sometimes nothing at all.

Someone in your office spends days matching those lines to what you booked and what shipped. This guide covers how to do that faster, and how to catch the commissions that never show up.

Why is tying out commission statements so hard?

Because the statement and your records don’t speak the same language. Your booking log has your customer, your PO number and the date you sold it. The principal’s statement has their invoice number, their name for the customer and the date the customer paid.

Lines paid on wholesalers’ point-of-sale reports add a third source: the wholesaler’s sale, not the factory’s shipment.

Rep software vendors know this well. Repfabric, for example, sells a wizard for keying in “those ugly un-importable reports” by hand.

Timing makes it worse. In a MANA survey of manufacturers, about a quarter paid commissions on invoice, while about two-thirds released them only after the customer paid.

So the commission on an order you booked in March can show up months later, mixed in with hundreds of other lines. MANA says the survey, with close to 200 responses, wasn’t statistically accurate, but most agency owners will recognize the pattern.

What does a missed commission cost you?

Nobody publishes a reliable number for how often principals’ statements come up short. That’s the point: unless you match every line, you can’t know your own number.

What is clear is that the money is worth chasing. Texas’s Sales Representative Act, which covers wholesale orders solicited in Texas, makes a principal that fails to pay commissions as required liable for three times the unpaid commission plus reasonable attorney’s fees.

There’s a catch: in a 2019 case brought by a sales rep against a gearbox manufacturer, the Texas Supreme Court held that tripling applies only to commission still unpaid when the court decides, not to commission paid late. The rep could still recover reasonable attorney’s fees. Oklahoma, Arkansas and other states have their own rules.

So the law is a bargaining chip, not a windfall: a documented shortfall gets paid faster than a hunch. This isn’t legal advice, so ask your attorney about your own agreements.

The other cost is time: days each month from your office manager or controller, and late, error-prone split payments to your own outside reps.

What should you fix before buying anything?

What can Excel already do?

More than most agencies use. If your Excel has Power Query (look under Data, Get Data, From File, From PDF), it can pull tables straight out of PDF files with its PDF connector, and its Folder connector can combine a whole month of statements at once. Build one query per principal that reshapes their statement into your standard columns, then merge it against your booking log.

That works well for PDFs a computer created. It struggles with scanned statements and layouts that change, and someone has to maintain a query for every principal. For an agency with a handful of lines, it may be all you need.

Which off-the-shelf apps handle this?

Rep agency platforms include commission tools:

Both are worth a demo, especially if you’re choosing a CRM or order system anyway. Ask each vendor to run your three messiest statements, not their sample files. The real work is getting 15 different layouts into one template.

Whoever you choose, ask where your statements are stored and whether your data is used to train AI models.

When does a custom build make sense?

When most of your statements arrive as PDFs in layouts that change, when you need to match against factory shipment data as well as your bookings, or when you want to keep the CRM and accounting setup you already have.

AI can now read a statement it has never seen and pull out each line. The value of a custom build is in the matching rules and the exceptions list, built around how your agency already works.

How do you set it up, step by step?

  1. Collect three months of statements from every principal, plus your booking log and deposit records.
  2. Define one standard row: principal, customer, ship-to, your PO number, the factory’s order number, invoice number and date, product line, territory, sales amount, commission basis, rate, commission, paid date and notes.
  3. Map each principal’s layout to that row once. AI does the reading; a person confirms the mapping.
  4. Check every statement against its own totals. The lines read must add up to the statement total, and the statements to the deposit. If they don’t, a person looks before anything gets matched.
  5. Set matching rules. Exact match on invoice or PO number first, then customer plus amount within a date window, with a small rounding tolerance.
  6. Build the exceptions list: shipped but unpaid past your window, a rate that doesn’t match the agreement (after allowing for special-pricing and reduced-rate jobs), short pays, chargebacks, returns and credit memos netted against commission, split credit on jobs specified in one territory and shipped to another, and customers you don’t recognize.
  7. Send each principal a dispute list every month, with the order documents attached. Then run your own reps’ splits from the matched data.

What could this look like for a real agency?

This is an illustration, not a client story. The numbers are made up.

Say you run a 9-⁠person plumbing and HVAC agency in Houston, carrying 14 lines across Texas and Oklahoma. Each month brings 14 statements and about 1,800 lines: six PDFs, five Excel files and three portal exports. Your office manager spends three days matching them by hand and usually only gets through the big lines.

With AI reading every statement into one format and matching it against your bookings, the monthly job can become a review of the exceptions, say 60 lines. That might take a morning.

The bigger change is coverage: every line from every principal gets checked, not just the large ones. And when a principal shorts you, you have the order, the shipment and the statement side by side.

When should you just buy an app?

Buy if most of your principals already send Excel or portal exports, you’re happy to run the agency on that platform’s CRM, and the vendor handles your three ugliest statements in the demo.

Build when PDFs dominate, your split rules are unusual, or you’d rather keep the systems you have.

Want a second opinion?

BlueHill is based in Spring, in north Houston, and works with clients over video and in person around Houston. The Document Matcher package reads your statements and ties them out against your records, so your team reviews only what doesn’t match. It’s a one-time setup, and the price is fixed in writing after a free call and a 90-⁠minute walk-through of how your work gets done ($500, credited toward the build). It typically takes three weeks and is built to work with your accounting system, ERP or spreadsheet. We confirm yours on the call.

You can own it and run it yourself, or BlueHill can run it for you for a monthly fee that covers fixes when a principal changes its format, new lines as you take them on, and a one-page monthly report. AI drafts the dispute lists and rep statements; a person approves anything before it goes out. See how it fits rep agencies, or book a free 30-⁠minute call.

Bring the job you read about.

Bring one task to a free 30-⁠minute call. We’ll talk through whether AI fits, what it would take and how to get started.

Book a free 30-⁠minute call

Free · 30 minutes on video · no obligation · no tech knowledge needed