Modeling Commission Calculations in Excel

Sales Commissions AnalysisDuring 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 is always an annual target variable (ATV) and an associated quota resulting in a personal commission rate (PCR).   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 C28.  Note that the variables in the workbook (in blue font) are defined with placeholders that you can change to emulate variations on your proposed comp plan.  If you use this file as the basis for your analysis the 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.

Click to download Excel modelThe 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.

Click to download Excel model

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.  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!

Cheers!

Bob Bacon

About Bob Bacon

I work with global B2B high tech Sales leaders to help them enable and optimize the effectiveness of their organization Find out more about Bob here: http://bobbacon.net/blog/about/
This entry was posted in Sales Comp, Sales Ops and tagged , , , , , , , , , , , , , , . Bookmark the permalink.

6 Responses to Modeling Commission Calculations in Excel

  1. Piero Stanchi says:

    Hi Bob,
    I found your model very useful.
    I have an incumbent comp plan based on commission and no bonus, for a set of different products (different in effort and skills needed). The KPI to improve are churn of top performers and overall productivity.
    My first thought, in order to give value asap, is to work on diacronic components of the CP, missing at the time of today. That is, cut compensation in two parts (commission and bonus), set bonus on a longer period with respect to commission, in order to improve both churn and productivity. For the bonus I will use your useful model.
    My client current model is anyway lacking of a lot of things: territories have different potential (no data available) and the CP does not reflect this; commission payouts for the products do not rely on actual performance and effort; data on average effort needed to sell every different product is not available; no coaching; no teamwork; etc. At the moment, being a very new salesforce (on a mix of new and old products), the CP seems to be based just on management perception. The point is to begin to improve from asap. How would you start?

  2. Bob Bacon says:

    Hi Piero,

    Thanks for your comments. This link is to an article I wrote a few years ago on comp plan design. http://bobbacon.net/blog/?p=123 It outlines the steps required to design an effective plan. Designing a plan that is perceived as being based on objective metrics will probably go a long way toward decreasing churn! Including acceleration of their commission rate once quota is achieved (what you call a bonus) might help with the productivity KPI.

    Good luck!

    Bob

  3. Hi Bob,

    When I saw your megaformula, my jaw dropped.

    I am the co-founder of 5thcents Technologies, and we have developed a system for analysing complex Excel formulas. Before I saw your formula I had never seen one this long. I wasn’t sure our system would cope with that formula, but it did. I am hoping to find out if you or some of your clients would be interested in trying out our system, called SKUAREROOT, https://www.skuareroot.com

    We will be happy to explore opportunities for networking and partnership.

    Blessing Museki

  4. Mark Benavides says:

    Hey Bob

    Thanks for all the hard work! I appreciate the article and the spreadsheet. Any chance I can grab a few minutes of your time?

  5. Spencer Hodson says:

    Bob,

    Thanks for the insights and comp model. Very helpful. More broadly, as we design comp plans and set quotas – what would be an ideal target to shoot for in terms of: 1) % of sales reps that make or overachieve plan and, 2) Do you recommend using those multipliers you have in your model or is that mostly just for software sales? Our sales reps sell very large enterprise deals (ie. >$20M) that can be very lumpy.

    • Bob Bacon says:

      Hi Spencer,

      Commission plans need to be designed to best motivate your particular team taking into consideration the uniqueness of your business. The multipliers I left in the spreadsheet for illustrative purposes are typical for individual contributor sales resources selling enterprise software. They are necessarily high to be competitive in the enterprise software industry. Your multipliers should be based on the standards in your industry.

      The desired percent of sales resources making quota seems to be more standard across industries based on my reading. Your target should be in the range of 65% to 75%. Given attrition and ramping of new hires it generally takes that average success rate to make the company number!

      Good Luck!

      Bob

Leave a Reply

Your email address will not be published. Required fields are marked *