How to forecast based on a sales pipeline

I work with a company in one guise at the moment, but I don't work with their Sales area. While I have no influence here, I am aware that their sales forecasting is broken.

Having worked in a number of companies in which I've been exposed to Sales, there is often one common yet fundamental issue with their forecasting: sales are considered as binary, things to be won or lost.

While individually this is often the case, it doesn't make for good forecasting.

In my view, there is a pretty straightforward way to forecast. In simple terms, you assign a value and percentage to each deal. The value represents the value of the deal in the event that it comes off. The percentage represents the likelihood of the deal coming off.

Sales table

Once done, you calculate the expected value of the deal, which is the product of the two. So, in the above table, if Deal 4 comes off, it'll be worth £183,000. But it only has a 60% chance of coming off. And so the "expected value" of the deal is simply £183,000 * 60%, or else £109,800. Expected value is a statistical term, one that makes sense here.

Now, that forecast is almost certain to be wrong. Deal 4 will either come in at £183,000, or it'll come in at nothing. But if you have enough deals in the pipeline and you are good at predicting likelihood, the above model works at an aggregate level.

For the 12 deals above, if all of them came in, they'd be worth a combined total of £1,492,000. But if you deflate the value of each deal depending on its likelihood to come in, then the expected value of the portfolio is only £685,600.

Take a simpler scenario, one in which there are 10 deals in the offing, each with a 30% chance of coming off, each valued at £1,000,000. An optimistic salesperson might give a sales forecast of £10,000,000. But if your likelihood percentages have been predicted accurately, then only three of those ten deals will come in, and the sales value will be £3,000,000.

I've worked with some companies that have formal definitions for their percentages. (If the legal agreements are drafted and are awaiting signature, then the percentage is 90%, for example. That means even at that stage, one deal in ten is expected to not come off.)

To me, this is the only way to forecast. You need to combine your likelihoods with your values to figure out the expected value of each deal before aggregating.


This entry was posted in General. Bookmark the permalink.