[SOLVED] Excel Topic 7 | Lab Exercises: Holt-Wiinters Method for Trend and Seasonality

Topic 7 | Lab Exercises: Holt-Wiinters Method for Trend and Seasonality

1

MS924D Spreadsheet Modelling & Demand Forecasting

Lab Exercises:

Holt-Winters Method for Trend and Seasonality

The exercises on this sheet allow you to extend you suite of exponential smoothing

methods to include both trend and seasonal data patterns.First we shall set up a basic

static model where the parameters are estimated from past data to provide projected

forecasts.Second we shall show how an updating mechanism can be built in to the

model so that the parameter estimates can be revised as they learn from the data.

Details about the new modelling methods are described in the relevant parts of the

exercise questions below.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

The managing director of an umbrella factory wants to forecast demand for his

umbrellas to aid in planning production.He is aware of the seasonality of the demand

for his product, and so splits the year into 5 equal periods for planning purposes (the

factory is closed for 2 weeks in the summer).He has the following data on demand

over the past 3 years:

Season

Year

12345

Weeks 1-10 11-20 23-3233-42 43-52

winterspringsummer autumnwinter

2014

2015

2016

129 1773470130

152 2254082163

200 25248104186

(in hundreds)

2

(a) Plot the time series and comment on the pattern of umbrella sales.

(b) We want to set up a basic Holt-Winters forecast model to forecast sales for
2017 and 2018. Since our data set shows a systematic increasing linear trend

and also regular periodicity then we need a model that captures these patterns.

Our Holt-Winters model can be written as follows

Does the expression of the model make intuitive sense?You should be able to

see the linear trend model as used in Holts method that is then multiplied by a

parameter that represents the effect of season. This parameter effectively inflates

or deflates the underlying trend of the data to take account of the peaks and

troughs of demand associated with systematic seasonal patterns. For our data, l

= 5 because there are 5 periods (or seasons) in our data.Since we want to

forecast for the next two years, then m is set to 1 to 10 corresponding to the five

periods in each of 2017 and 2018.

Here we set up the basic model which means that we use the observed data for

2014-2016 to estimate all parameters our model associated with the level, trend

and seasonal effects.Then we use this model to project demand in future time

periods.

The following steps lead you through the set-up of this model in a spreadsheet.

The steps involve estimating the parameters of the modelling components in

turn.Then using the model components to project the trend forward and adjust

by the relevant seasonal index to obtain a forecast for a particular period.

Most calculations will involve adding columns to extend the workings across

the rows corresponding to the time horizon to be modelled (e.g. 2014 period 1

2018 period 5). However we shall also create records of the common parameters

estimated across all the parameters.So think about creating a reserved, say, at

the top of your spreadsheet or to the side that is kept for the parameter estimates

calculated across all the historical data (i.e. 2014-6) and will be referenced when

we project forecasts for 2017-8.

i. First we need to obtain the underlying trend of the data.We start to
characterise this by fitting an appropriate moving average to smooth

fluctuations in the data.Since we have 5 periods in our season then

we use an MA(5) to smooth the data. Since this moving average is used

where

step ahead forecast

level of underlying stationary series at time

trend at time

seasonal index at time

length of season in time periods

t m t t t m l

t m

t

t

t

F L mT I

F m

L t

T t

I t

l

3

to smooth the data, we record the computed value of the moving average

in the cell aligned with the centre of the observations being averaged.

That is, if we average over time periods 1 to 5 then the computed value

of the moving average will be recorded in a cell aligned with time period

3. On completing this step you should have a new column with the

MA(5) values aligned with the mid-point of the set of values from which

the average has been calculated.

NOTE that this is different from the approach we take when making a

forecast with moving averages because then we usually record the

forecast value in the cell corresponding to the time period to which the

forecast relates. For example, if we use a moving average of time

periods 1-5 to forecast the demand in time period 5 then the value is

recorded in a cell aligned with time period 6.This was the approach we

used earlier when we were forecasting directly with moving averages.

ii. Now we want to obtain the slope parameter. First create a new column
and record the calculated difference between consecutive moving

average values. Second compute the average of these differences and

record this value in cell that is clearly labelled in your reserved space for

parameters as we shall reference it in later calculations since the average

difference provides an initial estimate of the slope of the linear trend.

Does this operation make sense? We are effectively averaging out the

local slopes between consecutive times to get an estimate of the global

slope for all the past data.This global slope becomes the estimate of the

trend parameter.

iii. Now we want to set up a new column that contains the values of the
trend at each time period, past and future. We do this by setting the mid-

point of the historical trend value to be equal to the average of the

smoothed moving average values. To do this you should identify the row

aligned with the middle point of the past data set (i.e. period 3 in 2015)

and then record the average the smoothed MA(5) values in this cell.

Then the rest of the trend values can be extrapolated by or adding the

slope value (the value recorded in the cell with the trend parameter saved

at the end of step ii) for time periods before or after the mid-point,

respectively.

iv. Now we estimate the seasonal effects for all past observed data. In a new
column calculate the seasonal value as the ratio of the appropriate

observed demand (input sales data) relative to the trend values

(computed in step iii) for each time period in each year (from period 1

2014 to period 5 2016).By calculating the seasonal value as a ratio we

are assuming a multiplicative seasonal effect.

v. Now we obtain seasonal indices for each season by averaging over the
seasonal values for the equivalent season in each year in the

initialisation data set.You should obtain five seasonal indices, one for

each of season 1-5.Hence for period 1, you will be averaging over the

three seasonal ratios computed in step iv for 2015. 2015, 2016 to obtain

4

one period 1 seasonal index. Record and label these five seasonal indices

appropriately in your reserved space for parameter estimates.

vi. We have now obtained all the parameter estimates for the past data and
are ready to start making our forecasts.We start by project the trend for

each period in 2017 and 2018.That is, extend the column created in step

iii to record the trend for each of the 5 periods in each of these two years.

You can conduct the calculations in several ways. The easiest is to

simply add on the value of the trend parameter (created in step ii) to

every row for the time period 1 2017 through to period 5 in 2018 as an

extension of the column of past trends created in step iii.This is a

practical way of implementing the linear trend expression that is at the

heart of the Holt-Winters model.

vii. Now we need to adjust the projected trend to take account of seasonal
effects by multiplying by the appropriate seasonal index. That is, we

record our forecasts in a new column and compute them as the product

of the projected trend values from step vi times the relevant seasonal

index computed in step v. For example, when forecasting for period 2 in

2017 and 2018 the appropriate trend values will be multiplied by the

seasonal index computed for period 2.

viii. Plot your time series including both the observed data and the new
forecasts you have just created. Does the pattern in the forecast data

make sense?If you have completed your calculations correctly then the

patterns for the forecast should just be an extension of the patterns

observed for the relevant time periods in the past data.

ix. Now consider the situation where we have data for the actual demand for
umbrellas in 2017.The data are:

Season1 2 3 4 5

2017209 320 60 117 219

Calculate the forecast errors for 2017 and calculate the ME, MAD, MSE,

MPE and the MAPE. Keep a record of your forecast error summaries

clearly labelled as we shall use them later when we compare this basic

Holt-Winters forecast model with a more dynamic version.

(c) Now we shall show how to create a more dynamic version of Holt-Winters that
involves updating the parameter estimates as new data become available. We

shall assume that the model has been initialised using past data as in part (b) and

then consider how we might use the data for 2017 to update the parameter

estimates and make forecasts based on the most recent data. The data we shall

use for the updating is shown below.Please imagine that this data is becoming

available on a period by period basis as 2017 unfolds so that you start to

appreciate the time dynamics of the data generating process.

Season1 2 3 4 5

2017209 320 60 117 219

5

We now want to dynamically update the forecasts using new data as it

becomes available.That is, at the end of period 1 of 2017 a forecast is to be

made to period 2, and so on.

We have an equation to update each of the level, trend and seasonal parameters

in our Holt-Winters model, respectively given by

You should be able to observe that the updating formulae for each of the Holt-

Winters parameters for the stationary, trend and seasonal parameters are based

upon an exponential smoothing mechanism. All formula essentially take a

weighted average of the last value of the parameter of interest with the most

recent relevant numerical estimate of that parameter from the latest observed

demand data.You should be able to see that the structure of the three formulae

are the same and in the form of an exponential smoothing, although the

equations differ for each of the parameters because they relate to different

components of the data patterns. The notation used is as defined in part (b).

i. You might find it useful to try writing out these equations in words (e.g.
T = trend, I = seasonal index, Y = observed demand etc) if this helps you

better appreciate the reasoning underpinning the updating mechanism.

ii. Think about how you might organise your spreadsheet.For example, it
might make sense to create a new worksheet where include a copy of

your working for steps I v in part (b) since these correspond to the

initialisation of the model parameters from the past data.You can then

extend the number of rows to allow for the forecasting in 2017 and add

extra columns for additional workings associated with each of the

parameter updates required for this dynamic updating version.

iii. Assume the following values for your smoothing coefficients

0.6 .You might want to set up your spreadsheet so that

these values can be changed so that you can try out different values to

see the effect.As usual, values of the coefficients that are closer to 1

implies more responsiveness of the parameter estimates to recent data.

1 11
t

t t t

t l

Y
L L T

I

1 11t t t tT L L T

1tt t l
t

Y
I I

L

where , ,are smoothing coefficients taking values between 0 and 1.

6

iv. Set up your spreadsheet to calculate updated values of the stationary,
trend and seasonal indices for each period in 2017, and hence obtain

forecasts for periods 1-5 in 2017.

Note 1 for the stationary and trend parameters you will need to update

based on the value from the previous time period (e.g. period 1 in 2017

will be an update of period 5 in 2016).However for the seasonal indices

then the updates need to correspond to the appropriate season. (e.g.

period 3 in 2017 will be an update of period 3 in 2016).

Note 2 in our basic Holt-Winters method set up in part (b) we did not

need to explicitly make calculations using the stationary value (L).This

was because our operations in step vi in part (b) projected the trend for

2017 and 2018 from the level in period 5 in 2016 quite naturally by

adding on the trend parameter estimate to the last value. For our

dynamic version of Holt-Winters with updating we need to be more

explicit in referencing the cell that contains the last value of the level

which we shall update. Please use the value of the cell corresponding to

period 5 of 2016 calculated in step iii of part (b) as your value for L at t-

1, assuming t corresponds to period 1 in 2017.

v. Plot your forecasts and the past data.Do you forecasts make sense in
relation to the patterns of past demand?

vi. Since you have the data for 2017, compute the error between the
forecasts generated under the dynamic updating and the observed

demand.Also, obtain the usual set of forecast error summaries (e.g.

MAPE etc).

(d) Compare your forecasts for 2017 with obtained in parts (b) and (c) based on
your time plots and your set of forecast error calculations.Which do you think

is most useful forecasting method in an operational context and why?

(e) If you have set up your spreadsheet in a way that allows you to change the
specified values of the smoothing coefficients, then try out different

combinations of coefficient values. Think through what effect you might expect

this to have on your model and then examine the effects on the forecasts

generated and the errors statistics.What set of smoothing values would you

recommend and why?

7

Exercise 2

The table below, also given in the Excel file Air Passengers, shows the number of

inward passengers to the UK travelling by air on scheduled services, during the period

from the first quarter of 1995 to the third quarter of 2004.

We shall use this data set to show how we can generate de-seasonalised values under an

additive model so that we understand the underlying data patterns adjusted

appropriately for time of year effects.We will then extend the analysis to obtain

forecasts for 2014.

Less direction is given in each step since we are creating a variant of the process

followed in Exercise 1.The variants relate to, for example, the length of the season, the

form of the seasonal effect, and the interim purpose of modelling which is to de-

seasonalise as well as forecast.

Year Quarter Passengers Year Quarter Passengers

1995 I 93372000 I 13665

1995 II 133352000 II 18636

1995 III 165452000 III 22743

1995 IV 112872000 IV 16034

1996 I 102622001 I 14302

1996 II 135722001 II 17782

1996 III 165832001 III 22987

1996 IV 120752001 IV 14684

1997 I 110032002 I 14269

1997 II 149262002 II 18858

1997 III 181472002 III 23347

1997 IV 130662002 IV 16702

1998 I 119292003 I 15150

1998 II 163232003 II 19430

1998 III 199492003 III 24521

1998 IV 142512003 IV 17818

1999 I 130832004 I 16447

1999 II 172492004 II 21602

1999 III 211372004 III 26213

1999 IV 15110

Source: Monthly Digest of Statistics.

8

(a)Plot the time series and comment upon the patterns displayed in the number of

air passengers.

(b) Model the trend in the data using an appropriate moving average. We now have
an even number of seasons per year and so a form of centred moving averages

are appropriate. Centred moving averages means calculating the moving

averages twice so that we centre the averaged value in the middle of the data

being averaged.

(c) Plot the smoothed data set created in part (b) on the original observations and
comment on how well the smoothed data tracks the trend.

(c) Compute the seasonal values for each quarter by computing the difference (or

deviation) between the observed and the trend values, and averaging for each

season to obtain the four seasonal indices. Differencing corresponds to an

additive time series model (unlike a multiplicative time series model where the

seasonal components are calculated as the ratio of observed to trend as in

Exercise 1).In general, an additive model is more appropriate when the

amplitude of seasonal cycles are the same, while a multiplicative model is more

appropriate the amplitude of seasonal cycles may vary.

(d) Compute the seasonally adjusted time series values by subtracting the
appropriate seasonal indices from the actual observations for each time point.

We shall call these new values the de-seasonalised series.

(e) Plot the de-seasonalised series and note the patterns you observe?Does it match
what you expect after you have removed the seasonal effects?

(e) Using a basic additive Holt-Winters make forecasts for the last quarter of 2004

by using an appropriate selection of the past data. Justify your choice of data to

initialise your forecasting model.You will need to compute the trend in a

similar manner to Exercise 1 and then project this trend and adjust by adding the

value of the appropriate seasonal index.