Monte Carlo Simulation of Sales Pipeline Projected Yield
I have partnered with a number of B2B software companies on behalf of Spark, and in each board meeting for these companies we review the sales pipeline. We look at the top deals in the pipeline and their likelihood to close. We consider pipeline coverage for upcoming quarters in hopes that it will be a leading indicator of whether we will hit our future goals or not.
When we review the sales pipeline, we often look at a weighted average of the Total Contract Value (TCV) of the prospects multiplied against their likelihood to close. Likelihood to close is usually defined by a sales team policy. Some companies define likelihood to close based on deal stage (have you done a demo yet? Is the contract out for signature?); other companies do a deal book review with Account Executives to determine likelihood to close in a more subjective way. The weighted average of the pipeline is the primary methodology of sales pipeline analysis I’ve seen in pretty much every B2B company’s board deck, and it’s often a company KPI.
But the weighted average alone tells an incomplete story when dealing with probable future outcomes and uncertainty. When you have a series of deals that each have some probability of closing, you have a rich probability distribution which is likely not normally distributed. A couple large deals in the pipeline can usually make-or-break the quarter for a sales team. Why not run a Monte Carlo simulation on a sales pipeline in order to really understand the full range of outcomes possible in this probability distribution? A Monte Carlo simulation will “play forward” the future by rolling dice against the probability of each deal in the pipeline, in accordance with the probability that deal will close. Monte Carlo naively simulating the future outcomes of the pipeline a thousand times, and then one can look at all those outcomes in aggregate to get a better feel for how the future might unfold.
I tweeted out asking if any sales team used Monte Carlo simulation on their pipe, and the tweet got a handful of likes, but no one showed me an example. So, I built this on a whim. Here’s a hypothetical sales pipeline and my work. The first sheet has all the raw data:
and the second sheet has the histogram of the Monte Carlo simulation results.
The x-axis is bins of the sum of the pipeline yield and the y-axis is the frequency with which the Monte Carlo dice rolls landed in that bin. The integral of that histogram is 1000 (for our 1000 simulations). As you can see, landing one large deal significantly impacts the outcome of the simulation, and there are a whole bunch of probable outcomes (eyeballing the histogram, looks like ~11% of the outcomes) on the left side of that histogram where the sales team fails to land a large deal.
When looking at a histogram of a monte carlo simulation, I find the following key variables informative:
Events outside those 95% thresholds are unlikely, but you should be prepared for the full range of events that happen within those 95% guard rails. In the case of my dummy data, you could drive a truck through those 95% gates. Most real world examples from actual sales teams in market will have more prospects and less variance in deal size, so the 95% gates will be tighter, but I think most leadership teams and boards would be surprised at the range of possible outcomes that they haven’t accounted for while staring down the results of their sales pipeline Monte Carlo simulation.
By contrast to the data generated by a Monte Carlo simulation, most companies only report weighted average of the pipeline, which will always be very close to the mean of the simulation above. The weighted average (or simulation mean) is misleading precise, too narrow, and fails to provide insight into the full range of possible outcomes.
Unfortunately, Google Sheets doesn’t have native support for running a Monte Carlo simulation, so here’s the code I wrote in App Script in order to generate 1000 Monte Carlo dice rolls against the sales pipeline outcomes. It’s pretty hacky code with hard-coded cells, but if you can write a Javascript “Hello World” then I think you can adapt it for your purposes in combination with the spreadsheet above:
function RunMonteCarloSim(total, runs) {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 4;
for (var i = 0; i < runs; i++) {
var monteOutput = sheet.getRange(“L” + row);
var trialSum = sheet.getRange(total);
monteOutput.setValue(trialSum.getValue());
row = 1 + row;
}
}RunMonteCarloSim(‘J24’,1000);
An ending caveat: I’m not sure whether I’d recommend all the B2B companies I work with do this analysis. This level of pipeline analysis is probably overkill for some smaller companies, and those calories spent doing this analysis would very likely be better spent on sales team training or building sales support materials like case studies or scripts. But, that’s why I did this brief dummy sample and published my work, so, in theory, it will be easier for others to copy without reinventing the wheel. And for larger companies with more analytical bandwidth, it could be a more useful exercise. I wonder if any sales CRM products offer a Monte Carlo simulation baked directly into their product already? If not, it wouldn’t be to hard to add a feature to automatically generate this type of report. I’m sure there must be a Force.com app (or a dozen) floating out there already.
