…or Forecasting Sales using Randomness.
This blog is also available as a video and you can see it here:
In 2015 I was running a technology services company and for their own completely legitimate reasons, one of our largest clients told me they needed to reduce the team size by about 90% (and could be higher). It was inevitable that this would create some stress in our business! But how much stress? And what could I do?
This blog is part of my occasional series showing how concepts from data science and ai can help to solve real life problems (mostly). The situation I describe here was real. The stress on our business was real. The methods I describe are an amalgam of my first few iterations towards a result that I was confident in.
So, our business was in trouble. I knew that making some of our people redundant was my solution of last resort and that I’d leave no stone unturned to find a way through that was good for everyone.
But how would I know when I’d reached the last stone?
Cash would become tighter and I knew I’d need the support of our bankers. I could foresee a day, many months in the future, when I would be in a room trying to convince some very serious players that our credit lines should remain in place. If that meeting went badly then it could be “Game Over”
I turned my attention to our sales forecast which of course is a critical input to the cash flow statement and understanding the general health of the business.
For a number of years prior to that, our growth had been constrained by the rate at which we could increase our capacity (recruit and integrate more people). Our sales forecasting was no more sophisticated than it needed to be (fairly primitive!). Clearly, that wouldn’t suffice in our new world!
Someone said that “predictions are hard, particularly when they’re about the future”. And our sales forecasting problem threw up its own challenges.
Our sales teams were talking in detail to about 30 organisations at any one time. That included stuff that we were just about to win, right down to projects where we might have had a few meetings, established there was a project with some budget and we were discussing specific ways to engage. On average we would be discussing 3 or 4 opportunities per client.
So our list of opportunities that were sufficiently strong enough to have an influence on our forecast ranged from a few tens of thousands of pounds for early engagements like Proof of Concept or Workshops through to many hundreds of thousands for large scale transformation work that could last many years.
The start dates varied from imminent to more than 6 months away.
The percentage certainty that we would win varied a lot too:
With all those variables, it’s not a trivial problem to solve and if we consider just the following (fictional) potential revenue streams:
How much would you tell the bank was in your 3 month revenue forecast?
We considered a number of ways to solve:
There are at least three problems with the threshold approach:
Many people (including me at some points in my life!) might be tempted to multiply the revenue by the chance of winning and use that. Let’s have a look:
On the plus side, that method was explainable and repeatable. There was even some sort of logic to it. But:
I wouldn’t. I can just look at the numbers and know that it misrepresents the truth. And there is no combination of potential outcomes that actually leads to that forecast. It’s not a real number and I believe it has zero value.
In the real world:
And here we run into a dichotomy:
Einstein was onto something when he said that “Things should be as simple as possible, but no simpler”!
So what can we do?
Well, there is an interesting (to me at least!) method called “Monte Carlo Simulation”. The name suggests it might involve gambling and it relies on the fact that in a game of chance (say roulette) the results of an individual turn can vary wildly, but when it’s played long enough then the bank wins!! The randomness of a crystallised individual spin averages out over many thousands of turns.
In our world of sales forecasting, we can try crystallising a future scenario for each of the opportunities and see if we win the opportunity or not. If it’s 80% likely that we will win Opportunity 1 then we can generate a random number and if it’s less than 80% then we can say that in that crystallised version of the future, Opportunity 1 was won.
If you’ve never practised thinking in this way before, it’s probably worth lingering on the previous paragraph and I’ll give you another bullet point to try and help cement the concept:
As the opportunities are all unconnected then we can crystallise a potential future for each of them too with their own random number. Let’s see what that looks like :
This tells us that in one possible future represented by the numbers in the column “Crystallised Future #1”, we would win Opportunity 1 and Opportunity 3. Our 3 month forecast would be £86k (25+30+31). Make sure you can see those numbers and where they come from on the grid.
“So what?” I seem to hear you say. And it’s not a bad call!
This single imagined future is no more likely than any other imagined future. Why should we pick this one? Why not create some more crystallised futures with different random numbers, perhaps they will be better?
The power of this thinking starts to appear when we realise that we can repeat it ten times and crystallise ten possible futures, each of them equally likely.
Brace yourself! The table below shows ten possible futures crystallised and with the associated revenue in months 1, 2 and 3. Can we conclude anything helpful from the data?
In these futures, we were so unlucky, that on one occasion (#9) that we didn’t win any of the opportunities. Man life sucks sometimes!!
A useful way to summarise these numbers is to say that:
Now we can start to get a feeling for how likely we are to achieve certain numbers
If one crystallised scenario is worthless and ten seem to add some value, we could probably conclude that more scenarios would provide an even better insight.
So, if we can do it for ten, we can do it for a hundred, or a thousand, or a hundred thousand! Each of the crystallised futures just as likely and valid as any of the others.
When we generate enough random futures and analyse them carefully to see how many of those futures generate which results then we can get a much better understanding of what our future might actually hold.
The following graph shows a similar summary but considering 500 scenarios
Take careful note that the revenue scale up the left hand side is logarithmic to make sure we can see the important detail at the lower levels. The x axis is the percentage of scenarios
Our understanding of our possible futures starts to become clearer and some key points to take out:
We’re analysing hundreds of crystallised, equally likely scenarios to see what outcomes they generate and using that to gain insight about our likely future. If you google: Monte Carlo Simulation, there’s a lot of additional material you can find.
The technique is really useful if you need to combine lots of dissimilar things that are uncertain.
I first came across Monte Carlo Simulations when I was talking with a client (mineral discovery etc) and as a throw away comment he mentioned that there was a room full of servers with high end graphics cards running hundreds of millions of Monte Carlo Simulations. It was a side comment and it would have wasted too much time to dig into it as we spoke. But my mind was racing! Hundreds of millions of Monte Carlo Simulations!!!! A bit of Google Fu on the train home and a whole new world opened up. Using randomness to simulate complex and uncertain situations many, many times and then analysing the distribution of those simulations to gain insight! Genius!
In this explanation of sales forecasting with Monte Carlo, I ignored some of the complexities of real life:
We weathered the storm.
I did meet with the bankers and as I’d hoped, the preparation we’d done gave me strength.
I believed my numbers, my story was compelling, our bankers supported us and our credit lines remained in place.
When I first started to think about this blog I wanted to try for two things :
Thanks for taking the time to stay with me and I hope you found something interesting and useful.
If some transformational event is causing your organisation to become stressed and you’d like to discuss ideas for finding a path through, then please give me a call.
Curious problem solver, business developer, technologist and customer advocate