How to build a sales commission tracker that replaces the spreadsheet

In many companies, commissions run on one spreadsheet maintained by one person: a sales operations lead, a controller, or the owner. Every month they pull closed deals, look up each rep's rate, apply the splits they remember, adjust for cancellations, and send totals to payroll. Reps get a number with little explanation. A few check it against their own spreadsheet, find a difference, and send an email. The month closes late and trust erodes a little each time.

A commission tracker turns that into a repeatable process everyone can see. This guide covers what the spreadsheet way costs, exactly what the tracker should contain, a worked example request, a rollout plan, mistakes to avoid and how to measure success.

What the spreadsheet way costs you

• Errors. A copied formula, a wrong rate, a missed split. Small mistakes become large over a year, and paying a rep too little hurts trust even more than paying too much hurts margin.
• One person dependency. If the person who maintains the spreadsheet leaves, the logic in their head leaves with them.
• Late closes. Calculating by hand takes days each month, and it competes with the rest of the close.
• Disputes. Reps cannot see the math, so they keep shadow spreadsheets. Every difference becomes an email thread.
• Plans that cannot change. A new accelerator or a mid-year spiff means rebuilding formulas, so leaders avoid incentives that would actually help.
• Commission software that is too heavy. Dedicated products can be priced and built for large sales organizations, with setup projects measured in months.

What the tracker should contain

Plans as rules

Each commission plan is stored as a set of rules, with an effective date and a version:

• Who the plan applies to
• What counts: new business, renewals, expansions, specific product lines, services
• The base: revenue, gross margin, or a fixed amount per unit or deal
• Rates, including tiers (for example one rate up to quota and a higher rate above it)
• Quota by period
• When commission is earned: on booking, on invoice, on customer payment, or a split between them
• Splits allowed and default shares
• Bonuses and spiffs, with their own dates and conditions
• Draws, recoverable or not
• Clawback rules: what triggers one and the time window
• Caps, if any

Store the signed plan document with each rep's assignment, with the date they acknowledged it. Plans change; old versions stay so past periods are always calculated on the rules that applied then.

People and assignments

Each rep record holds role, team, manager, plan assignment with start and end dates, quota by period and territory. Mid-period changes, such as a promotion or a territory move, are recorded with dates so the tracker can prorate where your plan says to.

Deals and payments

Closed deals come from your sales records or quoting tool, and payments come from your accounting system, on a schedule or by import. Each deal carries the customer, amount, margin if used, product lines, close date, owner and any split. Payments link to their deals so the tracker knows when a pay-on-payment commission is earned.

Credits and splits

Before calculation, each deal's credit is set: which reps get credit, in what share, in what role. Managers can adjust credit, with a reason, before the period locks.

The calculation

For every rep and period, the tracker calculates:

• Each eligible deal or payment line, the rule applied and the amount
• Progress to quota and the tier reached
• Accelerators when a threshold is crossed
• Bonuses and spiffs earned
• Draws paid and recovered
• Clawbacks from cancelled or unpaid deals
• The total due for the period

Every number shows its working, so anyone with permission can follow it from the deal to the payout.

The monthly cycle

1. Open: deals and payments flowing in for the period
2. Calculated: the tracker runs the period
3. Rep review: statements open to reps, who can flag lines with a comment
4. Manager review: managers resolve flags and approve their team
5. Finance approval: totals approved for payroll
6. Sent to payroll: exported in the format your payroll provider takes
7. Locked: period closed; later changes become adjustments in a future period

Disputes

A rep flags a line, chooses a reason (missing deal, wrong split, wrong rate, other) and adds a comment. The manager or sales operations lead resolves it: corrected, explained, or declined, with a note. The rep sees the outcome. Every dispute and decision stays on record.

Roles

Commission data is pay data. Each rep should see only their own numbers.

Reports

• Commission cost by period, team and plan, as a percent of revenue or margin
• Attainment by rep against quota
• Accelerator and spiff payouts
• Clawbacks by reason
• Disputes by reason and time to resolve
• Forecast commission for the quarter based on pipeline, if you want to plan cost ahead

Example request

Here is an example of a request a controller could hand to FORGE in plain words. Change the rules to match your plans.

"We have 12 sales reps on two plans. Plan A pays 8 percent of gross margin on new customers and 4 percent on existing customers, rising to 10 and 5 percent after the rep passes quarterly quota. Plan B pays a flat $250 per new service contract plus 3 percent of the first year's value. Commission is earned when the customer pays, and if an invoice is unpaid after 120 days, any commission already paid is clawed back on the next statement. Deals can be split between two reps with a percentage each. Deals come from our quoting tool and payments from a weekly export from our accounting system. Each month, calculate every rep's statement showing each deal, the rule used and the math, and open it to the rep for 3 days to flag anything. Managers resolve flags and approve their team, then I approve, and the app exports the totals in our payroll provider's format. Reps only see their own statements. Keep every plan version with the date it applied and the signed plan for each rep. I want reports on commission cost as a percent of margin, attainment by rep and disputes by reason."

Rolling it out

1. Write every plan down exactly. If two people describe the plan differently, settle it before you build anything. Ambiguity in the plan becomes disputes in the tracker.
2. Gather three months of history. Deals, payments, splits and what was actually paid.
3. Load plans and people. With effective dates and quotas.
4. Recalculate the past three months in the tracker. Compare line by line with what was paid. Every difference is either a spreadsheet error or a plan rule that needs clarifying.
5. Decide how to handle any historic differences. Agree with leadership before reps see anything.
6. Run one month in parallel. Calculate in both the spreadsheet and the tracker, and pay from the spreadsheet one last time.
7. Open statements to reps. Walk the team through how to read a statement and flag a line.
8. Pay from the tracker. Retire the spreadsheet and archive it read only.
9. Review plan cost at quarter end. Use the reports to tune plans for the next period.

Mistakes to avoid

• Building on an unclear plan. The tracker will do exactly what the rules say. Write them carefully.
• Skipping the parallel run. It is the only way to prove the new numbers before money moves.
• Hiding the math. A total without the working invites disputes.
• Editing locked periods. Changes after payment should be adjustments in a new period, so payroll and statements stay consistent.
• Too broad access. Commission is pay data. Reps see only their own; managers see only their team.
• Forgetting departures. Decide and write down what happens to commission on deals that pay after a rep leaves, and confirm it with your employment advisor.

Commission and wage rules vary by state and country, including whether plans must be in writing and when commission must be paid. Confirm your obligations with your employment lawyer or advisor, and confirm tax treatment with your payroll provider or accountant.

How to measure that it works

Launch checklist

1. Every plan written precisely and signed off
2. Signed plan on file for each rep
3. Three months of deals, payments and payouts collected
4. Plans, people, quotas and dates loaded
5. Past three months recalculated and differences explained
6. Access set so reps see only their own statements
7. Payroll export tested with your payroll provider's format
8. One month run in parallel
9. Reps walked through statements and flagging
10. Spreadsheet archived read only
11. Quarterly plan review scheduled
12. Obligations confirmed with your employment advisor

How FORGE handles it

FORGE lets your controller or sales operations lead describe the plans and monthly cycle in plain words and get a working tracker back, and most teams have their first real tool the same day. A panel of top AI models plans it around your rules, people and payroll handoff, a dedicated AI crew builds it, and every piece is checked for quality and security before it is saved. Reps check statements on their phones and finance runs the cycle on the web.

Pay data stays inside your company. People join only by invite, each rep sees only their own numbers, and there is a complete record of every calculation, flag and approval. Reps and managers who only use the tracker are free. When a new plan or spiff starts, you describe it and the tracker follows; changes cost a small fraction of a build and only count when something changes. Our team starts every company with a workflow workshop. Talk to us or see security.

Questions people ask