Excel-Moving Average analysis (trend analysis)

Zookeeper

From the author's website data analysis: data-driven website management, optimization and operation: http://item.jd.com/11295690.html

The moving average can effectively eliminate the random fluctuations of actual data values, so as to obtain a smoother data change trend chart. Through the analysis of historical trend changes, it can predict the direction of data changes in the first or several phases in the future.

1. Types of moving average computing

Based on the weights of each element used for prediction, the weights can be divided into simple moving average and weighted moving average.

An important concept of Moving Average is "interval". The calculation of Moving Average is limited within the interval. Taking the calculation of a simple moving average as an example, if the interval is 3, then each moving average is the average of the first three original data. In Figure 9-86, C4 is the average value of cells A2, A3, and A4, C5 is the average value of cells A3, A4, and A5, and so on. This moving average can effectively eliminate the impact of numerical fluctuations. For example, in Figure 9-86, the difference between the maximum and minimum values of the moving average is 9 (25-16), while the difference between the maximum and minimum values of the original data is 21 (31-10 ).

The weighted moving average only allocates different weights for each data within a period of time, rather than simply calculating the average. For example, in C4 cells shown in Figure 9-87, the interval is still 3, the weight of the first data is 35%, the second data is 30%, and the third data is 35%. Note: The sum of weights must be equal to 1. Comparing the data in figure 9-86 and Figure 9-87, we can see the difference between the weighted moving average and the simple moving average.

When weighted moving average is used, weight selection is a matter of attention. Generally, the most recent data can indicate the future, so the weight should be higher. For example, you can better estimate the sales volume for the next month based on the sales volume for the previous month. However, if the data is seasonal, the weight should also be seasonal. For example, it is not possible to predict the spring sales volume based on the sales volume of T-shirts in February.

Figure 9-86 simple moving average calculation

Figure 9-87 Weighted Moving Average calculation

2. Draw a moving average chart

You can use the data analysis tool provided by Excel to draw a simple moving average.

• Click the "Data Analysis" button on the "data" tab. In the displayed dialog box, select "Move average" and click "OK". The "Move average" dialog box shown in 9-88 is displayed.

Figure 9-88 "Moving Average" dialog box

• In the input area, select the original data area A1: A32. Because A1 is the title, select the "flag in the first line" option. Select 3 for the interval. The "output area" is used to specify the placement position of the moving average. Select a starting cell. Select "chart output" to draw a line chart at the same time.

• Click OK to view the moving average calculation result and the chart, as shown in 9-89.

Figure 9-89 moving average line chart

The "predicted value" data series in the figure are the line charts drawn using the moving average. It can be seen that the data series are much smoother than the actual values, making it easier to judge the trend. Because the interval is 3, the values of C2 and C3 are # N/.

3. Precautions for moving average Analysis

Moving Average has a smooth or smooth effect on the original sequence, and increasing the number of intervals will make the smooth fluctuation effect better, but this will also make the forecast value less sensitive to actual data changes, therefore, the moving average interval should not be too large. For example, Figure 9-90 is a line chart drawn at an interval of 6. You can see that the "predicted value" data series is much smoother than Figure 9-89.

Figure 9-90 the moving average line chart drawn when the interval is 6

When the data includes seasonal and periodic changes, the moving average interval is the same as the seasonal or periodic change length to eliminate the seasonal or periodic change impact.

Moving averages do not always reflect the trend well. Because it is an average value, the predicted value always stays at the past level and cannot be predicted to cause higher or lower fluctuations in the future.

Moving Average analysis can be performed only with a large amount of historical data. Zookeeper