How do I clearly explain the benefits of a rolling or moving average compared to straight data?

August 2, 2007 8:44 AM

I report on weekly sales and pricing information, and weekly data points are often skewed by one-time promotions, seasonality, and other random effects. When I create a 52-week rolling average of the data, it shows trends much more smoothly -- and accurately, I believe. My coworkers are not accustomed to seeing data as a rolling average, and I'm concerned that they won't understand it. What is a clear way to explain to them 1) how a rolling average works, 2) why it is useful for visualizing trends, and 3) any other benefits?
posted by scottso17 to work & money (10 comments total)

The section 'Moving Averages' (the linked page, plus the next four pages in sequence) from the Hacker's Diet explained moving averages pretty well for me. It talks in terms of weight, but the explains the concept and advantages/disadvantages in general terms.

You could use this text as-is -- weight change is something that most people can identify with, and they'll understand why you'd want to smooth out daily fluctuation to see the overall gain/loss trend.
posted by chrismear at 8:50 AM on August 2, 2007

Explain that rolling averages are a simple way to get rid of noise (e.g., randomness) out of the time series data. This comes at a price of a time lag in the rolling average reflecting significant trend changes.

This ignores the epistemological concerns of whether the data displays trends at all and the nature of the randomness in the time series.

Moving average should be intuitive, I would argue that's why it is so attractive to use in the first place. What exactly are they having difficulty with?
posted by geoff. at 9:04 AM on August 2, 2007

When plotting a moving average, I find it very valuable to also plot the underlying data series on the same graph. Usually behind the moving average, in a lighter colour and/or thinner line. Seeing the smooth line on top of the noisy one makes things very clear to casual observers.
posted by Nelson at 9:13 AM on August 2, 2007 [1 favorite]

"See how this line is all spiky? It's harder to see long term trends with all the random noise. This other line is smoother because every point on it represents the average value up to that day for the previous N days, so random fluctuations get averaged out."

If they don't get that, you'll have to resort to something like "trust me, it's math stuff."
posted by adamrice at 9:33 AM on August 2, 2007

I am a retail demand forecasting consultant. In other words I look at sales series all day long... Are you sure you wanna use a moving average to project trends? especially if you have highly seasonal series? If you don't want to over-complicate things, a moving average is OK, but I'd go to at least an exponentially-weighted moving average, so you can at least capture more recent/seasonal trend. And if you have a lot of data, trying to do something with the seasonality maybe even better.
posted by costas at 9:44 AM on August 2, 2007

Thanks for the great responses and questions.

Geoff: New things = confusing. Old things = comfortable. That's all.

Costas: I'm not looking to project trends but rather show that a trend is or isn't happening. In one particular case, I'm looking at average price per unit for a somewhat commoditized item. One-time promotions will affect price in the short-term, but I want to see if the price is trending up and down in the longer-term. If you think there's a better way to show that, I'm all ears (and eyes).

When you say, "trying to do something with the seasonality maybe even better," what do you mean?
posted by scottso17 at 10:12 AM on August 2, 2007

seasonal trends = shops sell more sandals and sunscreen in summer, more boots and snow shovels in winter.

if you have a lot of data, it may be easier to see trends on a seasonal scale than monthly or weekly.
posted by desjardins at 11:01 AM on August 2, 2007

Well, if you only wanna show trend for price (which shouldn't be that volatile), then I'd say that exponentially-weighted moving average is an even better idea: you don't want a promotion that run for a couple of weeks last year to bring down your projection right now. Pick a nice conservative factor (>0.9) and run a projection from a few weeks in the past: re-iterate until your projection mostly follows the data you already have. Then repeat with the same factor for the future.

Also, if you have a good amount of data and only a few promotions (say only a few weeks a year), I'd advise to throw out your promotional data altogether: unless you're explicitly trying to find the effect of the promotion, the data is not representative of the underlying trend.

Now for modeling seasonality: there's a bunch of things you can do, from simple modeling in Excel to more sophisticated modeling, but it's only worth it if you see a clear seasonal pattern and you want to project that pattern forward as well. An easy way would be to average all your same weeks together (Week 1 of 2007 with Week 1 of 2006 and so on) to get your weekly "indices". Then take the average of the indices and divide them by it to normalize them. Then divide your original data with your normalized indices and run EWMA on your (now) deseasonalized data. Finally, multiply your EWMA projection with your normalized indices to add seasonality back on. That's a trimmed-down, non-optimized triple exponential smoothing. For extra credit run an EWMA on the slope of the deseasonalized data, and then add the slope on the projection as well. (For the stats geeks out there: I know I've over-simplified triple ES, I am keeping it simple so it can be done in Excel in a couple of hours).

At the end of the day, depending on the quality and quantity of the data you have you can do a lot of math tricks, but it's only worth it if what you get back is worth the extra effort (and explaining to your co-workers ;-) I'd say move in steps of incrementing complexity until diminished returns kick in.
posted by costas at 11:09 AM on August 2, 2007

just take out a sheet of paper, draw a line across it and put in months across it for 3 yeras - jan , feb, march etc.

then cut a piece of paper into a little bar as long as 12 months on your paper, lay it on the paper and slide it along, and tell them it is always the average within that bar, the past 12 months that is
posted by Salvatorparadise at 11:59 AM on August 2, 2007

I don't know why you wouldn't chart both on top of each other. This will help to visually demonstrate to people how a moving average smooths the trend and that's valuable to "cut through the noise," but showing the spiky period data is also presumably useful since it gives you a handle on what happens when you pull the levers in your control (like price). I don't think it has to be either or.
posted by donovan at 12:35 PM on August 2, 2007