During a recent consulting engagement I found myself once again re-inventing a formula in Excel for modeling commission calculations to estimate the cost of the plan at various points above and below 100% attainment. This task is complicated by the infinite variations on how acceleration can be designed. While every plan is different the common structure always uses an annual target variable (ATV) and an associated quota resulting in a personal commission rate (PCR, also known as base commission rate or BCR). I decided to create a general commission formula that was flexible enough to be reusable year after year during multiple engagements and I am sharing it so you don’t have to reinvent it every time you’re thinking about changing your commission plan structure.
The Excel formula that drives this model is the most complex I’ve ever written or ever seen for that matter. The key to making this easier for you or your Sales Ops analyst to audit it is the use of named global variables and a structured layout in Excel. You will find the formula in the linked Excel workbook in cell D28. The variables in the workbook (in blue font) can be changed to simulate variations on your proposed comp plan. Note: If you use this file as the basis for your analysis, the 32 named global variables are already defined and you’re good to go but if you move the formula to another workbook it will not function until you redefine the global variables there.
The values you need to input are in blue font while calculations are in red font. You need to enter the annual quota, the annual target variable, the number of times your commission rate changes in your model (called inflections), for each of those inflections, where they occur as a percent of quota and finally, how much the PCR is increased or decreased by the multiplier. For example, if the PCR is 3% and at 100% of quota it changes to 6%, the first inflection is at 100% (expressed as a percent of quota) and the multiple is 200% (expressed as a percentage of the PCR).
Using this model you can define inflection points both above and below quota. I have included columns labeled “Attainment Range” and “ACR” to make it easier to understand the effect of the inflections and multipliers. Commission rates above the PCR are collectively called accelerators and individually referred to as an accelerated commission rate or ACR. Plans usually have more than one ACR. This model allows up to five ACRs.
The spreadsheet also includes a line chart which attempts to graphically illustrate the inflection points.
Here is how the chart appears for a model that has four inflections at 100%, 125%, 150% and 175%.
The inflections are subtle and not easy to see in the chart. The simplest way to identify them is by looking at the difference in incremental commission at given stages of attainment. A table is included in the spreadsheet with a column that shows the amount of commission earned for each 5% increment of quota. Where those incremental commission amounts change is where the inflections occur.
The common question asked by sales management to validate that the designed acceleration is appropriate is, “what does my rep earn if he reaches X% of quota?” With minimal guidance from Sales Ops the Sales VP can test “what if” scenarios on their own by entering the proposed attainment in one cell while seeing the result in another. They can also adjust the inflection points and multiples in real-time until they are satisfied that plan cost and sales rep motivation are properly balanced.
When your commission plan includes more than a single component this model may have to be replicated to capture the entire effect of the attainment of more than one component on commissions.
Caveats: This model assumes there is one inflection point at 100% of quota. Deviations from this approach are rare. I should also mention that I haven’t tested every possible permutation of this model so use it carefully.
Please let me know if you discover any issues or have suggestions for enhancements. I’ve already made one change at the request of a Sales VP to include the capability for below-quota inflections which also has to address the “forced” commission rate calculation to get to 100% of ATV at 100% of quota. I will be happy to further improve the model to address other common structures. After all, I plan on using it myself the next time I’m modeling commission calculations!