Monthly Commission Processing Time
2 days of manual CRM export, tier lookups, and calculation reduced to a 2-hour automated run covering 60 reps and 300+ monthly deals.
Case study
2 Days to 2 Hours: Sales Commission Calculator Automated for a 60-Person Sales Team
A distribution company's sales operations team spent two days every month calculating commissions manually from CRM exports. Disputes delayed payroll. Reps didn't trust the numbers. We built an automated Excel commission engine that calculates, formats, and distributes payslips in under 2 hours.
Payslip Disputes Reduced
94% reduction in commission disputes - reps receive a line-by-line deal breakdown showing exactly how their total was calculated, eliminating the black-box that drove challenges.
Commission Rules in One Place
Tier thresholds, product category rates, and bonus rules maintained in a single rules table - changed by the sales ops manager in minutes, effective on the next monthly run.
Payslips Generated and Emailed
60 personalised PDF payslips generated and emailed via Outlook in under 10 minutes - each one formatted identically with the rep's name, deal log, and total commission.
Payroll Processing Delay Eliminated
Commission totals delivered to payroll on the 3rd working day of the month - down from the 6th under the manual process. No more payroll delays caused by late commission figures.
First-Year ROI
300% - two days of senior sales ops time recovered per month, payroll delays eliminated, and dispute resolution overhead reduced to near-zero.
The Problem: Commissions Nobody Trusted
This B2B distribution company ran a 60-person field sales team on a tiered commission structure - base rates by product category, accelerators at quarterly targets, and team bonuses on regional performance. The structure was designed to motivate; the process for calculating it was destroying trust.
Each month, the sales operations manager exported closed deals from the CRM, pasted them into a master Excel calculator, looked up each rep's tier status, applied rates manually, and typed totals into a payslip template. The process took two full working days and still produced errors - usually wrong tier assignments when a rep had crossed a threshold partway through the month.
Reps received a single figure on their payslip with no breakdown. Disputes were frequent. Each one required the sales manager to reconstruct the calculation manually - tracing which deals had been included, confirming tier rates, and explaining why the total was what it was. Payroll regularly waited an extra day for commission totals to clear disputes before processing.
Three Problems Driving the Dysfunction
Manual Tier Lookup Errors
With tiered rates that change mid-month when a rep crosses a threshold, manual tier assignment required the ops manager to track running totals per rep as she calculated. Tier errors - applying a lower rate to deals that should have earned the higher accelerator - were the most common dispute trigger.
No Deal-Level Audit Trail
Reps received a total. When they questioned it, the ops manager had to rebuild the calculation from scratch. No deal log, no rate applied per deal, no way to show the work. The trust deficit was structural - not resolvable by asking reps to trust the figure.
Rules Scattered Across Formulas
Commission rates were encoded in cell formulas across the calculator workbook. A new product category or a tier threshold change meant the sales manager hunted through formula strings to find where to update. Changes occasionally missed edge cases and introduced new errors.
What We Built
We designed a three-layer Excel commission engine: a CRM data connection layer, a rules-based calculation engine, and a payslip generation and distribution module.
Power Query connects directly to the CRM's SQL database and pulls the closed deal register for the calculation period. Each deal record includes rep ID, deal value, product category, and close date. The connection runs on demand - no manual CRM export, no copy-paste.
The calculation engine applies tier rules from a dedicated rules table. As it processes each deal, it checks the rep's running total against tier thresholds and applies the correct rate - including threshold-crossing logic that splits a deal between two rates if it straddles a tier boundary. Split commission allocations are handled by the deal record's split percentage field.
The payslip generation module creates a formatted Excel sheet per rep showing every deal, the deal value, the product category, the rate applied, and the resulting commission - then the calculated total. A VBA routine converts each sheet to a PDF and emails it to the rep via Outlook. All 60 payslips are generated and sent in under 10 minutes.
Engine Capabilities
Live CRM Data Connection
Power Query pulls directly from CRM SQL database - no manual export. Deal register current to the minute the calculation runs.
Rules Table Architecture
Tier thresholds, category rates, bonus rules, and accelerator percentages in a single editable table. No VBA or formula edits needed when rules change.
Threshold-Crossing Tier Logic
Engine detects when a deal causes a rep to cross a tier threshold and splits the commission correctly - lower rate on the portion below, higher rate above. Exact to the penny.
Split Deal Commission
Deals shared between multiple reps allocated proportionally using the CRM split percentage field. Each rep's payslip shows their share with the split percentage visible.
Personalised PDF Payslips
Per-rep Excel sheet showing every deal, applied rate, and total - converted to PDF and emailed via Outlook. 60 payslips dispatched in under 10 minutes.
Payroll Summary Export
Single-click export of commission totals by rep in payroll-ready format - delivered to the payroll system the same day the payslips are sent.
After Go-Live: What Changed
The two-day monthly process became a two-hour morning task. The sales ops manager runs the CRM data pull, reviews the exception log for any deals flagged as unusual, confirms the totals, and sends payslips - all before lunch on the 3rd working day.
Commission disputes dropped by 94% in the first three months. Reps who previously challenged their figure every month stopped challenging because they could see the deal log in their payslip. The handful of genuine queries were resolved in minutes by reviewing the detailed breakdown rather than reconstructing the whole calculation.
Payroll receives commission figures on the 3rd working day, consistently. The payroll delay that had become an accepted irritation - sometimes pushing final payday by a day - no longer happens.
“Before this, I dreaded commission month. Two days of calculating, then two more days of disputes. Now I run the macro, check the exceptions, confirm the totals, and it's done. The reps trust the numbers because they can see every deal. I haven't had a dispute in three months.
Tech Stack
Excel VBA - calculation engine, payslip generation, PDF export, and Outlook distribution routine
Power Query - live SQL connection to CRM database for deal register extraction
Commission rules table - tier thresholds and category rates stored in structured Excel table, editable without VBA
Outlook object model - automated personalised PDF payslip emails to 60 recipients
Payroll export module - formatted commission summary output in payroll-system-ready layout
Frequently Asked Questions
How does the Excel commission engine pull data from the CRM?
The engine uses a Power Query connection to the CRM's SQL database - the same data the sales team sees in their CRM dashboard. Each monthly run pulls closed deals from the period, matches them to the representative record, applies the commission tier rules, and writes results to the calculation sheet. No manual export from the CRM, no copy-paste into a separate calculator. The connection runs in under 2 minutes for 60 reps and 300+ closed deals.
What happens when commission rules change - like a new tier threshold or a new product category?
Commission rules are stored in a dedicated rules table in the workbook, not hardcoded in VBA or formulas. When management changes a tier percentage or adds a new product category, the sales operations manager updates the rules table - a 5-minute task. The calculation logic reads from that table on every run. No VBA edits, no formula updates. Changes are effective from the next monthly calculation cycle.
How are commission disputes handled now versus before?
Before the system, disputes were resolved by the sales manager manually tracing which deals had been included and recalculating at the rep's request - a 30-60 minute process per dispute, with no audit trail. Now, every rep receives a detailed breakdown showing each deal, the deal value, the applicable tier rate, and the resulting commission - not just a total. Disputes dropped by 94% because reps can see exactly where their number came from. The handful of genuine discrepancies are resolved in minutes by reviewing the deal log.
Can the system handle split commissions between multiple reps on the same deal?
Yes - the deal record in the CRM supports an optional split percentage field. When a deal has a split, the engine reads the split allocation and distributes the commission proportionally across the contributing reps. Each rep's payslip shows their share of the deal and the split percentage applied. Split logic was one of the more complex rules to encode but covers about 15% of this client's deals.
Next step
Want similar results in your operations?
Book an Automation Opportunity Assessment. We map workflows and scope a practical build plan.
- No Onsite Visit Required
- No Technical Specification Required
- Assessment Before Commitment
- Clear Scope Before Work Begins
Questions before you book? Speak with our team at +1 385 386 3860
