"Prediction is very difficult, especially
if it's about the future."
--Nils Bohr, Nobel laureate in Physics
Forecasting using moving
average method
Course website: http://www.kf.vu.lt/~albud/progn/Portugal
1. Explaining the method
2. Using Excel: demonstration.
Forecasting a number of visitors
3. Assignments
Main idea of the method
The moving average uses the average of
a given number of the most recent
periods' value to forecast the value for
the next period.
Moving average smoothes down the
fluctuations in the data
Smoothing
Smoothing the data variation: a graphical presentation
Data Smoothed data
Moore smoothed data
Prerequisite data pattern
Moving average method is commonly
used when the pattern in the data does
not have periodical (seasonal or cyclic)
characteristics and is neither growing
nor declining rapidly.
Prerequisite data pattern: an
example
Horizontal (irregular variations)
A formula for the Moving
Average
If forecast for t period is denoted by Ft,
and the actual value of the time-series
was At-1 during period t-1, At-2 during
period t-2, etc., then n period simple
moving average is expressed as:
Ft=(At-1+At-2+...At-n)/n
For comparison: “not moving”
average
Month Number Average
of clients
January 50
February 30
March 20
April 30
May 32,5
(forecast)
Choosing the averaging period
The averaging period (value of n) must be
determined by the decision-maker. It is
important to try to select the best period to
use for the moving average.
As a general rule, the number of periods
used should relate to the amount of
random variability in the data.
Specifically, the bigger the moving average
period, the greater the random elements are
smoothed.
Calculation of a moving
average: an Example
Month Number Moving
of clients average
(2)
January 50
February 30
March 20 40
April 30 25
May 25
(forecast)
Calculation of errors: Example
Month Number of Moving Error Percentage
clients average error
(2) (MAPE)
January 50
February 30
March 20 40 -20 100
April 30 25 -5 17
May 25
(forecast)
Mean error 12,5 58,5
Forecasting with Excel
Forecasting a number of visitors of a small
library. Demonstration of the forecasting
procedure using MA method
Assignment 1. Repeating of the forecasting
procedure with the same data
Assignment 2. Repeating of the forecasting
procedure with the same data but using
different parameter (an interval 4).
Evaluation of MA
Advantage: very simple method.
Shortcomings:
Not applicable when trend exists,
No strict rule of choosing its parameter,
The new and the old data are treated in
the same way (while, in fact, the old
data should be treated as being less
signifficant).
Modification of Moving
Average method
Weighted Moving Average:
Simple moving average technique
assigns equal weights to each period of
historical data; the weighted moving
average technique assigns different
weights to historical data allowing the
model to respond quickly to any shift in
the series being studied.
End
Switch to Excel
Open the Workbook Moving_average.xls
Calculate Moving average using Excel
formulas or or Data Analysis ToolPak
Learning objectives
To learn how to compute moving
average manually and using Excel
To use moving average method in
forecasting
To calculate errors of forecasts