How small businesses can track commissions
Excel is a popular option for processing and monitoring commission-related metrics when dealing with smaller datasets. Some companies use a standard commission tracker template (using Excel or Google Sheets) as a starting point for building commission reports.
Here's how it works at a high level:
- Create columns for each employee's name, sales figures, commissions earned, and commissions paid (or owed).
- Enter each employee's sales figures into separate cells to create a running total for each month or quarter as well as a total for all months/quarters combined. You now have each employee’s earned in sales over a given period (such as one month).
- A commission tracker is a handy tool for managing sales and performance-based compensation. As a sales manager, having a clear, organized view of your reps is crucial, especially if you manage multiple teams with different compensation plans.
Looking for something more specific?
A tiered commission Excel template can be helpful when calculating tiered pay. Tiered commission rates incentivize reps based on a set of progressive milestones. Each time they hit a new target, they move up a tier and are rewarded with higher pay.
Alternatively, a sliding scale commission Excel template allows reps to calculate pay based on a sliding commission rate. For example, if a salesperson generates $25,000, then he or she will get a 20% commission. And so on.
When calculating your employees' commissions using a sliding scale, you might think there's a magic formula in Excel that can do all the work for you. In fact, there are multiple ways to calculate sliding scale commission with Excel formulas.
Here's what a simple method looks like:
- Create columns for each employee's name, sales figures, and commission.
- Create another table for all possible levels of performance (from lowest to highest), including commission rates. This way, as each employee reaches certain thresholds of sales volume, they'll get a percentage of their paycheck as commission.
- Use AND functions inside IF functions. The formula should check where the sales value lies on the sliding scale table and multiply the value by the sales figure to get the sliding commission value.
Commission tracking can be tricky, but a commission sheet template for smaller teams will help ensure you are on the right path to success.
Remember: every business is unique, so templates are just a starting point.
How to track commissions for enterprise
Incentives are one of the most impactful go-to-market investments a business can make. So it only makes sense to be able to manage commissions for maximum ROI.
However, determining how to track commissions effectively can be tricky, especially for businesses with hundreds (or thousands) of sales reps. The more sales reps, the more sales programs, and the more complex the commission plans tend to be.
As teams begin evaluating their existing compensation program and various commission tracking software, consider the following questions:
- What’s an ideal ROI? What’s a stretch ROI?
- How many sales reps do you manage? How quickly do you anticipate this number growing?
- How many comp plans do you currently manage?
- What data sources need to be connected?
- How vital is automation?
Commission forecasting and insights
Forecast earnings for future deals. Accurate comp reports.
These are the top two capabilities sales reps consistently report are essential. While 92% of sales reps and managers deem adding more visibility into compensation processes is critical to staying focused on the job, only 26% of companies offer a running breakdown of payouts, with metrics and visualization throughout a pay period and as it closes.
Transparency that motivates
Commission tracking software automates calculations — saving significant time and generating fast, accurate commission statements and reports.
Trying to do all of this by hand? Bad idea. The net result is often wasted time, excessive errors, and reps spending unnecessary energy on repetitive, menial tasks.
Compensation intelligence at your fingertips
The right commission tracking system helps teams gain insight into how to meet and exceed revenue goals. Through effective commission planning, the following questions can be answered:
- What is the compensation cost of sales?
- What motivates the salesforce?
- How does a change in commission structure impact rep behavior and top-line revenue?
- What happens if we change territory assignments?
- How do various quota multipliers impact things?
CaptivateIQ’s reporting suite allows users to share valuable data with team members using real-time sales compensation dashboards.
Segmenting dashboards saves time and helps to create powerful, customizable, useful reports. Quickly visualize data, forecast commission expenses reliably, identify trends, and eliminate weak spots.