Compare monthly sales with a target and estimate flat-rate commission using this free sales tracking template for Excel. The workbook is designed as a sales commission tracker, with January–December columns for sales, holdbacks, advances, projected commission, and calculated commission figures.
Use it as a monthly summary rather than a transaction register. It does not record individual customers, orders, products, quantities, or returns, and it does not update inventory or connect to a point-of-sale system.
Download the Free Sales Tracking Template for Excel
Format: Excel workbook (.xlsx), supplied in a ZIP archive.
Included: A Sales Commission Tracker worksheet, a hidden month-lookup sheet, annual target and commission-rate inputs, monthly summary sections, and a chart-period selection area.

Extract the ZIP file and save a working copy before editing. No separate Word or PDF template is included.
Before use: Correct the annual projected-sales total as described below, replace all sample entries, and define the meaning of holdbacks, advances, and calculated payments for your process. The original downloadable file has not been altered.
What the Sales Tracker Includes
- Annual Sales Target: The target entered in D6.
- Commission Percentage: One rate entered in D8 and applied to the sales amounts.
- Actual section: Monthly sales, holdbacks, and advances, with year totals.
- Projected section: An equal monthly allocation of the annual sales target and the corresponding projected commission.
- Commission section: Calculated Earned and Paid rows, with year totals.
- Chart period: A selection area supported by month-lookup data. Check that chart selections display the intended period in your Excel version.
The workbook does not implement tiered rates, quotas with accelerators, product-specific rates, taxes, payroll deductions, a payment ledger, or a complete holdback-release schedule. Keep those calculations and records separately if they apply.
How to Set Up the Monthly Sales Tracker
1. Define the reporting year and sales basis
Add the year, currency, salesperson or team, and an as-of date where useful. The original columns run January through December. Define whether sales means orders booked, invoiced sales, collected amounts, or another approved measure, then use that basis consistently.
Do not mix future months with completed months when reviewing actual results. Blank months can contribute zero to totals and calculations, but they do not prove that a full year’s data is complete.
2. Replace the sample target and rate
Enter the annual target in D6 and the applicable flat commission rate in D8. Use a percentage entry such as 10%, not the ordinary number 10. The supplied 2,000,000 target and 10% rate are examples, not recommendations.
3. Enter monthly sales and adjustments
Replace the sample amounts in rows 12–14. Retain supporting records for each monthly summary and document how any returns, cancellations, or adjustments affect the sales basis. The workbook does not supply a transaction-level adjustment trail.
4. Define holdbacks and advances
The formula subtracts a positive holdback and adds a positive advance. Confirm that these meanings fit the intended report before entering values. An advance paid as additional cash during the month is different from recovering an advance that was paid earlier.
The original calculation does not track an outstanding advance balance or carry unpaid holdbacks forward automatically. Do not enter an old advance again each month merely because it remains outstanding.
5. Reconcile the calculated figures
Check monthly sales against the source records and commission against the applicable plan. Reconcile actual payments independently. Rename the Paid row to Calculated Payment if that better describes how you use the sheet.
Correct the Annual Projected-Sales Total
The original formula in O17 is =SUM(Projected[[JAN]:[DEC]]). It sums the monthly cells across the entire Projected table, including its commission row.
With a 2,000,000 annual target and a 10% rate, projected sales total 2,000,000 and projected commission totals 200,000. The original O17 therefore shows 2,200,000, overstating projected sales by 200,000.
For the original layout, replace O17 with:
=SUM(C17:N17)
This totals the sales row only. Adjust the reference if you change the layout, and check the corrected amount against the annual target. This is a suggested correction, not a formula already changed in the downloadable file.
How the Monthly Commission Formulas Work
| Result | Formula or relationship |
|---|---|
| Projected sales, C17 | Annual Sales Target ÷ 12 |
| Projected commission, C18 | Commission Percentage × C17 |
| Earned commission, C21 | Commission Percentage × C12 actual sales |
| Calculated Paid amount, C22 | C21 − C13 holdbacks + C14 advances |
The monthly projection is an equal target allocation, not a forecast based on seasonality or sales history. If your business has a seasonal pattern, use the monthly sales seasonality worksheet as a separate analysis before deliberately changing the allocation.
The Earned label describes the workbook’s arithmetic. It does not independently determine when commission is earned under a compensation agreement. Likewise, the Paid calculation is not evidence of a bank transfer or payroll payment.
Sales Commission Example
The workbook’s January sample uses sales of 65,000, a 10% rate, holdbacks of 2,000, and advances of 100. All amounts below use one currency.
| Item | Calculation | Amount |
|---|---|---|
| Earned row | 65,000 × 10% | 6,500 |
| Holdback | Subtract entered amount | −2,000 |
| Advance | Add entered amount | 100 |
| Calculated Paid row | 6,500 − 2,000 + 100 | 4,600 |
The example demonstrates the existing formula, not a recommended commission arrangement. If the 100 represents recovery of an earlier advance, adding it would not express that intent. Define the process and revise the model rather than assuming the label resolves the accounting.
Choose the Right Supporting Sales Record
Use our monthly sales projections template for a separate sales-goal and actual comparison. Keep its purpose distinct from this commission calculation.
For stock quantities and reorder information, use a store inventory sheet. Neither workbook automatically transfers sales into inventory. Retain transaction records and investigate discrepancies before drawing conclusions from a monthly total.
Editing and Review Checklist
- Replace sample amounts and identify the year, currency, and reporting scope.
- Correct O17 and preserve the formulas you intend to use.
- Document the sales basis and the treatment of adjustments.
- Define holdbacks, advance payments, recoveries, and later releases separately.
- Check chart periods, hidden lookup dependencies, and year totals after editing.
- Reconcile actual payments and keep the supporting approval record.
- Review Print Preview before exporting or distributing the report.
Frequently Asked Questions
Does this record individual sales transactions?
No. It summarizes sales and commission-related inputs by month. It has no customer, item, quantity, or order-level transaction log.
Does it calculate profit?
No. It does not include the product costs and operating expenses needed for a profit calculation.
Can I use tiered commission rates?
The original model applies one flat rate. Tiered or conditional plans require a separate, tested calculation.
Does the Paid row show actual payments?
It calculates earnings minus holdbacks plus advances. Confirm actual payments through separate payment records.
Does it forecast monthly sales automatically?
It divides the annual target equally by twelve. It does not build a forecast from historical trends or pipeline data.
Can I open it in Google Sheets?
You can try importing the workbook. Verify structured references, named ranges, charts, selection controls, and formulas before relying on the results.









