Forecasting is used to plan for the future. Predominantly, it’s used to plan for sales levels, but it’s not only sales that can be forecasted. Expenses, quantities – really anything at all can be forecasted into the future. As long as you have an adequate amount of historical information to base your forecast on.
Companies forecast so that they can be proactive rather than reactive. Additionally, using the techniques of forecasting outlined here, you’ll gain valuable insights into what drives your sales (or expenses). This allows you to manipulate the drivers behind what you’re forecasting and better benefit your company.
Forecasting sales, in particular, is beneficial because as you see in the operating budget and financial budget. Sales will affect how much you plan to spend on materials, labor, and overhead. Plus it will affect when you collect cash from sales.
Forecasting was also used in the capital budget worksheet. Forecasting accurately was critical in capital budgeting because the timing and amount of cash flows (both in and out) will determine whether you take on profitable projects and reject unprofitable ones.
Making forecasts can be as simple or as complex as you want it to be. With these two methods of forecasting, all that’s needed is historical information for whatever it is you want to forecast. The rest is, more or less, calculated for you.
Looking for more spreadsheet templates?
Forecasting workbook example and download
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
Explanatory forecasting
Explanatory forecasting is so named because it attempts to explain what factors drive the Dependent Variable. Knowing what drives the Dependent Variable gives you the opportunity to manipulate the Independent Variables that are in your control. Thereby controlling the Dependent Variable.
Explanatory forecasting input
In cell B54, input the date for the month that you want to begin forecasting.
Dependent variable
The Dependent Variable is the thing that is manipulated (potentially) by the Independent Variables.
Type in what the Dependent Variable is. In this example, you can see I’m using “Sales.” Below the description, for the months and years past, enter the amounts for the Dependent Variable.
Make sure you enter an amount for every month. Even if the amount is 0 – enter that. Do not leave any of the cells blank.
Independent variables 1, 2, and 3
The Independent Variables are the factors that you feel might affect the Dependent Variable. After this information is entered, you might be surprised to find out that what you thought was a valid Independent Variable, in fact, has no bearing at all. Conversely, you might find out that the Independent Variable affects the Dependent Variable to a greater degree than you expected.
Enter a name for each Independent Variable and then below that, just as you did with the Dependent Variable, enter the historical amounts for each month.
Then, in order to generate a forecast, you’ll need to project out into the future the expected levels of the Independent Variables. For instance, in my example, I’m using “Ad expense,” “Num of Salespeople,” and “Sales tax rate” as potential drivers for sales.
Ad expense and the number of salespeople are under my control. So, therefore, if they drive sales, sales are under my control – to a certain degree anyway. Sales tax rates, of course, aren’t under my control. But, they are probably less volatile than sales. Less volatile and easier to predict.
Once the planned amounts for the Independent Variables are entered into the future, that’s all that needs to be done.
Again, make sure you enter an amount for every month. Even if the amount is 0, enter that. Do not leave any of the cells blank.
Explanatory forecasting output
Forecast dependent variable
Here you’ll see the forecast for the Dependent Variable based on the Planned amounts for the Independent Variables.
The historical information for the Dependent and Independent Variables is where the relationship between the two is established. So, once you enter the Planned amounts for the Independent Variables, that established relationship is used to create a forecast for the Dependent Variable.
The total for all periods is also included at the bottom of the forecast.
Correlation
Correlation represents the degree of the relationship between a particular Independent Variable and the Dependent Variable.
This amount will always be between -1.0 and 1.0. A Correlation of 1.0 implies that a move in an Independent Variable is matched with a proportional move in the Dependent Variable. Conversely, a Correlation of -1.0 implies that a move in an Independent Variable is matched with a proportional move in the Dependent Variable, but in the opposite direction.
No matter what the two variables are – even if they seem completely unrelated – there will almost always be some degree of Correlation. If there is not, then there will be a Correlation of 0, implying that the variables are completely unrelated.
% determination
Correlation squared.
The % determination states the amount of change in the Dependent Variable that can be explained by a change in an Independent Variable.
For instance, if the % determination is 20%, then 20% of any change in the Dependent Variable can be explained (theoretically) by a change in the Independent Variable.
Fixed amount
This is the expected amount for the Dependent Variable if this particular Independent Variable was 0 (zero).
In other words, this is what you can expect the Dependent Variable to be if the Independent Variable was nonexistent.
Variable amount
This is the expected change in the Dependent Variable given a change of 1 in the Independent Variable.
For instance, if the Variable amount for a particular Independent Variable is 14, then it can be estimated that a change of 1 in an Independent Variable will translate to a change of 14 for the Dependent Variable. The bigger this number, the more drastic the effect that you can expect that particular Independent Variable to have on the Dependent Variable.
Explanatory forecasting chart
This chart is indexed to 100% for all of the Independent Variables. This is done because the amounts for the Independent Variables might be drastically different. By indexing to 100%, and then showing the amount they rise or fall from there, makes it easier to see how these variables might rise or fall together.
Extrapolation forecasting
Extrapolation forecasting aims to do the same thing that explanatory forecasting does, except it goes about it in a different manner.
This method of forecasting takes historical data and breaks it down mathematically to ascertain seasonality, trend, cyclicality, and noise.
Extrapolation forecasting input
Enter the Dependent Variable that you want to forecast (E.g. Sales).
Also, enter the beginning date of the forecasting period in cell B54.
Dependent variable
Just as you did in the Explanatory Forecasting worksheet, enter three years of historical data for the Dependent Variable here.
Remember that if the amount was 0 (zero) for one of the months in the history, do not leave the cell blank. Enter 0 (zero). Or, the formulas for the output will not work correctly.
Extrapolation forecasting output
Seasonal index
The Seasonal Index is exactly what it sounds like. It is a representation of the seasonality of the Dependent Variable.
These amounts will revolve around 100%. If you calculate the average, you will find that it equals 100%.
With this information, you can confidently know at what point in the year that the Dependent Variable spikes. Likewise, when it will lag. You might have known this information intuitively, but now you know it with certainty.
Deseasonalized dependent variable
The Deseasonalized dependent variable is what your Dependent Variable would have looked like with the effects of seasonality stripped out.
It’s likely that you’ll find much less volatility in your Deseasonalized dependent variable than in the actual historical Dependent Variable data.
Trend line
The Trend Line is also exactly what it sounds like. It is the trend of your Deseasonalized dependent variable. As you’ll see below in the chart, these numbers, when charted, create a perfectly straight line.
If the Trend Line is increasing, then the Dependent Variable is trending up. Inversely, if the Trend Line is going down, then, of course, the Dependent Variable is trending down too.
Cyclical index
The Cyclical Index is similar to the Seasonal Index except that it reflects the ebbs and flows of the economy and/or your industry, rather than ebbs and flows due to the time of the year.
As with the Seasonal Index, the Cyclical Index revolves around 100%. Of course, as you can see, the periods below 100% and above 100% can stretch for much longer than the Seasonal Index. They can easily last for a year or more.
Decycled dependent variable
The Decycled dependent variable is getting down to the bare bones of the Dependent Variable. Any benefits or detriments you might have seen as a result of seasonality or cyclicality are stripped away.
What you’re left with is a Dependent Variable not nearly as affected by outside variables as your actual historical data.
Noise
Noise is the variation in the Dependent Variable. It can’t necessarily be accounted for.
Noise can be attributed to factors that aren’t measured, or to nothing more than simple randomness.
If Noise is low, then that’s probably a good thing. If Noise is high, that means that a lot of the changes in your independent variables are due to factors outside of your control and understanding. That is never good.
Forecasted data
Toward the bottom of the worksheet, you will see the forecast for the entire breakdown of the Dependent Variable – including the Deseason dependent variable, Trend, Cyclical Index, and the Decycled dependent variable.
The Forecast dependent variable is basically reassembled by working backward from the information gleaned previously.
Extrapolation forecasting chart
Here you’ll see all of the historical and forecasted information charted.
The Dependent Variable and the Trend Line are charted using the Y-axis on the left. the Seasonal Index and the Cyclical Index, since they are percentages, are charted using the Y-axis on the right side of the chart.
You’ll notice that the Seasonal Index repeats its pattern every year. You’ll also notice that the Cyclical Index repeats its pattern (more or less) every couple of years.