What the calculation already costs a commercial excellence team

A plan with four periodicities reaches 19 closes a year. Each close applies the same six mechanics to the same sales data, which is 114 mechanic applications against one dataset in a year.

The logic that decides all of it sits with a small commercial excellence team. It lives in working memory and in a set of workbooks that only that team can open safely. The team cannot take leave in the same week. None of what the team knows is written anywhere the wider business can read.

Every correction is made under time pressure, in the days between a close and a payout date the field already knows.

Provenance: 19 closes and 114 mechanic applications · anonymized mid-sized Indian pharma, top 50 by market share, 1,000 or more medical representatives

The single point of failure is the small group of people who know what the workbook is doing.

‍

What the break looks like from the outside

Three symptoms show up well before anyone calls this a calculation problem.

i) The same correction comes back

A medical representative is underpaid on stockist credit in March. The correction is issued in April. In June the same mechanic underpays a different representative in a different zone. Nobody connects the two, because each was closed as a ticket and the ticket is the whole record.

ii) Two reconciliations that do not agree

The finance team holds one number for the accrual. Sales operations holds another for the payout. Both are defensible. Both were built from the same source data, through two sets of adjustments that were never compared against each other.

iii) Queries arrive before the report does

The field knows first. Query volume rises in the days after statements go out, and rises on the same mechanic every period. Spreadsheet commission calculation problems are visible in the query queue weeks before a variance report picks up the pattern.

Symptoms cluster by mechanic, and that clustering is the signal that the break sits in the rule.

‍

What is actually happening

Three things, and each one is a decision the business made without deciding it.

1. The plan version is not fixed to the period

A slab moves in week three. A qualifier is relaxed for one division in week five. Both changes are made in the live workbook, and nothing records which version of the plan calculated which payout.

Months later a transfer or a promotion forces a recalculation of a closed period. The recalculation runs on today's rules against last quarter's sales. The difference that falls out is read as an error, and a correction is issued against it. The rule that produced the difference stays exactly where it was.

2. Credit is rebuilt rather than stored

The join between a payout and the source row that earned it is recreated by hand at each close. That join exists for the week of the close and then goes.

When a dispute arrives in the next quarter, the trail has to be reconstructed from files that have moved since. Reconstruction is a different exercise from opening a record, and it produces a defensible answer rather than the original one. The ELT and Calculation Engine stores the link at calculation time instead, so lineage is an export.

3. The exception is granted outside the record

A regional manager asks for a relaxation for a medical representative who landed at 98% of target. The request is reasonable, the relaxation is granted on a call, and it is applied in the sheet.

Across the deployments we run at Kennect, this is the mechanism a finance team finds hardest to reconstruct at audit. Where no incentive exception policy sits between the request and the calculation, what an auditor opens a year later is a payout the plan document does not explain. Incentive data readiness has the same shape: a data fix applied at file level and never written down.

Three mechanics carry the break more often than the rest in an Indian field plan, and all three were named unprompted in demo calls this year. Proration when a representative changes role or division mid-cycle, because the plan has to decide which territory earns the part-month. Clawback on a disbursal cancelled after payout, because the recovery lands one or two cycles after the sale that earned it. And a selective product scheme, where incentive is paid on four products out of twenty-four and the product list itself is a rule that changes.

Each of the three is a rule with a date attached. A workbook holds the rule. A workbook does not hold the date.

‍

Each of the three is reversible. None of the three reverses by correcting a payout.

‍

What gets tried first, and why it holds for a quarter

Three responses arrive before the record itself changes. Each one is sensible and each one buys time.

i) A second analyst is hired

The workload halves and the exposure doubles, because the logic now sits in two heads and the two heads do not agree on every mechanic. In the deployments we run, the second incentive analyst arrives before version control does, and that order is what creates the divergence rather than closing it.

ii) A checker is added to the cycle

A second pair of eyes reviews the output before payout goes out. Review catches arithmetic. Review does not catch a rule that changed mid-period. The reviewer is checking the same workbook against the same workbook, in the same week, under the same deadline.

iii) A reporting layer is built over the payout file

A dashboard goes up on the payout extract and the numbers become visible to leadership for the first time. Visibility is not lineage. A reporting layer presents what another system already calculated. The payout extract carries no plan version, no source row and no approval, so none of the three reaches the dashboard either.

Adding people or reporting to an unversioned record scales the weaknesses of that record along with the work.

‍

Whether the same break is coming

Three questions, and answering all three takes an afternoon.

1. Can the plan version that calculated last quarter's payout be named and produced?
2. Can a single payout be opened to show the source rows behind it, without rebuilding the join?
3. Is there a written record of every exception granted last year, carrying the approver and the date?

Two answers of no and the errors are structural. Structural errors repeat at a rate set by how often the plan closes, which for a four-periodicity plan is 19 times a year.

The condition that predicts the failure is whether the logic is stored anywhere other than in the people who run the cycle.

‍

What it costs when it does

One anonymized deployment, mid-sized Indian pharma, 1,000 or more medical representatives. Before automation the cycle ran on seven to ten spreadsheets and period close to payout took 2 to 3 months. After, spreadsheets per cycle fell to none and close to payout runs at 5 days. Queries per cycle fell by 80%, and more than 200 admin hours a month came back to the team.

The shape of the close week changes with those numbers. Before, the week after close belongs to reconciliation: pulling extracts, matching stockist data, chasing approvals by email, and holding the payout until the last mismatch is explained. After, the same week belongs to review, because the calculation has already run and what remains is the set of exceptions that need a decision.

Provenance: same anonymized Kennect deployment · spreadsheets per cycle, seven to ten to none · period close to payout, 2 to 3 months to 5 days · queries per cycle, 80% fewer · admin hours recovered, 200 or more per month

What the reading does not show. It is a before and after inside one organization. There is no matched group behind it, so the reading describes what changed in that deployment and does not forecast what changes elsewhere. These are operating measures. Neither figure is a return on incentive spend, and the two readings that answer that question are set out separately in ROIP and ROO.

Speed and query volume move first, because both sit downstream of the same record.

‍

How the break is prevented

Three moves change the condition, and none of the three requires a new plan. Freeze the plan version to the period, so a recalculation runs on the rules that were in force. Store credit at calculation time, so a dispute becomes a lookup. Route every exception through an approval that lands in the same record as the payout.

At Kennect we build all three into the cycle rather than around it. We hold the plan version against the close that used it. We store every payout against the source row that earned it. We route the exception through an approval an auditor can open a year later. The finance team keeps the audit trail it signs. Sales operations keeps the calculation it defends. Query and Dispute Management holds the thread against the payout in question. The medical representative sees the gap to the next slab in rupees, in the same app as before.

This is the India-first incentive complexity case without anything exotic in it. The plan itself is ordinary. The number of closes is what makes the plan hard, and the correction belongs in the record that produced the payout.

‍