Weighted-moving average (WMA) in Excel

Scene 1:

Welcome to the weighted moving average tutorial. In this video we will demonstrate the use of the WMA function in NumXL to smooth out time series data and create a sample forecast. As an example we'll be using monthly sales figures from a hypothetical company for the past 24 months, let's start now.

First, I'll create a simple four month moving average, type the WMA formula in the tool bar and press on the "fx" button on the left. 

Scene 2:

When the WMA function window pops up, specify the window size. For the forecast time or horizon go ahead and type zero or reference the A4 cell. Now click OK.

Scene 3:

The function returned #N/A because we don't have enough data points. Drag the cell to copy the formula, then select the cell and click on the "fx" button. 

Scene 4:

We need to change the input reference cell to include all data up to this point, so let's select all the data from the C4 to C5 cell. Once done, click OK.

Scene 5:

Now, copy the formula to the remaining cells. Great!

Next, let's do a simple forecast using the moving average. Go to the end of the cell range and copy the formula to the next one. Edit the formula to include only the cell range in the input data. Lock the N cell range of the input. Now, copy the formula to the other forecast points.

Scene 6:

Plot the moving average and notice how it lags behind the sample data. Next, let's do the same as before, but use a predefined set of weights. Enter the WMA formula, and click the "fx" button.

Scene 7:

Specify the input range.

Scene 8:

For the weights, select the cell range that contains the weights. Please note that weight values don't need to be normalized. For the forecast time, set T equals to zero for in sample smoothing. Now, click OK.

Scene 9:

Drag the cell to copy the formula, then edit its reference input cell range, and lock the beginning of it. Then copy the formula to the cells below.

For an an out-of-sample forecast; select the last cell and copy the formula. Now edit and lock the input cell range to the sample data. Copy the formula for the other forecast horizons.

Scene 10:

Lastly, plot the weighted moving average along with a forecast, note that the WMA is more responsive to change in the sample data because it has higher weights for recent observations.

Scene 11:

This concludes our presentation, thank you for watching!