Excel Forecasting: Single Exponential Smoothing & Weighted Moving Average Time Series Forecasting

‪@MattMacarty‬
#excel #forecasting
alphabench.com/data/excel-tim...
✅ Please SUBSCRIBE:
kzbin.info_...
Part II in Introductory Forecasting Series
Introduction to Single Exponential Smoothing and Weighted Moving Average Forecasting Models
✅ See Part I here: • Moving Average Time Se...
Demonstration of time series forecasting techniques using weighted moving averages and single exponential smoothing models to forecast the price of gold.
Download the Excel file used at the link above

[@AnandPenmatcha]

very good explanation sir

[@MattMacarty]

Thank you. Glad it helped.

[@MensGroomingAdvice]

Really helpful and well explained. I was unable to enter the sumproduct equation you did in C8 as Excel didn't like that one range was a number and the other a percentage. Not sure if it was an issue with my version of Excel (365) or me just doing something stupid (highly likely!). EDIT: I found the issue was that I was selecting too large a range, i.e. my spreadsheet was looking at Quarterly intervals so I had 3 months for my weights, but then used six weeks as my k range. Doh!

[@MattMacarty]

Hi thanks. It shouldn't be a problem with percent vs. Amy other kind of number. You may have been selecting two different sized arrays. Gunfire that the arrays are the same width in columns.

[@johnalwang]

this explain the fit of the goodness of the model. How about if I want to forward forecast for another 10 weeks?

[@MattMacarty]

These kind of stationary models are not useful for longer forecasts. The problem you run into with longer forecasts is increasing uncertainty the farther into the future you move. You might be able to use some kind of regression model more effectively but you will still have an increasing variance.

[@uponsunnah6986]

What's the best way to forecast the following and can we use the above method? Forecasting for each day if the week. Ie. Sunday vs previous Sundays and Mondays vs previous Mondays, etc.

[@MattMacarty]

The best method is going to depend on the underlying data. The methods in this tutorial are best for stationary data, data that doesn't appear to be going in a direction. If you have data that is tending to go up or down over time then you will need a more sophisticated forecasting model.

[@masterhummus2114]

Thanx a lot. really helpful but just a question. How do you forecast beyond the 6th month in 2018 using exponential smoothening because you don't have the actual demand of the previous month?

[@MattMacarty]

You can really only forecast 1-period into the future with these types of models

[@masterhummus2114]

@@MattMacarty got it. Thanx for the quick reply. Appreciate it

[@angelmaravilla4708]

Sir, in the present excercise, youi used "125.33" using the assumption that, "The last observation will be your First Forecast". However, in the actual data we don't have any 125.33; was that a small mistake, or how did you get that number?

[@angelmaravilla4708]

Amazing tutorial!!! Just one question: Why didnt you choose the "Evolutionary - Solver Method" instead of the "GRG Nonlinear"?? I understand that, the Evolutionary algorithm is more robust than GRG Nonlinear because it is more likely to find a globally optimum solution... but what is the functional and technical reason to use GRG Nonlinear??? Hope you can help me with thta, reggards

[@MattMacarty]

Thanks. Glad it helped. So the Solver does typically try to "figure" out what which algorithm will be best suited for whatever you are working on, this doesn't mean it's right, and you can always override. To be honest I didn't consciously pick one over the other.

[@angelmaravilla4708]

@@MattMacarty I'm a fresher in this things, and dont know what's the difference between both approaches, any light will be appreciated (amazkng tutorial btw)

[@pbv61]

RE the weights. I see you used 5 in this case. What that because you used a 5 period MA? I.E., one weight for each period. Or can you use any number of weights you choose. And if you do, how do you know to which part of the range your are measure the weight pertains or controls? Thanks for your time. PV

[@MattMacarty]

Yes I chose a 5-period weighted MA just so we could compare to the SMA method. You can base your forecast on any number of periods. I don't follow your last sentence.

[@pbv61]

@@MattMacarty Thanks for the reply. Let see if I explain better. If I were to use a 3-period moving average, then does that me I could use three weights (33.33% each), or could I still use 5 weights (20% each)? Next question: as to the several weights. Are the weights attributable to something. For example. If using 5 weights, is first weight measuring first 20% of the actual data range; the second weight is affecting the second 20% of data, etc. Hope this makes sense.

[@MattMacarty]

@@pbv61 You can use as any periods as you want, but the total of the weights needs to sum to 1. So you can use 5 periods for weights, but only use some of the previous observations in your forecast. However if you weight everything equally you are doing a SMA. For 5-periods it is equivalent to saying take 20% of obs at t-1 + 20% obs t-2, etc.

[@swapnilkushwaha5772]

Sir can i get your excel file

[@MattMacarty]

alphabench.com/data/excel-time-series-forecasting.html