Planning out the future of your Business isn’t something new for you. However, your Nostradamus skills haven’t got better overtime and you struggle to identify which method might suit you best. You’ve been Googling this once and it seems there’s as many methods as stars in the universe, right? So we’ve selected the top 3 methods you should keep in mind when preparing this tough exercise – side note, you’ll notice there’s no need to be a senior statistician to understand them.
Before we get started…
The same way you’d need flour for this Lemon sponge cake recipe, you also need a specific list of data to perform this set of analysis:
- Sales in financial value per month (or ideally week)
- Sales in quantity, units per month (or ideally week)
- Stocks (weekly as well or monthly) possibly in stores and including warehouses (if applicable)
- Dates: make sure to have proper time stamps on your data
I. Moving averages – for beginners
That’s a pretty good start if you don’t have anything. You can perform this analysis on both 3-month and 5-month overview to compare.
This technique will give you a trend overtime but only works if the products you’re selling consistently increase or decrease in sales. In other terms, it is not a good tool to analyze seasonal aspects of your sales. That’s why you need to combine different techniques all together to obtain better results.
II. Forecasting in our good ol’ Excel
Yes, you read right – Excel offers forecasting. Depending on your Excel version (before or after 2016 version) and your level of laziness, you can choose between:
- Using a set of defined functions
- Using our Forecast Worksheet template (2016 versions and onwards)
Using a set of defined functions
Step 1: Forecast using on of those two functions
- Forecast.linear(): it’s a basic linear regression. Meaning that it will smooth everything out and draw you a plain, regular line showing a trend (increasing or decreasing one of course). The main problem is that it usually does not work with seasonality in data (mostly selling bikinis in summer and coats in winter are seasonality impacts on your yearly bikini sales for example but Excel won’t see the difference)
- Forecast.ets(): this time, Excel did good. This function takes seasonality into account. You can manually input the seasonality and other inputs but only do so if you know forecasting and the business pretty well, otherwise it will return an error or inconsistent analysis
Step 2: confirm Excel’s assumption with this final function
- Forecast.ets.confint(): once you’re done with your two previous forecast functions, use this one to confirm the analysis is OK. It’s like asking a Crystal Ball if the weather is going to be fine tomorrow. It will answer Yes or No. This function returns a confidence interval for your predicted values at a specific target date. Confidence by default is set at 95% (a standard in statistics) meaning that 95% of the future values will lie in this range of values
Using the Forecast Worksheet
Download our template file, simply select your set of data, both quantities/financial values along with matching dates. Click on “Data”, “Forecast” and “Forecast Sheet”. Nothing to do on your side, Excel manages this all for you.
Ta-daaaaa!
So, what’s wrong with forecasts in Excel then?
You may think this is magic and your results are strong enough to be shown to the entire world. You’re partially wrong. Why?
- Whatever you’ll read on other sources, those ready-made forecast remain regressions. Something linear. But sales and company life are not linear. AT ALL.
- You’re missing additional inputs – Excel predicts future values for a defined set of values which are, most of the time sales or quantities, but does not take other variables into account. For retailers as an example, stocks are major data to be considered as we’ll see in the next part.
- A prediction of a total number doesn’t tell you anything – you must replicate this technique to all the products you’re selling and imagine doing this with your 2,500 bikinis references sold on your website!
For inventories - Sell-through being the Key KPI
For retailers, it won’t be a surprise to mention that sell-through is one the most important KPIs you must know on your daily activities.
Sell-through is the percentage of a product that is sold to consumers relative to the total quantity available in stores or in the warehouse.
Most of the companies calculate their sell-through on a monthly basis, however it can be on a shorter scale if the business evolves quickly. In addition to that, you must consider removing unsold stocks to make sure not to affect your sell-through calculations. If we refer to the example above, Item D has the lowest sell-thru rate and the remaining unsold stock could be removed from this perspective if sitting on shelves of the stockroom for too long. In that case, they should be discounted. There’s no standard for this metric but a dormant stock reach an average of 3-month low to no sales activity.
However, imagining a theory around Item D’s performance in this example is impossible. A low sell-through rate won’t tell you why a product isn’t selling. There’s actually of wide range of factors affecting your sales more than you could imagine: customer personas, seasonal or permanent pieces, upsell/cross-sell combinations etc.
They can’t rely exclusively on their sales data. And this is where a single human analysis isn’t enough to reveal your products’ potential or weaknesses.
Combining all the previous techniques we covered above is complex. Futhermore, it wouldn’t cover all the realm of your activities and related impacts on your sales.
This is were AI-powered solutions can come in handy.
Taking over the burden of crunching all your existing sales data and turn it into actionable forecasts, ready-to-use forecasting analytics help you get the insights you need... fast!
