ab-logo

Code School for Kids


Time Series Forecasting with Excel

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

This is a video demonstration of Excel weighted moving average and single exponential smoothing forecasting.

This is the second part in a series on forecasting. Part I can be found here and discusses moving averages, calculation and interpretation. If you are not familiar with time series forecasting it will be helpful to watch Part I which also discusses the calculation and interpretation of common error measures used in forcasting. This video is a continuation of Part I and covers two additional common methods of forecasting stationary time series: Weighted Moving Average and Single Exponential Smoothing.

Part III discusses how to determine if your forecast is better than what is known as a naive forecast, in other words, using today's price in this case to forecast tomorrow. You can calculate a ratio called Theil's U to answer that question. See the video here.

Weighted Moving Average Forecasts

The first segment of the video discusses Weighted Moving Averages. One drawback of forecasting with moving averages is that all the data used in the look-back period is weighted equally. As the name suggests, with the weighted moving average technique, historical data is weighted, with some observations being more important than others. The difficulty with weighted moving average is that both a look-back period and weights have to be selected. How do we determine how to weight past data? First we select an error measure to minimize and then with the help of Solver using the standard GRG non linear method we can let Excel try many values for weightS until in finds the best combination.

Single Exponential Smoothing

Exponential smoothing forecasts can respond quickly to changes in the observed data using a smoothing constant known as Alpha. The calculation looks deceptively simple, however it can be shown that the final formula actually relies on ALL previous data for the forecast. Alpha will be somewhere between 0 and 1. Smaller values of Alpha indicate a forecast that relies more heavily on older data, while higher values indicate recent data is more important. Once again we will use Solver to determine the optimal value for alpha.

This tutorial demonstrates the calculation and interpretation of common stationary forecasting methods, including Weight Moving Average and Single Exponential Smoothing. You can download the spreadsheet used in this tutorial here.