The ‘Flaw of Averages‘ is alive and well in sales forecasting in CRM software. I have fallen in love with salesforce.com (big time) and have been playing about with the forecasting module; great, but is doesn’t capture the binary nature of a sales opportunity. If I have an opportunity for $100k and assign a subjective probability of 60% that we will get it, what should we put in a forecast? Expected value = $100k x 60% = $60k – not really cos it either ends up $0 or $100k. Same principle that “The average human has one breast and one testicle” (Des McHale).

When these normalised expected values are used you don’t get a sense of the pipeline variance. For example, if we have 100 opportunities of $1k, each with a likelihood of 50% or, 1 opportunity of $100k with a likelihood of 50%, the expected value is the same, $50K, but I know which of these I would rather include in a forecast.

**It’s Monte Carlo time: **salesforce.com has a nice integration with Excel to export reports. Here is an example spreadsheet with a bunch of random values for the sale value and subjective likelihood to close

DOWNLOAD THE MONTECARLO FORECAST SPREADSHEET

**What does it do?** It extracts the pipeline data from salesforce and creates a list of the opportunities, their sales value and probability to close. A macro is then used to simulate 300 ‘possible outcomes’ i.e. in one outcome we may close on the first two opportunities and not the third, in a different outcome we may only close on one opportunity etc. From these simulated outcomes we then can pull out some stats; the expected sales figure, min, max and the variance i.e. possible spread of results.

**Lots of eggs in different baskets:** if we have 100 opportunities of $1k, each with a likelihood of 50% then it turns out that it is very likely we’ll close on at least $40k worth of business. The corollary is that it is also very unlikely that we’ll close on more than $60k of business. Like death and taxes, you have the inevitability of making a trade between risk and reward. Having a good estimate of the variance is very useful for planning especially if you need to watch cash flow. Also handy to keep in check the ever present cognitive bias of over confidence.

The graph above is a value-at-risk (VAR) chart for the case of 100 opportunities of $1k, each with a likelihood of 50%. The distribution shows that the most likely outcome is around the $50k mark, which is what the expected value should be. Most of the action happens in the $40-$60k region so you can use these as your upper and lower estimates when rolling up into a financial model.

In the example above it is pretty easy to calculate the exact expected return and variance. The Monte Carlo approach becomes useful in the real world case where the individual sales values and likelihood to close are all over the map. I’ve also found it useful because the integration sucks the real data out of salesforce seamlessly. In the example below there is a larger $40k opportunity with a likelihood of close of 70% grouped in with the smaller $1k opportunities; what we now see is a bimodal distribution, where depending on whether we close the big deal we are sitting on the lower or upper distribution – for the electronic engineers reading, there’s a sales ‘band gap’ J

Using this type of forecasting approach integrated with your real world CRM data helps figure out what the pipeline risk is more realistically. The big health warning is that you need good historically data on the likelihood to close, at different parts of your sales cycle and to apply consistently across a sales team, or you’ll end up with GIGO.