You are in charge of making a sales forecast and want to know what are the methods to make a forecast? Maybe you wonder: why is sales forecasting important? You will find in this post information about how to make a forecast, both in terms of methodology and tools to use.
You should have first look at our product page to see how we could help you building a sales forecast. Forecasting is very easy with datainsightout.
Sales forecasting is not a monolithic concept
3 key component to make a sales forecast
Sales forecasting depends on three things: the historical data which are available, the business context and the resources at your disposition: sales forecasting has many faces. Best case scenario, you have historical data at a daily level for the past three, four, five years or more - or worst case scenario, you might just have very little data available and will need to figure out how to make the best of it.
You might also need to develop more a business case rather than a pure sales forecast - or in the contrary, want to make a forecast for each sales person and understand where their customer portfolio will look like at the end of the year in terms of turnover.
The sales forecasting might be part of a company-wide three-month process with a top-down and bottom-up approach or something you need to figure out quickly and can tolerate approximations. Sales forecasting is a complex exercise and can have different realities - but, hey, forecasting is critical for any business. But as you will see, forecasting can be fun and is always an interesting challenging exercise if you like intellectual challenges. Demand forecasting is usefully throughout the company and the purchase department might also need to be on top of their forecasting skills.
When you have no historical data
Business cases and return on investment cases sales forecasting: This is the case when you deal with a new project and you need to have a sales forecast component. Getting a reliable projection however will be more complex as you can imagine. Being a new project, a new investment, a new market, this means that you won't have any data to use to model a projection. The tools your are going to use to build a topline projection and the methodology are going to be different - but serve the same purpose.
Upload a CSV and start generating sales forecasts at scale with time series analysis.
Step 1: Understand the type of sales forecast you need - and get the right data to build your forecast
If you need to do a sales forecast for an existing line of revenue...
Doing a sales forecast for a known activity is the easiest use case in terms of data gathering
You just need to fetch the data from your datawarehouse, ask your finance, planning and analytics department to provide you the historical data or pull yourself directly the data from your business intelligence reporting tools (Qlikview or Tableau Software allow you to define your view and download the report you need). If you don't know about the perimeter to download, the rule is simple: always go to the most granular level and choose to download dailies rather than weeky or monthly level data. You will get a more precise forecast if you use daily data.
Get all useful business dimensions - but don't get too granular. Downloading past results at a market level or at a product level can be a good idea in most of the cases as they will be by essence different from one another and could help explain the activity trends observed. However, you might not want to have too many dimensions to end up having a huge dataset: this will be almost impossible to handle and end up confusing you. Maybe you need to run a contribution to growth analysis to deep-dive further in the growth mechanisms of your business.
The rule of thumb is the following: Is the dimension a business driver or differentiator - or not. Modern data system are powerful and in the digital area, it's possible to collect and save almost everything at a cheap cost. But everything is not helpful when it comes to make a sales business prediction. So think about the breakdown which might be the most relevant to precit your future level of sales and just focus on a limited set of dimensions.
Pay attention to the data perimeter as you might end up with misalignement throughout the company: do you need to withdraw customer returns from your past orders - or do you want to let them in? What is the source of data your are going to use? Which table will you use? Is the source of data and the perimeter in line with the common financial standards of your company? Even within the same company, two tables might have small differencies in terms of logged information - make sure to be in control of the origin of the data you will use. Getting the support of your FP&A department is critical to ensure alignement and best practice.
How far in time the sales forecast should be? There are no rule, but a common practice if to do a sales forecast for the next 12 months at a monthly level. Beyond this time, you shouldn't take much risk going at a monthly level and stick to a yearly aggregated level. You could easily project your activity for the next 3 months at a daily level, but going further might be more complex if you want to get accuracy. Possible, but less accurate.
Unit sold or monetary value? You could go for both metrics. If you sell cars, then you can build a forecast with the number of car being sold (unit) or by using only their value (monetary value). A common practice is to use the monetary value only for doing a sales forecast. Forecasting is a complex process and having to translate unit solds into monetary value will add complexity to your model.
If your sales forecast concerns a new activity...
In this case, direct historical data to support your forecast will be missing. You might have however many ways to make it up for this missing information. The challenge when it comes to forecast a new activity, a new market or the sales of a new project is too clearly identify the most relevant drivers of your business case and get the best market assumptions.
Undertand what are business drivers. Sales forecast will have underlying drivers, metrics and KPIs which are going to have a direct impact on the sales performance. It can be things like : number of customers, percentage of new adopters, market or product ramp-up assumptions as you enable more and more features, average selling price, size of the population you could reach. You should focus on leading metrics and not on lagging metrics. Just a quick example to hel you understand. Leading metrics could be the number of active customers, how many transaction they make per year at your shop and the average size of their basket and these two combined, you have a lagging metric: the turnover. You can influence leading metrics - lagging metrics are just the result.
Use information coming from market researches and triangulate them. Do as much as possible market researches to capture relevant information. . Use the metrics you have seen in the past in existing markets and try to figure out if you can project them in your sales forecast for your new project. This is not ideal, but doing this triangulation, leveraging existing published market researches or buying them, will help you reduce the window of incertainty. Reducing incertainty is the goal of every sales forecast. The market you're about to go into might be new - but you probably already have an existing business - you could establish parallels from one market to another. Compare, benchmark, reduce uncertainty.
Let' consider the following example: If you want to sell let's say hygenical masks to italian pharmacists, you might end up assessing what is the number of pharmacies in Milan or region. If the information is not available after some googling, you might end up 1) assessing the number of people living in Milan, 2) assess a ratio of pharmacy / number of people which would sound reasonable and 3) deduct the number of existing pharmacies. You might get this ratio by dividing the number of population living in Italy and the official number in the country of pharamacies - which are two drivers metrics of your case failry easy to get on internet.
Step 2: "Top-down" and "bottom-up" are two complementary angles for a robut sales forecast
Top-down sales forecast starts with market data
How many smartphones have been sold last year in the UK? What is the size of the online shoe market? Starting from high level market data, assessing the growth of the market and then establishing an aspirational goal are the steps to do a top-down sales forecast. Advantage: you will make sure not to lose any market share and if you had planned to grow by +10% when the market grows by +20%, then you know you will need to revise your plans. Disavantage of a top-down ony approach: the data used to build it are generally 100% correct and when you start to dig deeper, then you realize that your assumption stands on a sand castle.
Bottom-up forecasting happens when you go from granular data to get to a final consolidate sales forecast number. This is the opposite: you start by using concrete data such as the number of items sold per month last year per market and extrapolate the overall sales level for next year. You can use information you have, such as the turnover per sales rep on average, the average basket, the number of transaction per customer: these data are realistic and help grounding your forecast into hard reality. Depending on the complexity and the number of forecasts you need to build, but you might ask yourself: should I use a statistic based forecast or not?
If you do a bottom-up forecasting, you should know about the difference between organic forecast and incremental forecast. Hopefully we have an article covering this topic.
Step 3: Using the right tool to balance between precision and being efficient
Use data science techniques and time series to forecast using past data
Sales forecast precision can be achieved using modern machine learning data science techniques. Time series analysis can be used to build a sales forecast with precision.
There are many sales forecasting algorithm you can use:
- Autoregressive Integrated Moving Average - best known as ARIMA forecasting
- Seasonal Autoregressive Integrated Moving-Average - SARIMA sales forecasting technique
- Holt Winter’s Exponential Smoothing
- Vector Autoregression Moving-Average - also known as VARMA forecasting technique
- Datainsightout is using the Prophet algorithm, which is an additive regression model which factors seasonalities and country specific holidays patterns.
These statistical sales forecasting techniques are efficient - and will help getting your sales baseline forecast (or organic forecast) pretty quickly, and save you the pain of factoring in multiple effects such as understanding weekdays versus weekends consumption patterns, holidays patterns, general trend or leap year effect. Some algorithms (the one we use!) like Prophet have embedded the holidays and calendar effects. This will be a forecast, meaning not 100% accurate - forecasts are never accurate. But will constitute the best practice to adopt to project your future level of sales.
We can help you understand how to use statistics and machine learning for time-series analysis.
Excel will be your companion for the rest
Excel offers many forecasting formulas and possibilities. Excel is a great tool to combine multiple forecasts - or to build directly forecasts into it. Excel offers the possibility to compute a CAGR. Excel has very simple forecasting formulas embedded as native functions you could use to project sales trends.
These functions might not be as powerful in terms of data fitting than pure Python or R machine learning models - but still will help. Excel will also be good to quickly play with the different hypothesis of your sales forecast: change a parameter in one cell and understand the sensitivity impact on other metrics and sales projection outcome. When you have a forecast made out of machine learning techniques, you should use excel to correct the results, based on your knowledge of your activity.
Using Excel, you can use this simple and basic methodology to build a forecast.
Forecast at a daily level and then aggregate them by week, month, quarter or year
You might need to do a monthly forecasting to keep track of the outlook of your activity. This is a merely a level of aggregation. What you need is to get the forecast done at a daily level: only by doing so, you will be able to factor in your forecast weekdays patterns versus daily patterns, bank holidays, black friday, Christmas or Thanksgiving effects.
You will be able to sum the forecasted period at a weekly or quarterly level. It will be easier if you build your forecast in this direction, rather than doing a monthly forecast and turning it into a weekly one.
Step 4: Sales forecast methodology and high level verification
Once you get your sales forecast, the journey is not over. You would need to compare what you produced with "the big picture". Big picture doesn't mean necessarily current market projections but where your company wants to go. A forecast outcome is fine in itself, but you would need to check that the result produced somewhat "fits" with the overall company goals and aspiration. Strech should be added to sales forecast to make it aspirational - and secure the bottom line. No sales forecast can be given to sales rep "raw" and every good sales forecast has a business strategy component - or better said: a "political" factor.
If you used a time-serie forecasting model, then you would need to test the accuracy of the model you built. You might also want to re-run a forecast in special occasions such as the COVID19.
Break it down at a sales person level and get the impact of the overall number at sales people level. This sales forecast need to live in your sales rep portfolio. To understand how realistic your sales forecast is, you will need to break it down and assign it to your each of your sales rep. This will also help to understand how realistic your sales forecast is - and if additional people ressources are required.
Match your sales forecast with the overall cost structure of the company. Every month, the company need to face fixed charges, such as paying the wages, paying the software licenses or paying the rent. The question is ask is: will the level of sales forecasted be high enough to reach the break-even point and start generate profits? Putting this sales forecast into perspective will help to assess if you need to factor a strech or not.
Use year over year % growth to check your forecast and graphics. One good technique, once you get to the absolute value, is to calculate the year over year growth rate. What is the general trend? Do you see a business growth acceleration or a softness? When? Starting which month? Getting a plot of the projected sales level is good to quickly spot what seems right or not.
Remember, sales forecasting is a disciplin between hard data science and art. You can use complex algorithm to help you - but in the end, it's all about knowing your business, its fine mechanisms, your market and assess correctly your ambitions.