Saturday, October 30, 2010

Creating a Simple Moving Average in 3 Steps in Excel

Creating a Simple Moving

Average in 3 Steps in Excel

Simple Moving Average in Excel - 2-Interval, 3_Interval, 6-Interval Charts
(Click On Image To See a Larger Version)

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

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. This blog article will provide a detailed explanation of the implementation of this technique in Excel.

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. 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/weighted-moving-average-accurate-simple.html

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

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

Step 2 – Create the Moving Average in Excel

Excel provides the Moving Average tool within the Data Analysis menu. The Moving Average tool creates a simple moving average from a data series.

Simple Moving Average in Excel - Data Analysis Tool
(Click On Image To See a Larger Version)

The Moving Average dialogue box should be filled out as follows in order to create a moving average of the previous 2 periods of data for each data point.

Simple Moving Average in Excel - 2-Interval Dialogue Box
(Click On Image To See a Larger Version)

The output of the 2-period moving average is shown as follows, along with the formulas that were used to calculate the value of each point in the moving average.

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

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

Simple Moving Average in Excel - Add Data Series to Chart0
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - New Data Series Information
(Click On Image To See a Larger Version)

The chart containing the original data series and that data’s 2-interval simple 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.

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

A 3-interval moving average can be created and placed on the chart using the same procedure as follows:

Simple Moving Average in Excel - 3-Interval Dialogue Box
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

It is interesting to note that the 2-interval simple moving average creates a smoother graph than the3-interval simple moving average. In this case the 2-interval simple moving average might be the more desirable than the 3-interval moving average.

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

For comparison, a 6-interval simple moving average will be calculated and added to the chart in the same way as follows:

Simple Moving Average in Excel - 6-Interval Dialogue Box
(Click On Image To See a Larger Version)

Simple Moving Average in Excel - Output
(Click On Image To See a Larger Version)

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

As expected,the 6-interval simple moving average is significantly smoother than the 2 or 3-interval simple moving averages. A smoother graph more closely fits a straight line.

Analyzing Forecast Accuracy

Accuracy can be described as goodness of fit. 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 Simple Moving Average

Bias, MAD, MSE, RMSE, and MAPE will be calculated in Excel to evaluate the 2-interval, 3-interval, and 6-interval simple moving average forecast obtained in this article and shown as follows:



Simple Moving Average in Excel - Simple Moving Averages
(Click On Image To See a Larger Version)

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

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

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

Simple Moving Average in Excel - 2-Interval Final Calculations
(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 simple moving average.

Simple_Mov_Av_15_3-Interval_Initial_Calculations_600
(Click On Image To See a Larger Version)

Simple_Mov_Av_16_3-Interval_Final_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 simple moving average.

Simple_Mov_Av_17_6-Interval_Initial_Calculations_600
(Click On Image To See a Larger Version)

Simple_Mov_Av_18_6-Interval_Final_Calculations_600
(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 simple moving averages as follows. The 3-interval simple moving average is the model that most closely fits that actual data.

Simple Moving Average in Excel - Error Summarization
(Click On Image To See a Larger Version)

 Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

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

5 comments:

  1. AnonymousDecember 4, 2011 at 3:01 PM

    This Helped me a lot. I am working on a forecasting problem, and that is just what I needed

    ReplyDelete
    Replies
    1. seep learnJuly 4, 2024 at 8:48 PM

      A Simple Moving Average (SMA) is a widely used technical analysis indicator that smooths out price data by calculating the average of a selected number of periods. It helps traders and analysts identify trends and potential reversals in financial markets. Here’s an overview of how the Simple Moving Average works:

      Machine Learning Final Year Projects

      Deep Learning Projects for Final Year

      Definition
      The Simple Moving Average (SMA) is calculated by averaging the closing prices of a security over a specified number of periods. For example, a 10-day SMA calculates the average closing price of the last 10 trading days.

      Delete
  2. BrendanJuly 4, 2014 at 9:53 PM

    thanks a lot!

    ReplyDelete
  3. Robert JohnOctober 20, 2022 at 2:07 AM

    If somebody asks, I would suggest this site if you want to acquire a Marketing Dissertation Writing Service since you can easily contact them and they can give you this wonderful service for your job.

    ReplyDelete
  4. AnonymousFebruary 13, 2024 at 11:18 AM

    Hello. Exploring multilingual spaces in LA https://antenalosangeles.org/ through Antena Los Angeles has been eye-opening! It's fascinating to witness the diversity of languages thriving in this vibrant city. Antena celebrates the richness of linguistic diversity, fostering connections and understanding among communities. As a student, it's inspiring to see how language can unite people from different backgrounds and cultures, making LA truly unique.

    ReplyDelete

Subscribe to: Post Comments (Atom)