Here at Sales Cookie, we help our customers automate their sales incentive program. Recently, one of our customers reached out because they wanted to switch from revenue-based sales incentives to profit-based commissions, making manual calculations difficult. This article describes the process and capabilities used to ensure accurate profit-based commission calculations. Note that we provide incentive program analysis and configuration service free of charge.
Here are the key requirements of this profit-based incentive plan:
- Each product has a well-known cost and target price
- X% should be paid over the difference between the sale price and the target price
- Y% should be paid over the difference between the target price and the cost
Let’s assume that product “modem X-12” has a cost of $1000 and a target price of $1200. Next, let’s consider two examples:
Example #1 – sales price was $1500 (above target)
- The rep should get X% of ($1500-$1200) – delta between sales price and target
- The rep should also get Y% of ($1200-$1000) – delta between target and cost
Example #2 – sale price was $1100 (under target)
- The rep should get X% of <zero> – delta between sales price and target
- The rep should also get Y% of (1100-1000) – delta between target and cost
We elucidated the following hidden requirements:
- We must prevent negative commissions. For example, if the sale price remains under cost, the commission should be zero (not a negative number).
- The product quantity may be greater than one. In this case, we must first divide the sale price by the quantity to calculate commissions. Once the commission has been calculated, it needs to be multiplied by the quantity.
- One commission is based on the difference between the target price and the cost. However, if the sales price happens to be lower than target, we need to use the difference between the cost and sale price instead. In other terms, we need to calculate the difference between the cost, and the lowest of [target price, sales price].
Step 1 – Sales Cookie Catalog
Sales Cookie includes a product catalog which can be referenced in calculations. Below is what the product catalog looks like in the application. We used the first column to track the “expected revenue”, i.e. the target price. We used the last column to track the cost. Later, we will show you how we can perform automated catalog lookups to calculate commissions.
Step 2 – Choosing an Approach
We have two choices available. We can either:
- Calculate commissions by scoring each transaction
- Crediting phase: calculate a score equal to the profit-based commission
- Reward phase: assign 100% of the total score as a payout
- Calculate commissions in the reward phase
- Crediting phase: do nothing (just credit revenue)
- Reward phase: calculate the profit-based commission
We chose the first approach (scoring) because this allows reps to view each transaction’s value and better understand their commission. Also, if later on we wanted to add sales goals, they would likely be based on profit (not revenue). So it makes sense to calculate profit upstream, in the crediting phase. An example of a sales goal could be “if total profit is over 30K, get an extra 10%”.
As a result, our plan will use scoring, and our reward table will look like this. We just have one attainment level, which grants 100% of the score as a monetary reward.
Step 3 – Defining a Scoring Formula
Next, we must calculate commissions based on our requirements. Here are some of the things we need to pay attention to:
- We need to perform catalog lookups. Scoring formulas support this use case. We can reference values such as [Transaction].[catalog_revenueperunit].
- We need to make sure each each of the two commission components can’t be negative. To ensure this, we can use the maximum between the commission and zero.
- We need to use the lowest value between the sale price and the target price when calculating the difference with the cost. To do this, we can use the minimum of the two.
Here is what our scoring formula looks like in Sales Cookie. As you can see, scoring formulas are just one of the options we offer in terms of scoring transactions. In this case, the calculation is quite complicated, so we used variables to make each step easier to understand.
Here is a more detailed view of our scoring formula. First we calculate the quantity and make sure it’s at least 1 to avoid divisions by zero. Next, we divide revenue by the quantity. Next, we perform catalog lookups to get the target and cost. Next, we calculate each component of the commission. We also ensure that we calculate revenue up to the target. Finally, we sum both commissions and multiply them by the revenue.
Profit-based incentive plans are usually more complex than revenue-based ones. In order to ensure a correct implementation, we must understand requirements as stated, but also identify all hidden requirements. Manually, this type of calculation would be cumbersome because it requires per-transaction catalog lookups in order to calculate profit. Sales Cookie provides advanced capabilities which help us handle the most complex plans. Visit us online to learn more and get help designing your incentive program!