Calculating the Moving Average

Background

Time moving average is a commonly used data smoothing method, which filters out short-term fluctuations and highlights long-term trends in data. In short, it involves averaging the data within a certain time window. For example, 5-day moving average, 10-day moving average, 30-day moving average, and 3-month moving average.

For example, if the size of the time window is 3 months, you can calculate the 3-month moving average through the following steps.

Assume Sales Amount from the first month to the fifth month are M1, M2, M3, M4, and M5 respectively.

• Moving average for the first month = (M1)/1

• Moving average for the second month = (M1 + M2)/2

• Moving average for the third month = (M1 + M2 + M3)/3

• Moving average for the fourth month = (M2 + M3 + M4)/3

• Moving average for the fifth month = (M3 + M4 + M5)/3

Expected Effect

The yellow line shows the fluctuation of sales with months, featuring large fluctuations and many sharp steep parts.

To better observe the trend of sales, you can use the 3-month moving average method. The following figure shows the processed line in blue.

Adding Data

Sample data: Supermarket Sales Data.xlsx

Create an analysis subject and upload the sample data.

Component Creation

1. Create a group table and drag Order Date and Sales into the table to calculate the sales in different years and months, as shown in the figure.

2. Calculate the moving average of sales in the past three months, as shown in the following figure.

3. Drag Moving Average of the Latest 3 Months into the chart, as shown in the following figure.

Expected Effect

For details, see the previous section "Expected Effect."