Weighted moving average explained for beginners

  -  

In Example 1 of Simple Moving Average Forecast, the weights given lớn the previous three values were all equal. We now consider the case where these weights can be different. This type of forecasting is called weighted moving average. Here we assign m weights w1, …, wm, where w1 + …. + wm = 1, and define the forecasted values as follows

*

In the simple moving average method all the weights are equal to 1/m.

Bạn đang xem: Weighted moving average explained for beginners

Example 1: Redo Example 1 of Simple Moving Average Forecast where we assume that more recent observations are weighted more than older observations, using the weights w1 = .6, w2 = .3 và w3 = .1 (as shown in range G4:G6 of Figure 1).

*

Figure 1 – Weighted Moving Averages

The formulas in Figure 1 are the same as those in Figure 1 of Simple Moving Average Forecast, except for the forecasted y values in column C. E.g. the formula in cell C7 is now =SUMPRODUCT(B4:B6,G$4:G$6).

The forecast for the next value in the time series is now 81.3 (cell C19), by using the formula =SUMPRODUCT(B16:B18,G$4:G$6).

Real Statistics Data Analysis Tool: Excel doesn’t provide a weighted moving averages data analysis tool. Instead, you can use the Real Statistics Weighted Moving Averages data analysis tool.

To use this tool for Example 1, press Ctr-m, choose the Time Series option from the main menu & then the Basic forecasting methods option from the dialog box that appears. Fill in the dialog box that appears as shown in Figure 5 of Simple Moving Average Forecast, but this time choose the Weighted Moving Averages option & fill in the Weights Range with G4:G6 (note that no column headings are included in the weights range). None of Parameter values are used (essentially # of Lags will be the number of rows in the weights range & # of Seasons and # of Forecasts will default khổng lồ 1).

Xem thêm: Định Nghĩa Của Từ ' Processing Nghĩa Là Gì ? Processing Tiếng Anh Là Gì

The output will look just like the output in Figure 2 of Simple Moving Average Forecast, except that the weights will be used in calculating the forecast values.

Example 2: Use Solver lớn calculate the weights which produce the lowest mean squared error MSE.

Using the formulas in Figure 1, select Data > Analysis|Solver and fill in the dialog box as shown in Figure 2.

*

Figure 2 – Solver dialog box

Note that we need to constrain the sum of the weights lớn be 1, which we vị by clicking on the Add button. This brings up the Add Constraint dialog box, which we fill in as shown in Figure 3 & then cliông xã on the OK button.

*

Figure 3 – Add Constraint dialog box

We next cliông chồng on the Solve button (on Figure 2), which modifies the data in Figure 1 as shown in Figure 4.

Xem thêm: Queries Là Gì ? Giải Thích Về Truy Vấn Database Cho Người Mới Bắt Đầu

*

Figure 4 – Solver Optimization

As can be seen from Figure 4, Solver changes the weights to lớn 0, .223757 and .776243 in order to lớn minimize the value of MSE. As you can see, the minimized value of 184.688 (cell E22 of Figure 4) is at least less than the MSE value of 191.366 in cell E22 of Figure 2). To loông xã in these weights you need to click on the OK button of the Solver Results dialog box shown in Figure 4.