How Can I Deal With Ever Changing Sales Data?
If your sales data changes constantly (ex: retroactively), you may wonder how commissions can be calculated correctly. Here are 3 different approaches you can use depending on your data.
For related topics, please refer to:
Track Each Change Individually
One solution is to track each change as a separate record in your CRM or Accounting system. Think about your transactions as an "event log", where each change is tracked and auditable. In the example below, 3 separate records track the amount for order "ID-123", each with a different effective date. As you can see, the entire history is available to us.
When calculating for a given month, Sales Cookie can lookup the commission paid so far on the same deal, and only issue a delta amount. Here is what the payout formula could look like. This payout formula looks up any previously paid commission, and deducts this from a re-calculated amount. Please refer to strategies used to prevent double-payment of commissions to learn more about this approach.
Please note - if each record does not already have a unique ID, Sales Cookie is able to generate unique IDs using a combination of fields (for example, the [Date] + [Order ID] combination).
Update Amounts & Effective Date
Suppose that you are unable to track each change as a separate record. For example, you only have one single opportunity record, which is constantly updated. Now, we no longer have an auditable list of changes (i.e. we don't know exactly when each change was made to the record). However, we can still calculate commissions correctly while preventing double-payment. The following approach assumes you move the effective date forward each time you make a change to the amount.
In January, the single record would look like this:
In February, the same record would look like this:
In March, the same record would look like this:
Because you move the effective date forward, the same strategy as described above can be used. By updating the effective date, you make it possible for the next period's calculation to re-process the same record, and simply pay the difference.
Of course, we cannot calculate commissions at any point of time (i.e. it's not possible for us to know what commissions would have been in January if we're already in March, as the data has already changed). But as long as we calculate commissions "forward", we can calculate amounts correctly.
Update Amounts But Not The Effective Date
Finally, suppose that, once again, you are unable to track each change as a separate record. For example, you only have one single opportunity record, which is constantly updated. And, for some reason, you are also unable to update the effective date. For example, the effective date is the Closed Date, which cannot be moved forward. In this case, we need a different approach.
What we could do is, each month, re-calculate year-to-date commissions. We will then deduct what has been paid in previous months. Alternatively, instead of re-calculating YTD commissions, we could re-process all records over the past N months. In this case, we don't need the formula above. Sales Cookie automatically deducts (as a lump sum) what has been paid previously when calculations periods overlap.
Your reps will essentially have:
- A re-evaluation of commissions earned on each deal over the past N months
- A corresponding total commission amount earned over the past N months
- A set of deductions for commissions already paid over the past N months
Related Articles
How Can I Deal With Ever Changing Hierarchies (Advanced)?
This article applies if you've configured teams to represent a single hierarchy, but ran into issues because your team hierarchy is changing all the time. Let's say that you have configured a fixed hierarchy like this: One option to deal with change ...Step-by-Step Sales Incentive Design
We have created a unique methodology to design sales incentives, which the following precise goals in mind: Provide a high degree of flexibility in the design of sales incentives Ensure strong alignment of sales incentives with business goals ...How Can We Ensure That Commissions Are Paid Only When We Get Paid?
This article applies if you want to pay commissions only when you get paid. For a more general introduction, click here. ============ Data Requirements ============ Ideally, If you have both a CRM and accounting system, you'd only use one system for ...What Type Of Support Is Available For HubSpot?
Overview By adding a HubSpot connection to your workspace, you can: Automatically import HubSpot users into your workspace We will detect users who are already present within your workspace We will only suggest adding missing ones You will be able to ...How Can I Prevent Double Payment Of Commissions?
If a transaction has its effective date changed to a later period, it's possible it may yield a double-commission in future calculation periods. For example, let's say you have a plan which pays monthly commissions. A transaction's closed date was ...