Using Excel 2016 Solver to Find the Optimum Value of Alpha for an Exponential Smoothing Forecast

[@veronicam3631]

I have been looking for days on how to do this and this is the only tutorial that really helped! Thank you!!!

[@wanovskaya]

Thank you very much for your great and instructive video. I have been looking for a good explanation and could not find anywhere a good explanation with all the formulas on how to find alpha. You explained everything very clearly. Thank you

[@williamrivera162]

Very helpful and instructive video. Thank you so much for teaching.

[@JohnDoe-dh6zy]

This is an insanely great tutorial. I thought this could be done and I was right!

[@viettrinh6694]

This is concise and simple, thank you!

[@rkumar8864]

Thanks for this video. This video just helped me to move ahead on my model.

[@TheStatsFilesDawnWrightPhD]

Thanks for the positive feedback.

[@tylerx8848]

Thank you so very much! This was very helpful and easy to understand.

[@ismailnassar4103]

Excellent & very infromative. Hoever, some times the solver makes alpha 0.0000000, how can i over omce this situation ?

[@davidgl1988peru]

This was really helpful! Thanks mate.

[@supphaChong]

Thanks for your helpful vdo. Anyway, I would like to know the reason why do we set MAPE as objective instead of MSE? Can we set MSE as objective?

[@patriciafernandes8976]

Great! It helped me a lot. Thanks!

[@CristianGuerra15]

You are great! Thank you so much!

[@dakuberbicara4425]

where is the previous video before this video

[@suchAbrokenDream]

how do we decide which method we should use: evolutionary or GRG non linear? ( Is the same on Holts-winters method?)

[@SuperBobmarlon]

Very nice video , thanks

[@jackroberts6648]

Any way to use a solver and Analysis toolpak to avoid having to type in formulas every time?

[@SurfTheStreets01]

im doing it but my numbers arnt changing

[@mutindafestus5619]

How did you decide on the first value

[@TheStatsFilesDawnWrightPhD]

If I am understanding your question, it really doesn't matter as long as it is in the range of appropriate values. Here, alpha must be >=0 and

[@mutindafestus5619]

+mutinda festus also in making predictions how can i predict the next say 4 period, or this is restricted to one step prediction

[@TheStatsFilesDawnWrightPhD]

The formula for simple exponential smoothing requires an "actual/observed" value for the preceding period, thus this method is restricted to just the next period. Longer-term time series forecasts are best done using other techniques.

[@chenghungwu345]

Dear Mr. Wright, what technique you will use to predict the periods further than the next period? Thank you!

[@chenghungwu345]

Do you have video regarding that?

[@amarsaxena6782]

I used this technique on a dataset - and it gave me a sub-optimal result. I was checking the calculation by solving it manually and realised it. I first used GRG Nonlinear to optimize it (I got alpha=0.26). When computed manually, the alpha was 0.46. Then I used Evolutionary to optimize. This time I got the correct value (alpha = 0.458) Why did GRG Nonlinear not work?

[@TheStatsFilesDawnWrightPhD]

It depends on how your problem is set up. Here is a good explanation of the difference between Evolutionary and GRG www.engineerexcel.com/excel-solver-solving-method-choose/

[@MrSayon20]

I got a problem. Whenever I use solver to minimize alpha and my constraints are set to be >=0 and

[@TheStatsFilesDawnWrightPhD]

Sayon, you should be minimizing your error metric, e.g. MAPE, not Alpha. In this example, MAPE is the "Objective" cell. Alpha is your "Changing Variable."

[@MrSayon20]

Thanks for such a prompt response. Sorry I explained my problem wrong. Even when I am minimizing my MSE by changing the solver variable cell, since the solver constraint has been set as mentioned above, it's taking the least value, i.e. 0. I tried by changing the lower and upper bound of the constraints to 0.01 and 0.99 respectively. Similar result (optimal alpha =0.01). I checked with few of my mates n this seems like a machine specific problem which only I am facing.

[@TheStatsFilesDawnWrightPhD]

Sayon, I'll need to see your spreadsheet as I think there is a bust in your model. If you want, send it to [e-mail] and I will take a look.

[@Sidthefresh]

I'm experiencing the same problem! What do I do?

[@prasannahemanthk]

I have zeros in my Actual Value column, so it is creating a problem for calculating the Absolute % Error. How to handle this.

[@TheStatsFilesDawnWrightPhD]

I'm not sure I understand your question correctly. Usually, you must have a time series of actual quantitative values of a variable, e.g. sales, stock prices, etc., in order to make a forecast. Could you clarify your question a bit?

[@TheStatsFilesDawnWrightPhD]

OK, I think I understand. You have missing values in your historic time series data. You need to use one of the several methods of estimating the missing values. Choosing the best one depends on the nature of your time series data and the extent to which data points are missing. However, one possible method is to use the Excel Trend function to interpolate or extrapolate from the data you do have.

[@prasannahemanthk]

Thank you for the suggestion. :)

[@chiki3850]

my Alpha Value always returns to 1 after using the solver... why is that so?

[@jw-dz6eg]

A high Alpha of 1 means that 1) maybe your did not set up the solver correctly or 2) you have high variability between different periods... example forecast for 6 months vs actual (High Variability) 5 10 9 18 3 4 = high alpha....vs (Low Variability) 10 9 8 9 7 10 = low alpha....*Note Alpha is always between 0 and 1

[@Frfoosh1]

I LOVE YOU

[@OussRit]

Thank you :)

[@TheStatsFilesDawnWrightPhD]

You are welcome!