Blog · Board and management reporting · Sales teams
A sales commission dashboard connects credited sales, reported earnings and gross payouts by rep. Build a balance bridge from compensation and finance exports, then use the differences to identify timing items and reporting errors.
A sales commission dashboard reconciles credited sales, reported earned commission and gross commission payments by rep. Build it around the movement in unpaid commission, then show the records behind any unexplained difference. Sales and compensation teams need that bridge before they need a leaderboard.
Credited sales are the sales assigned to a rep under the company's crediting policy. Earned commission is the amount reported by the approved compensation calculation. Paid commission is the gross commission component of completed payments, before withholding and excluding salary and expense reimbursements.
These are separate measures. A current payment can settle an earlier earning period; an earning can remain unpaid without being overdue. Keep scheduled payment dates and approval status beside the amount when those fields are available. A calculated unpaid balance is an operational reporting balance, not automatically a reconciled general-ledger liability.
Use the existing checks before the commission run to validate crediting inputs. This dashboard follows the earnings into the payment record. For choices about the wider reporting audience, see the sales dashboard examples.
Choose a reporting cutoff, such as September 30, 2026, and preserve the files used for that version. Export records far enough back to explain the opening unpaid balance. Do not assume the current month's files contain every unpaid item.
| Sheet | One row represents | Required columns |
|---|---|---|
| Credits | A sales credit assigned to a rep | Credit line ID, rep ID, credit date, credited amount |
| Earnings | A reported earning line | Earning line ID, rep ID, earning date, earned amount |
| Adjustments | A separate approved balance adjustment | Adjustment ID, rep ID, posting date, signed amount |
| Payments | A completed commission payment line | Payment line ID, rep ID, payment date, gross commission amount |
| Opening | A rep's unpaid balance at the start | Rep ID, opening amount |
Keep currency, plan version, approval status and original transaction reference where available. Restrict each calculation to the selected currency and approved reporting scope before using the formulas below. Adjustment amounts must exclude corrections already included in the earnings export.
Aggregate each sheet by rep before combining totals. Joining every earning to every payment by rep alone repeats rows when a rep has several of each. A payment-to-earning allocation reference supports detailed settlement reporting; without it, describe the result as a rep balance rather than claiming which earning was paid.
In each transaction sheet, put the identifier in column A, rep ID in B, date in C and amount in D. In Opening, use rep ID in A and opening amount in B. Put the first day of the reporting month in Summary!K1 and the first day of the next month in K2, both as actual dates.
On Summary, column A holds rep IDs. Columns B through F hold credited sales, opening unpaid, earned commission, adjustments and payments. In B2, enter:
=SUMIFS(Credits!D:D,Credits!B:B,$A2,Credits!C:C,">="&$K$1,Credits!C:C,"<"&$K$2)
Use the same structure for D2, E2 and F2, replacing Credits with Earnings, Adjustments and Payments respectively. In C2, retrieve the opening amount:
=SUMIFS(Opening!B:B,Opening!A:A,$A2)
These formulas sum amounts by rep and date. Microsoft's Excel SUMIFS documentation and Google's Sheets SUMIFS documentation describe the same multiple-criteria structure. Using an exclusive next-month boundary also includes transactions with time values on the last day.
In G2, calculate closing unpaid commission, then fill all formulas down:
=C2+D2+E2-F2
Covirage is not affiliated with Microsoft or Google.
Consider an illustrative US equipment distributor. The approved exports summarize to these rows; all amounts are USD, and a negative adjustment reduces the unpaid balance.
| Rep ID | Credited sales (USD) | Opening unpaid (USD) | Earned (USD) | Adjustment (USD) | Gross paid (USD) |
|---|---|---|---|---|---|
| R01 | 100,000 | 1,000 | 5,000 | -200 | 4,500 |
| R02 | 80,000 | 600 | 4,000 | 0 | 3,200 |
| R03 | 60,000 | 400 | 3,000 | -100 | 2,500 |
| Total | 240,000 | 2,000 | 12,000 | -300 | 10,200 |
Now work the closing balance rather than subtracting payments from current earnings alone:
| Rep ID | Calculation (USD) | Closing unpaid (USD) |
|---|---|---|
| R01 | 1,000 + 5,000 - 200 - 4,500 | 1,300 |
| R02 | 600 + 4,000 + 0 - 3,200 | 1,400 |
| R03 | 400 + 3,000 - 100 - 2,500 | 800 |
| Total | 2,000 + 12,000 - 300 - 10,200 | 3,500 |
The rep balances sum to $3,500, matching the total bridge. The illustrative reported earnings-to-credit ratio is 12,000 / 240,000 = 5.0%, rounded to one decimal place. That describes these records; it does not establish the commission rate in any rep's agreement.
Compare the calculated closing amounts with an independently exported unpaid statement. A zero bridge residual proves the arithmetic; agreement with the independent statement checks completeness. Neither check proves that the underlying compensation rules were applied correctly.
Show credited sales, earnings, signed adjustments, gross payments and closing unpaid beside the selected period. Below them, display one row per rep with the same fields. Retain a separate exception list containing the source line ID, reason, owner and next review date.
For duplicate payment IDs, enter this beside each source payment row:
=COUNTIFS(Payments!A:A,A2)
A result greater than one needs investigation, provided the identifier is defined as unique. Microsoft's COUNTIFS documentation confirms that the function counts rows meeting the supplied criteria. Blank identifiers require their own exception check.
Use sales commission analysis for the broader earnings investigation and sales commission forecasting for future assumptions. The dashboard records observed movements. The treatment of sales commission in the income statement is a separate accounting question.
Bring a sample of your own crediting, earnings and payment exports to Covirage, including the opening balances and approved adjustments. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account and bring the files behind your next compensation review.
See Covirage for sales teams for the wider sales reporting context.
The two amounts can belong to different periods. A payment may settle earlier earnings, while current earnings may await the agreed payout date or approval. Use an opening balance and payment allocation records to distinguish normal timing from missing or duplicated entries.
Compare gross commission payments with gross earned commission. An employee's bank deposit can include other pay and exclude withholding or deductions, so it is not a direct comparison. Use the commission component of the payment export, with the same currency and reporting scope.
This reporting method uses earnings already calculated and approved in your compensation records. It checks how those earnings relate to credited sales and payments. Determining entitlement requires the applicable compensation agreement, crediting rules and approval process; a reporting ratio does not replace them.
Sales operations should own rep identifiers and credited sales definitions. The compensation administrator should own reported earnings and approved adjustments, while finance owns payment evidence and balance reconciliation. Assign each exception to one person so review meetings produce a correction or a documented timing explanation.