3 Month Weighted Moving Average Excel

Moving Average This example teaches you how to calculate the moving average of a time series in Excel . A moving average is used to smooth out irregularities (peaks and valleys) to easily recognize trends. 1. First, lets take a look at our time series. 2. On the Data tab, click Data Analysis. Note: cant find the Data Analysis button Click here to load the Analysis ToolPak add-in . 3. Select Moving Average and click OK. 4. Click in the Input Range box and select the range B2:M2. 5. Click in the Interval box and type 6. 6. Click in the Output Range box and select cell B3. 8. Plot a graph of these values. Explanation: because we set the interval to 6, the moving average is the average of the previous 5 data points and the current data point. As a result, peaks and valleys are smoothed out. The graph shows an increasing trend. Excel cannot calculate the moving average for the first 5 data points because there are not enough previous data points. 9. Repeat steps 2 to 8 for interval 2 and interval 4. Conclusion: The larger the interval, the more the peaks and valleys are smoothed out. The smaller the interval, the closer the moving averages are to the actual data points. Creating a Weighted Moving Average in 3 Steps Overview of the Moving Average The moving average is a statistical technique used to smooth out short-term fluctuations in a series of data in order to more easily recognize longer-term trends or cycles. The moving average is sometimes referred to as a rolling average or a running average. A moving average is a series of numbers, each of which represents the average of an interval of specified number of previous periods. The larger the interval, the more smoothing occurs. The smaller the interval, the more that the moving average resembles the actual data series. Moving averages perform the following three functions: Smoothing the data, which means to improve the fit of the data to a line. Reducing the effect of temporary variation and random noise. Highlighting outliers above or below the trend. The moving average is one of the most widely used statistical techniques in industry to identify data trends. For example, sales managers commonly view three-month moving averages of sales data. The article will compare a two-month, three-month, and six-month simple moving averages of the same sale data. The moving average is used quite often in technical analysis of financial data such as stock returns and in economics to locate trends in macroeconomic time series such as employment. There are a number of variations of the moving average. The most-commonly employed are the simple moving average, the weighted moving average, and the exponential moving average. Performing each of these techniques in Excel will be covered in detail in separate articles in this blog. Here is a brief overview of each of these three techniques. Simple Moving Average Every point in a simple moving average is the average of a specified number of previous periods. A link to another article in this blog which provides a detailed explanation of the implementation of this technique in Excel is as follows: Weighted Moving Average Points in the weighted moving average also represent an average of a specified number of previous periods. The weighted moving average applies different weighting to certain previous periods quite often the more recent periods are given greater weight. This blog article will provide a detailed explanation of the implementation of this technique in Excel. Exponential Moving Average Points in the exponential moving average also represent an average of a specified number of previous periods. Exponential smoothing applies weighting factors to previous periods that decrease exponentially, never reaching zero. As a result exponential smoothing takes into account all previous periods instead of a designated number of previous periods that the weighted moving average does. A link to another article in this blog which provides a detailed explanation of the implementation of this technique in Excel is as follows: The following describes the 3-step process of creating a weighted moving average of time-series data in Excel: Step 1 8211 Graph the Original Data in a Time-Series Plot The line chart is the most commonly-used Excel chart to graph time-series data. An example of such an Excel chart used to plot 13 periods of sales data is shown as follows: Step 2 8211 Create the Weighted Moving Average With Formulas in Excel Excel does not provide the Moving Average tool within the Data Analysis menu so the formulas must be constructed manually. In this case a 2-interval weighted moving average is created by applying a weight of 2 to the most recent period and a weight of 1 to the period prior to that. The formula in cell E5 can be copied down to cell E17. Step 3 8211 Add the Weighted Moving Average Series to the Chart This data should now be added to the chart containing the original time line of sales data. The data will simply be added as one more data series in the chart. To do that, right-click anywhere on the chart and a menu will pop up. Hit Select Data to add the new series of data. The moving average series will be added by completing the Edit Series dialogue box as follows: The chart containing the original data series and that data8217s 2-interval weighted moving average is shown as follows. Note that the moving average line is quite a bit smoother and raw data8217s deviations above and below the trend line are much more apparent. The overall trend is now much more apparent as well. A 3-interval moving average can be created and placed on the chart using nearly the same procedure as follows. Note that the most recent period is assigned the weight of 3, the period prior to that is assigned and weight of 2, and the period prior to that is assigned a weight of 1. This data should now be added to the chart containing the original time line of sales data along with the 2-interval series. The data will simply be added as one more data series in the chart. To do that, right-click anywhere on the chart and a menu will pop up. Hit Select Data to add the new series of data. The moving average series will be added by completing the Edit Series dialogue box as follows: As expected a bit more smoothing occurs with the 3-interval weighted moving average than with the 2-interval weighted moving average. For comparison, a 6-interval weighted moving average will be calculated and added to the chart in the same way as follows. Note the progressively decreasing weights assigned as periods become more distant in the past. This data should now be added to the chart containing the original time line of sales data along with the 2 and 3-interval series. The data will simply be added as one more data series in the chart. To do that, right-click anywhere on the chart and a menu will pop up. Hit Select Data to add the new series of data. The moving average series will be added by completing the Edit Series dialogue box as follows: As expected, the 6-interval weighted moving average is significantly smoother than the 2 or 3-interval weighted moving averages. A smoother graph more closely fits a straight line. Analyzing Forecast Accuracy The two components of forecast accuracy are the following: Forecast Bias 8211 The tendency of a forecast to be consistently higher or lower than actual values of a time series. Forecast bias is the sum of all error divided by the number of periods as follows: A positive bias indicates a tendency to under-forecast. A negative bias indicates a tendency to over-forecast. Bias does not measure accuracy because positive and negative error cancel each other out. Forecast Error 8211 The difference between actual values of a time series and the predicted values of the forecast. The most common measures of forecast error are the following: MAD 8211 Mean Absolute Deviation MAD calculates the average absolute value of the error and is computed with the following formula: Averaging the absolute values of the errors eliminates the canceling effect of positive and negative errors. The smaller the MAD, the better the model is. MSE 8211 Mean Squared Error MSE is a popular measure of error that eliminates the cancelling effect of positive and negative errors by summing the squares of the error with the following formula: Large error terms tend to exaggerate MSE because the error terms are all squared. RMSE (Root Square Mean) reduces this problem by taking the square root of MSE. MAPE 8211 Mean Absolute Percent Error MAPE also eliminates the cancelling effect of positive and negative errors by summing the absolute values of the error terms. MAPE calculates the sum of the percent error terms with the following formula: By summing percent error terms, MAPE can be used to compare forecasting models that use different scales of measurement. Calculating Bias, MAD, MSE, RMSE, and MAPE in Excel For the Weighted Moving Average Bias, MAD, MSE, RMSE, and MAPE will be calculated in Excel to evaluate the 2-interval, 3-interval, and 6-interval weighted moving average forecast obtained in this article and shown as follows: The first step is to calculate E t . E t 2. E t , E t Y t-act . and then sum then as follows: Bias, MAD, MSE, MAPE and RMSE can be calculated as follows: The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 3-interval weighted moving average. Bias, MAD, MSE, MAPE and RMSE can be calculated as follows: The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 6-interval weighted moving average. Bias, MAD, MSE, MAPE and RMSE can be calculated as follows: Bias, MAD, MSE, MAPE and RMSE are summarized for the 2-interval, 3-interval, and 6-interval weighted moving averages as follows. The 2-interval weighted moving average is the model that most closely fits that actual data, as would be expected. 160 Excel Master Series Blog Directory Statistical Topics and Articles In Each TopicHow to Calculate Weighted Moving Averages in Excel Using Exponential Smoothing Excel Data Analysis For Dummies, 2nd Edition The Exponential Smoothing tool in Excel calculates the moving average. However, exponential smoothing weights the values included in the moving average calculations so that more recent values have a bigger effect on the average calculation and old values have a lesser effect. This weighting is accomplished through a smoothing constant. To illustrate how the Exponential Smoothing tool works, suppose that you8217re again looking at the average daily temperature information. To calculate weighted moving averages using exponential smoothing, take the following steps: To calculate an exponentially smoothed moving average, first click the Data tab8217s Data Analysis command button. When Excel displays the Data Analysis dialog box, select the Exponential Smoothing item from the list and then click OK. Excel displays the Exponential Smoothing dialog box. Identify the data. To identify the data for which you want to calculate an exponentially smoothed moving average, click in the Input Range text box. Then identify the input range, either by typing a worksheet range address or by selecting the worksheet range. If your input range includes a text label to identify or describe your data, select the Labels check box. Provide the smoothing constant. Enter the smoothing constant value in the Damping Factor text box. The Excel Help file suggests that you use a smoothing constant of between 0.2 and 0.3. Presumably, however, if you8217re using this tool, you have your own ideas about what the correct smoothing constant is. (If you8217re clueless about the smoothing constant, perhaps you shouldn8217t be using this tool.) Tell Excel where to place the exponentially smoothed moving average data. Use the Output Range text box to identify the worksheet range into which you want to place the moving average data. In the worksheet example, for example, you place the moving average data into the worksheet range B2:B10. (Optional) Chart the exponentially smoothed data. To chart the exponentially smoothed data, select the Chart Output check box. (Optional) Indicate that you want standard error information calculated. To calculate standard errors, select the Standard Errors check box. Excel places standard error values next to the exponentially smoothed moving average values. After you finish specifying what moving average information you want calculated and where you want it placed, click OK. Excel calculates moving average information.