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
(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 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. 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/excelmarketingforecastingtechnique3.html
The following describes the 3step process of creating a weighted moving average 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 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 2interval 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.
(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, 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 2interval 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.
A 3interval 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.
(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 a bit more smoothing occurs with the 3interval weighted moving average than with the 2interval weighted moving average.
(Click On Image To See a Larger Version)
For comparison, a 6interval 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.
(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 3interval 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)
(Click On Image To See a Larger Version)
As expected, the 6interval weighted moving average is significantly smoother than the 2 or 3interval 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 = ∑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 E_{t}, E_{t}^{2}, E_{t}, E_{t} / Y_{tact}, and then sum then 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 3interval 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 6interval 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 2interval, 3interval, and 6interval weighted moving averages as follows. The 2interval 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)
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
 Overview of Hypothesis Tests Using the Normal Distribution in Excel 2010 and Excel 2013
 OneSample zTest in 4 Steps in Excel 2010 and Excel 2013
 2Sample Unpooled zTest in 4 Steps in Excel 2010 and Excel 2013
 Overview of the Paired (TwoDependentSample) zTest in 4 Steps in Excel 2010 and Excel 2013
 tTests in Excel
 Overview of tTests: Hypothesis Tests that Use the tDistribution
 1Sample tTests in Excel
 1Sample tTest in 4 Steps 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 in 8 Steps 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
 2IndependentSample Pooled tTest in 4 Steps 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
 2 Sample Pooled tTest – Effect Size in Excel 2010 and Excel 2013
 2Sample Pooled tTest Power With G*Power Utility
 MannWhitney U Test in 12 Steps in Excel as 2Sample Pooled tTest Nonparametric Alternative in Excel 2010 and Excel 2013
 2 Sample Pooled tTest = SingleFactor ANOVA With 2 Sample Groups
 2IndependentSample Unpooled tTests in Excel
 2IndependentSample Unpooled tTest in 4 Steps 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 4 Steps 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 in 8 Steps 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 4 Steps in Excel 2010 and Excel 2013
 2Sample Pooled Hypothesis Test of Proportion in 4 Steps 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
 zBased Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
 tBased Confidence Intervals of a Population Mean in 2 Steps 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 2 Steps 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
 Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013
 Residual Evaluation For Simple Regression in 8 Steps 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
 Complete Multiple Linear Regression Example in 6 Steps 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 in 6 Steps 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
 Hosmer Lemeshow Test in Excel – Logistic Regression GoodnessofFit Test in Excel 2010 and Excel 2013
 SingleFactor ANOVA in Excel
 Overview of SingleFactor ANOVA
 SingleFactor ANOVA in 5 Steps 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 7 Steps 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 in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Psi – RMSSE – in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Omega Squared in Excel 2010 and Excel 2013
 Power of SingleFactor ANOVA Test Using Free Utility G*Power
 Welch’s ANOVA Test in 8 Steps in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 BrownForsythe FTest in 4 Steps in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 TwoFactor ANOVA With Replication in Excel
 TwoFactor ANOVA With Replication in 5 Steps 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
 Randomized Block Design ANOVA in Excel
 RepeatedMeasures ANOVA in Excel
 SingleFactor RepeatedMeasures ANOVA in 4 Steps in Excel 2010 and Excel 2013
 Sphericity Testing in 9 Steps For Repeated Measures ANOVA in Excel 2010 and Excel 2013
 Effect Size For RepeatedMeasures ANOVA in Excel 2010 and Excel 2013
 Friedman Test in 3 Steps For RepeatedMeasures ANOVA in Excel 2010 and Excel 2013
 ANCOVA 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 tDistribution’s PDF in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution’s CDF 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
 VLOOKUP
No comments:
Post a Comment