Test this
One deal dated the 14th and one dated the 16th, with the edit on the 15th. Then a deal dated the 14th that is amended on the 20th. The amendment is the case spreadsheets usually get wrong.
Edge case
A commission plan that changes in the middle of the quarter.
The hard part is not typing the new rate. It is knowing which deals still belong to the old rule, which quota dollars were already counted, and which accelerator starts over.
Walk the period in order.
- Write the effective date. Deals closed before it keep the old rate. Deals closed on or after it use the new rate. “Closed” has to mean one field, not whoever last edited the row.
- Decide what happens to attainment already booked. If the new accelerator uses year-to-date quota, say whether the old bookings count toward the new threshold.
- Freeze a copy of the old tier table. The live tab will be overwritten. A later dispute needs the table that was true on the close date.
- Recalculate only the deals on the new side of the date. Do not rerun the whole quarter and then try to remember which numbers payroll already took.
- Tell the rep which rule applied to which deal. A single new statement total hides the split between old and new.
Leave this alone
Do not change the plan mid-cycle if the only goal is a cleaner spreadsheet. People rarely want a commission change in the middle of the year. If you do it, the date has to be the point of the exercise.
What the checker adds
“Most cycles” on mid-period changes is worth two points. Pair it with two plans or a tier table and the score moves into the range where reconstruction, not arithmetic, is the work.