From the course: Excel Data Analysis for Supply Chain: Forecasting

Centered moving average - Microsoft Excel Tutorial

From the course: Excel Data Analysis for Supply Chain: Forecasting

Centered moving average

“

- [Instructor] Creating a seasonal index or seasonal indices can be complex. Not necessarily hard, but complex. There's lots of steps. So, what we're going to do in this chapter is we're going to sort of walk through this a little bit slowly, and we're going to start with a concept of the centered-moving average. You're going to need this as you start to develop your seasonal indices. And so, the first thing we're going to do is we're going to start off with, because we're working with quarters, so let's say that this organization has four quarters of business throughout the year. And what we're going to do, the first thing we want to do is we want to take a four-period moving average, and we know how to do that already, but I want to point out where we're going to put this. So, for periods one through four, we're going to put our four-period moving average in this spot right here. It might not make a lot of sense right now, but don't worry about that. For now, let's just go with that. So, our four-period moving average is just the average of periods one, two, three, and four. All right, so, we did that. And then we want to do the same thing for periods two, three, four and five. So again, if we want to do it separately, we could copy down, whichever one you prefer. I'm going to do it separately here, just so you can see that I'm grabbing periods two, three, four and five. (keyboard clicking) So, now we have our four-period moving average for periods one through four, our four-period moving average for periods two, three, four and five. And you'll notice that next to them, on the right, I've put the numbers 2.5 and 3.5, and you might go, what is that about? What it's doing is, it's averaging the periods. So, notice for this particular four-period moving average, we are using periods one, two, three and four. If you take the average of one, two, three and four, it's two and a half. Then if you take the average of periods two, three, four and five, so add up 2, 3, 4, and five, divide by four and you'll get 3.5. And you might say, okay, what does this mean? Well, if this is sort of a average of period 2.5, and this is an average of period 3.5, if we average these two together, that's sort of like the average of period three. And so, that's what we're going to do. We're going to take the average (keyboard clicking) of our two four-period moving averages, and now what we found is something called the centered-moving average, the centered-moving average for period three. So, again, let me kind of explain to you. We did a four-period moving average for periods one, two, three and four, and we averaged this out to be 2.5, adding one, two, three, four and five, dividing by four, it's 2.5. So, this is kind of like saying, well, this is kind of the average of period 2.5, and this is kind of the average of period 3.5. So, if I average 2.5 and 3.5, it gives me period three, and this is what we call our centered-moving average. And we're going to do use this throughout this chapter. So, I want you to remember. The other thing I wanted to point out to you is please, while you may be wanting to copy this formula down all the way down to the bottom, remember you shouldn't go past this area right here, because this is going to be the four-period moving average of period seven, eight, nine, and 10. If you decide to copy down to here, you're actually going to be taking the four-period moving average, including this cell down here, which we don't have data for. So, you'll just need to remember how far down you can go before you start running out of data. All right, but remember, this was all about that centered-moving average, and we're going to be using that going forward.

Contents