Sales compensation costs can be a significant expense, varying based on a complex array of factors that may be hard to predict. Add in the annual plan design changes, and the risk of unexpected cost overruns may be significant. To mitigate this risk, thoughtful plan designers build models to predict the cost of the proposed plans over a range of business outcomes.

Effective modeling approaches range from back-of-the-envelope estimates based on historical costs and known minor plan changes to full simulation of the plan year with thousands of iterations and a dizzying array of statistical predictions. But of course the model results are only as accurate and useful as the assumptions on which they are based and the questions they were designed to answer.

The first step in building an effective model is determining the right questions to answer.

There are a few questions we always want to answer (e.g., “What is it likely to cost if everything goes as expected?”), and there are always specific questions related to the business situation, competitive environment and known uncertainties for the coming year. Clarifying the key questions first will ensure the model is built to answer them.

This presentation offers three effective modeling approaches that use Excel as the primary tool:

  1. A synthetic distribution based on a reasonable expectation regarding over- and under-performance
  2. A distribution based on actual historical attainment distributions
  3. A simulation based on a randomly-generated attainment distribution iterated 1000 times (or as many as you feel you need)

In all three techniques compensation costs are calculated under the proposed new plan over a range of over- and under-performing scenarios

The theory behind these three approaches will be illustrated with an example data set and proposed sales compensation plan changes.

Download the Excel file

See live examples of all three model types

Download the presentation

The presentation includes some introduction and explanation, how-to steps, and a comparison of the types of models, their strength and weaknesses

Tags: ,