Sales commission forecasting: estimate expense and payment

Sales commission forecasting separates the expense a company expects to report from the cash it expects to pay. Start with an approved sales scenario and compensation assumptions, then assign the resulting amounts to expense and payment periods separately. The payable balance proves whether the timing holds together.

Start with approved inputs and separate dates

Bring a sales forecast by team and period, an approved commission calculation or assumption summary, the opening commission payable, and actual expense and payment exports. Keep the scenario date and plan version on every forecast row. The compensation owner approves eligibility and rates; finance approves expense treatment; payroll confirms payment timing.

The minimum calculation file needs a row identifier, team, earning period, eligible sales, approved rate, commission obligation, expense period, and scheduled payment date. If finance supplies a separate amortization schedule, use that schedule for expense rather than treating the new obligation as immediate expense.

The example below assumes finance has approved immediate expensing of the illustrated obligations. It uses flat rates, no thresholds, and no adjustments. These are example assumptions, not a recommendation about accounting policy or a substitute for the approved compensation calculation.

Use the same sales scenario throughout. Definitions such as commit and best case belong in your sales forecast categories; do not quietly attach a best-case commission estimate to a commit revenue forecast.

Calculate a small team forecast

A US equipment distributor forecasts these sales. On a worksheet named Plan, columns A through F contain team, earning month, eligible sales, rate, new obligation, and payment month. Store the months as actual dates, using the first day of each month.

In Excel, calculate E2 and fill down:

=C2*D2

The independent total check is:

=SUMPRODUCT(C2:C5,D2:D5)

Microsoft documents SUMPRODUCT as multiplying corresponding ranges and summing their products. Here it returns $67,000, matching the obligation column. The rates are assumptions supplied by the compensation owner; the arithmetic does not validate the underlying plan rules.

For this example, October expense is $20,000 plus $10,000, or $30,000. November expense is $25,000 plus $12,000, or $37,000. Amounts are shown to whole dollars without intermediate rounding.

Put expense and cash into their own months

On a summary sheet, put October 1, 2026 in A2. Under the approved immediate-expense assumption, calculate October expense from the earning-month column:

=SUMIFS(Plan!$E$2:$E$5,Plan!$B$2:$B$5,A2)

Calculate scheduled cash from the payment-month column:

=SUMIFS(Plan!$E$2:$E$5,Plan!$F$2:$F$5,A2)

Microsoft's SUMIFS documentation explains the criteria-based summation used here. If the export contains individual payment dates instead of month keys, sum dates from the month start up to, but excluding, the next month start.

The business also has an opening payable of $8,000, scheduled for payment in October. Add that opening-balance payment separately; it is not another new expense.

For columns B through E in this rollforward, closing payable is:

=B2+C2-D2

The full check is $8,000 plus $67,000 minus $75,000 equals zero. Carry each closing balance into the next opening balance. A different expense schedule would require a separate expense reconciliation; the payable rollforward still follows obligations and payments.

Compare actuals without mixing measures

Freeze the forecast before loading actuals. Join by team, period, measure, and currency, then calculate actual minus forecast. These actual amounts are illustrative exported results:

October expense is $1,000 above forecast; November payments are $1,000 below forecast. Those differences need separate explanations. A lower payment can mean a timing delay even when expense is higher. Use a sales commission dashboard for the recurring review and sales commission analysis for investigating differences.

Where commission forecasts go wrong

• Using all sales as eligible sales. Apply the approved exclusions before multiplying by a rate.
• Replacing plan rules with an average. Thresholds, splits, and adjustments need approved calculated inputs or explicit scenario assumptions.
• Expensing the opening payable again. It belongs in the balance and cash schedule, not automatically in current expense.
• Calling missing payments zero. Confirm the payroll export covers the period before treating an absent row as no payment.
• Overwriting the forecast. Preserve the original version so a revised estimate cannot erase the variance.

Covirage is not affiliated with Microsoft.

Compare your commission files in Covirage

Upload your approved commission forecast, expense summary, and payment export; Covirage maps the columns, and its tools compute and check the comparisons while the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data. See financial planning and reporting for the supporting finance workflow.