- Sales commision templates
- Tiered commissions - AE
Tiered commissions - AE
The Tiered Commission Plan involves sales representatives being paid out at different rates for different levels of quota attainment. In this particular plan, we use the Payout Curve to decide the overall commission to be paid out to a sales representative.
Payout Curve: Plot of Quota Attainment (x-axis) vs Payout Percentage (y-axis) corresponding to quota attainment.
Credits are the percentage of sales that a sales representative has generated. Generally, it's 100% of the sales generated.
Quota is a sales target that a sales representative is expected to achieve within a defined period (ex: Monthly, Quarterly, Annual, etc)
Inflation Points are attainments at which payout rates either accelerate or deaccelerate. In the above example: 20%, 40%, 70% & 100% on x-axis forms the inflation points.
On-Target Earning is the total variable pay in the sales rep's plan that is paid out upon achieving 100% of the sales target. The actual payment may be higher or lower based on his quota attainment
Quota Attainment = Credits / Quota
Configuring the Spreadsheet Template:
Step 1: Update the Calendar Configuration
Update the year in the calendar configuration sheet. Select range (E3:14) and replace 2021 to a year for which you plan to run the sales comp. The rest of the configuration will be refreshed automatically.
Step 2: Configure the period setting for your plan
Based on how your plan requires you to calculate the quota attainment (monthly, quarterly, semi-annual or annual), set the Period for this spreadsheet. Your final calendar is set up based on the Period Input automatically as shown in the gif below.
Step 3: Update the Employee Management Spreadsheet
Fill in details about all the sales representatives in your current plan. Both ID and On-Target Earning are mandatory fields. ID should have unique value to every sales representative. If you have configured your plan at a quarterly level (refer to Step 2) - then ensure you update the On-Target Earnings at the Quarterly level for every sales representative on the payroll for this plan.
Step 4: Update the Quota for every Sales Rep mentioned in the Quota tab
All individuals mentioned in Employee Management File will automatically appear in Quota File. Based on the period setting - the first month of the periods will be populated automatically in this file. Update the assigned quota for every employee for the given period.
Step 5: Update the Rate Table Configuration
Update Min and Max Range of every tier. If your final tier is capped, then ensure you add an additional row in the rate table and update the max range of the capped tier as the min range of this tier - keep the max range as null and update the rate as 0%. Non-yellow highlighted area will be auto-refreshed
Tier Range: Difference between Max and Min values of the tier
Payout Tier: Max Payout in the current tier - Max Payout in the previous tier
Payout Rate: Slope of the line the forms this tier. This is the additional payout percentage for every 1% rise in attainment in the current tier
Differential Rate: (Payout Rate in the current tier) - (Payout Rate in the previous tier)
Rate Table: is the tabular representation of every inflation point and maximum payout in the respective tier
Step 6: Update Sales Order file at Employee and Order Level
Update the sales order details as shown in the gif.
Amount refers to the deal size that the sales representative has closed. Percentage Share refers to what percentage of the deal size should the sales representative be credited while determining his attainment.
Credits = Amount * Percentage Share
Once you have completed the above 6 step configuration - your Sales Commission for every representative is calculated to date. If you have set up a period at the Quarterly level - all calculations will be done at Quarter to Date Level (if current date < quarter end date) or quarter level (if you are looking at the period retrospectively).
All Employee View:
Please select the period start month of the period for which you want to view the commission for every employee on the payroll. If you have set up a period at the quarterly level and want to view the commissions for Q2, then select Apr-2021 (assuming the year is 2021).
How is this view created?
In this view, we pull details of all employees (Name, ID, and OTE) from the Employee Management file. Total Sales and Credits for every employee is then accumulated at the selected period level and attainment for the respective employee is calculated at the period level. Payout Percentage is then calculated for the attainment using the SUMPRODUCT formula and Rate Table. Payout Percentage is multiplied with OTE to calculate the commission for the respective employee for the selected month.
Employee Statement Drill Down:
For the selected period in the 'All Employee View' file - we can look at the drill-down of orders for a particular employee. This creates visibility to check the orders for which individual commissions were calculated. Simply select the employee ID for whose drill-down needs to be viewed. On the top right corner, you can view the summary.