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
Performing Exponential
Smoothing in 3 Steps in
Excel
(Click On Image To See a Larger Version)
Overview of the Moving Average
The moving average is a statistical technique used to smooth out shortterm fluctuations in a series of data in order to more easily recognize longerterm 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 threemonth moving averages of sales data. The article will compare a twomonth, threemonth, and sixmonth 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 mostcommonly 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/excelsmostforecastingtoolsimple.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. 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/weightedmovingaverageaccuratesimple.html
Exponential Smoothing
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.
Exponential smoothing is sometimes referred to as an EMA (Exponential Moving Average) or an EWMA (Exponential Weighted Moving Average). There are two versions of a formula for Exponential Smoothing that are commonly used. Exponential smoothing was first suggested by Robert Goodell Brown in 1956. The exponential smoothing that is attributed to him and referred to as “Brown’s Simple Exponential Smoothing” is shown as follows:
S_{0} = X_{0}
S_{t} = αX_{t} + (1α)S_{t1}
Where
α = Smoothing constant 0 < α < 1
S_{t} = Y_{texp} = Forecast Value at time = t
X_{t} = X_{tact} = Actual Value at time = t
This formula will be used to perform exponential smoothing in this article. This exponential smoothing formula is convenient because it can be applied when as few as 2 time periods of data are available. It is good practice however to wait until at least several samples are available before performing exponential smoothing. A constant timeseries data stream will take approximately 3/α stages to reach 95 percent of the actual value.
The initial value of S_{0} plays an important role in calculating every subsequent S_{t}. Several methods of initializing the value of S_{0} should be evaluated. One possibility is to set S_{0} to the target value of the process. Another method is to set S_{0} to the average of the first four or five actual data values. The smaller the alpha, the greater is the importance of the proper value of S_{0 }because lower value of alpha cause greater smoothing of the forecast and therefore less resemblance to the original data observations.
This simple form of exponential smoothing can also be classified as an Autoregressive Integrated Moving Average (ARIMA) (0, 1, 1) model with no constant term.
Another commonly used version of the exponential smoothing formula is the following:
S_{t} = αX_{t1} + (1α)S_{t1}
This formula is attributed to J. Stuart Hunter in 1986. Excel uses this formula for its Exponential Smoothing tool as part of the Data Analysis ToolPak. This article will not cover the use of this formula but will cover the use original exponential smoothing formula, which was the following:
S_{0} = X_{0}
S_{t} = αX_{t} + (1α)S_{t1}
This formula is referred to as exponential smoothing because it can be shown with algebraic manipulation and direct substitution that the following formula, S_{t} = αX_{t} + (1α)S_{t1}, contains the exponentially decreasing weighted average of all previous data observations.
The following describes the 3step process of performing exponential smoothing of timeseries data in Excel:
Step 1 – Graph the Original Data in a TimeSeries Plot
The line chart is the most commonlyused Excel chart to graph timeseries data. An example of such an Excel chart used to plot 13 periods of sales data is shown as follows:
(Click On Image To See a Larger Version)
Step 2 – Create the Exponential Weighted Moving Average With Formulas in Excel
Excel does provide an Exponential Smoothing tool within the Data Analysis menu but it uses a different formula than the original exponential smoothing formula that will be applied in this blog article. The advantage of using a formula instead of a Data Analysis ToolPak tool is that a formula automatically adjusts when data is changed. Further, the formula will automatically recalculate the output if the smoothing factor alpha is changed. A Data Analysis does not automatically recalculate output and must be for each instance that a change is made is made either to data or to alpha. In many cases it is better to use formulas in place of Data Analysis ToolPak tools for those reasons. In cases when a large number of separate formulas working together is required, such as regression or ANOVA, the Data Analysis ToolPak is the preferred solution.
Exponential smoothing can be easily implemented with the following formulas. In this case the smoothing constant alpha is set to 0.2. Alpha can be set to any value between 0 and 1. The closer alpha is to 0, the more smoothing will occur in the forecasted values. The closer alpha is to 1, the less smoothing will occur and consequently the forecasted values will more closely resemble the observed data values.
(Click On Image To See a Larger Version)
Step 3 – Add the Exponential 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, rightclick 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:
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
The chart containing the original data series and that data’s exponential weighted moving average with alpha = 0.2 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.
(Click On Image To See a Larger Version)
An exponential weighted moving average with alpha = 0.5 can be created and placed on the chart using nearly the same procedure as follows.
(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 2interval series. The data will simply be added as one more data series in the chart. To do that, rightclick 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:
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
As expected less smoothing occurs with the alpha = 0.5 than with alpha = 0.2.
(Click On Image To See a Larger Version)
For comparison, an exponential weighted moving average will be calculated with alpha = 0.8 and added to the chart in the same way as follows. Note the progressively increasing alpha caused forecasted values to be closer to actual data observations and less “smoothed.”
(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 alpha = 0.2 and alpha = 0.5 series. The data will simply be added as one more data series in the chart. To do that, rightclick 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:
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
Here is the updated graph withal three exponential weighted moving average data series along with the original data time series.
(Click On Image To See a Larger Version)
As expected, the alpha = 0.2 series weighted moving average is significantly smoother than the other two weighted moving averages that have alpha = 0.5 and alpha = 0.8. 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 = ∑E_{t}/n = ∑(Y_{tact} – Y_{test})/n
A positive bias indicates a tendency to underforecast. A negative bias indicates a tendency to overforecast. 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 = ∑ E_{t} / n = ∑ (Y_{tact} – Y_{test}) / 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 = ∑ E_{t}^{2} / n = ∑ (Y_{tact} – Y_{test})^{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 = ∑ ( E_{t} / Y_{tact} ) * 100% / n = ∑ ( (Y_{tact} – Y_{test}) / Y_{tact} ) * 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 2interval, 3interval, and 6interval weighted moving average forecast obtained in this article and shown as follows:
(Click On Image To See a Larger Version)
The first step is to calculate the alpha = 0.2 values of E_{t}, E_{t}^{2}, E_{t}, E_{t} / Y_{tact} , and then sum them as follows:
(Click On Image To See a Larger Version)
Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:
(Click On Image To See a Larger Version)
The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the alpha = 0.5 exponential weighted moving average.
(Click On Image To See a Larger Version)
Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:
(Click On Image To See a Larger Version)
The same calculations are now performed to calculate Bias, MAD, MSE, MAPE and RMSE for the alpha = 0.8 exponential weighted moving average.
(Click On Image To See a Larger Version)
Bias, MAD, MSE, MAPE and RMSE can be calculated as follows:
(Click On Image To See a Larger Version)
Bias, MAD, MSE, MAPE and RMSE are summarized for the alpha = 0.2, alpha = 0.5, and alpha = 0.8 exponential weighted moving averages as follows. The alpha = 0.8 exponential weighted moving average is the model that most closely fits that actual data, as would be expected.
(Click On Image To See a Larger Version)
*****************************************
Here is a link to this article if you wish to link to it:
Excel Forecasting Tool #3  Exponential Smoothing
Excel Master Series Blog Directory
Statistical Topics and Articles In Each Topic
 Histograms in Excel
 Bar Chart in Excel
 Combinations & Permutations in Excel
 Normal Distribution in Excel
 Overview of the Normal Distribution
 Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
 Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
 Solving Normal Distribution Problems in Excel 2010 and Excel 2013
 Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
 An Important Difference Between the t and Normal Distribution Graphs
 The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
 Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An EasyToUnderstand Way
 tDistribution in Excel
 Binomial Distribution in Excel
 zTests in Excel
 tTests in Excel
 Overview of tTests: Hypothesis Tests that Use the tDistribution
 1Sample tTests in Excel
 Overview of the 1Sample tTest in Excel 2010 and Excel 2013
 Excel Normality Testing For the 1Sample tTest in Excel 2010 and Excel 2013
 1Sample tTest – Effect Size in Excel 2010 and Excel 2013
 1Sample tTest Power With G*Power Utility
 Wilcoxon SignedRank Test As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 Sign Test As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 2IndependentSample Pooled tTests in Excel
 Overview of 2IndependentSample Pooled tTest in Excel 2010 and Excel 2013
 Excel Variance Tests: Levene’s, BrownForsythe, and F Test For 2Sample Pooled tTest in Excel 2010 and Excel 2013
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and Shapiro Wilk Tests For TwoSample Pooled tTest
 TwoIndependentSample Pooled tTest  All Excel Calculations
 2Sample Pooled tTest – Effect Size in Excel 2010 and Excel 2013
 2Sample Pooled tTest Power With G*Power Utility
 MannWhitney U Test in Excel as 2Sample Pooled tTest Nonparametric Alternative in Excel 2010 and Excel 2013
 2Sample Pooled tTest = SingleFactor ANOVA With 2 Sample Groups
 2IndependentSample Unpooled tTests in Excel
 2IndependentSample Unpooled tTest in Excel 2010 and Excel 2013
 Variance Tests: Levene’s Test, BrownForsythe Test, and FTest in Excel For 2Sample Unpooled tTest
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and ShapiroWilk For 2Sample Unpooled tTest
 2Sample Unpooled tTest Excel Calculations, Formulas, and Tools
 Effect Size for a 2IndependentSample Unpooled tTest in Excel 2010 and Excel 2013
 Test Power of a 2Independent Sample Unpooled tTest With GPower Utility
 Paired (2Sample Dependent) tTests in Excel
 Paired tTest in Excel 2010 and Excel 2013
 Excel Normality Testing of Paired tTest Data
 Paired tTest Excel Calculations, Formulas, and Tools
 Paired tTest – Effect Size in Excel 2010, and Excel 2013
 Paired tTest – Test Power With GPower Utility
 Wilcoxon SignedRank Test As a Paired tTest Alternative
 Sign Test in Excel As A Paired tTest Alternative
 Hypothesis Tests of Proportion in Excel
 Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
 1Sample Hypothesis Test of Proportion in Excel 2010 and Excel 2013
 2Sample Pooled Hypothesis Test of Proportion in Excel 2010 and Excel 2013
 How To Build a Much More Useful SplitTester in Excel Than Google's Website Optimizer
 ChiSquare Independence Tests in Excel
 ChiSquare GoodnessOfFit Tests in Excel
 F Tests in Excel
 Correlation in Excel
 Pearson Correlation in Excel
 Spearman Correlation in Excel
 Confidence Intervals in Excel
 Overview of zBased Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
 tBased Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
 Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
 Confidence Interval of Population Proportion in Excel 2010 and Excel 2013
 Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013
 Simple Linear Regression in Excel
 Overview of Simple Linear Regression in Excel 2010 and Excel 2013
 Simple Linear Regression Example in Excel 2010 and Excel 2013
 Residual Evaluation For Simple Regression in Excel 2010 and Excel 2013
 Residual Normality Tests in Excel – KolmogorovSmirnov Test, AndersonDarling Test, and ShapiroWilk Test For Simple Linear Regression
 Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
 All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
 Prediction Interval of Simple Regression in Excel 2010 and Excel 2013
 Multiple Linear Regression in Excel
 Basics of Multiple Regression in Excel 2010 and Excel 2013
 Multiple Linear Regression Example in Excel 2010 and Excel 2013
 Multiple Linear Regression’s Required Residual Assumptions
 Normality Testing of Residuals in Excel 2010 and Excel 2013
 Evaluating the Excel Output of Multiple Regression
 Estimating the Prediction Interval of Multiple Regression in Excel
 Regression  How To Do Conjoint Analysis Using Dummy Variable Regression in Excel
 Logistic Regression in Excel
 Logistic Regression Overview
 Logistic Regression Performed in Excel 2010 and Excel 2013
 R Square For Logistic Regression Overview
 Excel R Square Tests: Nagelkerke, Cox and Snell, and LogLinear Ratio in Excel 2010 and Excel 2013
 Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
 Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
 HosmerLemeshow Test in Excel – Logistic Regression GoodnessofFit Test in Excel 2010 and Excel 2013
 SingleFactor ANOVA in Excel
 Overview of SingleFactor ANOVA
 SingleFactor ANOVA Example in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For Each SingleFactor ANOVA Sample Group
 KruskalWallis Test Alternative For Single Factor ANOVA in Excel 2010 and Excel 2013
 Levene’s and BrownForsythe Tests in Excel For SingleFactor ANOVA Sample Group Variance Comparison
 SingleFactor ANOVA  All Excel Calculations
 Overview of PostHoc Testing For SingleFactor ANOVA
 TukeyKramer PostHoc Test in Excel For SingleFactor ANOVA
 GamesHowell PostHoc Test in Excel For SingleFactor ANOVA
 Overview of Effect Size For SingleFactor ANOVA
 ANOVA Effect Size Calculation Eta Squared (?2) in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Psi (?) – RMSSE – in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Omega Squared (?2) in Excel 2010 and Excel 2013
 Power of SingleFactor ANOVA Test Using Free Utility G*Power
 Welch’s ANOVA Test in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 BrownForsythe FTest in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 TwoFactor ANOVA With Replication in Excel
 TwoFactor ANOVA With Replication in Excel 2010 and Excel 2013
 Variance Tests: Levene’s and BrownForsythe For 2Factor ANOVA in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
 Excel Post Hoc Tukey’s HSD Test For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication – Test Power With GPower Utility
 ScheirerRayHare Test Alternative For 2Factor ANOVA With Replication
 TwoFactor ANOVA Without Replication in Excel
 Normality Testing in Excel
 Creating a Box Plot in 8 Steps in Excel
 Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
 ChiSquare GoodnessofFit Test For Normality in 9 Steps in Excel
 KolmogorovSmirnov, AndersonDarling, and ShapiroWilk Normality Tests in Excel
 Nonparametric Testing in Excel
 MannWhitney U Test in 12 Steps in Excel
 Wilcoxon SignedRank Test in 8 Steps in Excel
 Sign Test in Excel
 Friedman Test in 3 Steps in Excel
 ScheirerRayHope Test in Excel
 Welch's ANOVA Test in 8 Steps Test in Excel
 BrownForsythe F Test in 4 Steps Test in Excel
 Levene's Test and BrownForsythe Variance Tests in Excel
 ChiSquare Independence Test in 7 Steps in Excel
 ChiSquare GoodnessofFit Tests in Excel
 ChiSquare Population Variance Test in Excel
 Post Hoc Testing in Excel
 Creating Interactive Graphs of Statistical Distributions in Excel
 Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
 Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the ChiSquare Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution in Excel 2010 and Excel 2013
 Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
 Solving Problems With Other Distributions in Excel
 Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
 Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
 Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
 Solving Beta Distribution Problems in Excel 2010 and Excel 2013
 Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
 Solving Poisson Distribution Problems in Excel 2010 and Excel 2013
 Optimization With Excel Solver
 Maximizing Lead Generation With Excel Solver
 Minimizing Cutting Stock Waste With Excel Solver
 Optimal Investment Selection With Excel Solver
 Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
 Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
 Optimizing a Bond Portfolio With Excel Solver
 Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers
 ChiSquare Population Variance Test in Excel
 Analyzing Data With Pivot Tables
 SEO Functions in Excel
 Time Series Analysis in Excel
nice one! Please provide me a link for double and triple exp. smoothing as well...
ReplyDelete