I’ve had a lot of inquiries about how to implement and manage sales commissions. As such, I thought I’d write this post for those looking for a sample commissions excel table. Keeping track of a sales professional’s commissions is pretty straightforward, except for those instances where a sale results in a loss.This sometimes happens when sales people sell dead stock. The following excel sheet is for those companies selling with two currencies, such as the Canadian & American dollar. I've included the conversion in the attached sample.
This particular sample commission table is predicated on measuring the sales professional’s performance by the amount of gross profit generated per sale. Most companies measure the value of a given sale by the amount of gross profit it brings in. At the end of the day, if there isn’t enough gross profit, then the sales total itself doesn’t matter much. If you’re not accustomed to paying sales commissions based on gross profit, then you should probably read the following post: Should Your Company Pay Commission on Sales Totals or Gross Profit?
This particular sample commission excel table is based on sales in US dollars which are then converted to Canadian. This amount is then converted into a commission amount. In the following table, the commission rate is 3% on gross profit. Our example is of a Canadian company whose main market is in the United States. They sell in US dollars, convert the gross profit to Canadian dollars and then determine the sales professional’s overall commission by multiplying the Canadian dollars by 3%.
The highlighted blue line includes a sale where the costs were higher than the sales total. In this particular transaction, the gross profit is negative - corresponding to the red gross profit in parenthesis. This sometimes occurs when the costs suddenly spike on a given order – such as incurring high freight costs. I’ve included this so you know the excel sheet has the functionality to process negative gross profit sales. You can download the sample commissions excel table here:Download Sample Commission Excel Table
Hopefully this sample commissions excel table will help make it easier to track the gross profit on a given sale, and convert that sale into a commission amount for the sales professional. Most sales commissions are paid out at the end of each month. This particular table is merely an example and most sales transactions in a given month, are much more involved, and much larger in number. You can play around with the table itself.
Again, this excel table is predicated on paying a commission on gross profit. However, once could configure it around paying a commission on sales totals.
How much does your company charge its business-to-business (B2B) customers for product returns? The above video explains a simple process to covering your holding costs. If you want to learn more about the strategies outlined in the above video, then please read: B2B Restocking Fees: Three Simple Steps to Covering Carrying Costs