Challenge
-
- Commission payment was being held up for as much as a day each week which caused challenges with retention of some of the sales representatives and morale generally.
- The business could not scale if the payment process was not improved.
- Risk of revenue leakage if payments are calculated incorrectly and running on thin margins did not leave much spare margin.
- Wanted to reduce the amount of time spent on reporting.
- Complex payment structure – depending on their contract sales representatives were being paid different commissions. The commissions were paid over a 6 week period with for example 20% paid in Week 3 and 80% in Week 6 less any clawbacks.
- Clawing back money from any of the sales reps was very difficult so everything had to be correct.
Solution
Analytical Models developed a bespoke commission reconciliation engine using Excel and VBA, that synchronized six weeks of retailer activity with four weeks of internal sales data. The system was designed to handle a complex staggered payment cycle:
-
- Automated Reconciliation: The engine identifies valid sales and manages ‘Gross’ (initial x%) and ‘Net’ (final 100-x%) payment phases, automatically deducting prior disbursements.
- Dynamic Hierarchies: It supports multiple agent/leader structures, calculating split commissions and generating individual, auditable workbooks for every Team Leader.
- Visual Audit Trail: To reduce disputes, the system provides high-visibility reporting, instantly flagging cancellations to ensure transparency for sales staff.
Outcome
-
- Commission preparation time reduced from days to hours.
- Level of supporting reporting and documentation massively improved.
- Reps were impressed and pleased with getting paid on-time and even early
- Revenue leakage identified – hundred of thousands of dollars
- Disputes with sales reps decreased.
- Word of mouth about our payment times increased the number of people wanting to work for us.
- Hundreds of thousands of dollars in unpaid commissions were identified.
- In a tight financial position at one point, plugging the revenue leakage meant the difference between surviving and closing their doors.
- Resulted in operational cost savings of $30k per year.
- Automated Professionalism & Trust Infrastructure
- Scalable Communication: Implemented an automated distribution layer that generated and emailed unique, verified commission workbooks to each Team Leader every week.
- Reduced Operational Friction: By providing transparent, CC’d reporting to all stakeholders simultaneously, we eliminated the ‘manual inquiry’ phase, reducing disputes and allowing management to focus on growth rather than reconciliation.
- Recruitment Edge: The speed and professional quality of the reporting became a competitive advantage, directly contributing to higher sales rep retention and improved word-of-mouth in the recruitment market.
Figure 1: Example of a Gross Report for a Team Leader, Cancelled Sales are shaded in red with white font whilst Cancelled Sales are shaded in yellow