SIXTH SESSION : "Forecasting Methods"

Sixth session : Forecasting Methods 1
SIXTH SESSION :
"Forecasting Methods"
Objectif : - Dominer les techniques de prévisions de la demande basées sur les séries
chronologiques;
- Visualisation de différents modèles de demande et interprétation graphique
de leurs paramètres sur feuille Excel (en salle informatique);
- Comprendre le fonctionnement de différentes méthodes visant à évaluer les
paramètres de ces modèles;
Contenu : - Enoncé de l’exercice à réaliser en salle informatique(pages 2 à 7);
- Solution de l’exercice à réaliser en salle informatique (pages 8 à 14)
- Enoncés des exercices supplémentaires (pages 15 à 21);
- Solutions (pages 22 à 34);
Mise à part la question 2, Les exercices supplémentaires se réfèrent à des
matières qui ne sont pas au programme de l’années 2000-2001. Ne pas faire les
exercices supplémentaires 1, 3, 4 et 5.
Monitorat : - Séance autodidacte;
- Exercice à réaliser impérativement par deux en salle informatique;
- Plage horaire et locaux (voir annonce aux valves);
- Démarrage : Double-cliquez sur « p:\prod2100\prev.xls ».
En tapant un chiffre de 1 à 6 dans la case A2, vous réaliserez les différents
exercices sur ordinateur.
Site WEB : - Il est possible de se procurer le fichier prev.xls sur la page WEB
suivante :
http://www.prod.ucl.ac.be/enseignement/notes/prod2100.html

Sixth session : Forecasting Methods 2
COMPUTER LABORATORY
PROBLEM DESCRIPTION
This software introduces the main concepts of forecasting methods based on time series
analysis. The different exercises have always the same structure :
PART I : OBSERVATION & MODEL
You proceed here in 3 steps :
1) You can observe a set of points corresponding to the past demand.
2) You choose the best model corresponding to the past demand and maybe on the basis of
other informations (for example, it is natural to observe some seasonality for that market).
3) You estimate the different parameters for the chosen model (you have to estimate the
trends, the seasonal coefficients,…..).
PART II : EVALUATION OF THE MODEL
This second part allows to evaluate the quality of the model. Here, Tracking Signal (TSE)
and Mean Absolute Deviation(MAD) are used.
PART III : FORECASTING
The model you selected and assessed in parts I and II can be extrapolated in the future. From
these decisions, you can realize the forecasts for the future. The closer to the past demand
your model is, the more reliable your forecast is.
By building a confidence interval, you can assess the reliability of your forecast.

Sixth session : Forecasting Methods 3
Now, you can open the file ‘Prev.xls’. You obtain then the following presentation :
Exercise= 1
Observe
Model
Parameters
Period Nb
Demand
Forecast
the
Winter 93
150 40 a0= 40 0
Spring
252 40 b= 0 2
Summer
346 40 A/M= a -4
Autumn
453 40 T= 1 3
Winter 94
552 40 c0 0 2
Spring
651 40 c1 1 1
Summer
747 40 c2 1 -3
Autumn
853 40 c3 1 3
Winter 95
945 40 c4 1 -5
Spring
10 51 40 c5 1 1
Summer
11 52 40 c6 1 2
Autumn
12 49 40 c7 1 -1
Winter 96
13 50 40 c8 1 0
Spring
14 51 40 c9 1 1
Summer
15 49 40 Test 0.00 -1
Autumn
16 51 40 OK OK Lower boundUpper bound 151
Winter 97
17 40 SE= 162 40 40 2 40
Spring
18 40 MAD= 10.1 40 40 40
Summer
19 40 TSE= 16 40 40 40
Autumn
20 40 40 40 40
Winter 98
21 40 C.I. 0 0.5 40 40 40
Spring
22 40 Indic1 0% 0.00 40 40 40
Summer
23 40
L.L.(17)
40 Somme partielle des ci40 40 40
Autumn
24 40
Avg(17)
40 040 40 40
Winter 99
25 40
U.L.(17)
40 140 40 40
Spring
26
40
Indic2
32%
2
40
40
40
0
10
20
30
40
50
60
0 2 4 6 8 10 12 14 16 18 20 22 24 26
Past Data Model on Past Data Forecast
Lower limit Upper limit
.
Remark : you can only change the elements in the green cells!!!
Exercise 1 : Enter ‘1’ in the cell ‘ number of the exercise’(first line and second column)
Demand calculated
with the model and
the parameters
Past demand
for the chosen
exercise(1 to 6)
Model parameters that
you have to specify
in function of the past demand
Number of
the exercise
You enter here the probability
(between 0 and 99 (%))
used for building
a confidence interval
Chart with past data, model
on the past data and forecast.
It’s possible to build
a confidence interval
2 OK are necessary!!!
Otherwise your model is not
coherent

Sixth session : Forecasting Methods 4
In the red part, you can observe the past demand for the first product.
PART I : OBSERVATION & MODEL
Answer now the following questions and complete your Excel spreadsheet :
1. Which model can you use to represent this past demand (the chart can help you)? Give
the mathematical formulation of this model.
2. Estimate the different parameters of the model :
a0 = Y-value at the origin (F3 cell)
b = trend of your model(if there is no trend, b = 0) (F4 cell)
3. Is your model seasonal? (If your model is not seasonal, set T = 1 and choose a
multiplicative model with c0 = 1 or an additive model with c0 = 0)
4. If your model is seasonal, you can choose between two types of models : multiplicative
or additive. If you choose a multiplicative model, enter m in the F5 cell and if you
choose an additive model, enter a in the same cell.
5. If your model is seasonal, how many periods (T) does he cover? Enter the number of
periods in the F6 cell.
6. If your model is seasonal, give an estimation of the seasonal coefficients for each period.
Enter the value for the first coefficient (c0) in the F7 cell. Do the same for the following
coefficients.
Always verify that you have an OK in the yellow part’s second column. If you have an
KO, it means that the average of your seasonal coefficients is not equal to 1 for a
multiplicative model or is not equal to 0 for an additive model. This average appears in
the F16 cell.
PART II : EVALUATION OF THE MODEL
Whenever you choose a model, you have to test its accuracy. Different methods exist to
determine the reliability of your model.
1. On the chart :
In turquoise you can observe the model on the past data. So, you can compare your
model with the actual data and verify the robustness of your model.
2. Mean Absolute Deviation(MAD) measures the accuracy of the model.
In the sixth column in turquoise (cell F20), you can see the calculated MAD
corresponding to your model. That gives an idea of the distance between your model
and the past data.
MAD =
ei
n
i
n
= 1

Sixth session : Forecasting Methods 5
3. Tracking Signal (TSE) allows to check whether your model is biaised or not. If your
model is biased, then you under (or over-) estimate the actual demand repeatedly.
TSE =
e
M A D
i
i
n
= 1 -> cf. F21 cell
If there is no bias, the TSE should be small. We could decide the limits it should not exceed.
4. In the F19 cell, you can observe the sum of the errors (SE) = ei
i
n
= 1
Theoretically, if there is no bias, this sum should remain close to 0.
Check now the quality of your model.
PART III : FORECASTING
1. Forecastings can be done from the model you have built up in part 1 and validated in part
II. Observe these forecasts and retrieve these values from the mathematical formula
of your model.
2. For your forecast, you can build a confidence interval in which the actual demand
should fall :
Prob (lower limit LC < demand < upper limit UC) = α%
=> LC = forecast - z * σ = forecast - z * 1.25 * MAD
UC = forecast + z * σ = forecast + z * 1.25 * MAD
On your worksheet, you can determine this probability in the F23 cell.
N.B. : The standard deviation can be approximated by 1,25 * MAD.
For instance, set 95 in this cell and choose a wrong model (e.g. : for the exercise 1, you set
a0= 10, b0= 0, T = 1, c0 = 0 and your model is additive (a)). You can observe the
confidence interval in pink for the lower limit and in black for the upper limit on your
chart and on the grey part for the seventeenth period (LL17 and UL17). This interval is
very large.
Now enter the parameters of the best model for this exercise (cf. solutions). With a
probability of 95, you observe a smaller interval. This model is more accurate.
Now instead of 95, set 50 in the F23 cell. You can observe a smaller interval. It’s logical
because the chosen probability (50%) that the actual demand should belong to this
interval is smaller.
Thus, the breadth of the confidence interval can be influenced by two factors :
• the MAD, depending on the quality of your model;
• the confidence degree you have chosen for building your interval.