Chapter 52: Using Moving Averages to Understand Time Series

Overview

• I’m trying to analyze the upward trend in quarterly revenues of Amazon.com since 1996. Fourth quarter sales in the U.S. are usually larger (because of Christmas) than sales during the first quarter of the following year. This pattern obscures the upward trend in sales. Is there any way that I can graphically show the upward trend in revenues?

Time series data simply displays the same quantity measured at different points in time. For example, the Data and Chart worksheet in the file Amazon.xlsx, shown in Figure 52-1 on the next page, displays the time series for quarterly revenues in millions of dollars for Amazon.com. Our data covers the time interval from the first quarter of 1996 through the fourth quarter of 2005.

Figure 52-1: Quarterly revenues for Amazon sales

To graph this time series, select the range B5:B43, which contains the quarter number (the first quarter is Quarter 1, and the last is Quarter 38). Next, hold down the Ctrl key and select the range containing revenue (D5:D43). Then choose Chart on the Insert tab, and choose the second option under the Scatter chart type. (Scatter with Smooth Lines and Markers.) Then delete the blue points and line on the x-axis. The time series plot is shown in Figure 52-2 on the next page.

Figure 52-2: Time series plot of quarterly toy revenues

There is an upward trend in revenues, but the fact that fourth quarter revenues dwarf revenues during the first three quarters of each year makes it hard to spot the trend. Because there are four quarters per year, it would be nice to graph average revenues during the last four quarters. This is called a 4 period moving average. Using a 4 quarter moving average smooths out the seasonal influence because each average will contain one data point for each quarter. Such a graph is called a moving average graph because the plotted average “moves” over time. Moving average graphs also “smooth out” random variation, which also helps us get a better idea of what is going on with our data.

To create a moving average graph of quarterly revenues, we can modify our chart. Select the graph, and then click a data point until all the data points are displayed in blue. Right-click on any point and click Add Trendline, and then select the Moving Average option. Set the period equal to 4. Microsoft Office Excel 2007 now creates the 4 quarter moving average trend curve that’s shown in Figure 52-3.

Figure 52-3: Four-quarter moving average trend curve

For each quarter, Excel plots the average of the current quarter and the last three quarters. Of course, for a four-quarter moving average, our moving average curve starts with the fourth data point. The moving average curve makes it clear that Amazon.com’s revenues had a steady upward trend through Quarter 19. In Quarter 20 (the fourth quarter of 2000), the trend curve dips, indicating that revenue began to slow during late 2000, which was the start of the tech bubble bursting. This caused a recession in the entire world economy, thereby lessening the sharp upward trend in Amazon.com sales.