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.

Terminologies Used:

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.

Learn moreabout how
Elevate can work for your team

Tired of running your commissions on spreadsheets? Afraid of switching because it may be too costly, or time consuming? Well, allow us to take care of all that, and more.