Forecasting is a thankless task, but a necessary one, and for solving such problems, Microsoft Excel has a very decent toolkit – from the simplest linear trend functions to sophisticated statistical tools from the Analysis Toolpak add-on. One of the easiest to implement and yet very effective are exponential smoothing forecasting functions.
The essence of this method (if you do not go into mathematical details) can be explained relatively easily. If, for example, we made a forecast in a very primitive way using the arithmetic mean, then all historical data would be taken with the same weight (in statistics, this “average temperature in a hospital” method, by the way, even has an official name – “naive forecast”). When forecasting using the exponential smoothing method, the idea is accepted that old data should have less weight than new ones. The change in this weight depending on the newness or age of our data occurs along an avalanche-like exponential curve – hence the name of the technique.
In Microsoft Excel, for its implementation, there are two main functions that have appeared since the 2016 version of Excel:
- PREDICTION.ETS (FORECAST.ETS) – Calculates future predicted values based on historical data.
- FORECAST.ETS.DOVINTERVAL (FORECAST.ETS.CONFINT) – calculates the range of the confidence interval – the corridor of error, within which, with a given probability, our forecast should come true.
It is especially pleasant that manually entering these functions and their numerous arguments is completely unnecessary – Microsoft Excel has a much more convenient tool for this, called Forecast sheet (Forecast Sheet). Let’s look at working with it in the following example.
As initial historical data, we will take real statistics on car sales in Our Country for 2019-2020 from the AutoVercity website (all brands in total):
Let’s imagine for a moment that it’s the end of 2020 and we want to use this data to make a monthly forecast for car sales for the next year and a half. Select our entire table and on the tab Data use the button Forecast sheet (Data — Forecast Sheet).
In the window that opens, set the following settings:
- Completion date forecast
- Seasonality – almost never correctly determined automatically, unfortunately, so it’s better to set it manually. In most businesses, it is annual (i.e. the “pattern” of fluctuations is similarly repeated from year to year), so let’s set it to 12 months.
- Probability with which we require future actual values to fall into the corridor confidence interval. The greater this probability, the wider the interval (i.e., the more blurred the forecast). Typically, values of 90-95% are used.
- In the lower right corner of the window, you can additionally select reaction to empty cells (they can be filled with zeros or the average of neighboring values - interpolation) and on duplicates (usually they are averaged). However, if possible, it is better to prepare the original historical data in advance so that there are no such gaps or duplicates in them.
After pressing the button Create a new sheet will be formed with a forecast table and a chart that is built on it:
At the top of the table there will be rows with historical data (blue line), and at the moment they end, there will be a switch to three new columns with the forecast by the FORECAST.ETS function and the upper and lower bounds of the confidence interval calculated using the FORECAST.ETS.DOVINTERVAL function.
- Modeling and estimating the probability of winning the lottery
- Delivery Optimization in Excel with Solver
- Quickly add new data to a chart