How Do You Do Predictive Analytics in Excel Without Rocket Science? The Easiest Sales Forecasting Template.
You know the problem entirely. You are using Excel for your sales forecast. Your spreadsheet reports your next quarter revenue, and you need a reliable result.
Accurately forecasting sales is critical for your job. You are an experienced sales manager and have a strong affinity to sales analytics. Only with precise sales forecasts can you negotiate healthier sales goals with your team and management.
With reliable sales predictions, you can improve your sales process and prioritize accounts.
How do you forecast sales today?
You are estimating future sales using your team’s inputs. Nobody knows customers better. Once a month, every sales rep takes a few hours to fill up their estimations.
And your sales team can accurately predict your total revenues in the next quarter. Your sales forecast, however, is rubbish. How come? Let us explore some ideas about sales forecast using an excel example.
What happens during your sales forecasting?
Your sales managers send your controller every month a twelve-months rolling forecast. It takes them around half-a-day monthly each to fill the spreadsheet knowingly.
You (or your controller) aggregate the forecast using each input to estimate next quarter’s total sales. And finally, when the quarter is over, you compare how good your sales forecast was.
Look at the example below:
Now you wait three months and see that you sold 2.000.000 €. By comparing your team’s estimations and the actual demand, you get an idea about how good your sales forecast is. And you can do that in excel, fact.
Your team predicted, for example, two million euros sales and, three months later, you sold two million euros. How good is that?
How good is your sales forecast?
It turns out it is not bad, not bad at all. You wrote “2.000.000 €”, and that is what you sold. Predictive analytics with excel is relatively easy, after all. You got the total sales of the quarter wholly right. So, bingo! Problem solved?
Not really.
How good is your sales forecast, indeed? It might have been pure random. Furthermore, how much does it cost you to forecast sales? Is there any better way to do this without stress or rocket science?
To all these questions, there is an answer: Predictive analytics. And you can do it with Excel.
Let’s review each point in turn and see how you can implement a simple Predictive Analytics Model in Excel to estimate sales figures without rocket science.
How to measure the performance of your sales forecasting model in excel?
First and foremost, two pressing questions, if you have not thought about them, what is a model and how do you recognize a good one?
A model is a simplification of reality. Experts use models continuously to solve problems using data. Second, for a sales forecasting model to work well, it will need to solve the problem you face.
What is a good model? Well, here, the answer might vary.
A good model could be the one the most accurately and consistently delivers your future sales revenues or the one that costs you the less to implement.
The first aspects to consider when creating a model in excel is which one is your target variable, and which one is (or are) the dependent variable(s). Your target could be the total sales of the next quarter or the specific number of products you will sell in the subsequent rolling twelve months.
If you are a distributor or a manufacturer of thousands of products, you could see that the second problem is a lot more challenging. You might get all your demand-per-product predictions wrong, yet your total sales right.
Errors can balance and compensate for each other. You get the misleading notion that your process is working, from a horrible granular forecast. Having a false impression that your sales forecast is working is very dangerous – when it is not.
Sales forecasting is critical in B2B.
Let us again bring your excel sales forecast template into the discussion. For simplification, I will also assume that you have already consolidated all the individual predictions into one. In other words, you added John’s, Maria’s and Klaus’ inputs into one.
Furthermore, continuing with the example above, we will add the real sales per product, and we will assume that you got the total sales of the quarter right.
Now look at the chart below:
There is a null deviation in the total forecast. However, in this chart, you can see that even if your sales team misses all individual sales forecasts per product, they might still get the total correctly — Fortuna te favet.
In other words: How many individual products have you accurately predicted? If, for example, we take an absolute 10 % as the maximum tolerable deviation, I will argue that you have only forecasted one article correctly out of seven (the number 7).
If you had produced or stocked your products based on after forecasting them, you would have ended with production bottlenecks, pricy overstock, and angry customers.
The easiest Predictive Analytics Excel Template for sales forecasting.
My great-grandmother emigrated to Argentina at the end the 19-century from a small village north of the Lake Como, Italy. Once arrived, she lived in the middle of the country, Cordoba, later moved to Buenos Aires. These three places have a completely different climate. She was still very good at predicting the weather.
If you asked her, “what is the weather tomorrow, Nonna?”, she would say: “like today”. And she right most of the time. How could she do it?
Meteorologists have a name for this effect. It is called “persistence”; the hypothesis that the weather will be the same tomorrow as it was today. My great-grandmother was betting on persistence.
Persistence is one of two necessary checks that any weather forecast should pass to prove its performance. The other one is climatology. The model must be better than the long-term historical average of
conditions on a particular date for a specific region.
Therefore, successful sales analytics test forecast against their persistence and their long-term average. You can do the same. Let’s use our excel example to picture this idea, where I have added two further columns.
The first column is the sales team’s total forecast, followed by the previous quarter’s sales and a long-term sales average. The last column is the real revenues that we were trying to predict accurately.
As you can see, both the persistence and the long-term average are less accurate for the total sales, yet a higher accuracy per each product line. Both would be by my definition much better models (and a lot cheaper!).
CALCULATE NOW THE ROI OF QYMATIX PREDICTIVE SALES SOFTWARE
How Do You Do Predictive Analytics in Excel Without Rocket Science? Summary
Let’s sum-up. Using the previous quarter and a long-term average, you can create an Excel sales forecast without rocket science.
But do not stop there. You can combine them and design and an ensemble of models. Please make your example using our predictive analytics excel template discussed here and tell us how it goes.
Repeat the assessment comparing with previous quarters how good your model would have predicted your sales. Now see if several combinations of these models would have performed better.
Finally, release your sales team from the time-wasting and error-prone effort of having to foresee the future.
Your valuable Key Account Managers can invest their time in selling and supervising your model’s output before you use it. Remember to assess the cost benefits of your model as well.
Write to us today if you want to improve your sales forecasting.
I WANT PREDICTIVE ANALYTICS FOR B2B SALES.
Further Read:
Nate Silver (2015): The Signal and the Noise: Why So Many Predictions Fail – but Some Don’t.
Hyndman, R. (2006). Another Look at Measures of Forecast Accuracy, Foresight, Issue 4, 43-46.