A sales forecast, like a weather forecast is an educated guess at what the future may bring. You can build your forecasts for anything from the weather to stock market futures. You can go about making these predictions all sorts of ways: making your best guess; compiling and merging other people’s guesses; or you can even forecast based on your dreaming.
There are some inexperienced decision-makers in business who have no faith in sales forecasting. People, who have had no real experience with forecasters, seem to imagine them as some combination of a weather forecaster and a fortuneteller with a crystal ball.
Forecasting is a not a magic. Forecasting is a solid math!
The truth is, real business forecasting uses quantitative data collection methods, mathematics, and logical reasoning, there is very little guesswork involved. Your historical data can be used to make accurate predictions about what will happen in the future, allowing you to order the appropriate amount of stock, hire enough employees, and ensure your business runs smoothly. The financial gains that can be achieved with business forecasting are real.
With the help of Excel, you can use your historical data to make predictions about the future. By looking at your data from the past, you can extrapolate trends that will allow you to make forecasts that are accurate and trustworthy.
These forecasts are useful in all areas of business from sales, to marketing and management,. With your new knowledge on forecasting, you are on your way to making better business decisions.
Collect Your Data
Before you can start forecasting, you need to collect your data. For your first forecast, you need to have the data for at least one year of revenues, but if you have data from further back, your forecasts will be even more accurate. Other data you might find helpful for creating forecasts would be tax statements, employee pay rolls, order and inventory records.
Once you have your data collected, you can start creating your first forecast with Excel.
When using Excel, you have to enter your data in the form of a list. There is nothing mysterious about an Excel list; it is like a simple table.
When you start Excel, you will see a blank worksheet. This worksheet consists of columns and rows. You will enter your data into these columns and rows based on three rules.
Rule number one: ‘Separate Variables into Columns’. You have to keep different variables in different columns. For example, you can put sales dates in the first column, revenue in the second column, sales representative names in the third, product in the fourth, and so on.
Rule number two: ‘Date Goes First’. Once you decide on which variable to place in each row, you will use the first row to label your variables. For example, you might put “Sales Date” in the first row of column A.
Rule number three: ‘Each Row is a Record’. You have to keep different records in different rows. When it comes to recordings sales information, it is best to keep each row as a separate period of time. For example, you could put information about sales that were made on April 10 in one row, and information about sales that were made on April 11 in another row. Depending on the scale you want to look at, you can also put individual sales in each row.
What you are doing here is “ordering your data.” You are telling Excel how much you sold in each period. That is why you want to enter your data in chronological order: by day, month, and year. Because you will be forecasting into the future, your data should be ordered in ascending chronological order: your oldest data will be at the top and your newest will be at the bottom.
If you want to forecast the future, you need to get a handle on what has happened in the past. So, you will always start with what is called a baseline. This is your history, your revenue and sales numbers, order history, and so on, compiled into comparable periods. By lining up comparable sets of data, we can start to get an idea of what trends have happened in the past and what the future may hold.
Some examples of baselines could include total monthly revenues from year 1 to year 3, which could be used to make a forecast for next month’s revenue; or number of units sold weekly from the beginning of the year through the end of the year, which could help you forecast next week’s potential sale numbers.
Data arranged like this is sometimes called a time series, but in this course, we are using the term baseline.
When you start with your forecast, you have to decide what period you want to cover.
Forecasting sales on a daily basis is generally not necessary, unless you are forecasting for an ‘annual sale’ day. If your company is like the most, you will need a bigger picture. You will need a longer period for your forecast if you want to use it to plan your inventory levels, make decisions about number of salespeople to hire, and to figure out what your expected revenue will be.
You will need to match your selected period with your reasons for forecasting. Typical periods are a month, a quarter, or a year.
For ordering inventory, you may want to forecast your sales for next month. For estimating earnings, you may want to forecast your sales for the next quarter. For making hiring decisions, you may want to forecast your sales for next year.
Think of Accuracy in Advance
The more data you have entered, the more accurate forecast you can make. If you are trying to make a monthly forecast and only have three months of data, your forecast may not be very accurate.
The basic idea with all forecasting methods, is that something regular and predictable is going on, called the signal. Beer sales regularly rise on major sport even dates and predictably slope downward on other days of the month. Sales of skating shoes regularly rise during the fall and winter, and predictably drop during the spring and summer.
On the other hand, when something else is going on, something irregular and unpredictable, then it is called noise. If a local sporting goods store has a sale on, discounting skating shoes from May through July, you and your friends may buy new skating shoes during the spring and summer, even though the regular sales pattern (the signal) says that people buy skating shoes during the fall and winter. As a forecaster, you cannot predict this special sale. It’s random and tends to depend on things like overstock. It is noise.
When you forecast, you are trying to separate the signal from the noise. To help with this separation you will want to impose some order on the chaos of numbers. One way to do this is to use equally spaced periods of approximately equal length to create a baseline.
The fundamental idea for sales forecasting is that market forces push your sales up or down. When you average your sales results from month to month, quarter to quarter, or year to year, you can get a better idea of the long-term trend that is influencing your sales results.
A moving-average forecast for a given time period involves previous periods. For example, you find the average sales results of the first three months of your data set.
Then you find the average of the next three-month period and so on.
The resulting chart is your moving average that is often called a moving average forecast.
Now, you can get an idea of the general direction that your sales are taking. With the moving average, you can forecast your sales one period ahead.
Before proceeding with the sales forecasting, you need to walk through the short checklist.
First, you want to select the right number of periods. If you use too few, the forecasts will respond to random errors in the baseline that you want to smooth out. If you use too many, the forecasts lag behind real changes in the level of the baseline — maybe too far for you to react effectively.
Second, make sure to use the same number of actual observations in calculating each moving average. If the first moving average that you have uses three periods from the baseline, then all the moving averages in your forecast need to use three periods.
Moving averages work very well with stationary baselines whose levels do not increase or decrease much over a long period. You can use moving averages with baselines that trend up or down, but you should usually detrend them first.
How do you distinguish a stationary baseline from one that is trending up or down? This baseline certainly looks stationary. It has spikes, peaks, and valleys, but overall the baseline does not appear to trend up or down. On the other hand, this baseline is clearly trends up.
However, sometimes it is not entirely clear whether it is stationary or trended. We will give you a hint. Usually, if you cannot instantly recognize the trend in your baseline then, most likely, there is no trend. Do not stare at your chart for too long!
Every data set is a mix of signal and noise. Signal usually carries the information that we care about. Noise is simply the discrepancy between the signal and the actual results. How do you identify that signal?
The theory says that over time, some special and unpredictable events, which constitutes the noise, averages out. Moving averages try to get the noise to cancel itself out in the baseline, and emphasize the signal.
Where does all of the noise come from? There are many more sources of noise than of signal. Every one of your customers, every one of your sales representatives, your production facilities, your distribution channel, possibly your customers’ customers, is a source of noise that drags your actual results from the signal.
You never see the true values of the signal and the noise in practice, but the best you can really do is:
- Calculate a moving average using your actual results;
- Treat the moving average as if it were the signal — this is the whole point behind getting moving averages;
- And, treat the difference between the actuals and the moving average as if it were the noise.
Once you have your baseline you will be able to track any trends. A trend is the tendency of the level of a baseline to rise, or fall, over time.
When forecasting future sales, a trend line will indicate how well the gradual growth in revenues tracks against the dates when the revenues were recognized.
Generally, the greater the incline in the trend line, the stronger the relationship between the time period and the revenue
Regardless of the direction of the trend, rising or falling, the fact that a trend exists can cause problems for your forecasts in some contexts — but there are ways of dealing with those problems. For instance, over the course of a year, your baseline might rise and fall on a seasonal basis. Perhaps you sell a product whose sales rise during warm weather and fall during cold. If you can see roughly the same pattern occur within each year, over a several-year period, you know that you are looking at seasonality. You can take advantage of that knowledge to improve your forecasts.
Correlation is a fundamental part of forecasting. You can do forecasting without knowing a thing about correlation, but you handicap yourself if you do not bother. Correlations are key to understanding forecasts, and they play an important part in diagnosing how well your forecasts work. Better yet, they are easy to understand.
The first thing you need to understanding correlation is two variables. Let us start by assuming those variables are people’s height and weight. You know just from general life experience that the taller a person is, the more that person tends to weigh. It is not anything like one for one; however, you know that there is a strong tendency for height and weight to go together.
Imagine you have data on a people height and weight for a dozen of strangers. You used that data to plot a chart. The points on the chart that are higher up the vertical axis also tend to be farther along the horizontal axis.
These data points do not lie directly on a straight line, but you can imagine one running through the middle of the data.
Once you have your trend line set, you can make some statements about the relationship between two variables. The closer those points come to lying on the imaginary straight line, the stronger the relationship between the two variables.
You can express the strength of the relationship with a number. It turns out that, because of the way it is calculated, the number must be between minus one and plus one. That number is called the correlation coefficient.
If the correlation coefficient is positive, then the imaginary straight line runs from the lower left to the upper right. If the correlation coefficient is negative, then the line runs from the upper left to the lower right.
The closer the correlation coefficient is to plus one or minus one, the stronger the relationship. The closer it is to zero, the weaker the relationship.
The R-squared coefficient, called coefficient of determination, is the square of the correlation coefficient. R-squared measures how much variation in one variable is attributable to the other variable.
Using Excel, you can calculate the correlation coefficient between two variables very easily. Just use the CORREL function, with two ranges of data values as its arguments.
Regression. The Formula.
Now that you know some of the fundamental principles necessary for forecasting, how do you do it? You can use a regression formula. Regression is a standard technique in forecasting.
When you use regression to make a forecast, you are relying on one variable to predict another. The idea behind regression is that one variable has a relationship with another variable. Suppose your company sells consumer products. It is a good bet that the more advertising you do, the more you will sell. At least it is worth checking out whether there is a relationship between the size of your advertising budget and the size of your sales revenue. If you find that, there is a dependable relationship — and if you know how much your company is willing to spend on advertising — you are in a good position to forecast your sales.
Here is the regression formula for your forecast:
Sales = a * Advertising + b
Sales is called ‘response’, or ‘dependent variable’. ‘Advertising is called ‘factor’, or ‘independent variable’
‘a’ is the regression coefficient of factor ‘Advertising’
‘b’ is a constant , often called ‘intercept’
However, regression does not work well with baselines that have sudden and prolonged changes in level. Also, mathematics of regression requires that you have more time periods in your baseline than variables in your forecast. The rule of thumb is to have at least 10 records per variable. For example, if you are using 3 predictors, a baseline needs to be at least 30 periods long.
Making sure you understand seasonality, will reduce forecasting mistakes. A seasonal baseline is a baseline that rises and falls regularly. For example, swimsuits have higher sales revenue during summer and lower sales revenue during winter.
For accurate forecasting, you have to manage seasonality, and know the difference between a cycle and a seasonal pattern. A cycle is different from a seasonal pattern because it often covers more than one year. Cyclical baselines rise and fall but not necessarily on a regular basis. A good example is a stock market index cycle as it is often related to recessions.
For more information and practical examples we advice you to take the online Sales Forecasting course by SCANBA