{"id":690,"date":"2021-01-20T14:10:00","date_gmt":"2021-01-20T22:10:00","guid":{"rendered":"http:\/\/bobbacon.net\/blog\/?p=690"},"modified":"2022-11-15T13:53:10","modified_gmt":"2022-11-15T21:53:10","slug":"modeling-commission-calculations-excel","status":"publish","type":"post","link":"https:\/\/bobbacon.net\/blog\/archives\/690","title":{"rendered":"Modeling Commission Calculations in Excel"},"content":{"rendered":"

\"Sales<\/a>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\u00a0points\u00a0above and below 100% attainment.\u00a0 This task is complicated by the infinite variations on how acceleration\u00a0can be\u00a0designed.\u00a0 While every plan is different the common structure always uses\u00a0an annual target variable (ATV) and an associated quota resulting in a personal commission rate (PCR, also known as base commission rate or BCR).\u00a0\u00a0 I decided to create a general commission formula that\u00a0was flexible enough to\u00a0be 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\u00a0commission plan structure.<\/p>\n

The Excel formula that drives this model\u00a0is the most complex I’ve ever written or ever seen for that matter.\u00a0 The key to making this easier for you or your\u00a0Sales Ops\u00a0analyst to audit\u00a0it is the use of named global variables and a structured\u00a0layout in Excel.\u00a0 You will find the formula in the\u00a0linked Excel workbook<\/a> in cell C28.\u00a0 The variables in the workbook (in blue font) can be changed to simulate variations on your proposed comp plan. \u00a0Note: 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.<\/p>\n

\"Click<\/a>The values you need to input are in blue font while calculations are in red font.\u00a0 You need to\u00a0enter 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\u00a0by the multiplier.\u00a0 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).<\/p>\n

Using this model you can define inflection points\u00a0both above and below quota.\u00a0 I have included columns labeled \u201cAttainment Range\u201d and “ACR” to make it easier to understand the effect of the inflections and multipliers.\u00a0 Commission rates above the PCR are collectively called accelerators and individually referred to as an accelerated commission rate or ACR. \u00a0Plans usually have more than one ACR.\u00a0 This model allows up to five ACRs.<\/p>\n

\"Click<\/a><\/p>\n

The spreadsheet\u00a0also includes a line chart which attempts to\u00a0graphically illustrate the inflection points.<\/p>\n

Here is how the chart appears for a model that has four inflections at 100%, 125%, 150% and 175%.<\/p>\n

The inflections are subtle and not easy to\u00a0see in\u00a0the chart.\u00a0 The\u00a0simplest way to identify them is by looking at the difference in incremental commission at given stages of attainment.\u00a0\u00a0A table is\u00a0included in the spreadsheet with a column that shows the amount of commission earned for each 5% increment of quota.\u00a0 Where those incremental commission amounts change is where the inflections occur.<\/p>\n

The common question asked by sales management\u00a0to validate that the designed acceleration is appropriate is, \u201cwhat does my rep earn if he reaches X% of quota?\u201d\u00a0 With minimal guidance from Sales Ops\u00a0the Sales VP can test \u201cwhat if\u201d scenarios on their own by entering the proposed attainment in one cell\u00a0while seeing the result in another.\u00a0 They can also adjust the inflection points and multiples in real-time\u00a0until they are satisfied that plan cost and sales rep motivation are properly balanced.<\/p>\n

When your\u00a0commission plan\u00a0includes more than a single component<\/a> this model\u00a0may have to be replicated to capture the entire effect of the attainment of more than one component\u00a0on commissions.<\/p>\n

Caveats:\u00a0 This model assumes there is\u00a0one inflection point at 100% of quota.\u00a0 Deviations from this approach are rare.\u00a0 I\u00a0should also\u00a0mention that I haven\u2019t tested every possible permutation of this model so use it\u00a0carefully.<\/p>\n

Please let me know if you discover any issues or have suggestions for enhancements.\u00a0 I’ve already made one change at the request of a Sales VP to include the capability for below-quota inflections which\u00a0also has to address the\u00a0“forced” commission rate calculation to get to 100% of ATV at 100% of quota.\u00a0 I will be happy to further\u00a0improve the model to address other\u00a0common structures.\u00a0 After all, I plan on using it myself\u00a0the next time\u00a0I’m modeling commission calculations!<\/p>\n

Cheers!<\/p>\n

Bob Bacon<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u00a0points\u00a0above and below 100% attainment.\u00a0 This task is complicated by the infinite variations … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"no","footnotes":""},"categories":[23,4],"tags":[85,84,26,81,11,82,88,13,86,87,83,27,29,89,12],"class_list":["post-690","post","type-post","status-publish","format-standard","hentry","category-sales_compensation","category-sales_ops","tag-accelerators","tag-acr","tag-bob-bacon","tag-commission-modeling","tag-commissions","tag-excel","tag-icm","tag-incentive-comp","tag-inflection-point","tag-inflections","tag-pcr","tag-plan-components","tag-sales-compensation","tag-spm","tag-variable-comp"],"_links":{"self":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/comments?post=690"}],"version-history":[{"count":81,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/690\/revisions"}],"predecessor-version":[{"id":1428,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/690\/revisions\/1428"}],"wp:attachment":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/media?parent=690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/categories?post=690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/tags?post=690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}