Blog · Board and management reporting · Sales teams
Compare commissions with credited revenue and gross profit by rep and segment. Work through the payout timing bridge, spreadsheet formulas and checks that make the comparison reliable.
Sales commission analysis matches commissions to the credited sales that earned them, then compares the effective payout rate with gross profit. Start with the sales cohort and the approved plan rules, because a payment date alone does not identify the results being rewarded. Keep the cash reconciliation alongside the commercial comparison.
For a quarterly review, take the credited transaction export, the commission statement and a finance-approved gross profit export. Keep one line per credited transaction and rep. Include transaction ID, account ID, rep ID, segment, credit date, credited revenue, attributed gross profit, plan version, applicable rate, adjustments, commission amount and payment date.
If several reps share a sale, use the credited share of revenue and gross profit for each rep. The shares must reconcile to the original sale before commissions are compared. The sales-export preparation guide covers identifiers, dates and control totals in more detail.
Write down whether the review includes bonuses, reversals, draws or management overrides. Use approved plan inputs; an analyst should not infer an entitlement from a payout rate. The checks before the commission run cover validation before approval. This review asks what the approved spending bought after the sales period.
An illustrative US industrial supplier reviews sales credited from April 1 through June 30, 2026, using statements available through July 31. All amounts below are USD. Each row aggregates transactions with the same rep, segment and approved rate; the underlying checks retain transaction detail.
The example uses simple revenue-based rates, without thresholds or adjustments. Cohort commissions are fully paid by July 31. Gross profit is measured before these commissions, so dividing commissions by gross profit does not subtract them twice.
| Rep | Segment | Credited revenue (USD) | Gross profit (USD) | Approved rate | Cohort commissions (USD) |
|---|---|---|---|---|---|
| A | Enterprise | 400,000 | 160,000 | 5.00% | 20,000 |
| B | Enterprise | 200,000 | 60,000 | 7.00% | 14,000 |
| C | Small business | 150,000 | 75,000 | 6.00% | 9,000 |
| D | Small business | 250,000 | 100,000 | 4.00% | 10,000 |
| Total | 1,000,000 | 395,000 | 53,000 |
With the table in A1:F6, recompute the expected commission for the first detail row in G2, then fill down:
=ROUND(C2*E2,2)
Put =F2-G2 in H2 and investigate every nonzero difference. Microsoft's ROUND documentation explains rounding to a specified number of digits. Here the illustrative plan rounds each transaction to cents before aggregation; use the rounding rule actually stated in your plan.
The comparison measures in I2 and J2 are:
=IF(C2=0,"Review",F2/C2)
=IF(D2<=0,"Review",F2/D2)
The first formula returns the effective payout rate. The second returns the share of gross profit consumed by commission and flags nonpositive gross profit for separate review. Calculate totals from summed dollars, retaining full precision until display. Percentages below are rounded to two decimal places.
| Rep | Effective payout rate | Commission / gross profit | Gross profit after commission (USD) |
|---|---|---|---|
| A | 5.00% | 12.50% | 140,000 |
| B | 7.00% | 23.33% | 46,000 |
| C | 6.00% | 12.00% | 66,000 |
| D | 4.00% | 10.00% | 90,000 |
| Total | 5.30% | 13.42% | 342,000 |
The total is $53,000 / $1,000,000 = 5.30%. Gross profit after commission reconciles as $395,000 minus $53,000 = $342,000. It is a limited contribution measure; it still has to cover salaries and other operating costs.
Use the same matched rows to summarize each segment. In a separate summary, put the desired segment label in A2. This Excel formula sums its commissions:
=SUMIFS(Detail!$F$2:$F$5,Detail!$B$2:$B$5,$A2)
Change the sum range to column C for revenue or D for gross profit. Microsoft documents SUMIFS as summing values that meet specified criteria. Add a credit-period criterion when the detail sheet contains more than one period.
| Segment | Revenue (USD) | Gross profit (USD) | Commissions (USD) | Payout rate | Commission / gross profit |
|---|---|---|---|---|---|
| Enterprise | 600,000 | 220,000 | 34,000 | 5.67% | 15.45% |
| Small business | 400,000 | 175,000 | 19,000 | 4.75% | 10.86% |
| Total | 1,000,000 | 395,000 | 53,000 | 5.30% | 13.42% |
Enterprise carries the larger commission burden relative to gross profit. Rep B warrants a conversation about product mix, discounting and the approved rate. The table does not establish that the rate is wrong or that commission caused the lower margin.
Quarterly cash payments do not equal the commissions attached to the quarter's sales. The statement separates them as follows:
| Payment component | Amount (USD) |
|---|---|
| Paid during quarter for current-quarter sales | 48,000 |
| Paid during quarter for earlier sales | 8,500 |
| Total quarter cash payments | 56,500 |
| Current-quarter commissions paid in July | 5,000 |
| Current-quarter cohort commissions: 48,000 + 5,000 | 53,000 |
The $56,500 total must tie to the commission payment control total, while $53,000 ties to the matched sales cohort. Dividing quarter cash by quarter sales would give 5.65%, which mixes earning periods. Record any still-unpaid balance separately instead of silently treating it as zero.
For a recurring review layout, use the sales commission dashboard; future obligations belong in sales commission forecasting. Covirage is not affiliated with Microsoft.
Bring a sample of your own credited-sales, payout and gross profit exports, together with the approved plan rules. Covirage maps the columns, and its tools compute and check the matched rates and reconciliation; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business; creating the account takes about a minute.
See Covirage for sales teams for the supporting context.
It is commission attributed to a defined set of sales divided by the credited revenue for those same sales. It can differ from a headline plan rate because the plan includes different products, accelerators, split credits or adjustments. State which components are included before comparing reps.
Use payments to explain cash leaving the business and earned commissions to review the cost attached to a sales period. Connect the views with transaction identifiers and a timing bridge. A quarter's payments can include amounts earned earlier and exclude amounts that will be paid later.
A higher rate is a reason to examine the plan and sales mix. It may reflect an approved accelerator, a difficult segment or a strategic product. Compare gross profit and the documented rules before drawing a conclusion about either the rep or the compensation plan.