Case study · Finance

Defensible sales commission reporting

From monthly workbooks to a fully functional commission portal – built in stages, on the data the business already has.

The challenge

Sales commission is one of those processes that seems simple in the first instance, until you start to map it out. Different clients carry different rates. Plans start and end at different points in the year. Some accounts grow beyond their original scope and deserve a different reward. Residual income from a plan signed in December might not be paid until January – but it should still count towards last year’s target.

For this client, those rules lived in people’s heads and in a monthly spreadsheet. The numbers were usually right, but proving why they were right – to a salesperson, a finance director or an auditor – took time and goodwill. The brief here, was to retain the flexibility to reward genuinely strong performance, while removing human error and making every figure defensible.

The approach: build on what’s already available

The quickest way to lose a process user’s trust is to replace everything at once. So rather than starting with a new application, I started with what the business already relied on: its bookings data in SQL Server and its familiarity with the monthly Excel workbook.

The work was sequenced so that each stage delivered something usable on its own:

1. Get the data right first. A single, tested monthly aggregation of sales by client, category and salesperson – with canonical client names, unallocated sales clearly labelled rather than silently dropped, and known anomalies excluded at source.
2. Put the rules into the database, not the spreadsheet. Commission plans, rates and targets became time-bounded records in structured tables rather than formulas buried in cells and easily overwritten.
3. Generate the workbook, don’t hand-build it. A Python pipeline produces the monthly commission workbook automatically from the database, so the familiar output stays the same while the manual effort disappears.
4. Move data entry and approval into a portal. Once the foundations are proven, the parts that still need a human; creating plans, approving them, making adjustments; move into a secure web application.

At every step, the business kept working. Nothing was switched off until its replacement had been proven against it.

Technical design

A few principles run through every component. They’re what make the system defensible rather than just automated.

• Rules as data. Rates, thresholds and plan windows are configurable values held in tables. Changing a commercial rule means adding a record, not editing code or rewriting formulas.
• Append-only, forward-only corrections. Committed records are never edited. If something needs correcting, a new record is added that references the original. Closed months stay closed; corrections land in the next report with a clear reference back to the period they relate to. The history is always intact.
• Maker/checker approval. Anyone creating a plan or adjustment cannot approve it themselves – and that rule is enforced by the database itself, not just by the user interface.
• Recognition separate from attribution. Every commission line carries two stamped dates: when the income was recognised, and which year’s performance it counts towards. That resolves the December-plan-paying-in-January problem cleanly, without the need for retrospective edits.
• Controlled data access. The application talks to the database only through stored procedures, so business logic sits in one tested place and access is tightly scoped.
• Sensible edge-case handling. Overlapping plans for the same client are blocked by default, with an explicit override route. Back-to-back renewals raise a warning rather than an error. Rates are locked when a plan is created, so a renewal becomes a new plan rather than an edit to an old one.

Where it is now: the foundation workbook

Today, the monthly commission report is produced by a three-stage Python build:

The workbook can also handle an “accelerator” rule. When a client’s monthly billing passes an agreed threshold, the report automatically splits it into a base line and a highlighted accelerator line, so growth is visible and rewarded – and a different commission amount can be applied to the accelerator amount.

The result is a report the team already knows how to read, generated consistently every month, with every figure traceable back to its source.

Where it’s going: a fully functional portal

The portal is a Flask web application hosted on Azure App Service, deployed automatically through GitHub Actions. Every change goes through a feature branch and a reviewed pull request before it reaches the live system.

It is being delivered in the same staged way as the workbook:

• Commission plan entry. Authorised users create a plan and its revenue shares in a single transaction, with plan codes generated automatically.
• Approval workflow. Maker/checker sign-off, with self-approval blocked at the database level.
• Adjustments. Entry, approval and reversal of corrections covering data restatements, plan corrections, attribution changes, clawbacks and discretionary awards – all append-only, and all landing in the correct reporting period.
• Go-live hardening. Ensuring only approved records feed the monthly report, role-based permissions on approval, and snapshotting closed months so that late changes to source data can never move a figure that has already been paid.

The end state is a system where the spreadsheet is an output, not the system of record: plans are entered once, approved by the right person, and flow through to the monthly report with a complete audit trail.

Why it works

The same thinking I bring to AI governance applies here: evidence over assertion. Every commission figure can be traced to a booking, a plan, a rate and an approval. Every correction has a reason and a reference. And because the system was built on the client’s existing data and reporting habits, adoption didn’t depend on anyone changing how they work overnight.