Sales Commissions - Cumulative / Over Quota Formula

[kari481]

Hi -

I'm wondering if anyone can assist with the following:

Trying to figure out a way to automatically recognize when a salesperson goes over their sales quota (in this case, $250,000) - so, we can see which Opportunity (aka, Sale) puts them over their Sales Quota. And - once that happens, the formula for calculating their commission would need to change and calculate it based on their "Accelerator Commission Rate"

Right now we just are looking at the sheet and figuring this out by eye and then entering the formula in manually

Here are some other terms we use when calculating commissions:
1. Should an opportunity close with a < 12 month term, commissions will be paid in accordance with the Term length and not ACV.
Example: Customer signs a 9 month deal where ACV = $50,000 and 9 month value = $37,500. Commissions will be paid on $37,500
2. On a multi-year deal, should Customer pay more than 12 months on the first payment, Year 2 commision (or any partial payment of year 2) will be paid with the first year ACV payment.
Example: Customer signs a 36 month deal where ACV = $50,000 and Customer pays all 3 years upfront. Commissions will be paid upon receipt of payment as follows:
AE Calculation: (ACV (year 1) * Base Rate or Accelerator Rate (if in accelerators)) + (ACV (year 2) * 2%)

[kari481]

[JeteMc]

This formula, pasted into M15 and copied down (into the cells where you currently have commissions) yields the same results that you had in those cells previously:

Please Login or Register  to view this content.

I also added the following conditional formatting rule that highlights the two cells you had previously highlighted in yellow:

Please Login or Register  to view this content.

Here is the formula and rule applied to your file:
Example A-1 (Recovered).xlsx
Let me know if you have any questions.