"We were in excel hell for years. Merging sheets, manual entry, and hours of reconciliation. We tried every solution including leading AMS vendors with no luck."
The challenge
ABP Insurance’s commission process ran on one giant spreadsheet and one person’s institutional knowledge.
Like most independent agencies, ABP had no reliable way to answer a basic question: how much revenue is this agency, this carrier, or this producer actually generating each month? Their agency management system nominally offered commission accounting, but it didn’t work for their AMS data model or complex producer splits. So, like most agencies, they’d retreated to Excel.
The monthly reality was a three-week scavenger hunt. ABP works with more than 25 carriers, and each one publishes commission statements on its own schedule (the 7th, the 10th, the 25th), inside its own portal, in its own format: Excel, PDF, or paper statements that arrived by mail. ABP’s operations lead spent the first three weeks of every month pulling statements down, normalizing each carrier’s idiosyncratic layout, and entering data into their monthly spreadsheet. Formats changed without warning. A statement that processed cleanly one month would arrive the next with reordered columns and blank rows, and internal attempts to automate the parsing in Power BI turned into whack-a-mole maintenance.
Then came the merge: thousands of statement lines matched by hand against AMS reports, using policy numbers that weren’t always trustworthy. Some policies turned out to be misfiled under the wrong carrier entirely. Transaction types were no better. A carrier’s “new business” was often actually a rewrite, and a single statement could require correcting 15 to 20 misclassified lines.
The compensation logic itself was too complex for any off-the-shelf system. Producer splits varied by person, by transaction type, by producer/CSR pairing, and by policy-level overrides: audit holds, acquired legacy books, salaried-servicer accounts, cross-sell exceptions, carrier-specific carve-outs. The rules were encoded in a master spreadsheet’s formulas, were hard to audit, and the exceptions to the rules existed nowhere but in one employee’s memory. Ownership admitted to losing sleep over the fragility. One mis-keyed formula could silently misdirect producer pay for months.
And because each month lived in its own static file, ABP couldn’t trend anything: no revenue over time by producer or carrier, no way to tell whether an acquired book of business was holding or bleeding, no revenue basis for designing producer compensation. When a producer asked to see their commission history with a carrier, it was hard to give them a clear audit trail. Once commissions were finally calculated, agency leadership personally re-keyed every payment, salary plus commission, into a payroll system by hand.
The cost went beyond hours. ABP had stood up an Azure data lake specifically to enable modern reporting, but the commissions and dashboard projects behind it had stalled, and organizational plans that depended on reliable data were stuck behind them. By spring 2026, leadership had named it explicitly: solving commissions was the agency’s top priority.
The Solution
Rather than force ABP into another closed system, AccountAim worked with the agency as a design partner and rolled out in phases. Crawl, walk, run.
The crawl phase delivered value from data ABP already had. AccountAim ingested previously compiled commission statements and stood up trended reporting within days, giving the agency its first month-over-month view of commissions by carrier, producer, CSR, and transaction type. Leadership was already spotting surprising producer-level trends in the first dashboard walkthrough.
The walk phase automated intake. AccountAim’s parsing engine extracted structured data from 25+ carrier statement formats, PDF and Excel alike, and staying resilient as carriers changed layouts month to month. Now, ABP has real-time carrier statement data reconciling against their AMS data.
The run phase codified the commission logic. Working directly with ABP’s ops lead, AccountAim translated embedded formulas and tribal knowledge into a configurable rules engine that handles producer-level splits by line of business and transaction type, producer-pair combinations with different rates for new, renewal, and rewrite business, policy-level overrides for audit holds and legacy books and cross-sell exceptions, and multi-condition logic. Payout timing follows the statements, so producers get paid on what carriers actually remitted, including trailing audits and endorsements attributed to the correct policy term.
Reconciliation is built in. Every parsed line is matched against AMS data automatically, with exceptions surfaced in a filterable queue: unmatched policies, term-attribution mismatches, misclassified transaction types. The checks that previously consumed days of manual verification now run themselves.
AccountAim’s own dashboards add payroll-ready producer payout summaries (commission, salary, and bonus in one exportable view) and self-serve producer portals, ending the era of manual payroll re-keying and “can I see what I got paid on this account?” fire drills.
"AccountAim was able to quickly put into place something we’ve been trying to do for years. Working with the team was just easy.”
The Results
The headline number is time. Roughly 60 hours of manual work eliminated every month: the statement-gathering, converting, and merging cycle that consumed the ops lead’s month shrank to exception handling.
Payroll prep became a single export. Commission calculations, salary, and bonuses flow to one payroll-ready output, replacing hand-built formulas and manual keying and closing the door on the silent misallocation errors that keep agency owners up at night.
For the first time, ABP has revenue intelligence. The agency can trend revenue by producer, carrier, and book of business over time, including cohort views of acquired books that put real numbers behind acquisition decisions. Producer conversations shifted from gut feel to data-driven coaching and incentivization, informing a comp design to move upmarket.
Transparency followed. With policy-level drill-down available on demand, commission questions that once required weeks of archaeology are answered in seconds.
And the selling team sells. With commission operations off their plate, ABP’s data lead reclaimed time for revenue-producing work and the agency’s next reporting priorities, unblocking the initiatives that had been stalled behind the commissions bottleneck.
"We’re able to run our business in a much more proactive, data-driven, and strategic way thanks to AccountAim’s commission intelligence."