GORS Forecasting Course
1
MS924 Spreadsheet Modelling & Demand Forecasting
Lab Exercises: Extensions of Exponential Smoothing with
Updating & Trend
The exercises on this sheet allow you to consolidate your understanding of the forecast
error analysis and to extend the simple exponential smoothing method in different
ways. First by developing an adaptive mechanism for the smoothing coefficient and
secondly by including an additional parameter to allow trends with the time series to be
modelled.
The exercises are designed to be comprehensive insofar as they also include summary
explanations of the method extensions. Fuller details about each method can be found
in the directed reading material. The exercises are designed to help you develop your
understanding of the methods and to translate your understanding through the
spreadsheet application of the methods to data. The spreadsheet is simply being used as
calculator hence our focus is upon the correctness of the method implementation.
All exercises assume a time series is the data input to modelling and, in general, is
denoted by
1 2
, ,…
t
Y Y Y where
t
Y is the observed demand at time period t. Our goal is to
obtain a forecast for the next time periods,
1 2 3
, , ,…
t t t
F F F
.
The data sets with the observed the time series are given in excel files as directed in
each exercise. As well as showing your calculations in your spreadsheet, please note
your interpretation of your findings as this will provide a useful record of your analysis
and will help you prepare for the assignment.
Exercise 1
Consider the data on units shipped for a product in the table below and given in the file
“Shipment Units Data Set”.
Time Period Sales Units (thousands)
1 200
2 135
3 195
4 197.5
5 310
6 175
7 155
8 130
9 220
10 277.5
11 235
2
(a) Plot the shipment unit data and comment upon the patterns in the time series.
(b) Set up a spreadsheet model to support simple exponential smoothing forecasts.
i. Use an initial smoothing value of alpha =0.2 to generate forecasts for
time period 12 for the unit data.
ii. Calculate a variety of forecast error statistics such as Mean Error (ME),
Mean Absolute Error (MAE), Mean Absolute Percentage Error (MAPE)
and Mean Square Error (MSE).
iii. Try different values of the smoothing coefficient alpha and assess which
gives the best forecast accuracy and consistency using the forecast error
statistics computed. Interpret the best value of alpha in terms of how it
allocates weight to newer relative to older data.
(c) The simple exponential smoothing method, as implemented in part (b) requires
the user to set the parameter value. For example, set alpha=0.2. This parameter
value then remains static until it is manually changed by the analyst. Do you
think this is practicable when scaling up exponential smoothing to real
applications?
To avoid the need for manual setting of parameters, we can consider the
adaptive exponential smoothing method which has a learning capability
allowing the parameter value to be updated as new data becomes available.
The expression for adaptive exponential is a more complex version of the
formula for simple exponential smoothing and there is a need for greater
organisation of the spreadsheet calculations. But if you think it through carefully
then it should be quite intuitive.
The basic formula for adaptive exponential smoothing is the same as for the
simple version except that the smoothing parameter, alpha, is no longer constant
but is allowed to vary with time, and so has the subscript t.
The adaptive value of alpha is given by
1
1
where
1
1
and
t t t
t t t
t t t
A E A
M E M
E Y F
1 1t t t t tF Y F
1
t
t
t
A
M
3
The adapted value of alpha is based upon the absolute value of the ratio of the
smoothed forecast error relative to the smoothed absolute value of the forecast
error. The numerator is a measure of the accuracy and the denominator is a
measure of the consistency.
Beta , 0 1 , is a second smoothing coefficient and often set to 0.2.
You should be able to see we are just calculating the forecast error then
smoothing it so that we carry a recent history of the error information. The
length of this recent history is set by the value of . We then set the new
smoothing value, , to be the ratio of the absolute value of the smoothed
forecast error to the smoothed absolute value of the forecast error. This ratio
captures information about the accuracy of the forecast which in turn allows the
adaptive exponential smoothing forecast to track the recent demand data (if the
value is close to 1) or track the longer term patterns in demand (if the value is
close to 0). Think about this reasoning both by looking at the formula but also
as you think through the set-up of your spreadsheet.
i. Now build a simple spreadsheet model to obtain adaptive exponential
smoothed forecasts for the shipment unit data in time period 12. To set-
up the model make the following assumptions to initialise values.
Initialisation Set Value Description
2
F
1
Y Forecast at period 2 is observed demand at period 1
1
A 0 Initial smoothed forecast error is set to zero
1
M 0 Initial smoothed absolute forecast error is set to zero
0.2 Smoothing coefficient for forecast errors must be set
2 3 4
, , 0.2 Initial smoothing coefficient for demand forecast set
until sufficient data to start learning from past demand
ii. How does the adaptive exponential smoothing forecast compare with the
one obtained using simple exponential smoothing based upon the set of
forecast error performance measures? Which forecasting method do you
think is better? Why?
4
Exercise 2
The data set below, given in the Excel file “Trendy Sales Data Set” relates to sales units
for a very trendy fashion product.
Time Period Sales Units Time Period Sales Units
1 143 13 206
2 152 14 193
3 161 15 207
4 139 16 218
5 137 17 229
6 174 18 225
7 142 19 204
8 141 20 227
9 162 21 223
10 180 22 242
11 164 23 239
12 171 24 266
(a) Plot the trendy sales data and comment upon the patterns in the time series.
(b) Do you think it would be appropriate to forecast the trendy sales using a simple
method such as moving averages or exponential smoothing? Explain your
reasons. Try applying one of these methods and see if the computed forecasts
are consistent with your intuition.
(c) It is also possible to extend exponential smoothing to obtain forecasts when
there is a linear trend observed in the data.
The extension of exponential smoothing that includes parameters to capture the
trend as well as the variation around the mean level of demand is called Holt’s
method.
Holt’s method extends our concepts of smoothing to the two parameters of a
linear model (i.e. a straight line), which are the intercept (or level as it is often
called in demand forecasting) and the slope (or the trend as it is often called in
demand forecasting).
The basic formula for Holt’s method has the form of the equation of a straight
line and is given by:
The values of the m represent how many time periods ahead we want to forecast
(e.g. for the next time period we set m = 1). The values of the trend parameter
t m t t
F L b m
5
(given by tb ) and the level parameter (given by tL ) at time period t are found
using exponential smoothing formulae given by
Again try to understand the reasoning behind this method both by looking at the
formula but also as you think through the set-up of your spreadsheet.
In summary we now have two smoothing coefficients, one associated with each
parameter. The trend at time period t is a weighted average of the trend at time
t-1 and the local slope given by the difference in the levels of demand at time
periods t and t-1. The slope at time period t is a weighted average of the
observed demand at time t and the forecast demand based on the level and local
increase in demand anticipated at time period t-1.
For the trendy sales data, set up one-step ahead forecasts using Holt’s method
using the following initialisation values. Note that given we have more
parameters within the model then we have additional values to initialise. Note
also that here we are keeping the smoothing values as constants and so they will
not be updated during the analysis as in Exercise 1.
Initialisation Set Value Description
1
F 143 Forecast at time period 1
1
L 143 Level at time period 1
1
b 9 Trend at time period 1
0.1 Smoothing coefficient for trend
0.5 Smoothing coefficient for level of demand
m 1 Length of steps ahead for forecast
(d) Note that Excel has built-in functions to fit trend lines. This is the trendline
function that can be used with scatter plots. Find out how to use this
functionality to fit an appropriate trend-line to the data and so forecast sales in
months 12, 24 and 25. Justify your choice of trend-line. Would using such a
trend line be useful in a demand forecasting context in comparison with Holt’s
method?
1 1
1 1
1
1
where 0 1 and 0 1
t t t t
t t t t
L Y L b
b L L b