Sales targets and commissions template in Excel

Sales targets and commissions template in Excel

Closing the month and having to explain to each salesperson how their commission turned out is one of the most uncomfortable moments in a business. The target was set by word of mouth, the actual sales are spread across invoices, receipts and credit notes, and the commission ends up being worked out by hand in the last hour. When the number that comes out does not match what the salesperson expected, there is nothing to show and the conversation turns into a matter of trust.

This sales targets and commissions template solves that with a single sheet: every salesperson has a target, actual sales and a commission rate, and the file calculates achievement, commission earned, the achievement bonus and the total to pay. It carries twenty salespeople, an achievement threshold and a bonus amount editable in the header, column totals and a team summary.

It is an Excel file (.xlsx) ready to use, with no macros and no add-ins. You open it, save it under the name of the business and the month, and start filling it in with the figures for the period.

What it is and who it is for

It is an internal control tool, not a labour or accounting document: it exists so that commissions are settled with the figures in plain sight and so that each salesperson can see, without asking, where they stand against their target. The logic repeats in every row and takes four inputs: the salesperson, the monthly target, the actual sales and the commission rate that applies to them. Everything else is calculated by the sheet.

That simplicity is exactly the point. When the target, the actual sale and the commission rate sit in the same row, the argument stops being about the rule and becomes about the data: whether the actual sales are correctly added up, whether the credit note was deducted, whether that sale belonged to another salesperson. The file decides nothing on its own, it shows the complete arithmetic, and that is what makes a settlement easy to sign off.

It works in distributors, hardware stores, shops, dealerships, service companies and sales teams of any size. It behaves the same with three salespeople as with twenty: the time it takes changes, not the structure.

How this format differs from a daily sales register

Here the axis is the settlement: target against actual, threshold and bonus in full view. A daily sales register measures something else, what was sold and how it was paid, with the average ticket and the breakdown by payment method at its centre. They complement each other: the daily register feeds the actual sales in this file at month end, and this file turns those sales into commissions.

What the file includes

Everything comes inside a single sheet. There is no need to build formulas or name ranges: the bonus threshold and its value are written once in the header and the automatic columns read them from there.

The team summary is the part people use least at first and appreciate most later: with two numbers it says whether the team made it or not, and it lets you compare one month against another without opening anything else.

The columns of the sheet

The sheet has eight columns and only three are typed in; the rest are calculated. It is worth understanding what goes where before starting, because almost every settlement error begins with writing in the wrong place or leaving a sale without cleaning it up.

The rule is simple: you type the target, the actual sale and the commission rate, and nothing else. If someone types over an automatic column, the formula is lost in that row and the team total stops matching the detail.

How commissions are settled: an example with numbers

The calculation has three steps and none of them is complicated: actual sales are divided by the target to get achievement, actual sales are multiplied by the commission rate to get commission earned, and achievement is compared with the threshold to decide the bonus. The example is a salesperson with a target of twenty million, sales of eighteen and a half million, and a commission of three percent.

The commission comes from multiplying 18,500,000 by 0.03, which gives 555,000. Achievement comes from dividing 18,500,000 by 20,000,000, which gives 0.925, that is ninety-two and a half percent, which the sheet rounds to ninety-two. Because that result sits above the threshold of ninety percent, the bonus is paid in full and the salesperson receives the commission plus the bonus.

It is worth looking at the opposite case, the one that usually causes complaints: with the same sales and a threshold of ninety-five percent, the commission earned is still 555,000, but the bonus drops to zero by five points. There lies the whole value of having the threshold written in the header instead of in the memory of whoever settles the payroll.

Two warnings about the figures. First: actual sales must be net sales for the period, not invoiced sales, because a cancelled invoice or a return generates no commission. Second: if the business settles on collected sales rather than on sales made, that rule has to be written down before the month starts and applied the same way for everyone.

Step by step

1. Download the file and save it under the name of the business and the month. One file per month keeps the history tidy and makes periods easy to compare.
2. Fill in the header before the rows: month, achievement threshold and bonus amount. These are the parameters the automatic columns read.
3. Write one row per salesperson with their target and their commission rate, and at month end complete the actual sales once they are cleaned up.
4. Check the achievement of each row and reconcile sales against the daily register or the system, to make sure no credit note was left out.
5. Read the team summary: if overall achievement does not match what you expected, the mistake is usually a mistyped target or a sale assigned to the wrong salesperson.
6. Pay on the total to pay and keep the file together with the settlement backup, so that any later complaint is answered with the same document.

Tips and common mistakes

• Setting the target by word of mouth. If the target was not written down before the month began, any settlement can be argued about. The target is communicated, written in the header and supported by the same document.
• Settling on invoiced sales instead of net sales. Returns and credit notes reduce sales and therefore reduce commission. If they are not deducted, the business pays on money that never came in.
• Keeping the bonus threshold in someone's memory. The threshold belongs in the header, visible to everyone. A threshold that changes every month without being written down is the first source of complaints.
• Typing over the automatic columns. Achievement, commission, bonus and total calculate themselves. If they are typed by hand, the formula is lost and the team total stops matching.
• Using the same commission rate for everyone without agreeing it. The column accepts different rates per salesperson, but each one must be agreed before the period, not after seeing the result.
• Settling on the last day without reviewing the month. The final days move the most sales and bring the most assignment errors. It is worth closing the register two days early and settling calmly.

When it is worth moving to software

While the team is small, sales are recorded in one place and a single person settles the payroll, the template is enough and it does something no system does: it forces the rules to be agreed before the calculation. Moving to software is justified when concrete signs appear: there are several sales channels that must be added together, actual sales are built by hand from documents in different places, some salespeople also handle collections and not only new sales, or paying the commission depends on the customer having paid.

At that point a system such as Kardex Tauro makes sense, where the sale is born from the document that creates it, whether an invoice, a cash receipt or a credit note, and actual sales per salesperson come from the same record that feeds billing. With Kardex Tauro the commission can be settled on the sales of the period or on what was actually collected, without building the figure by hand. The template remains useful to agree the scheme, to audit a single month and to understand the numbers before automating them.

Download the template, write the threshold and the month's targets in the header and settle with the arithmetic in plain sight. When the rules and the figures sit in the same row, commission stops being a matter of trust and becomes a number.