Basic Concepts
In Example 1 of Simple Moving Average Forecast, the weights given to 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.
Example
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 and 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).
Data Analysis Tool
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 and 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 and 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 and # of Seasons and # of Forecasts will default to 1).
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.
Using Solver to reduce error
Example 2: Use Solver to 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 to be 1, which we do by clicking on the Add button. This brings up the Add Constraint dialog box, which we fill in as shown in Figure 3 and then click on the OK button.
Figure 3 – Add Constraint dialog box
We next click on the Solve button (on Figure 2), which modifies the data in Figure 1 as shown in Figure 4.
Figure 4 – Solver Optimization
As can be seen from Figure 4, Solver changes the weights to 0, .223757 and .776243 in order to 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 lock in these weights you need to click on the OK button of the Solver Results dialog box shown in Figure 4.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage
References
Fidelity (2024) Weighted moving average (WMA)
https://www.fidelity.com/learning-center/trading-investing/technical-analysis/technical-indicator-guide/wma#:~:text=Description,a%20corresponding%20Simple%20Moving%20Average.
Rynearson, S. (2022) Weighted moving (rolling) average – Excel and Google sheets
https://www.automateexcel.com/stats/weighted-moving-average/
hi, i’m using excel 2019 mac, and i’ve downloaded the resource pack, but i keep running into issues when it comes to the weighted moving average. i’m following all the steps correctly but i keep getting the ‘Invalid weights range selected’ error. i have no idea what to do, i’ve been trying to solve this issue for 3 hours – i’ve done everything but it still keeps saying this, please help
Hello Aisha,
If you email me an Excel spreadsheet with your data and explain what you entered into the dialog box, I will try to figure out what went wrong.
Charles
Hello sir,
Can we use holts linear trend model for yearly data from 1990 to 2020 and for doing forecasting do we have to split the dataset into training and testing data and one more how to find the value of alpha and beta precisely?
Hello Mani,
You can apply Holt’s Linear Trend for yearly data from 1990 to 2020. You don’t have to split the data into training and testing data, but this approach can give you information about the accuracy of your forecasts.
Charles
Hey I have sales data for year 2020 and 2021 and I want to forecast the sales for next year. Can I use 3months WMA to forecast sales for 2022 ? Or I can forecast only for 1 month ? IF not , which method of time series data can I use to forecast data for whole year ?
Hello Abhishek,
If you have monthly data for 2020 and 2021 (i.e. 24 values), then you will only be able to forecast one month into 2022.
If you have quarterly data for 2020 and 2021 (i.e. 8 values), then you will only be able to forecast one quarter into 2022.
If you want more months or quarters, you can use some other forecasting methods, such as Holt-Winters.
Charles
Thank you for the website and this statistical tool. it is really helpful.
Hi,
If we want to find the WMA at 3rd reading with 3 weighted factors and say we just have T1 and T2 like in this case. How can we find forecast at T3?
Meghana,
Sorry but I don’t understand your question. Are you saying that you only have two data values T1 and T2 and want to forecast T3?
Charles
Charles,
Let us assume that we are given 3 weighted factors and the observations are T1 to T5. For this I know the forecast starts from T4. But is there anyway I can forecast for T1, T2 and T3?
Meghana,
I don’t know whether there is a right way to calculate forecast values for the first three times. I’ll assume that you are conducting the WMA so that the lag is 3 and the forecast at time 4 is the weighted average of T1, T2 and T3 and the forecast at time 4 is the weighted average of T2, T3 and T4. In this case, if I had to make a forecast for time 2 it would be T1 and the forecast for time 3 would be the weighted average of T1 and T2. Even though we are calling this a forecast it is really weighted average smoothing shifted one time period.
For exponential moving average, here are some techniques:
https://blog.fugue88.ws/archives/2017-01/The-correct-way-to-start-an-Exponential-Moving-Average-EMA
Charles
How can we use moving or weighted averages when we have 0 demand during certain periods (4 continuous periods – 0 demand, etc). Percentage errors go sky high in such cases
Jasbir,
If you are using a simple moving average model with say lags = 3, then even when you have 4 zeros in a row, the model executes correctly and the MSE and MAE error terms are calculated correctly. The MAPE error term gives an error even with one zero.
Charles
Thanks Charles,
In weighted average, if the solver optimizes the weights to 0,0,1 – does that mean the best solution is to ignore the demand of first 2 points and focus on the immediate third for analysis?
Yes, that is correct.
hi, im been using real statistics tool pack and have had great time using this tool. However, when I try to use the weighted moving average and input my weights and click okay, it gives me an error message that “invalid weights range selected”. The weights are put in order from smallest to largest and formatted the same as your example. Please help!
Also I am using a Mac with excel 2016
Hi Matthew,
Are you using weights that have a comma as the decimal symbol?
In any case, if you email me an Excel file with your data and tell me what address you entered into the Weights field, I will try to figure out what is going wrong.
Charles
This is of immense value to me, I am learning Time Series.
A suggestion: You might want to replace the SOLVER PARAMETERS screen image with one in which the “Make unconstrained variables non-negative” box is checked. I believe this is what you have used, else a negative value of alpha is obtained for this example.
Hello Amlesh,
Yes, you are correct. I have just updated the webpage with your suggested change. BTW, the answer will be the same even if this change is not made.
Thanks for your help.
Charles
on what basis we choose these weights?. Are they random? do we have to try different weights until we get best prediction?
Hello Husan,
Sometimes you have some idea about what the weights should be based on similar studies or past experience. If you don’t know what weights to use you can calculate the weights that yield the smallest error. This approach is described in Example 2 of this webpage.
Charles
Hi, Charles,
I used Excel’s Solver for optimize weights in a weighted moving average procedure.
I got these values for the time series
w1 =0.26
w2 =-0.319
w3 = 1.038.
Is this the correct result?
Or should in Solver have a constraints of
w1 >= 0
w2 >= 0
w3 >= 0?
Thanks in advance for your attention.
Andrew,
Yes, I believe that that weights need to be non-negative. I don’t believe that I set used these constraints, yet I still got non-negative values. I don’t know why this would be so.
Charles
Is the free download compatible with MacBook Pro? Secondly, how do I know when to apply simple moving average or weighted moving average?
Btw, very insightful website. Thank you
Yes, Real Statistics is compatible with MacBook Pro provided you are using Excel 2011 or Excel 2016.
Whether to apply simple moving average or weighted moving average depends on your requirements. If you know the weights, then use the weighted version.
Charles
Hi Charles,
In this article, you said that
yihat = (wm*y(i-m)+…+w1*y(i-1)) / m
However, I think SUMPRODUCT is yihat = (wm*y(i-m)+…+w1*y(i-1)).
Do I need to calculate ‘/m’?
Hi Hansol,
Thank you very much for your comment. The formula as written is incorrect. The weights take care of the /m part and so this part of the formula should be removed.
I have now corrected the webpage.
I really appreciate your help in improving the website and making it more accurate and easier to understand.
Charles
Hi Charles,
Whenever I use Solver in excel for my WMA, it resulted to this:
For WMA Weights
m-1 0.660671462
m-2 0.0
m-3 0.0
m-4 0.339328538
Total 1.00000000025
Is this normal that it return zero on my Month 2 and 3 then the total sum was more than 1?
I’m sure i set the constrain to equals to 1.
Thanks for your response.
Hope you can drop me an email.
Lawrence,
Without seeing the data, I can’t say whether it is normal to get zeros for two of the months.
For Solver probably 1.00000000025 is close enough to 1.
Charles
Hi Charles,
I was wondering how you would know what would be an acceptable level for your mean average error and mean squared error to be within (or within tolerance per say) to say that this forecast is solid. Or is this an arbitrary question and will always be case specific.
Thanks, really like the content you have here. I hope all the best in publishing your book soon. Hopefully it will be paperback and not a kindle version. I still prefer paperbacks any day of the week)
Eric,
I don’t have an answer for what is an acceptable level for the MAE or MSE.
I am pleased that you like the website content. I have finished writing the book, but proof-reading it is slow going. I hope to get it published early next year. Sorry to say, but initially it will be an e-book.
Charles
Hi, Charles,
When I try to produce a weighted moving average forecast, it seems that I have to place the output in the same worksheet as the data instead of in a new worksheet, as I can do for the other forecasting methods.
If I try to place the weighted moving average forecast in a new worksheet (by selecting “New” for “Output Range”), I get all 0’s for the forecast because the forecast column formula references a range in the new worksheet that is blank instead of referencing the weight range in the worksheet with the data.
This isn’t a major issue because it’s easy to work around. I was just wondering if there is a step I’m not performing correctly or if this is how the software has to work for this particular method.
Thank you for any guidance you have.
Kevin,
There is an error in the Weighted Moving Average data analysis tool when you request that the output appear on a new worksheet.
Thanks for catching this error. I will fix it in the next release of the software.
I appreciate your help in improving the Real Statistics software and sorry that I hadn’t caught this problem during the testing phase.
Charles
Thank you for clarifying. This software is great!