Post on 12-Jan-2016
description
REGRESION
Meses (X) Cantidades (Y) XY X2
1 4,735 4,735 12 4,635 9,270 43 5,512 16,536 94 6,137 24,548 165 6,580 32,900 256 6,061 36,366 367 5,946 41,622 498 4,135 33,080 649 5,427 48,843 81
10 4,584 45,840 10011 4,984 54,824 12112 4,025 48,300 14413 4,555 59,215 16914 4,969 69,566 19615 5,039 75,585 22516 3,717 59,472 25617 4,250 72,250 28918 7,066 127,188 32419 7,066 134,254 36120 7,351 147,020 40021 3,824 80,304 44122 3,323 73,106 48423 4,250 97,750 52924 7,351 176,424 57625 6,113 152,825 62526 5,450 141,700 67627 5,852 158,004 72928 4,632 129,696 78429 5,253 152,337 84130 4,897 146,910 900
31 5,143 159,433 961
32 5,245 167,840 1024
33 4,878 160,974 1089
34 4,927 167,518 1156
35 4,977 174,195 1225
36 5,225 188,100 1296
666 188,114.00 3,468,530.00 16,206.00
PROMEDIO 5,225.39
PROMEDIO MOVIL SIMPLE
MESES CANTIDADES PRONOSTICO PRONOSTICO
1 4,735 2 4,635 3 5,512 4,685 4 6,137 4,961 5,074 5 6,580 5,428 5,825 6 6,061 6,076 6,359 7 5,946 6,259 6,321 8 4,135 6,196 6,004 9 5,427 5,381 5,041
10 4,584 5,169 4,781 11 4,984 4,715 5,006 12 4,025 4,998 4,784 13 4,555 4,531 4,505 14 4,969 4,521 4,290 15 5,039 4,516 4,762 16 3,717 4,854 5,004 17 4,250 4,575 4,378 18 7,066 4,335 3,984 19 7,066 5,011 5,658 20 7,351 6,127 7,066 21 3,824 7,161 7,209 22 3,323 6,080 5,588 23 4,250 4,833 3,574 24 7,351 3,799 3,787 25 6,113 4,975 5,801 26 5,450 5,905 6,732
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
27 5,852 6,305 5,782 28 4,632 5,805 5,651 29 5,253 5,311 5,242 30 4,897 5,246 4,943 31 5,143 4,927 5,075 32 5,245 5,098 5,020 33 4,878 5,095 5,194 34 4,927 5,089 5,062 35 4,977 5,017 4,903 36 5,225 4,927 4,952
666 188,114 173,227 173,348 promedio 5,225
PROMEDIO MOVIL PONDERADO
Ponderacion Meses Cantidades Pronostico
0.2 1 4,735 0.3 2 4,635 0.5 3 5,512
4 6,137 5,053 5 6,580 5,649 6 6,061 6,234 7 5,946 6,232 8 4,135 6,107 9 5,427 5,064
10 4,584 5,143 11 4,984 4,747 12 4,025 4,953 13 4,555 4,425 14 4,969 4,482 15 5,039 4,656 16 3,717 4,921 17 4,250 4,364 18 7,066 4,248 19 7,066 5,551 20 7,351 6,503 21 3,824 7,208 22 3,323 5,530 23 4,250 4,279 24 7,351 3,887 25 6,113 5,615 26 5,450 6,112 27 5,852 6,029
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
Pronostico 3 MDATOS HIST
28 4,632 5,784 29 5,253 5,162 30 4,897 5,187 31 5,143 4,951 32 5,245 5,091 33 4,878 5,145 34 4,927 5,041 35 4,977 4,976 36 5,225 4,942
Sumatoria 666 188,114 173,269
A= 2 / 36 + 1SUAVIZACION EXPONENCIAL
A= 2 / 37
A=0,05 MESES CANTIDADES F(1) = F(0) + α * (Do - Fo) PRONOST
0.05 1 4,735 4,635 2 4,635 4,640 4,640 3 5,512 4,640 4,640 4 6,137 4,683 4,683 5 6,580 4,756 4,756 6 6,061 4,847 4,847 7 5,946 4,908 4,908 8 4,135 4,960 4,960 9 5,427 4,919 4,919
10 4,584 4,944 4,944 11 4,984 4,926 4,926 12 4,025 4,929 4,929 13 4,555 4,884 4,884 14 4,969 4,867 4,867 15 5,039 4,872 4,872 16 3,717 4,881 4,881 17 4,250 4,823 4,823 18 7,066 4,794 4,794 19 7,066 4,907 4,907 20 7,351 5,015 5,015 21 3,824 5,132 5,132 22 3,323 5,067 5,067 23 4,250 4,980 4,980 24 7,351 4,943 4,943 25 6,113 5,064 5,064 26 5,450 5,116 5,116 27 5,852 5,133 5,133 28 4,632 5,169 5,169
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
DATOS HIST.PRONOST.
29 5,253 5,142 5,142 30 4,897 5,147 5,147 31 5,143 5,135 5,135 32 5,245 5,135 5,135 33 4,878 5,141 5,141 34 4,927 5,128 5,128 35 4,977 5,118 5,118 36 5,225 5,111 5,111
b = n Σxy - (Σx * Σy)
(n Σx²) - (Σx)²
REGRESION
Y2 Regresion
22,420,225 5,278 21,483,225 5,275 30,382,144 5,272 37,662,769 5,269 43,296,400 5,266 36,735,721 5,263 35,354,916 5,260 17,098,225 5,257 29,452,329 5,254 21,013,056 5,251 24,840,256 5,248 16,200,625 5,245 20,748,025 5,242 24,690,961 5,239 25,391,521 5,236 13,816,089 5,233 18,062,500 5,230 49,928,356 5,227 49,928,356 5,224 54,037,201 5,221 14,622,976 5,218 11,042,329 5,215 18,062,500 5,212 54,037,201 5,209 37,368,769 5,206 29,702,500 5,203 34,245,904 5,200 21,455,424 5,197 27,594,009 5,194 23,980,609 5,191 26,450,449 5,188 27,510,025 5,185 23,794,884 5,182 24,275,329 5,179 24,770,529 5,176 27,300,625 5,173
1,018,756,962.00 188,114.00
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
f(x) = − 2.98043758043758 x + 5280.52698412698R² = 0.000964299088451526
PRONOSTICODATOS HIST.Linear (DATOS HIST.)
-
indice P 2M P 3M
0.906
0.887
1.055 4,942
1.174 5,959 5,826
1.259 7,334 6,835
1.160 7,375 7,048
1.138 7,192 7,123
0.791 4,751 4,903
1.039 5,235 5,588
0.877 4,194 4,535
0.954 4,774 4,498
0.770 3,685 3,850
0.872 3,927 3,950
0.951 4,080 4,299
0.964 4,592 4,355
0.711 3,560 3,453
0.813 3,561 3,721
1.352 5,387 5,862
1.352 7,651 6,776
1.407 9,940 8,620
0.732 5,275 5,241
0.636 3,553 3,867
0.813 2,906 3,931
1.407 5,327 5,344
1.170 6,786 5,820
1.043 7,021 6,158
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
1.120 6,475 7,061
0.886 5,009 5,146
1.005 5,270 5,339
0.937 4,632 4,916
0.984 4,995 4,850
1.004 5,039 5,117
0.934 4,849 4,756
0.943 4,772 4,798
0.952 4,669 4,778
1.000 4,952 4,927
36
8 MAX
PROMEDIO MOVIL PONDERADO
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
Pronostico 3 MDATOS HIST
4032.454223
SUAVIZACION EXPONENCIAL
indice esta pronostico
0.91 4,200
0.89 4,116
1.05 4,894
1.17 5,500
1.26 5,989
1.16 5,622
1.14 5,585
0.79 3,925
1.04 5,108
0.88 4,337
0.95 4,698
0.77 3,797
0.87 4,257
0.95 4,628
0.96 4,699
0.71 3,472
0.81 3,922
1.35 6,483
1.35 6,636
1.41 7,056
0.73 3,756
0.64 3,222
0.81 4,050
1.41 6,954
1.17 5,924
1.04 5,336
1.12 5,748
0.89 4,582
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
DATOS HIST.PRONOST.
1.01 5,169
0.94 4,824
0.98 5,054
1.00 5,155
0.93 4,799
0.94 4,835
0.95 4,874
1.00 5,110
REGRESION
A =
B =
A= 5,281
B= - 3
Datos Historicos
4735 4555 6,113
4635 4969 5,450
5512 5039 5,852
6137 3717 4,632
6580 4250 5,253
6061 7066 4,897
5946 7066 5,143
4135 7351 5,245
(Σy * Σx² ) - (Σx * Σxy) EN LA CELDA Y51 SIGUEN LOS PRONOSTICOS
(n Σx²) - (Σx)²
n Σxy - (Σx * Σy)
(n Σx²) - (Σx)²
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
f(x) = − 2.98043758043758 x + 5280.52698412698R² = 0.000964299088451526
PRONOSTICODATOS HIST.Linear (DATOS HIST.)
5427 3824 4,878
4584 3323 4,927
4984 4250 4,977
4025 7351 5,225
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
- MINIMOS
PROMEDIO MOVIL PONDERADO PONDERACION
0.45
0.32
0.23
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
DATOS HIST.PRONOST. 3 M
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
Pronostico 3 MDATOS HIST
MINIMOS
MINIMOS DMA
SIST RASTREO
SUAVIZACION EXPONENCIAL
0 5 10 15 20 25 30 35 400
1000
2000
3000
4000
5000
6000
7000
8000
DATOS HIST.PRONOST.
EN LA CELDA Y51 SIGUEN LOS PRONOSTICOS
AQUI ESTAN GRAFICADOS
TANTO LOS DATOS HISTORICOS COMO LOS PRONOSTICOS
MAX - MINIMOS
MINIMOS
0 -
1011.1966963604 1013.11067629
Cantidades (Y) INDICE ESTAC. PRONOSTICO
4,735 0.91 4,782
4,635 0.89 4,679
5,512 1.05 5,561
6,137 1.17 6,188
6,580 1.26 6,631
6,061 1.16 6,104
5,946 1.14 5,985
4,135 0.79 4,160
5,427 1.04 5,456
4,584 0.88 4,606
4,984 0.95 5,005
4,025 0.77 4,040
4,555 0.87 4,569
4,969 0.95 4,982
5,039 0.96 5,049
3,717 0.71 3,722
4,250 0.81 4,254
7,066 1.35 7,068
7,066 1.35 7,064
7,351 1.41 7,345
3,824 0.73 3,819
3,323 0.64 3,316
4,250 0.81 4,239
7,351 1.41 7,328
6,113 1.17 6,090
5,450 1.04 5,427
5,852 1.12 5,824
4,632 0.89 4,607
5,253 1.01 5,222
4,897 0.94 4,865
5,143 0.98 5,106
5,245 1.00 5,205
4,878 0.93 4,838
4,927 0.94 4,883
4,977 0.95 4,930
5,225 1.00 5,173
SUMA 36 188,121
AQUI ESTAN GRAFICADOS
TANTO LOS DATOS HISTORICOS COMO LOS PRONOSTICOS
MES REGRESION PROMEDIO
37 #REF!
#REF!
38 5,167
39 5,164
40 5,161
41 5,158
42 5,155
4958 548
Meses (X) Cantidades (Y) xy x2 y2 regresion1 5,437 5,437 1 29560969 6026.677182 6,700 13,400 4 44890000 6022.479753 6,631 19,893 9 43970161 6018.282334 6,747 26,988 16 45522009 6014.08495 5,814 29,070 25 33802596 6009.887476 6,259 37,554 36 39175081 6005.690057 6,305 44,135 49 39753025 6001.492628 6,392 51,136 64 40857664 5997.29529 5,105 45,945 81 26061025 5993.09777
10 5,960 59,600 100 35521600 5988.9003411 6,521 71,731 121 42523441 5984.7029212 6,093 73,116 144 37124649 5980.5054913 5,906 76,778 169 34880836 5976.3080714 5,126 71,764 196 26275876 5972.1106415 5,320 79,800 225 28302400 5967.9132116 5,385 86,160 256 28998225 5963.7157917 6,582 111,894 289 43322724 5959.5183618 6,101 109,818 324 37222201 5955.3209419 5,241 99,579 361 27468081 5951.1235120 5,361 107,220 400 28740321 5946.9260821 5,558 116,718 441 30891364 5942.7286622 5,572 122,584 484 31047184 5938.5312323 6,112 140,576 529 37356544 5934.3338124 6,394 153,456 576 40883236 5930.1363825 5,827 145,675 625 33953929 5925.9389526 4,937 128,362 676 24373969 5921.7415327 5,028 135,756 729 25280784 5917.544128 6,043 169,204 784 36517849 5913.3466829 6,489 188,181 841 42107121 5909.1492530 6,649 199,470 900 44209201 5904.9518231 6,465 200,415 961 41796225 5900.754432 6,234 199,488 1024 38862756 5896.5569733 6,859 226,347 1089 47045881 5892.3595534 5,237 178,058 1156 27426169 5888.1621235 6,105 213,675 1225 37271025 5883.9646936 5,821 209,556 1296 33884041 5879.76727
sumatorias 666 214316 3948539 16206 1286880162 214316PROMEDIO 5,953
0 5 10 15 20 25 30 35 40 -
1,000
2,000
3,000
4,000
5,000
6,000
7,000
8,000
Cantidades (Y)pronostico
indice estac. pronostico0.91328692 5504.08545 A =1.12544094 6777.945261.11385058 6703.46723 B =1.13333582 6815.977850.97661397 5869.340081.05136341 6314.162751.05909032 6356.122711.07370425 6439.32134
0.8575188 5139.194031.00113851 5995.71874 a = 6030.87460317461.09537319 6555.483111.02347935 6120.92386 b = -4.1974259974260.99206779 5928.902720.86104631 5142.2638
0.8936337 5333.128360.90455216 5394.491981.10561974 6588.961141.02482316 6103.150810.88036358 5239.152370.90052073 5355.33020.93361205 5548.203080.93596372 5558.24977
1.0266709 6092.607811.0740402 6369.20488
0.97879766 5800.29520.82929879 4910.89310.84458463 4997.866811.01508054 6002.523111.08999795 6440.960551.11687415 6595.088041.08596652 6408.0217
1.047164 6174.662191.15214916 6788.877120.87969167 5179.767171.02549506 6033.97675 mes regresion0.97778981 5749.17649 37 -155.304762
36 214327.498 38 -159.50218839 -163.69961440 -167.8970441 -172.09446642 -176.291892
(Σy * Σx² ) - (Σx * Σxy)
(n Σx²) - (Σx)²
n Σxy - (Σx * Σy)
(n Σx²) - (Σx)²
0 5 10 15 20 25 30 35 40 -
1,000
2,000
3,000
4,000
5,000
6,000
7,000
8,000
Cantidades (Y)pronostico
0 5 10 15 20 25 30 35 40 -
1,000
2,000
3,000
4,000
5,000
6,000
7,000
8,000
Cantidades (Y)pronostico