1. Forecasting techniques in Excel
Hello, and welcome to this screencast where we’ll review various forecasting techniques in Excel.
First, let’s talk about moving averages. Moving averages are averages that move with the time series. For example, a 3-month moving average would be an average across the previous three months. This type of forecast is called a simple moving average.
We can easily set this up in a time-series by averaging the previous three cells and using that the value as our estimate.
A weighted moving average is similar, but it gives more importance to more recent data than older data. Therefore, it's better at adapting to changes. To do this, we simply multiply each period by an assigned weight.
For example, let's say that the first period should be weighted the least at 15%, then the second period should be weighted at 25%, and the most recent period should be weighted at 60%. While these weights are being set arbitrarily, there is no set method for finding the correct weights to use; however, the most recent values should be weighted more heavily than the previous values, and they should all add up to 100%.
Excel is a great tool for crafting customized formulas for forecasts like this, but it also has some powerful forecasting tools that are simple to use.
First, we can forecast using charts. Line and bar charts are best for this. We simply insert a graph on our data, and then add a trend line. We can then edit the trendline with the options here to create a forecast. We can set the length of the forecast, as well as the type. There are many options to pick from, and the icons next to each option clarify how the trendlines will look, so we should pick the one that follows our underlying data the best.
Finally, Excel has a powerful tool called Forecast Sheet that will automatically create a forecast using a sophisticated exponential triple-smoothing algorithm that can also detect seasonality in our data.
To use this, we select our data range to be used in the forecast, then click Forecast Sheet on the Data ribbon.
Here we can see a preview of our forecast on a line chart, and there is a bar chart option here too. We can extend the forecast here by changing the End date.
Also notice how the confidence interval is listed here, which is very useful in giving us a reliable range where our prediction could fall. We can change this by opening the options and changing the confidence interval.
While lowering the confidence interval makes the forecast more precise, it ignores the entire range of possible outcomes, which makes our forecast more unreliable. 95% is the standard confidence level that is recommended for most forecasts.
Seasonality can be set manually, but its best to let Excel detect it unless you know what your seasonality is.
Finally, we click okay and Excel will create a new worksheet for us, with formulas already written and our graph.
Notice that Excel uses FORECAST.ETS for the point estimate and FORECAST.ETS.CONFINT to get the confidence intervals. The ETS stands for exponential triple-smoothing.
The function here seems like a lot at first, so let's break it down. First, is the period being forecasted, then the range of the values, and the timeline. The timeline must all be at equal intervals, for example by month. Then we enter our confidence interval here, which we’ll change to 95%. Seasonality is 1, which means it’s detected automatically, and data completion set to 1 means the formula will interpolate any missing values.
Then, these intervals are subtracted and added to the forecast value to get the lower and upper bound, respectively.
Now you’ve seen it, it's your turn to give it a try!
2. Let's practice!