10. Moving Average Calculation

Moving Average Screencast. Now let’s take a look at how to implement the moving average in this spreadsheet. So, the moving average is an average of a set number of periods. Unlike the cumulative mean, where we take all past data into account and average it all up, we’re picking a set number of periods that we then average going forward. So in this case, I’m going to use a moving average of 3. And that means we can only start after we have collected 3 periods’ worth of demand data. And I’m here in Period 4. And now I’m going to start typing in the formula. So type in average, so all we have to do in the spreadsheet is type in the word average, we open the parentheses and then tell the spreadsheet what periods to average together.
Now, as you see here, we have an average of the cells B2, B3, and B4.
And in this case, I want all of the cells to move as I copy the rows downward. Therefore, I’m not going to add in any dollar signs or anything like that to fix the cells. We also see our result is 22.666666667, and that means that in order to get the same units coming out of the forecast as was goes into the demand, we should apply the round function, Without any decimals.
And then it’s going to round it up to 23. So there is this, and all I need to do now is copy these down. We’ll make it appear 31 here. And there you have it. I already included a chart, so we see how it looks graphically. The moving average of 3 is a lot smoother than what we would have with the naive method, but not quite as smooth and stable as the cumulative mean, which becomes a straight line here in the later part of the demand data.
So this is the moving average with an N=3. I would like to show you one additional version of this, N=3 should go in here. Let’s make this a little bigger, and I would like to show you how an N of 5 would look like. It’s really not that complicated. All we need to do is wait one, two, three, four, five periods, until we have, Five periods to average together.
And then we apply exactly the same formula, average,
One, two, three, four, five, together. We want to be consistent with our round function here, no decimals, and there’s that. All I need to do is to copy this down. Okay, here is this, and now, I’m going to add in some more data. In other words, we are going to add in,
Another range, and that will be column D.
Okay, here we go. We update our chart. And here’s this. So we see the larger N actually makes the forecast a little bit more stable.
You can see this a little better here. So it’s a little bit more stable then the rather nervous, N=3, so as we increase N, it will actually be a little bit more stable, less like the naive forecast, more like the cumulative mean.
Now the obvious question becomes now, how do you decide? Do you pick an N of 3 or an N of 5 or something different? Well, that’s why we looked at the forecast accuracy metrics. And if I was just to pick one metric that I would trust the most, it would be the mean squared error.
So, I’m going to calculate the squared error for my N=3 and the squared error for N=5, taking a small shortcut here, if you don’t mind. Let’s push this over a little bit, and now, I am taking demand minus forecast. And I’m taking all of that to the second power, and I’m doing the same thing here later on as well, so equals. And then, minus our forecast raised to the second power. So now I just copy this.
Okay, and then I copy this here. We don’t want to use this value. And quite honestly, for calculation purposes, or for comparative purposes, we should use the same number of periods that we averaged in together. So it’s going to start here. Let’s say MSE (3), and MSE, (5).
=average of this stuff here.
And the MSE (5) is the average of all of these values here.
Okay, so it is very close. But we do see that the MSE for the moving average with N=3, or where we average it together three periods, is slightly larger than the moving average where we average five periods together. So if I was going to pick the moving average, either 3 or 5, I would pick the moving average with 5. Because the mean squared error, which is one of the most meaningful accuracy measures, is slightly better.
So there you have it. This is the moving average, and I showed you two variations of it. One where we average three periods together, and one where we average five periods together.

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