Sales Commission Tracking Spreadsheet

Commission disputes are one of the most common sources of employee relations problems. A well-structured commission tracking spreadsheet prevents disputes by making the math transparent and agreed upon before commission checks are written.

Commission Structure Types

Before building the tracker, know which structure you’re using:

Flat percentage: Rep earns X% of everything they sell. Simple, easy to track.

Tiered: Different rates at different sales levels. Example: 5% on first $50k, 8% on $50k-$100k, 10% above $100k/month. More complex to calculate but creates strong incentive to exceed thresholds.

Draw against commission: Rep receives a guaranteed “draw” (like a salary advance) and earns commission against it. If they earn more than the draw, they get the difference. If less, they owe back the difference (recoverable draw) or it’s forgiven (non-recoverable draw).

Quota-based: Commission only kicks in once a quota is hit. Zero commission below quota, then a percentage on everything above.

The Spreadsheet Structure

Sheet 1: Transaction Log

One row per sale:

• Date
• Sales rep name
• Customer name
• Product/service
• Deal size
• Commission rate (pulled from rules table)
• Commission amount
• Payment period (which month’s commission check this falls into)
• Status (Paid / Unpaid / Disputed)

Sheet 2: Rep Summary

Pivot from Sheet 1. For each rep, show:

• Total sales this period
• Commission tier reached
• Gross commission earned
• Draw amount (if applicable)
• Net commission payable

Sheet 3: Commission Rules

Your commission structure in table form:

• Sales threshold (low, high)
• Commission percentage
• Effective date

Reference this table from Sheet 1 to auto-calculate rates. When rates change, update the table with a new effective date row — old sales still calculate correctly.

Tiered Commission Calculation

This is where most spreadsheets go wrong. For tiered commission, you don’t apply the highest tier to all sales — you apply each tier only to the sales within that tier.

Example: 5% on first $50k, 8% on $50k-$100k, 10% above $100k.

For a rep who sold $120,000:

• $50,000 × 5% = $2,500
• $50,000 × 8% = $4,000
• $20,000 × 10% = $2,000
• Total commission: $8,500 (not $120,000 × 10% = $12,000)

This distinction matters enormously and is a common source of both over- and under-payment.

Conditional Formatting for Review

Add visual flags to your transaction log:

• Yellow: Sales entered but commission not calculated yet
• Green: Commission approved and scheduled for payment
• Red: Dispute or issue requires review

This lets you or your bookkeeper see the status of every commission at a glance.

The Monthly Close Process

At the end of each commission period:

1. Freeze the transaction log for that period (no new entries after close)
2. Run rep summary for each salesperson
3. Have each rep sign off on their summary (or send via email for confirmation)
4. Release commission payments through payroll
5. Mark transactions as “Paid” with the payment date

The sign-off step is critical for preventing disputes. If a rep agrees to the number before you pay it, post-payment disputes are rare.

Set up your commission spreadsheet before the next commission period closes. Retroactively reconciling commission disputes is expensive in time and morale. A clean system from the start is worth the upfront investment.