Tuesday, November 2, 2010

Creating a Weighted Moving Average in 3 Steps in Excel

This is one of the following three articles on Time Series Analysis in Excel

Forecasting With Exponential Smoothing in Excel

Forecasting With the Weighted Moving Average in Excel

Forecasting With the Simple Moving Average in Excel

 

Creating a Weighted

Moving Average in 3 Steps

in Excel

Weighted Moving Average in Excel - Graphs With 2, 3, and 6-Interval Moving Averages
(Click On Image To See a Larger Version)</< p>

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:

  1. Smoothing the data, which means to improve the fit of the data to a line.

  2. Reducing the effect of temporary variation and random noise.

  3. 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:

http://blog.excelmasterseries.com/2010/10/excels-most-forecasting-tool-simple.html

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:

http://blog.excelmasterseries.com/2010/11/excel-marketing-forecasting-technique-3.html

The following describes the 3-step process of creating a weighted moving average of time-series data in Excel:

Step 1 – 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:

Weighted Moving Average in Excel - Raw Data
(Click On Image To See a Larger Version)

Step 2 – 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.

Weighted Moving Average in Excel - 2-Interval Moving Average Formulas
(Click On Image To See a Larger Version)

Step 3 – 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:

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

The chart containing the original data series and that data’s 2-interval weighted moving average is shown as follows. Note that the moving average line is quite a bit smoother and raw data’s deviations above and below the trend line are much more apparent. The overall trend is now much more apparent as well.

Weight_Mov_Av_5_2-Int_Series_600

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.

Weighted Moving Average in Excel - 3-Interval Weighted Average Formulas
(Click On Image To See a Larger Version)

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:

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

As expected a bit more smoothing occurs with the 3-interval weighted moving average than with the 2-interval weighted moving average.

Weighted Moving Average in Excel - 2 and 3-Interval Weighted Moving Average Series
(Click On Image To See a Larger Version)

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.

Weighted Moving Average in Excel - 6-Interval Weighted Moving Average FOrmula
(Click On Image To See a Larger Version)

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:

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

Weighted Moving Average in Excel - Add Series Dialogue Box
(Click On Image To See a Larger Version)

Weighted Moving Average in Excel - 2, 3, and 6-Interval Moving Average Series
(Click On Image To See a Larger Version)

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 – 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:

Bias = ∑Et/n = ∑(Yt-act – Yt-est)/n

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 – 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 – Mean Absolute Deviation

MAD calculates the average absolute value of the error and is computed with the following formula:

MAD = ∑ |Et| / n = ∑ |(Yt-act – Yt-est)| / n

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 – 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:

MSE = ∑ Et2 / n = ∑ (Yt-act – Yt-est)2 / n

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 – 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:

MAPE = ∑ ( |Et| / Yt-act ) * 100% / n = ∑ ( |(Yt-act – Yt-est)| / Yt-act ) * 100% / n

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:

Weight_Mov_Av_14_Data_600
(Click On Image To See a Larger Version)

The first step is to calculate Et, Et2, |Et|, |Et| / Yt-act, and then sum then as follows:

Weighted Moving Average in Excel - 2-Interval Moving Average Initial Calculations
(Click On Image To See a Larger Version)

Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:

Weight_Mov_Av_16_2-Interval_FInal_Error_Calculations_600
(Click On Image To See a Larger Version)

The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 3-interval weighted moving average.

Weighted Moving Average in Excel - 3-Interval Moving Average Initial Calculations
(Click On Image To See a Larger Version)

Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:

Weight_Mov_Av_18_3-Interval_Final_Error_Calculations_600
(Click On Image To See a Larger Version)

The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the 6-interval weighted moving average.

Weighted Moving Average in Excel - 6-Interval Moving Average Initial Calculations
(Click On Image To See a Larger Version)

Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:

Weighted Moving Average in Excel - 6-Interval Moving Average Final Calculations
(Click On Image To See a Larger Version)

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.

Weighted Moving Average in Excel - Moving Average Accuracy Summary
(Click On Image To See a Larger Version)

 Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at
Labels: , , , , , , ,

3 comments:

  1. research papers essaysJanuary 6, 2021 at 7:06 AM

    Great and usaful information! Writing an essay is extremely useful because it allows the author to learn clearly and correctly formulate thoughts, structure information, correctly argue findings.

    ReplyDelete
  2. casinononaamsMarch 15, 2021 at 2:01 AM

    All candidates join our team? A multi-level recruitment procedure ensures only the best writers get to work on your orders www.capstonepaper.net. Your requirements are considered? We take the provided instructions seriously and write your paper in accordance with them.

    ReplyDelete
  3. Diksha GuptaAugust 3, 2022 at 12:41 AM

    Hi I have read a lot from this blog thank you for sharing this information. We Provide all the essential topics in data science like,Python, Machine Learning,AI and Deep Learning, Tableau, etc.
    For more information just log in to our website :- Data Science course in Mumbai

    ReplyDelete

Subscribe to: Post Comments (Atom)