UNIVERSIDAD DEL MAGDALENA

PROGRAMA DE INGENIERÍA INDUSTRIAL 2023-1

PRONÓSTICOS DE ENFOQUE CAUSAL
REGRESION LINEAL MULTIVARIADA

Dados los siguientes datos del precio de la acción de Ecopetrol en la BVC y de las variables
independientes COLCAP y precios del petróleo, diseñe:
(1) un modelo de regresión UNIVARIADO entre el precio de Ecopetrol (y) y COLCAP (x 1)
(2)
Un modelo de regresión MULTIVARIADO para estimar el precio de la acción de Ecopetrol en
función de las variables independientes. Estime los parámetros del modelo multivariado y
decida que variables son significativas Consulte los datos actuales de las variables dadas y
realice pronósticos para una serie de fechas actuales. Compare los resultados del pronóstico
con los datos reales y calcule el error observado.
DATOS DEL TALLER
y x1 x2 x3 x4
precio
Obs # Fecha y = precio EC x = COLCAP precio WTI BRENT precio MME
1 20080122 1,740.00 879.67 $89.2 $88.3 $75.4
2 20080123 1,700.00 890.89 $87.0 $86.7 $73.9
3 20080124 1,800.00 933.79 $89.4 $89.0 $75.9
4 20080125 1,790.00 939.61 $90.7 $90.9 $77.5
5 20080128 1,735.00 930.97 $91.0 $91.4 $78.2
6 20080129 1,760.00 946.51 $91.6 $92.0 $78.9
7 20080130 1,750.00 936.55 $92.3 $92.6 $79.2
8 20080131 1,765.00 937.33 $91.8 $92.2 $78.2
9 20080201 1,760.00 938.16 $89.0 $89.5 $75.5
10 20080204 1,805.00 945.18 $90.0 $90.4 $76.3
11 20080205 1,760.00 920.47 $88.4 $88.8 $74.7
12 20080206 1,720.00 900.89 $87.1 $87.7 $73.8
13 20080207 1,750.00 891.30 $88.1 $88.5 $74.1
14 20080208 1,770.00 906.03 $91.8 $92.0 $77.4
15 20080211 1,820.00 920.78 $93.6 $93.5 $79.4
16 20080212 1,815.00 915.74 $92.8 $92.8 $78.7
17 20080213 1,810.00 918.85 $93.3 $93.3 $79.2
18 20080214 1,880.00 928.67 $95.5 $95.1 $81.5
19 20080215 1,900.00 929.63 $95.5 $94.7 $81.0
20 20080219 1,950.00 945.40 $100.0 $98.3 $83.5
21 20080220 1,950.00 941.38 $100.7 $98.3 $83.5
22 20080221 1,955.00 944.38 $98.2 $96.2 $82.0
23 20080222 1,945.00 940.05 $98.8 $97.0 $82.3
24 20080225 1,950.00 938.49 $99.2 $97.6 $82.4
25 20080226 1,960.00 938.58 $100.9 $88.5 $84.1
26 20080227 1,970.00 935.15 $99.6 $98.3 $83.5
27 20080228 1,985.00 931.03 $102.6 $100.9 $86.1
28 20080229 1,975.00 914.90 $101.8 $100.1 $85.7
29 20080303 2,010.00 896.73 $102.5 $100.5 $87.2
30 20080304 2,000.00 859.79 $99.5 $97.7 $84.8
31 20080305 1,910.00 897.61 $104.5 $101.6 $88.3
32 20080306 1,955.00 889.83 $105.5 $102.6 $89.0
33 20080307 1,950.00 900.79 $105.2 $102.4 $88.9
34 20080310 1,985.00 907.83 $107.9 $104.2 $90.1
35 20080311 1,990.00 911.72 $108.8 $105.3 $90.8
36 20080312 2,010.00 914.89 $109.9 $106.3 $91.5
37 20080313 2,025.00 912.74 $110.3 $107.5 $92.1
38 20080314 2,055.00 902.13 $110.2 $107.5 $91.8
39 20080317 2,095.00 890.57 $105.7 $101.8 $87.6
40 20080318 2,060.00 899.67 $109.4 $105.6 $90.5
41 20080319 2,080.00 902.56 $104.5 $100.7 $86.7
42 20080325 2,065.00 895.08 $101.2 $100.6 $85.7
43 20080326 2,045.00 891.73 $105.9 $104.0 $89.1
44 20080327 2,050.00 887.89 $107.6 $105.0 $90.0
45 20080328 2,080.00 890.03 $105.5 $103.8 $88.5
46 20080331 2,090.00 889.28 $101.6 $100.3 $86.1
47 20080401 2,090.00 913.73 $101.0 $100.2 $84.8
48 20080402 2,095.00 919.30 $104.8 $103.8 $87.2
49 20080403 2,100.00 923.04 $103.8 $102.5 $87.0
50 20080404 2,150.00 922.00 $106.2 $104.9 $88.4
51 20080407 2,210.00 928.32 $109.1 $107.0 $91.1
52 20080408 2,215.00 930.57 $108.5 $106.5 $90.5
53 20080409 2,205.00 936.53 $110.9 $108.4 $92.0
54 20080410 2,225.00 922.05 $110.1 $108.2 $91.7
55 20080411 2,220.00 917.73 $110.1 $108.8 $91.8
56 20080414 2,190.00 917.57 $111.8 $109.8 $92.9
57 20080415 2,195.00 931.61 $113.8 $111.2 $94.6
58 20080416 2,245.00 936.55 $114.9 $112.6 $94.9
59 20080417 2,275.00 934.66 $114.9 $112.3 $94.8
60 20080418 2,280.00 952.82 $116.7 $113.9 $95.9
61 20080421 2,330.00 953.25 $117.5 $114.4 $96.4
62 20080423 2,370.00 942.21 $118.3 $116.5 $98.1
63 20080424 2,420.00 949.52 $116.1 $114.3 $96.7
64 20080425 2,385.00 953.07 $118.5 $116.4 $98.6
65 20080428 2,375.00 962.40 $118.8 $116.4 $98.7
66 20080429 2,400.00 969.11 $115.7 $113.4 $96.0
67 20080430 2,455.00 985.95 $114.6 $112.6 $93.8
68 20080502 2,565.00 1,012.31 $116.4 $114.7 $96.6
69 20080506 2,600.00 1,032.08 $121.9 $120.3 $102.3
70 20080507 2,650.00 1,010.96 $123.6 $122.4 $103.1
 71 20080508 2,895.00 1,001.24 $123.9 $123.9 $103.3
72 20080509 2,730.00 1,008.49 $126.0 $125.5 $105.4
73 20080512 2,750.00 1,010.27 $124.2 $122.7 $103.5
74 20080513 2,835.00 1,015.63 $125.7 $124.2 $104.6
75 20080514 2,815.00 1,027.74 $124.3 $121.9 $103.4
76 20080515 2,800.00 1,024.58 $124.0 $122.7 $104.2
77 20080516 2,770.00 1,016.11 $126.7 $125.4 $106.2
78 20080519 2,730.00 1,016.10 $128.5 $127.9 $108.4
79 20080520 2,775.00 1,009.48 $133.6 $133.3 $112.1
80 20080521 2,770.00 1,015.60 $130.6 $130.6 $111.0
81 20080522 2,780.00 1,008.67 $131.9 $131.3 $111.6
82 20080523 2,885.00 1,003.07 $128.3 $128.1 $109.5
83 20080527 2,850.00 999.50 $130.7 $130.6 $110.8
84 20080528 2,845.00 1,000.23 $126.7 $127.1 $107.6
85 20080529 2,790.00 1,010.15 $127.4 $128.0 $107.5
86 20080530 2,810.00 1,019.78 $128.1 $127.6 $108.3
87 20080603 2,790.00 1,004.80 $124.5 $124.8 $105.7
88 20080604 2,810.00 1,015.92 $122.3 $122.1 $103.7
89 20080605 2,760.00 1,015.36 $127.9 $127.5 $108.1
90 20080606 2,730.00 1,013.73 $140.0 $138.1 $116.9
91 20080609 2,755.00 1,004.28 $134.3 $133.9 $115.3
92 20080610 2,845.00 990.50 $131.5 $131.0 $113.9
93 20080611 2,820.00 989.39 $136.5 $135.3 $117.7
94 20080612 2,770.00 990.29 $137.0 $136.1 $117.9
95 20080613 2,785.00 991.85 $134.9 $134.3 $116.7
96 20080616 2,775.00 991.66 $133.9 $134.0 $116.6
97 20080617 2,770.00 979.84 $133.8 $133.7 $115.6
98 20080618 2,760.00 972.13 $136.4 $136.4 $117.0
99 20080619 2,730.00 941.47 $131.9 $132.0 $114.2
100 20080620 2,680.00 959.27 $135.0 $134.9 $116.5
101 20080623 2,565.00 959.13 $137.0 $136.1 $117.5
102 20080624 2,610.00 956.22 $137.0 $136.6 $118.1
103 20080625 2,630.00 956.14 $134.6 $134.4 $116.4
104 20080626 2,635.00 942.99 $139.6 $139.8 $121.3
105 20080627 2,590.00 945.31 $140.6 $140.6 $122.4
106 20080701 2,555.00 924.70 $140.2 $139.8 $123.1
107 20080702 2,615.00 924.47 $141.5 $140.7 $126.6
108 20080703 2,565.00 938.44 $144.1 $144.3 $128.7
109 20080704 2,575.00 943.76 $145.3 $146.1 $131.2
110 20080707 2,690.00 936.94 $141.5 $141.9 $128.0
111 20080708 2,720.00 925.66 $136.0 $136.3 $123.4
112 20080709 2,695.00 932.35 $136.4 $136.1 $123.4
113 20080710 2,630.00 929.81 $141.2 $141.5 $127.9
114 20080711 2,655.00 917.54 $145.1 $144.5 $132.2
115 20080714 2,650.00 915.40 $145.2 $143.9 $132.7
116 20080715 2,605.00 901.61 $138.7 $138.8 $128.1
117 20080716 2,595.00 900.00 $134.6 $136.2 $125.4
118 20080717 2,530.00 909.83 $129.3 $131.1 $121.2
119 20080718 2,485.00 911.32 $128.9 $130.3 $120.0
120 20080721 2,460.00 919.19 $131.3 $133.0 $121.2
121 20080722 2,490.00 917.09 $127.3 $129.4 $118.0
122 20080723 2,510.00 922.51 $124.6 $125.2 $114.3
 Taller de Regresión Lineal asincrónico:
(1) Verificar el cálculo de los parámetros de la regresión lineal univariada mediante la f
(2) Calcular el R 2 del modelo de re
(2.1) El método gráfico (agregando una tendencia linea
(2.2) La función 'COEFICIENTE.R2(conocido_y; conocido_x)
(2.3) La relación R 2 = Varianza explicada/Varianza Total.
(2.3.1) Utilice la función VAR(matriz) para calcular la varianza de y (Varianza Total).
Utilice el modelo de regresión estimado para calcular los valores de Y(datos pronosticados por el modelo) para c
(2.3.3) Calcule la desviación (residuos de la regresión) de los datos:
(2.3.4) Calcule la varianza de los residuos = Ʃ(y-Y) 2 (Varianza no explicada)
(2.3.5) Calcule la Varianza Expicada = Varianza Total - Varianza No explicada
Calcule el R2 = Varianza Explicada/Varianza Total

precioECOP
COLCAP 2,138.00 -398.00
desviación
observaciones x (x-xp) x2 y y2 (y-yp) (y- yprom)2
1 879.67 -57.63 773814 1,740.00 3,027,600 -398 158,404
2 890.89 -46.40 793693 1,700.00 2,890,000 -438 191,844
3 933.79 -3.51 871956 1,800.00 3,240,000 -338 114,244
4 939.61 2.31 882858 1,790.00 3,204,100 -348 121,104
5 930.97 -6.33 866709 1,735.00 3,010,225 -403 162,409
6 946.51 9.21 895877 1,760.00 3,097,600 -378 142,884
7 936.55 -.75 877131 1,750.00 3,062,500 -388 150,544
8 937.33 .03 878582 1,765.00 3,115,225 -373 139,129
9 938.16 .86 880137 1,760.00 3,097,600 -378 142,884

10 945.18 7.88 893369 1,805.00 3,258,025 -333 110,889

11 920.47 -16.82 847273 1,760.00 3,097,600 -378 142,884

12 900.89 -36.41 811606 1,720.00 2,958,400 -418 174,724

13 891.30 -45.99 794422 1,750.00 3,062,500 -388 150,544
14 906.03 -31.27 820886 1,770.00 3,132,900 -368 135,424
15 920.78 -16.52 847833 1,820.00 3,312,400 -318 101,124
16 915.74 -21.56 838574 1,815.00 3,294,225 -323 104,329
17 918.85 -18.45 844283 1,810.00 3,276,100 -328 107,584
18 928.67 -8.63 862427 1,880.00 3,534,400 -258 66,564
19 929.63 -7.67 864217 1,900.00 3,610,000 -238 56,644
20 945.40 8.10 893779 1,950.00 3,802,500 -188 35,344
21 941.38 4.08 886198 1,950.00 3,802,500 -188 35,344
22 944.38 7.08 891857 1,955.00 3,822,025 -183 33,489
23 940.05 2.76 883701 1,945.00 3,783,025 -193 37,249
24 938.49 1.19 880755 1,950.00 3,802,500 -188 35,344
25 938.58 1.28 880926 1,960.00 3,841,600 -178 31,684
26 935.15 -2.15 874509 1,970.00 3,880,900 -168 28,224
27 931.03 -6.26 866825 1,985.00 3,940,225 -153 23,409
28 914.90 -22.40 837046 1,975.00 3,900,625 -163 26,569
29 896.73 -40.57 804125 2,010.00 4,040,100 -128 16,384
30 859.79 -77.51 739244 2,000.00 4,000,000 -138 19,044
31 897.61 -39.68 805710 1,910.00 3,648,100 -228 51,984
32 889.83 -47.47 791797 1,955.00 3,822,025 -183 33,489
33 900.79 -36.50 811428 1,950.00 3,802,500 -188 35,344
34 907.83 -29.47 824152 1,985.00 3,940,225 -153 23,409
35 911.72 -25.58 831231 1,990.00 3,960,100 -148 21,904
36 914.89 -22.40 837032 2,010.00 4,040,100 -128 16,384
37 912.74 -24.55 833103 2,025.00 4,100,625 -113 12,769
38 902.13 -35.17 813832 2,055.00 4,223,025 -83 6,889
39 890.57 -46.72 793122 2,095.00 4,389,025 -43 1,849
40 899.67 -37.62 809412 2,060.00 4,243,600 -78 6,084
41 902.56 -34.74 814612 2,080.00 4,326,400 -58 3,364
42 895.08 -42.22 801171 2,065.00 4,264,225 -73 5,329
43 891.73 -45.57 795179 2,045.00 4,182,025 -93 8,649
44 887.89 -49.41 788342 2,050.00 4,202,500 -88 7,744
45 890.03 -47.27 792145 2,080.00 4,326,400 -58 3,364
46 889.28 -48.01 790826 2,090.00 4,368,100 -48 2,304
47 913.73 -23.57 834907 2,090.00 4,368,100 -48 2,304
48 919.30 -18.00 845110 2,095.00 4,389,025 -43 1,849
49 923.04 -14.26 851998 2,100.00 4,410,000 -38 1,444
50 922.00 -15.30 850081 2,150.00 4,622,500 12 144
51 928.32 -8.97 861784 2,210.00 4,884,100 72 5,184
52 930.57 -6.73 865965 2,215.00 4,906,225 77 5,929
53 936.53 -.77 877080 2,205.00 4,862,025 67 4,489
54 922.05 -15.24 850183 2,225.00 4,950,625 87 7,569
55 917.73 -19.57 842227 2,220.00 4,928,400 82 6,724
56 917.57 -19.73 841936 2,190.00 4,796,100 52 2,704
57 931.61 -5.68 867904 2,195.00 4,818,025 57 3,249
58 936.55 -.75 877118 2,245.00 5,040,025 107 11,449
59 934.66 -2.64 873584 2,275.00 5,175,625 137 18,769
60 952.82 15.52 907866 2,280.00 5,198,400 142 20,164
61 953.25 15.95 908691 2,330.00 5,428,900 192 36,864
62 942.21 4.91 887766 2,370.00 5,616,900 232 53,824
63 949.52 12.22 901584 2,420.00 5,856,400 282 79,524
64 953.07 15.77 908347 2,385.00 5,688,225 247 61,009
65 962.40 25.10 926218 2,375.00 5,640,625 237 56,169
66 969.11 31.81 939167 2,400.00 5,760,000 262 68,644
67 985.95 48.65 972099 2,455.00 6,027,025 317 100,489
68 1,012.31 75.01 1024765 2,565.00 6,579,225 427 182,329
69 1,032.08 94.78 1065188 2,600.00 6,760,000 462 213,444
 70 1,010.96 73.67 1022047 2,650.00 7,022,500 512 262,144
71 1,001.24 63.94 1002480 2,895.00 8,381,025 757 573,049
72 1,008.49 71.19 1017048 2,730.00 7,452,900 592 350,464
73 1,010.27 72.97 1020649 2,750.00 7,562,500 612 374,544
74 1,015.63 78.34 1031512 2,835.00 8,037,225 697 485,809
75 1,027.74 90.45 1056257 2,815.00 7,924,225 677 458,329
76 1,024.58 87.28 1049759 2,800.00 7,840,000 662 438,244
77 1,016.11 78.81 1032477 2,770.00 7,672,900 632 399,424
78 1,016.10 78.80 1032451 2,730.00 7,452,900 592 350,464
79 1,009.48 72.19 1019056 2,775.00 7,700,625 637 405,769
80 1,015.60 78.31 1031451 2,770.00 7,672,900 632 399,424
-

promedio x $ 937 105,988

sumatoria x2 ### VAR TOTAL 8479030 8,479,030
promedio y 2,138
sumatoria xy
sumatoria (y-Y)
analítico
Covarianza XY 10,744 134
Dispersion estándar x 40.91
Dispersion estándar y 327.6
CORRELACION 0.80157076 = (CovarianzaXY)/(desvX)*(descY)

VARIANZA TOTAL OBSERVADA 8,479,030

VARIANZA NO EXPLICADA 3,031,120
VARIANZA EXPLICADA 5,447,910
R2 = 0.64251569

ɣ=

(CovarianzaXY)

(desvX)*(desvY)
 a = y- b

al univariada mediante la fórmula dada (utilizar esta plantilla para verificar las sumatorias)

 xy
ular el R 2 del modelo de regresión utilizando:
gando una tendencia lineal al diagrama de dispersión dado)

b=
_y; conocido_x)

x
tal). (2.3.2)
ados por el modelo) para cada x de los datos.
la regresión) de los datos: d = y - Y (dato real - dato pronosticado)
anza no explicada)
(2.3.6)

(x-xp)*(y-yp) xy Y (y - Y) (y - Y)2
22,937.12 1,530,621 1,768 -28 789
20,324.75 1,514,521 1,840 -140 19,643
1,187.09 1,680,815 2,115 -315 99,513
-802.83 1,681,893 2,153 -363 131,629 Cálculo analítico
2,549.33 1,615,237 2,097 -362 131,331 sumaXY
-3,481.27 1,665,854 2,197 -437 191,068 y promedio
289.20 1,638,967 2,133 -383 146,854 x promedio
-10.84 1,654,382 2,138 -373 139,268 suma x2
-324.32 1,651,155 2,144 -384 147,078 n

-2,625.37 1,706,054 2,189 -384 147,152

6,359.49 1,620,034 2,030 -270 72,907 b=

15,217.91 1,549,534 1,904 -184 33,974 a=

17,845.84 1,559,781 1,843 -93 8,608 R2 =
11,507.53 1,603,669 1,937 -167 27,985
5,253.29 1,675,817 2,032 -212 44,930 0.801570764
6,964.31 1,662,062 2,000 -185 34,080 0.64251569
6,051.45 1,663,116 2,020 -210 43,924 0.64251569
2,226.23 1,745,898 2,083 -203 41,053
1,824.36 1,766,302 2,089 -189 35,645
-1,522.89 1,843,527 2,190 -240 57,597
-767.53 1,835,692 2,164 -214 45,884
-1,296.35 1,846,267 2,183 -228 52,198
 -531.79 1,828,404 2,156 -211 44,388
-223.24 1,830,047 2,146 -196 38,268
-227.58 1,839,610 2,146 -186 34,673
360.62 1,842,249 2,124 -154 23,784
958.39 1,848,103 2,098 -113 12,722
3,650.56 1,806,932 1,994 -19 371
5,192.69 1,802,428 1,878 132 17,527
10,695.71 1,719,586 1,641 359 129,222
9,048.15 1,714,441 1,883 27 714
8,686.67 1,739,618 1,833 122 14,806
6,862.94 1,756,547 1,904 46 2,145
4,508.90 1,802,039 1,949 36 1,307
3,785.76 1,814,320 1,974 16 262
2,867.67 1,838,938 1,994 16 250
2,774.51 1,848,309 1,980 45 1,989
2,919.25 1,853,870 1,912 143 20,378
2,009.15 1,865,752 1,838 257 66,000
2,934.74 1,853,327 1,897 163 26,732
2,014.89 1,877,322 1,915 165 27,218
3,081.80 1,848,344 1,867 198 39,192
4,238.00 1,823,584 1,846 199 39,798
4,348.23 1,820,167 1,821 229 52,512
2,741.84 1,851,252 1,835 245 60,234
2,304.67 1,858,604 1,830 260 67,695
1,131.14 1,909,701 1,987 103 10,662
773.98 1,925,930 2,022 73 5,261
541.91 1,938,378 2,046 54 2,866
-183.59 1,982,297 2,040 110 12,144
-646.19 2,051,594 2,080 130 16,798
-517.86 2,061,218 2,095 120 14,440
-51.75 2,065,039 2,133 72 5,178
-1,326.28 2,051,569 2,040 185 34,169
-1,604.66 2,037,359 2,012 208 43,100
-1,025.82 2,009,480 2,011 179 31,906
-324.02 2,044,892 2,102 93 8,740
-80.50 2,102,545 2,133 112 12,506
-361.82 2,126,345 2,121 154 23,701
2,204.11 2,172,430 2,238 42 1,795
3,063.30 2,221,079 2,240 90 8,027
1,140.28 2,233,045 2,170 200 40,181
3,445.89 2,297,833 2,216 204 41,440
3,896.19 2,273,077 2,239 146 21,244
5,949.60 2,285,705 2,299 76 5,756
8,333.69 2,325,855 2,342 58 3,345
15,422.85 2,420,509 2,450 5 22
32,028.66 2,596,567 2,619 -54 2,965
43,788.87 2,683,406 2,746 -146 21,422 PARA n=122 datos
37,716.60 2,679,053 2,611 39 1,534
48,403.36 2,898,587 2,548 347 120,123
42,144.34 2,753,172 2,595 135 18,242 para n = 80 datos
44,659.73 2,778,247 2,606 144 20,625
54,599.91 2,879,322 2,641 194 37,712
61,231.64 2,893,098 2,719 96 9,306
57,778.95 2,868,817 2,698 102 10,361
49,808.44 2,814,622 2,644 126 15,913
46,648.17 2,773,941 2,644 86 7,436
45,981.86 2,801,316 2,601 174 30,162
49,489.20 2,813,223 2,641 129 16,741

105,988

848,769
161,164,243
3,031,120
con fórmula
10,744 varianza desviació 11386.94091
40.91 1673.8719 40.913 45.43790965
327.612 107329.494 327.612 262.4766656
0.8015707645

Varianza de y
Varianza de los residuos
a = y - bx

 xy - n(y)(x)
b= 2 2
x - n(x )

Cálculo analítico
161,164,243
$ 2,138 Diagrama de dispersión xy
937.3 3,500.00
70,414,460
80

3,000.00
Cálculo en excel
6.41860 6.4185998 f(x) = 6.41859981709984 x − 3878.14165322568
R² = 0.64251569045468
2,500.00
-$ 3,878 -3878.142
0.64251569045 0.6425157

0.801570764 2,000.00
-0.80157076
0.642515683
1,500.00

1,000.00

500.00
 1,000.00

500.00

.00
850.00 870.00 890.00 910.00 930.00 950.00 970.00 99

ARA n=122 datos b= 6.5018

 a= -3819.103

ra n = 80 datos b= 6.41860
a= -3878.14165
grama de dispersión xy

R2 = (ɣ)2
3878.14165322568

R2 = 0.6425
ɣ= 0.80156
0.64256256
930.00 950.00 970.00 990.00 1,010.00 1,030.00 1,050.00