Home · Solutions · Sales & marketing
Solution · Sales & marketingEvery commission line carries its own proof, before anyone has to ask for it
Sales commissions calculated and explained
Robots build each statement from billing, payments, CRM attribution and versioned plan rules, and every line shows the transaction, the rule and the arithmetic behind it.
Executive summary
Commission is calculated in one enormous workbook, and every dispute means rebuilding it.
We turn the commission cycle from a file somebody opens into a job that runs.
Statements arrive on a fixed date with the same explanation for everyone, so the cycle stops depending on one analyst's availability.
Microsoft SharePoint statement library; the payroll import file; Microsoft Power BI semantic model
Business problem
Incentive compensation
A commission statement is a promise with a formula behind it, and the formula sits across three systems that were never built to agree. Billing knows what was invoiced, credited and collected. The CRM knows who owned the deal and how it was split. The plan document holds the rates, the tiers, the caps and the treatment of a cancelled policy. Nobody owns the join, so a person performs it by hand every month in a workbook.
That workbook grows predictably. A new product adds a column, a mid-year plan change adds a tab, and every exception the business creates, a parental leave, a transfer between teams, a house account, is settled by typing a value over a formula. After two years only its author can run it.
The cost lands on the sales floor and in payroll. A statement showing a total but not a derivation cannot be checked, only trusted or argued with, so queries arrive as replies and chat messages with no register and no due date. Late statements then push corrections into the following month.
How it works today
The pattern below turns up wherever more than a handful of people are paid on variable compensation.
- PersonThe commission analyst exports invoices, credit notes and cleared payments from the ERP into a workbook, one tab per period
- PersonDeal ownership, splits and product codes come out of the CRM and are pasted alongside, matched on customer number and date
- Risk of errorRows that do not match, renamed customers, credit notes with no traceable original, are corrected by hand and recorded nowhere
- PersonPlan rules are applied through nested formulas and typed-over values for leavers, transfers and house accounts
- WaitingThe sales director reviews a summary tab and approves by email, six to ten working days after the period closed
- PersonStatements are exported one at a time and sent to 220 people from the analyst's own mailbox
- PersonQueries come back as replies and chat messages, each investigated by reopening the workbook, and anything found late waits for the next cycle
Why the current process costs more than it appears
Behind every exception is an hour nobody logged.
- Every query reopens the entire calculation. A statement is a number, not a set of explained lines, so answering one question means rebuilding evidence from three systems, ten or fifteen times a cycle.
- Sales capacity is the expensive half. When a team leader spends an afternoon reconstructing his numbers in a private spreadsheet, the company pays twice: for the calculation, and for the distrust of it.
- Corrections arrive late, and a line fixed after the payroll cut-off is paid a month afterwards. A small arithmetic error becomes a fairness argument, and in several countries a payroll correction of its own.
- Nobody can reconstruct a payment made two years ago. The plan sits as a PDF in a folder, the workbook has been saved over many times, and the reasoning behind an amount survives only in formulas since rewritten.
Cost of inaction
The visible number is the smaller part. What continues alongside it is the pattern: statements that land late, corrections a month after the mistake, and the best people spending selling hours proving their own numbers. Those appear not as budget lines but as overtime at period end and as deals nobody worked.
The exposure that grows quietly is evidentiary. Commission is contractual pay, and pay has to be explainable. If a former employee, a works council or an auditor asks in three years how one amount was reached, the answer has to be rebuilt from a workbook saved over hundreds of times.
A plausible organisation with realistic proportions. The figures are there to be recalculated on your data; they are not a client result.
A European insurance broker and distributor with 220 commissioned staff across five countries: agents, account executives and team leaders. Billing runs in SAP, attribution in the CRM, the tenant is Microsoft 365 E3, and commission is calculated centrally by two people in finance.
220 statements a month over roughly 9,000 commissionable transactions and four plan types: new business, renewal, cross-sell and a team override. Tiers, caps, splits and clawbacks on early cancellations all apply.
One master workbook joins the SAP extract, the CRM extract and the plan tables. Preparation, checking, sending and query handling come to about 25 minutes per participant, signed off by email.
The join itself. Unmatched rows consume the first week and disputes the second, and the payroll cut-off arrives whether or not the cycle is finished.
Robots collect billing, payment and attribution data on a schedule, apply the approved plan version and write one explained line per transaction. Statements are published per person and announced in Microsoft Teams, queries become tracked tasks with evidence, and sales management releases the cycle before anything is paid.
In the modelled case analysts stop assembling data and start reviewing exceptions, statements land on a fixed date, and most queries are answered by the statement itself. Those figures are a model, not a measurement.
Proposed solution
We turn the commission cycle from a file somebody opens into a job that runs. On a fixed day, robots pull invoices, credit notes and cleared payments from SAP and deal ownership, splits and product data from the CRM. The plan stays in an Excel workbook that finance and sales management own, but it stops being a calculator and becomes a rule table: rate tables, tier bands, split and override rules, each row valid between two dates. The robot validates it first, and a version with overlapping ranges, out-of-bounds rates or products with no rule stops the cycle and raises an alert.
Explainability is the design, not a reporting extra. The engine writes one record per commissionable transaction carrying the source, the attribution, the rule and the arithmetic: invoice or policy number, customer, booking and payment dates, the deal, the owner and why their split applies, the plan version, the rate-table row that fired and the amount. The statement renders those records, and the same records answer a query, which is why the answer takes a minute rather than a morning.
Distribution, disputes and release run where the sales force already works. Each person gets a statement in a personal SharePoint library and a Microsoft Teams message with the total and a link. Querying a line opens a task in UiPath Action Center carrying that line and its evidence, with a due date and an escalation to the sales manager when a split or a rate is contested. An accepted query produces a dated correction line with a reason code rather than an edit of history.
UiPath Orchestrator time triggers, queues and audit; UiPath Data Fabric (formerly Data Service) entities with audit history; UiPath Action Center tasks completed in Microsoft Teams; UiPath Integration Service connectors for Microsoft OneDrive & SharePoint and Microsoft Teams; Microsoft SharePoint permissions with Microsoft Purview retention; Microsoft Power BI semantic models
The plan rule model and its validator, the calculation engine with the line-level explanation record, credit-note and clawback treatment, the statement renderer, the per-person publication, the dispute workflow, the release approval and the payroll export
SAP billing, credit-note and cleared-payment extracts through UiPath SAP activities (BAPI and OData); CRM attribution through the UiPath Integration Service connector for Salesforce or Microsoft Dynamics 365 CRM; the payroll import file in your payroll system's format
How the automated process works
- AutomationA time trigger in Orchestrator starts the cycle on the agreed day; robots collect billing, credit notes and payments from SAP and ownership, splits and product data from the CRM
- AutomationThe plan workbook is validated first, and a version that fails the check stops the cycle and alerts the plan owner instead of producing statements
- AutomationEvery transaction is matched to an owner and a rule, and one explained line is written with the source, the attribution, the plan version, the rate and the arithmetic
- PersonTransactions with no owner, splits that do not add up and products with no rule become Action Center tasks with the evidence attached
- PersonSales management releases the cycle in Microsoft Teams after reviewing totals by team, the exception list and the movement against last month
- AutomationStatements are rendered, filed into each person's SharePoint library under their own permissions and announced in Teams; the released cycle also produces the payroll file and refreshes Power BI
- PersonA participant queries a line from that message; the query becomes a tracked task with an owner, evidence and a due date, closed by a dated correction
Human-in-the-loop model
Automation handles
- Collecting billing, payment and attribution data and matching every transaction to an owner and a rule
- Applying the approved plan version: tiers, caps, splits, team overrides and clawbacks on cancelled business
- Rendering statements, publishing them per person under the right permissions and generating the payroll file
- Reminders, due-date tracking on open queries and the monthly reporting refresh
People decide
- The plan itself: rates, tiers, thresholds and who sits on which plan, owned by sales management with finance
- Whatever the rules cannot settle: a deal with no recorded owner, a contested split, a goodwill payment
- Whether a cycle is released, in one approval standing between the calculation and anyone's pay
- The outcome of every query, recorded as a decision with a reason rather than a silent edit
Before and after
Systems and integrations
The stack is deliberately short: one engine, one execution layer, one place where a person decides.
Inputs
- SAP billing, credit notes and cleared payments
- CRM deal ownership, splits and product data
- the plan workbook in Microsoft Excel on SharePoint
- the participant list from the HR system
Automation layer
- UiPath Orchestrator
- UiPath Robots
- UiPath Integration Service
- UiPath Data Fabric
- UiPath Action Center
Target systems
- Microsoft SharePoint statement library
- the payroll import file
- Microsoft Power BI semantic model
Human touchpoints: the statement message in Microsoft Teams; Action Center query and exception tasks in Teams; the release approval by sales management
Technologies used
run the cycle on a schedule, queue participants, retry, log and hold the audit record
Aplan versions, statement lines, corrections and dispute history as governed entities with audit
Aexception tasks, queries and the release approval, completed without leaving Teams
Areads the Microsoft Excel plan workbook, collects attribution, publishes statements, posts notifications
Aper-person statement library with permissions, versioning and Microsoft Purview retention
Acommission cost, plan mix and dispute reporting for sales and finance management
Ainvoices, credit notes and cleared payments as the calculation base
AIllustrative economic model
Numbers you can check against your own data.
The 25 minutes is not typing time; it is the monthly cost per participant spread across extracting and joining data, resolving unmatched rows, applying overrides, checking, sending and answering queries. Those minutes illustrate typical ranges, not a client measurement. The €35 rate is a fully loaded hourly cost for a commission analyst in Central Europe. Time the sales force spends recalculating its own statements is excluded, for want of a defensible basis.
Run the numbers on your data
An illustrative estimate from your own inputs. It models released capacity; it is not a promise of savings.
Business benefits
- Statements arrive on a fixed date with the same explanation for everyone, so the cycle stops depending on one analyst's availability
- Every line answers "why this amount" by itself, naming the transaction, the attribution, the plan version and the arithmetic, so most queries never get raised
- The queries that remain carry an owner, evidence and a due date instead of an email thread, and the outcome is written back where the next person can see it
- Payroll receives an approved file on time, so corrections stop rolling into the following month
- Commissioned staff stop keeping shadow spreadsheets, which returns selling time where it costs the most
The management view
- Commission cost becomes visible while the quarter is still running, by team, plan, product and country
- The plan is a versioned artefact with an owner and an approval behind each change, so a rate agreed in March can be evidenced in November
- Release is a control rather than a courtesy: nothing is published or paid before sales management signs the cycle off
- The process survives holidays, resignations and growth: the rules live in a validated workbook, the calculation in a scheduled job
Board-level KPIs
Security and governance
An auditor should be able to reconstruct every decision.
- A statement is personal pay data. Each is filed where only the participant, their manager and the commission team have rights, under the Microsoft Purview retention your payroll policy requires
- Robots read the ERP and the CRM through dedicated technical accounts with read rights on the relevant objects only; secrets sit in the Orchestrator credential store, or in Azure Key Vault where one is already in use
- Statements, calculation data and human tasks exist in two places: your Microsoft 365 tenant and the EU region of UiPath Automation Cloud
- Whoever can change a rule cannot also release a cycle. Every line carries the plan version it was produced under, and each correction records the person, the reason and the timestamp
Why now
Pay transparency has moved from good practice to law. Directive (EU) 2023/970 defines pay to include complementary and variable components, which is what a commission is, and it is being written into national law across the EU
Plans change more often than they used to, with new products and mid-year adjustments landing in a workbook never built to hold versions. The modelled €3,208 a month of handling is the visible cost; the retroactive corrections are the expensive one
The integration work no longer needs custom development: scheduled robots, ERP and CRM connectors, governed entities with an audit history and human tasks completed inside Microsoft Teams are documented, standard capabilities
Relevant executive roles
The plan becomes an instrument that can be changed and defended, not a workbook that resists both
Commission cost is visible during the period and reconcilable to billing afterwards, and the accrual stops being an estimate
Variable pay can be explained to an employee, a works council or an inspector from a record rather than from memory
A scheduled, auditable integration with the ERP and the CRM replaces a file that one person can run
Common questions and objections
That is the argument for a versioned rule set, not against one. Plans are dated tables that the plan owner edits and approves, so a change effective 1 January applies from 1 January, and closed cycles keep the rules they were calculated under.
Trust comes from the explanation, not the calculator. Each line names the invoice, the deal, the split and its reason, the plan version, the rate and the arithmetic. The parallel run puts the first comparison against the old numbers before anyone's pay depends on it.
Then that is the first finding, and worth having in writing. The engine never guesses: a transaction with no owner, or a split that does not add up, becomes an exception with a name and a due date on it, so a problem the workbook used to absorb becomes fixable.
When this is not the right solution
- Fewer than about fifty participants on one simple plan, where a good workbook and a monthly review cost less than an engine
- Compensation negotiated case by case with a large discretionary component; the deterministic part is then too small to justify the build
- Deal ownership is not recorded anywhere, so attribution would have to be invented rather than read; CRM discipline comes first
A question for the next management meeting
If a top performer asked today how one specific line on last month's statement was calculated, how long would it take us to answer, and would we still be able to answer it in three years?
Implementation approach
A scope without ambiguity, before anything is signed.
We deliver
- A rule model built from your current plans: rate tables, tiers, caps, splits, team overrides and clawback treatment, each dated
- The workbook validator that rejects an inconsistent plan version before a single statement is calculated
- The calculation engine and the line-level explanation record, covering credit notes, cancellations and retroactive corrections
- The statement template, the per-person SharePoint library with permissions and the Teams notification
- The query and correction workflow in Action Center and Teams, with due dates, reason codes and full history
- A parallel run over three closed cycles, then reporting and the payroll export
We need from you
- The current plan documents and three months of the workbook with the statements that were issued
- A named plan owner in sales management and a commission owner in finance, plus the participant list with plans and standing exceptions
- Read access to billing and payments in the ERP and to the CRM, with a technical account for each
Stages
Discovery
Plans, exception cases, query history and where today's numbers come from
Rule model
The plan expressed as dated, versioned tables and agreed between sales and finance
Build
Data collection, calculation engine, statement, publication, queries and the payroll export
Parallel run
Three closed cycles recalculated and reconciled line by line against what was paid
Go-live
The first live cycle under supervision, then hypercare and the next country
Departmental. Effort is driven by the number of plan variants and exception rules, by how reliably the CRM records deal ownership, and by how far back corrections must reach.
Pick one line on last month's statement and prove it without opening the workbook.
Send us one closed commission cycle: the plan documents, the workbook and the statements that went out. We recalculate it and return an exception list with the disputes it would have prevented.
Recalculate one commission cycle with usThe neighbouring process usually has the same problem
The pipeline review starts with an argument about the data, not about the deals.
View solution HR & peoplePayroll inputs consolidated and checked before the runPayroll errors are found by the employee on payday, not by the team that built the file.
View solution Management & planningThe management pack in Power BI, not fourteen Excel filesThe board pack should not depend on which analyst merged which spreadsheet on which day.
View solutionIndustries we deliver this in most oftenRetail & e‑commerceServices & ITFinance & insurance