Field sales commission with clawbacks for cancelled orders

Why the monthly-formula approach breaks

The usual first version is a sheet with one row per rep and a formula like "orders this month × 5% − cancellations this month × 5%". It works until three things happen, and in field sales they all happen in the first quarter:

• An order booked in June is cancelled in August. Which month's sheet does the deduction belong to, and at what value?
• A rep's clawbacks in a quiet month exceed their earnings, and the formula produces a negative payout.
• Someone "fixes" June's sheet after the cancellation, and now June's total no longer matches what was actually paid.

A ledger answers all three. It is also the only structure that lets you answer the question a rep will eventually ask: "Show me exactly why my payout was ₹5,000 this month."

Write the scheme rules as parameters

Before code, get these four rules in writing from sales operations. The values below are hypothetical:

Some schemes also write off carried-forward balances when a rep leaves, or cap the recovery per month. Add those as extra parameters rather than special cases buried in code.

The ledger in pandas

Synthetic orders for two reps from June to October 2026.

import pandas as pd

RATE = 0.05          # hypothetical: 5% of order value
WINDOW_DAYS = 90     # cancellations within 90 days of booking are clawed back

orders = pd.DataFrame({
    "order_id": ["O1", "O2", "O3", "O4", "O5", "O6", "O7"],
    "rep": ["Kavya", "Kavya", "Kavya", "Manoj", "Manoj", "Manoj", "Kavya"],
    "booked": pd.to_datetime(["2026-06-10", "2026-06-24", "2026-07-15",
                              "2026-06-05", "2026-07-02", "2026-08-20", "2026-09-12"]),
    "value": [200000, 150000, 120000, 300000, 80000, 60000, 250000],   # synthetic
})
cancels = pd.DataFrame({
    "order_id": ["O2", "O4", "O5"],
    "cancelled": pd.to_datetime(["2026-08-03", "2026-10-01", "2026-08-12"]),
})

# Ledger of commission events: +earned in the booking month, -clawback in the
# month of cancellation (only if inside the window).
earn = orders.assign(month=orders["booked"].dt.to_period("M"),
                     amount=orders["value"] * RATE, event="earned")
c = cancels.merge(orders, on="order_id", how="left", validate="one_to_one")
c["days"] = (c["cancelled"] - c["booked"]).dt.days
c["in_window"] = c["days"] <= WINDOW_DAYS
claw = c[c["in_window"]].assign(month=lambda d: d["cancelled"].dt.to_period("M"),
                                amount=lambda d: -d["value"] * RATE, event="clawback")
print(c[["order_id", "rep", "days", "in_window"]].to_string(index=False))

ledger = pd.concat([earn, claw])[["rep", "month", "order_id", "event", "amount"]]
monthly = (ledger.pivot_table(index=["rep", "month"], columns="event", values="amount",
                              aggfunc="sum", fill_value=0)
                 .reindex(columns=["earned", "clawback"], fill_value=0))

# Months with no events still need a row, or a carried-forward balance vanishes.
months = pd.period_range("2026-06", "2026-10", freq="M")
monthly = monthly.reindex(pd.MultiIndex.from_product([orders["rep"].unique(), months],
                                                     names=["rep", "month"]), fill_value=0)

# Never pay a negative amount; carry the unrecovered part to next month.
rows = []
for rep, grp in monthly.groupby(level="rep"):
    carry = 0.0
    for (_, month), r in grp.iterrows():
        due = r["earned"] + r["clawback"] - carry
        paid = max(due, 0.0)
        carry = max(0.0, -due)
        rows.append((rep, str(month), r["earned"], r["clawback"], paid, carry))
out = pd.DataFrame(rows, columns=["rep", "month", "earned", "clawback", "paid", "carry_forward"])
print(out.to_string(index=False))

# Controls: total paid + closing carry = total earned + total clawback.
for rep, g in out.groupby("rep"):
    assert round(g["paid"].sum() - g["carry_forward"].iloc[-1], 2) == \
           round(g["earned"].sum() + g["clawback"].sum(), 2)

Walking through Kavya's months

• June: two orders worth ₹3,50,000 earn ₹17,500, paid in full.
• July: one order earns ₹6,000.
• August: order O2 (₹1,50,000, booked 24 June) is cancelled 40 days later, inside the window. The ₹7,500 clawback exceeds her August earnings of zero, so she is paid nothing and ₹7,500 carries forward.
• September: a ₹2,50,000 order earns ₹12,500. The carried ₹7,500 is recovered first and she is paid ₹5,000.

Manoj's O4 was cancelled after 118 days, outside the window, so no clawback applies. His O5 cancellation creates a ₹4,000 clawback against ₹3,000 earned in August, leaving ₹1,000 still carried at the end of October. That open balance is exactly what finance needs to see when deciding what happens if he resigns.

Two details that prevent disputes

The window is measured in days, and the table shows the count. The printed days column lets anyone check the 40, 41 and 118 without trusting the code. Decide in the scheme whether the window counts calendar days, and whether day 90 itself is inside; the code uses <= 90.

Months with no activity still get a row. Without the reindex over a full month range, September and October would not exist for Manoj, and his ₹1,000 balance would appear to vanish. An absent row reads as "nothing owed", which is wrong.

Controls for every payout run

The assert at the end checks, per rep, that everything paid minus the closing carry-forward equals total earned plus total clawbacks. If that ever fails, an event was lost or double counted. Add two more before sending the sheet for approval:

1. 1Every cancellation matches exactly one order (validate="one_to_one" in the merge enforces it).
2. 2Total clawback this month, company-wide, is compared against last month. A sudden spike usually means a bulk cancellation was loaded twice, not that customers changed their minds.

Presenting it to reps and managers

Give each rep a statement, not a formula: opening carry, orders earned this month with order IDs, clawbacks with order ID and days since booking, closing carry and amount paid. Managers get the summary table plus one number that matters for behaviour: clawback value as a share of commission earned over the last three months, by rep. A persistently high share usually means orders are being booked before they are firm.

This builds directly on slab design, covered in Sales incentive slabs: flat-rate versus incremental payouts. Ledger-style thinking, where history is appended rather than rewritten, is also the foundation of most financial data work we teach in the Data Analyst programme.