12. Exponential Smothing Calculation

Exponential smoothing screen cast.
Now let’s look at the exponential smoothing forecasting method. Which is a forecasting method that takes an average view of all past demand, but it weights more recent data more heavily, and older data less heavily. So we get something that resembles more the moving average, than the cumulative mean, even though it takes into account all demand data.
So, now let’s take a look at how we were going to implement it into a spreadsheet.
In order to start the exponential smoothing formula we have to have an initialization value. Because the forecast itself depends on past demand and past forecast. So to start we are going to take a naive view of demand and we’re going to use the value of 20 as our initialization value. Then in period 2 we can apply the exponential smoothing formula. Now, let’s take care of the round function ahead of time.
In order to set up the exponential smoothing formula, we have this coefficient called alpha which we can actually vary. And to do that more efficiently, I’m going to use a separate cell for alpha because then that allows me to just change one cell and vary how reactive or how smooth the forecast is. So alpha has to be a fixed cell,
Which means we need a dollar sign right in here,
Times, Past demand,
Plus 1 minus alpha and that needs a dollar sign, times the prior forecast. So the exponential smoothing formula is made up of alpha times prior demand plus 1 minus alpha times the prior forecast. And that way it takes into account with a certain weighting of prior forecast, and how well they did. So now all I need to do is, tell the round function that we don’t want any decimals, and here is our forecast exponential smoothing. And we’re not going to see much until we have a value of alpha, but let me just write this down first, okay.
Now this defaults to, The 0 here. So that means, with an alpha of 0, we place no weight on prior demand all the way to the prior forecast, so that’s why you see all of these values as 20. So alpha we can set as anything between 0 and 1. So let’s set for example to point 1, And we don’t see much happening. Point 3, here we see some stuff happening already.
Now the question becomes, what value of alpha should you pick? And we can do it graphically because we see, well, 0.4 becomes a little bit more reactive, 0.5 even more so. But how reactive is too reactive, and what really makes for a good forecast? So, in order to decide, we have to calculate an accuracy statistic. I like that mean squared error the best, so here we start it. Demand minus forecast, you’ll want to square that. There we go. I’m going to copy that down.
There’s that, now I’m not using the very first period because this was just an estimate, and it has nothing to do with exponential smoothing. So on period 31, we also don’t have any demand data, so I didn’t calculate a squared error for that. But we have anything from period 2 all the way through period 30. So the mean squared error becomes, the average of all those error values.
Okay, up until here. And it’s about 10.75.
When we look at this forecast, if we let’s say increase alpha, okay, well increasing it from 0.5 to 0.6 gave us an increase in MSE, so it made it less accurate, that’s no good. What if we make it smaller? Well, that made it a little better. What if we go even smaller? Okay, it gets even better. So we could play this game over and over, until we find a value that is optimal.

Lecture Videomp4
Subtitles (English)
Transcript (English)
Would you like to help us translate the transcript and subtitles into additional

Jim Rohn Sứ mệnh khởi nghiệp