Modelli pratici per Excel
1
2
Raffaele Scurelli
Modelli Excel per la Statistica
3
Prima edizione
Copyright©2014 Raffaele Scurelli Tutti i diritti sono riservati a norma di legge, è vietata la riproduzione, parziale o totale senza autorizzazione scritta da parte dell’autore. Qualsiasi riproduzione, parziale o totale, anche a uso interno o a scopo didattico, priva di autorizzazione scritta sarà perseguita ai sensi di legge.
Stampato in agosto 2014
4
Indice
Introduzione CAPITOLO 1 – DINAMICA ESOGENA MODELLI CAUSALI 1. 2.
3.
4. 5.
6. 7. 8.
Obiettivo n.1: costruzione di una tabella analisi dati Obiettivo n.2: costruzione degli indici Indice di covarianza Indice di correlazione Obiettivo n.3: calcolo dei coefficienti della funzione di correlazione Pendenza Intercetta Obiettivo n.4: costruzione di un grafico partendo da una tabella dati Obiettivo n.5: automazione del calcolo degli indici Indice di covarianza Indice di correlazione Pendenza Intercetta Obiettivo n.6:casistica di esempio Definizioni Modello foglio di lavoro
8 11 11 13 16 17 17 17 18 20 25 25 26 27 28 31 51 53
CAPITOLO 2 – DINAMICA ENDOGENA MODELLI ESTRAPOLATIVI
59
Obiettivo n.1: metodi di analisi Medie mobili Modello di regressione Analisi classica Obiettivo n.2: comparazione dei metodi di analisi Obiettivo n.3: casistica di esempio Modello foglio di lavoro
59 59 61 64 65 68 85
1.
2. 3. 4.
CAPITOLO 3 – COSTRUZIONE DI UN BUDGET TRAMITE L’UTILIZZO DELLA FUNZIONE INTERPOLATRICE 87 BIBLIOGRAFIA
105
5
6
7
Introduzione Modelli Excel© per la statistica 2 fa parte della serie Modelli pratici per Excel. Come nel primo volume si affrontano le tematiche pratiche delle applicazioni statistiche in ambito delle valutazioni economico produttive. Il testo è impostato in modo tale che la comprensione degli argomenti risulti la più immediata possibile, comprendendone il funzionamento attraverso l’ausilio del software di calcolo Excel. Gli argomenti presentati sono: la descrizione del modello esogeno, previsione del valore di una variabile y attraverso l’interpolazione con una variabile di riferimento x, nel primo capitolo; la descrizione del modello endogeno, previsione del valore di una variabile y attraverso l’interpolazione con la sua serie storica nel secondo capitolo; applicazione dei modelli descritti nella previsione di budget nel terzo capitolo.
8
9
10
1.DINAMICA ESOGENA MODELLI CAUSALI Il foglio di lavoro permette di visualizzare l’evolversi della variabile y in relazione con la variabile x individuata come la migliore attraverso una matrice di correlazione, dinamica esogena. Obiettivo n.1: costruzione di una tabella analisi dati Il prezzo di un prodotto y è direttamente collegato con la variazione di prezzo di altri prodotti x1 x2 x3. Si vuole individuare quale tra le variazioni di prezzo di questi prodotti è maggiormente correlata con il prodotto y. Riferimento 24 mesi.
n
24
1
2
µ
σ²
y
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146 96,7
x1
22
25
24
32
31
35
38
35
39
38
41
45
25
25
28
31
33
34
38
38
25
28
37
42
x2
115
125
126
141
158
168
181
175
198
187
201
254
135
124
147
158
164
175
185
189
174
201
224
x3
58
64
58
54
59
61
59
58
64
61
78
85
51
49
54
59
61
64
72
74
67
84
86
3 σ
700 26,5
32,9 40,3
6,3
256
173 1370
37
94
65,6
139 11,8
La tabella descrive i seguenti argomenti: 1. 2. 3.
Media Varianza Scarto quadratico medio
µ σ² σ
Il procedimento di automazione delle formule di calcolo prevede i seguenti passaggi: 1.Media CELLA Z2=MEDIA(B2:Y2)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
11
2.Varianza CELLA AA2 =VAR.P(B2:Y2)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
3.Scarto quadratico medio
CELLA AB2=RADQ(AA2)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
12
Obiettivo n.2: costruzione degli indici Ʃ
yi-µ i
-43
-32
-40
-23
-10
-2
9
-2
28
21
32
48
-32
-39
-18
-11
-10
-2
9
10
-1
22
30
49
x1i-µ i
-11
-8
-9
-1
-2
2
5
2
6
5
8
12
-8
-8
-5
-2
0
1
5
5
-8
-5
4
9
(yi-µ i)*(x1i-µ i)
464
250
352
20
18
-4
48
-4
173
109
262
586
250
305
86
-1
-2
53
6
-109
125
450
x2i-µ i
-58
-48
-47
-32
-15
-5
8
2
25
14
28
81
-38
-49
-26
-15
-9
2
12
16
1
28
51
83
(yi-µ i)*(x2i-µ i) 2493 1534 1881 735
149
9
71
-3
697
290
892 3894 1217 1911 467
165
91
-3
108
161
0
616 1534 4073
20
48
σxy
ρ
3505 146 0,87
x3i-µ i
-8
-2
-8
-12
-7
-5
-7
-8
-2
-5
12
19
-15
-17
-12
-7
-5
-2
6
8
1
18
20
(yi-µ i)*(x3i-µ i)
324
50
301
263
64
8
-61
13
-45
-98
401
938
462
642
205
70
44
3
60
87
-1
411
618 1401
28
22981
958 0,98
6160 257 0,82
Vengono trattati i seguenti argomenti: 1.
Calcolo degli scarti dalla media
2.
Prodotto degli scarti dalla media
3.
Indice di covarianza
4.
Indice di correlazione
yi-µi; xi-µi (yi-µi)*(xi-µi) σxy ρ
Il procedimento di automazione delle formule di calcolo prevede i seguenti passaggi: Conta numeri
y
54
65
57
74
87
95
106
95
n
24
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146
28
21
32
48
-32
-39
-18
-11
-10
-2
9
10
-1
22
30
49
CELLA Y1=CONTA.NUMERI(B2:Y2)
1.Calcolo degli scarti dalla media yi-µi
-43
-32
-40
-23
-10
-2
9
CELLA B6=B2-Z2
-2
CELLA C6=C2-Z2; . . . . CELLA Y6=Y2-Z2;
13
x1i-µ i
-11
-8
-9
-1
-2
2
5
2
6
5
8
12
-8
-8
-5
CELLA B7=B3-Z3
-2
0
1
5
5
-8
-5
4
9
12
16
1
28
51
83
6
8
CELLA C7=C3-Z3; . . . . CELLA Y7=Y3-Z3;
x2i-µ i
-58
-48
-47
-32
-15
-5
8
2
25
14
28
81
-38
-49
CELLA B9=B4-Z4
-26
-15
-9
2
CELLA C9=C4-Z4; . . . . . CELLA Y9=Y4-Z4;
x3i-µ i
-8
-2
-8
-12
-7
-5
CELLA B11=B5-Z5
-7
-8
-2
-5
12
19
-15
-17
-12
-7
-5
-2
CELLA C11=C5-Z5; . . . . . CELLA Y11=Y5-Z5;
14
1
18
20
28
2.Prodotto degli scarti dalla media (Yi-µi)*(X 1i-µi) 464
250
352
20
18
-4
48
-4
173
109
262
586
250
305
86
20
-1
-2
48
53
6
-109
125
450
CELLA B8=B6*B7 > TRASCINARE LA CASELLA PER COPIARE LA FORMULA
CELLA B10=B9*B6
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
CELLA B12=B11*B6 > TRASCINARE LA CASELLA PER COPIARE LA FORMULA
15
3.Indice di covarianza
CELLA Z7=SOMMA(B8:Y8)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
CELLA AA7=Z7/Y1
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
16
4.Indice di correlazione
CELLA AB7=AA7/(AB2*AB3)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
Il valore più elevato è X2 che presenta indice di covarianza e correlazione rispettivamente di [958;0.98]. Obiettivo n.3: calcolo dei coefficienti della funzione di correlazione Si calcolano i coefficienti della funzione di correlazione Y=aX+b. 1.Pendenza
σxy/σ²x
CELLA AA9 > COPIA > CELLA Z14 INCOLLA VALORI
17
CELLA AA4 > COPIA > CELLA AA14 INCOLLA VALORI
CELLA AB14=Z14/AA14
2.Intercetta
µy-a*µx
CELLA Z4 > COPIA > CELLA Z15 INCOLLA VALORI
CELLA AA15=AB14*Z4
CELLA AB15=Z15-AA15
18
Determiniamo i valori con l’ausilio della funzione interpolatrice y
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146
y*
56
63
64
74
86
93
102
98
114
106
116
153
70
62
78
86
90
98
105
108
97
116
132
154
CELLA C13=(AB14*C4)+AB15 CELLA D13=(AB14*D4)+AB15 CELLA E13=(AB14*E4)+AB15 CELLA F13=(AB14*F4)+AB15 CELLA G13=(AB14*G4)+AB15 CELLA H13=(AB14*H4)+AB15 CELLA I13=(AB14*I4)+AB15 CELLA L13=(AB14*L4)+AB15 CELLA M13=(AB14*M4)+AB15 CELLA N13=(AB14*N4)+AB15 CELLA O13=(AB14*O4)+AB15
CELLA B13=(AB14*B4)+AB15
…. CELLA Y13 =(AB14*Y4)+AB15
Il confronto con i valori di y e quelli ottenuti con la funzione di correlazione y* ci permette di definire con una certa percentuale di precisione quale sarà la variazione del prezzo del prodotto y al variare del prezzo del prodotto x2. Calcolando la media delle differenze tra i valori y ed i valori di y* si ottiene uno scostamento attorno al 4.19% y
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146
y*
56
63
64
74
86
93
102
98
114
106
116
153
70
62
78
86
90
98
105
108
97
116
132
154
7,5%
7,3% -0,9%
0,0%
3,6%
3,0%
0,6%
1,2% -2,5%
3,6% -3,2% 11,6%
0,1% -1,2% -2,2% -3,7%
3,0% -8,9% -10,0% -10,1% 5,5%
-1,1%
4,0%
###
5,8% 4,19%
19
Obiettivo n.4: costruzione di un grafico partendo da una tabella dati L’utilizzo dei grafici permette di visualizzare quanto esposto in modo immediato. SCHEDA INSERISCI > GRAFICO A DISPERSIONE > SELEZIONE DATI
TASTO DESTRO SUI PUNTI DEL GRAFICO > SELEZIONARE LINEA TENDENZA > VISUALIZZA EQUAZIONE
20
SCHEDA INSERISCI > GRAFICO A DISPERSIONE > SELEZIONE DATI
TASTO DESTRO SUI PUNTI DEL GRAFICO > SELEZIONARE LINEA TENDENZA > VISUALIZZA EQUAZIONE
21
SCHEDA INSERISCI > GRAFICO A DISPERSIONE > SELEZIONE DATI
TASTO DESTRO SUI PUNTI DEL GRAFICO > SELEZIONARE LINEA TENDENZA > VISUALIZZA EQUAZIONE
22
I grafici visualizzano in maniera immediata quale delle variazioni di prezzo dei tre prodotti x1 x2
x3 è correlata con la variazione di prezzo di y in misura maggiore; piÚ vicini sono i punti alla linea di tendenza maggiore è la correlazione. Visualizziamo i valori y* ottenuti con la funzione di correlazione, con i valori y.
SCHEDA INSERISCI > ISTOGRAMMA > SELEZIONE DATI
Il grafico dimostra la correlazione tra le variazioni di prezzo del prodotto x2 con la variazione di prezzo del prodotto y. Nella pagina seguente si riporta il foglio di lavoro completo.
23
24
Obiettivo n.5: automazione del calcolo degli indici Procediamo ad automatizzare il calcolo degli indici di covarianza e correlazione. Covarianza SCHEDA DATI > ANALISI DATI > COVARIANZA
INTERVALLO INPUT A2;Y5 > INTERVALLO OUTPUT B6 > SELEZIONARE RIGHE ETICHETTE
y
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
x1
22
25
24
32
31
35
38
35
39
38
41
45
25
25
28
31
33
34
38
38
25
28
37
42
x2
115
125
126
141
158
168
181
175
198
187
201
254
135
124
147
158
164
175
185
189
174
201
224
256
x3
58
64
58
54
59
61
59
58
64
61
78
85
51
49
54
59
61
64
72
74
67
84
86
94
y
x1
x2
x3
y
700
x1
146 40,3
x2
958 198 1370
x3
257 44,1 377 139
146
25
Correlazione
SCHEDA DATI > ANALISI DATI > CORRELAZIONE
INTERVALLO INPUT A2;Y5 > INTERVALLO OUTPUT G6 > SELEZIONARE RIGHE ETICHETTE
y
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
x1
22
25
24
32
31
35
38
35
39
38
41
45
25
25
28
31
33
34
38
38
25
28
37
42
x2
115
125
126
141
158
168
181
175
198
187
201
254
135
124
147
158
164
175
185
189
174
201
224
256
x3
58
64
58
54
59
61
59
58
64
61
78
85
51
49
54
59
61
64
72
74
67
84
86
94
y
x1
x2
x3
y
x1
x2
x3
y
700
y
x1
146 40,3
x1
0,87
1
x2
958 198 1370
x2
0,98 0,84
x3
257 44,1 377 139 x3
1 1
0,82 0,59 0,86
1
Otteniamo i dati precedentemente calcolati.
26
146
Procediamo ad automatizzare i coefficienti della funzione di correlazione. Pendenza
SCHEDA FORMULE > STATISTICHE > PENDENZA
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146
22
25
24
32
31
35
38
35
39
38
41
45
25
25
28
31
33
34
38
38
25
28
37
42
115
125
126
141
158
168
181
175
198
187
201
254
135
124
147
158
164
175
185
189
174
201
224
256
58
64
58
54
59
61
59
58
64
61
78
85
51
49
54
59
61
64
72
74
67
84
86
94
27
Intercetta SCHEDA FORMULE > STATISTICHE > INTERCETTA
54
65
57
74
87
95
106
95
125
118
129
145
65
58
79
86
87
95
106
107
96
119
127
146
22
25
24
32
31
35
38
35
39
38
41
45
25
25
28
31
33
34
38
38
25
28
37
42
115
125
126
141
158
168
181
175
198
187
201
254
135
124
147
158
164
175
185
189
174
201
224
256
58
64
58
54
59
61
59
58
64
61
78
85
51
49
54
59
61
64
72
74
67
84
86
94
28
Y
X1
X2
X3
Y
X1
X2
Y
700
Y
X1
146 40,3
X1
0,87
X2
958
X2
0,98 0,84
X3
257 44,1
198 1370 377
139 X3
X3
1 1
3,63
-23
1
0,70
-24
0,82 0,59 0,86
1 1,85
-24
Inseriamo l’equazione Y=aX+b
CELLA N8=(L8*N7)+M8; CELLA N9=(L9*N7)+M9; CELLA N10=(L10*N7)+M10
Inserendo i valori nella cella N7 otterremo il risultato interpolato. Visualizziamo il grafico Inserendo i dati di y e y* SCHEDA INSERISCI > ISTOGRAMMA > SELEZIONA DATI
29
Riportiamo il foglio di lavoro completato
30
Obiettivo n.6: casistica di esempio Esempio n.1 Si vuole individuare il miglior modello di correlazione tra la variazione di costo dei semilavorati
x1 x2 x3 e il costo medio del prodotto y.
31
Il valore più elevato è x2 che presenta indice di covarianza e correlazione rispettivamente di [0.11;0.98]; l’indice di covarianza maggiore sembrerebbe x3 ma è calcolato in maniera relativa, al denominatore abbiamo la somma degli scarti , va dunque reso confrontabile tramite frazione [0.11/2.73] [0.13/3.23] in questo modo avremo [0,04029;0,04024] che indica valore maggiore per x2. Procedere ad individuare una correlazione tra una fase di produzione e l’intero processo produttivo permette, sempre con la dovuta prudenza, di proiettare il costo della fase produttiva sull’intero processo di produzione velocizzandone i calcoli, poche tessere del mosaico permettono di visualizzare l’intera figura. Utilizzando la funzione interpolatrice avremo valori non molto dissimili da quelli reali; y
26,12
26,95
26,85
26,52
26,45
26,48
26,48
26,52
26,89
27,12
27,15
27,25
27,65
27,85
28,02
28,12
28,05
28,05
28,18
28,42
28,45
28,54
28,59
28,65
y*
26,44
26,94
26,88
26,55
26,49
26,49
26,49
26,49
26,88
27,10
27,16
27,27
27,66
27,77
27,88
27,99
27,88
27,88
28,05
28,27
28,32
28,60
28,88
28,99
Calcolando la media delle differenze tra i valori y ed i valori di y* si ottiene uno scostamento attorno allo 0.35% y
26,12
26,95
26,85
26,52
26,45
26,48
26,48
26,52
26,89
27,12
27,15
27,25
27,65
27,85
28,02
28,12
28,05
28,05
28,18
28,42
28,45
28,54
28,59
28,65
y*
26,44
26,94
26,88
26,55
26,49
26,49
26,49
26,49
26,88
27,10
27,16
27,27
27,66
27,77
27,88
27,99
27,88
27,88
28,05
28,27
28,32
28,60
28,88
28,99
1,2%
-0,1%
0,1%
0,1%
0,2%
0,0%
0,0%
-0,1%
0,0%
-0,1%
0,0%
0,1%
0,0% -0,3% -0,5% -0,5% -0,6% -0,6% -0,5% -0,5% -0,4%
0,2%
1,0%
1,2%
###
0,35%
Passiamo ad utilizzare il secondo modello automatizzato descritto nell’obiettivo n.5; eventuali errori presenti con l’utilizzo del modello precedente potranno facilmente risultare noti Covarianza
32
Correlazione
Funzione interpolatrice
Inserendo i valori di x2 x2
5,06 5,15 5,14 5,08 5,07 5,07 5,07 5,07 5,14 5,18 5,19 5,21 5,28 5,30 5,32 5,34 5,32 5,32 5,35 5,39 5,40 5,45 5,50 5,52
otterremo i valori di y* y*
26,44 26,94 26,88 26,55 26,49 26,49 26,49 26,49 26,88
27,10
27,16
27,27 27,66 27,77 27,88 27,99 27,88 27,88 28,05 28,27 28,32 28,60 28,88 28,99
Che possiamo confrontare con i valori di y
33
Riportiamo il foglio di lavoro completato
34
Esempio n.2 Individuare il notebook che meglio interpola il software applicativo.
Pendenza ed intercetta devono essere calcolati con i valori della variabile x3, le modalità sono state descritte nell’obiettivo n.3 di questo capitolo a cui si rimanda.
35
Il valore più elevato è x3 che presenta indice di covarianza e correlazione rispettivamente di [9637623; 0,98]. L’importanza di conoscere l’intensità dell’indice di correlazione permette di effettuare previsioni di vendita del proprio prodotto in relazione con il prodotto correlato nonché scelte di politiche distributive e di partnership commerciale con i produttori. Utilizzando la funzione interpolatrice possiamo visualizzare la tendenza di vendita, come evidenzia il grafico alcuni periodi saranno in linea con la funzione interpolatrice altri risulteranno dissimili anche se non completamente errati; y
5425 5532 5896 5963 6254 6894 6945 7125 7254 7456 7894 8214 8945 9124 9245 9254 9354 9358 9456 9542 9563 9654 9789 9795
y*
5836 5924 6012 6078 6232 6672 6738 7113 7399 7619 7905 8125 8500 8720 8940 8962 9006 9006 9314 9578 9600 9799 10085 10767
Calcolando la media delle differenze tra i valori y ed i valori di y* si ottiene uno scostamento attorno allo 2.95% con oscillazioni comprese tra [-4.98; 9.92]% y
5425 5532 5896 5963 6254 6894 6945 7125 7254 7456 7894 8214 8945 9124 9245 9254 9354 9358 9456 9542 9563 9654 9789 9795
y*
5836 5924 6012 6078 6232 6672 6738 7113 7399 7619 7905 8125 8500 8720 8940 8962 9006 9006 9314 9578 9600 9799 10085 10767 7,6%
7,1%
2,0%
1,9% -0,3% -3,2% -3,0% -0,2%
2,0%
2,2%
0,1%
-1,1% -5,0% -4,4% -3,3% -3,2% -3,7% -3,8% -1,5%
0,4%
0,4%
1,5%
3,0%
9,9%
###
2,95%
Passiamo ad utilizzare il secondo modello automatizzato descritto nell’obiettivo n.5; eventuali errori presenti con l’utilizzo del modello precedente potranno facilmente risultare noti
Covarianza
36
Correlazione
Funzione interpolatrice
Inserendo i valori di x3 x3
354
358
362
365
372
392
395
412
425
435
448
458
475
485
495
496
498
498
512
524
525
534
547
578
otterremo i valori di y* y*
5836 5924 6012 6078 6232 6672 6738 7113 7399 7619 7905 8125 8500 8720 8940 8962 9006 9006 9314 9578 9600 9799 10085 10767
Che possiamo confrontare con i valori di y
37
Riportiamo il foglio di lavoro completato
38
Esempio n.3 Individuare il prodotto con intensità di correlazione più elevata con l’intento di effettuare una singola campagna promozionale.
Pendenza ed intercetta devono essere calcolati con i valori della variabile x1, le modalità sono state descritte nell’obiettivo n.3 di questo capitolo a cui si rimanda.
39
Il valore piÚ elevato è x1 che presenta indice di covarianza e correlazione rispettivamente di [552;0.98]. La correlazione positiva con altri prodotti permette di individuare eventuali cause comuni di declino delle vendite, nonchÊ di effettuare politiche distributive, campagne promozionali e miglioramenti nei processi di produzione che vertono su entrambi i prodotti. Utilizzando la funzione interpolatrice possiamo visualizzare la tendenza di vendita; y
987
975
912
925
921
925
924
914
905
865
845
841
838
835
835
834
831
829
827
824
822
820
816
815
y*
967
953
901
939
934
939
934
901
896
873
859
854
844
844
844
840
835
830
826
821
816
812
807
797
Calcolando la media delle differenze tra i valori y ed i valori di y* si ottiene uno scostamento attorno allo 1.14%; y
987
975
912
925
921
925
924
914
905
865
845
841
838
835
835
834
831
829
827
824
822
820
816
815
y*
967
953
901
939
934
939
934
901
896
873
859
854
844
844
844
840
835
830
826
821
816
812
807
797
0,7%
0,5%
0,2% -0,2% -0,4% -0,7% -1,0%
-2,1% -2,3% -1,2%
1,5%
1,4%
1,5%
1,1% -1,4% -1,0%
0,9%
1,6%
1,5%
0,8%
1,1%
1,1%
-1,1% -2,1%
###
1,14%
Passiamo ad utilizzare il secondo modello automatizzato descritto nell’obiettivo n.5; eventuali errori presenti con l’utilizzo del modello precedente potranno facilmente risultare noti.
Covarianza
40
Correlazione
Funzione interpolatrice
Inserendo i valori di x1 x1
124
121
110
118
117
118
117
110
109
104
101
100
98
98
98
97
96
95
94
93
92
91
90
88
934
939
934
901
896
873
859
854
844
844
844
840
835
830
826
821
816
812
807
797
otterremo i valori di y* y*
967
953
901
939
Che possiamo confrontare con i valori di y
41
Riportiamo il foglio di lavoro completato
42
Esempio n.4 Individuare il prodotto concorrente che meglio interpola le vendite, correlazione negativa.
Pendenza ed intercetta devono essere calcolati con i valori della variabile x1, le modalità sono state descritte nell’obiettivo n.3 di questo capitolo a cui si rimanda.
43
Il valore piÚ elevato è x1 che presenta indice di covarianza e correlazione rispettivamente di [-5663;-0.96]. Un indice di correlazione negativa indica una relazione inversamente proporzionale, se y aumenta x1 diminuisce e viceversa. Utilizzando la funzione interpolatrice possiamo visualizzarne la tendenza; y
689
675
698
725
715
705
856
945
905
902
915
908
895
875
856
845
865
874
885
914
923
901
912
904
y*
722
719
715
710
694
687
838
855
905
916
936
922
911
899
874
851
859
878
888
905
911
900
899
896
Calcolando la media delle differenze tra i valori y ed i valori di y* si ottiene uno scostamento attorno allo 2.17% con oscillazioni comprese tra [-9.52;6.58]% y
689
675
698
725
715
705
856
945
905
902
915
908
895
875
856
845
865
874
885
914
923
901
912
904
y*
722
719
715
710
694
687
838
855
905
916
936
922
911
899
874
851
859
878
888
905
911
900
899
896
4,8%
6,6%
2,4%
-2,1% -2,9% -2,6%
2,7%
2,2%
0,8% -0,7%
0,5%
0,3%
-1,0%
-1,3%
-0,1%
-1,5% -0,9%
-2,1% -9,5%
0,0%
1,5%
2,3%
1,5%
1,8%
###
2,16%
Passiamo ad utilizzare il secondo modello automatizzato descritto nell’obiettivo n.5; eventuali errori presenti con l’utilizzo del modello precedente potranno facilmente risultare noti. Covarianza
Correlazione
44
Funzione interpolatrice
Inserendo i valori di x1 x1
1052 1054 1058 1062 1075 1081 956
942
901
892
875
887
896
906
926
945
939
923
915
901
896
905
906
908
855
905
916
936
922
911
899
874
851
859
878
888
905
911
900
899
896
otterremo i valori di y* y*
722
719
715
710
694
687
838
Che possiamo confrontare con i valori di y y
689
675
698
725
715
705
856
945
905
902
915
908
895
875
856
845
865
874
885
914
923
901
912
904
y*
722
719
715
710
694
687
838
855
905
916
936
922
911
899
874
851
859
878
888
905
911
900
899
896
45
Riportiamo il foglio di lavoro completato
46
L’utilizzo dello strumento analisi dati di Excel ci permette di visualizzare la correlazione esistente tra i prodotti x1 x2, x1 x3, x2 x3. Possiamo visualizzare una tabella dove si rappresentano le relazioni tra i prodotti e il mercato di riferimento.
94 2
94 5
80 5
45 6
90 1
90 5
79 5
47 8
89 2
90 2
79 6
49 5
87 5
91 5
82 3
48 6
88 7
90 8
82 6
49 7
89 6
89 5
87 4
41 2
90 6
87 5
88 1
45 3
92 6
85 6
88 5
42 1
94 5
84 5
86 9
45 1
93 9
86 5
87 1
44 2
92 3
87 4
87 6
46 5
91 5
88 5
87 1
47 1
90 1
91 4
86 3
46 3
89 6
92 3
87 4
46 8
90 5
90 1
86 8
46 2
90 6
91 2
n
90 1
46 1
90 8
90 4
24
84 0 16 4 1 41
45 5
95 0 46 7 7 68
84 5 74 9 6 87
σ
41 2
σ²
72 3
µ
42 1
85 6
80 2
70 5 41 2
71 5
81 4
72 5 45 1
69 8
79 5
10 5 2 10 5 4 10 5 8 10 6 2 10 7 5 10 8 1 95 6
68 9 45 6
67 5
y
81 5
24
44 8
59 5
x1 84 1
0,1% 0,0% 0,8%
46 5
0,5% -0,4%
84 5
1,0%
47 8
1,6% -0,6% 0,9% -0,4%
85 1
0,0% 0,5% 0,7% 0,3% -1,5%
x2 x3
1,5%
3070 3039 3045 3058 3036 3012 3035 3022 3067 3067 3081 3104 3114 3067 3116 3096 3124 3110 3141 3157 3145 3148 3148 3174 30 9 1 20 5 2 45 -1,0% 0,2% 0,4% -0,7% -0,8% 0,8% -0,4%
T
-1,0% 3,2% 3,4% -0,7% -0,6% 20,5% 10,9% -5,6% -0,3%
1,0% -1,5% -1,7% -0,7% -3,7% -0,6%
1,4%
1,5%
0,3% 2,8%
1,4% -2,5%
1,2% -1,7%
22,4% 22,2% 22,9% 23,7% 23,6% 23,4% 28,2% 31,3% 29,5% 29,4% 29,7% 29,3% 28,7% 28,5% 27,5% 27,3% 27,7% 28,1% 28,2% 29,0% 29,3% 28,6% 29,0% 28,5% 27,3% 0,07% 2,62%
3, 404% y
1,2% 0,2% 0,0% 2,0%
1,4% -12,2% -1,0% -5,8% -1,0% -2,4% 0,6% 0,7% 2,7% 0,6% 2,7% -1,5% -1,3% -1,8% -2,0% -0,2% 0,9%
0,1% -0,6%
34,3% 34,7% 34,7% 34,7% 35,4% 35,9% 31,5% 31,2% 29,4% 29,1% 28,4% 28,6% 28,8% 29,5% 29,7% 30,5% 30,1% 29,7% 29,1% 28,5% 28,5% 28,7% 28,8% 28,6% 30,8% 0,07% 2,55%
26 , 74 2 %
x1
1,4% -0,4% -7,9%
1,4% -1,7%
9,1% 4,8%
3,1% -2,5%
1,9% -15,8% 8,2% -6,5% 6,2% -1,6% 4,2% 0,8% -1,3%
0,3% -0,7% -3,5% -1,7% 3,2% -2,2% -9,5% 9,7% -1,2% -0,3% 2,6% 0,0% 7,4% -0,8%
1,1% -2,7% 0,7% -0,4%
-1,1% -0,5%
1,0% -1,3% -1,0%
1,2% -0,7% 3,0%
27,7% 27,8% 27,6% 26,7% 26,2% 27,0% 26,4% 23,9% 26,2% 25,9% 25,8% 26,5% 26,5% 28,5% 28,3% 28,6% 27,8% 28,0% 27,9% 27,6% 27,4% 27,8% 27,6% 28,4% 27,2% 0,01% 1,06%
-1,7% -3,8%
15,6% 15,3% 14,7% 14,9% 14,9% 13,7% 13,9% 13,6% 14,9% 15,6% 16,1% 15,7% 16,0% 13,4% 14,5% 13,6% 14,4% 14,2% 14,8% 14,9% 14,7% 14,9% 14,7% 14,5% 14,7% 0,01% 0,72%
-16 , 84 9 % -3, 6%
x2
3, 9%
x3
47
Procediamo con esplicare i passaggi necessari alla sua costruzione: 1.
Somma delle quote di mercato
n
24
y
689
856
945
905
902
915
908
895
875
856
845
865
874
885
914
923
901
912
904
845 7496
87
x1
1052 1054 1058 1062 1075 1081 956
942
901
892
875
887
896
906
926
945
939
923
915
901
896
905
906
908
950 4677
68
x2 x3
478
465
448
456
451
412
421
412
456
478
495
486
497
412
453
421
451
442
465
471
463
468
462
461
455
595
24
851
845
841
815
795
814
802
723
805
795
796
823
826
874
881
885
869
871
876
871
863
874
868
901
840 1641
41
T
3070 3039 3045 3058 3036 3012 3035 3022 3067 3067 3081 3104 3114 3067 3116 3096 3124 3110 3141 3157 3145 3148 3148 3174 3091 2052
45
675
698
725
715
705
µ
σ²
σ
CELLA B6=SOMMA(B2:B5)
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
T
3070 3039 3045 3058 3036 3012 3035 3022 3067 3067 3081 3104 3114 3067 3116 3096 3124 3110 3141 3157 3145 3148 3148 3174
2.
Quote di mercato dei prodotti
CELLA B8=B2/B6;CELLA C8=C2/C6……..CELLA Y8=Y2/Y6
y
689
675
698
725
715
705
856
945
905
902
915
908
895
875
856
845
865
874
885
914
923
901
912
904
915
901
896
905
906
908
CELLA B10=B3/B6;CELLA C10=C3/C6……..CELLA Y10=Y3/Y6
x1
48
1052 1054 1058 1062 1075 1081 956
942
901
892
875
887
896
906
926
945
939
923
CELLA B12=B4/B6;CELLA C12=C4/C6……..CELLA Y12=Y4/Y6
x2
478
465
448
456
451
412
421
412
456
478
495
486
497
412
453
421
451
442
465
471
463
468
462
461
876
871
863
874
868
901
0,1%
0,0%
0,8%
1,4% -2,5%
1,2%
-1,7%
CELLA B14=B5/B6;CELLA C14=C5/C6……..CELLA Y14=Y5/Y6
x3
851
3.
845
841
815
795
814
802
723
805
795
796
823
826
874
0,3%
-1,5%
881
885
869
871
Variazione percentuale quote mercato
CELLA C7=C6/B6-1 TRASCINARE LA CASELLA PER COPIARE LA FORMULA
-1,0%
0,2%
0,4% -0,7% -0,8%
0,8% -0,4%
1,5%
0,0%
0,5%
0,7%
1,6% -0,6%
0,9% -0,4%
1,0%
0,5% -0,4%
1,4%
1,5%
0,3%
2,8%
-1,5%
-1,3%
CELLA C9=C8/B8-1
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
-1,0%
3,2%
3,4% -0,7% -0,6% 20,5% 10,9% -5,6% -0,3%
1,0%
-1,5%
-1,7% -0,7% -3,7% -0,6%
CELLA C11=C10/B10-1
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
1,2%
0,2%
0,0%
2,0%
1,4% -12,2%
-1,0% -5,8%
-1,0% -2,4%
0,6%
0,7%
2,7%
0,6%
2,7%
-1,8% -2,0% -0,2%
0,9%
0,1% -0,6%
49
CELLA C13=C12/B12-1
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
-1,7% -3,8%
1,4% -0,4% -7,9%
1,4%
-1,7%
9,1%
4,8%
3,1% -2,5%
1,9% -15,8%
8,2% -6,5%
6,2%
-1,6%
4,2%
0,8%
-1,3%
1,0%
-1,3%
-1,0%
1,2% -0,7%
3,0%
CELLA C15=C14/B14-1
TRASCINARE LA CASELLA PER COPIARE LA FORMULA
0,3% -0,7% -3,5%
4.
-1,7%
3,2% -2,2% -9,5%
9,7%
-1,2% -0,3%
2,6%
0,0%
7,4% -0,8%
1,1% -2,7%
-1,1% -0,5%
Somma delle variazioni percentuali
CELLA A9=SOMMA(C9:Y9); CELLA A11=SOMMA(C11:Y11); CELLA A13=SOMMA(C13:Y13); CELLA A15=SOMMA(C15:Y15);
CELLA A7=SOMMA(C7:Y7)
3,404% y
0,7% -0,4%
-1,0%
0,2%
0,4% -0,7% -0,8%
0,8% -0,4%
1,5%
0,0%
0,5%
0,7%
0,3% -1,5%
1,6% -0,6%
0,9% -0,4%
1,0%
0,5% -0,4%
0,1%
26,742%
x1
0,8%
-1,0%
x2
3,2%
3,4% -0,7% -0,6% 20,5% 10,9% -5,6% -0,3%
1,0% -1,5% -1,7% -0,7% -3,7% -0,6%
1,4%
1,5%
0,3%
2,8%
1,4% -2,5%
1,2% -1,7%
34,3% 34,7% 34,7% 34,7% 35,4% 35,9% 31,5% 31,2% 29,4% 29,1% 28,4% 28,6% 28,8% 29,5% 29,7% 30,5% 30,1% 29,7% 29,1% 28,5% 28,5% 28,7% 28,8% 28,6%
-16,849%
1,2%
0,2%
0,0%
2,0%
1,4% -12,2% -1,0% -5,8% -1,0% -2,4%
0,6%
0,7%
2,7%
0,6%
2,7% -1,5% -1,3% -1,8% -2,0% -0,2%
0,9%
0,1% -0,6%
15,6% 15,3% 14,7% 14,9% 14,9% 13,7% 13,9% 13,6% 14,9% 15,6% 16,1% 15,7% 16,0% 13,4% 14,5% 13,6% 14,4% 14,2% 14,8% 14,9% 14,7% 14,9% 14,7% 14,5%
-3,6%
x3 3,9%
-1,7% -3,8%
1,4% -0,4% -7,9%
1,4% -1,7%
9,1%
4,8%
3,1% -2,5%
1,9% -15,8%
8,2% -6,5%
6,2% -1,6%
4,2%
0,8% -1,3%
1,0% -1,3% -1,0%
27,7% 27,8% 27,6% 26,7% 26,2% 27,0% 26,4% 23,9% 26,2% 25,9% 25,8% 26,5% 26,5% 28,5% 28,3% 28,6% 27,8% 28,0% 27,9% 27,6% 27,4% 27,8% 27,6% 28,4% 0,3% -0,7% -3,5% -1,7%
3,2% -2,2% -9,5%
9,7% -1,2% -0,3%
2,6%
0,0%
7,4% -0,8%
1,1% -2,7%
0,7% -0,4%
-1,1% -0,5%
1,2% -0,7%
Come indicato dall’indice di correlazione si visualizza il prodotto che acquisisce quote di mercato, il prodotto a cui vengono sottratte e l’intensità della relazione.
50
0,0%
22,4% 22,2% 22,9% 23,7% 23,6% 23,4% 28,2% 31,3% 29,5% 29,4% 29,7% 29,3% 28,7% 28,5% 27,5% 27,3% 27,7% 28,1% 28,2% 29,0% 29,3% 28,6% 29,0% 28,5%
3,0%
DEFINIZIONI
ni X Y µx
Numerosità seriazione Variabile Variabile Media X
µy
Media Y
xi-μx
Scarti dalla media variabile X
yi-μy
Scarti dalla media variabile Y
σxy
Indice di covarianza
σ²x
Varianza variabile X
σ²y
Varianza variabile Y
σx
ρ
Scarto quadratico medio variabile X Scarto quadratico medio variabile Y Indice di correlazione
a
Pendenza
b
Intercetta
σy
Sommatoria degli x valori osservati diviso n Sommatoria degli y valori osservati diviso n Indica omogeità o dispersione dei valori della seriazione, elemento di calcolo per l’indice di covarianza Indica omogeità o dispersione dei valori della seriazione, elemento di calcolo per l’indice di covarianza Indica l’eventuale convergenza o divergenza tra le variabili oggetto di analisi Media dei quadrati degli scarti dalla media Media dei quadrati degli scarti dalla media Risultato comparabile con i valori e gli indici di posizione Risultato comparabile con i valori e gli indici di posizione Indica il grado di correlazione tra le due variabili Indica la pendenza dell’equazione Y=aX+b
1/n Ʃi xi
Indica l’intercetta dell’equazione Y=aX+b
µy- a* µx
1/n Ʃi yi
1/n Ʃ (xi-μx) (yi-μy)
1/n Ʃ(xi-μx)² 1/n Ʃ(yi-μy)² [1/n Ʃ(xi-μx)²]½ [1/n Ʃ(yi-μy)²]½ σxy/ σx* σy σxy/ σ²x
51
52
MODELLO FOGLIO DI LAVORO N.1
53
54
MODELLO FOGLIO DI LAVORO N.2
55
56
MODELLO FOGLIO DI LAVORO N.3
57
58