of 45

• date post

12-Jan-2016
• Category

Documents

• view

217

0

Embed Size (px)

description

estrategias de planeacion

Transcript of EJERCICIO PRONOSTICO REGRESION

EJEMPLO

REGRESION

1011.19669636041013.1106762943Meses (X)Cantidades (Y)XYX2Y2RegresionA =(y * x ) - (x * xy)EN LA CELDA Y51 SIGUEN LOS PRONOSTICOSCantidades (Y)INDICE ESTAC.PRONOSTICO14,735 4,735 122,420,2255,278 (n x) - (x)4,735 0.914,78224,635 9,270 421,483,2255,275 B =n xy - (x * y)4,635 0.894,67935,512 16,536 930,382,1445,272 (n x) - (x)5,512 1.055,56146,137 24,548 1637,662,7695,269 AQUI ESTAN GRAFICADOS TANTO LOS DATOS HISTORICOS COMO LOS PRONOSTICOS6,137 1.176,18856,580 32,900 2543,296,4005,266 6,580 1.266,63166,061 36,366 3636,735,7215,263 6,061 1.166,10475,946 41,622 4935,354,9165,260 5,946 1.145,98584,135 33,080 6417,098,2255,257 4,135 0.794,16095,427 48,843 8129,452,3295,254 A=5,281 5,427 1.045,456104,584 45,840 10021,013,0565,251 4,584 0.884,606114,984 54,824 12124,840,2565,248 4,984 0.955,005124,025 48,300 14416,200,6255,245 4,025 0.774,040134,555 59,215 16920,748,0255,242 4,555 0.874,569144,969 69,566 19624,690,9615,239 4,969 0.954,982155,039 75,585 22525,391,5215,236 B=-3 5,039 0.965,049163,717 59,472 25613,816,0895,233 3,717 0.713,722174,250 72,250 28918,062,5005,230 4,250 0.814,254187,066 127,188 32449,928,3565,227 7,066 1.357,068197,066 134,254 36149,928,3565,224 7,066 1.357,064207,351 147,020 40054,037,2015,221 7,351 1.417,345213,824 80,304 44114,622,9765,218 3,824 0.733,819223,323 73,106 48411,042,3295,215 3,323 0.643,316234,250 97,750 52918,062,5005,212 4,250 0.814,239247,351 176,424 57654,037,2015,209 7,351 1.417,328256,113 152,825 62537,368,7695,206 6,113 1.176,090265,450 141,700 67629,702,5005,203 5,450 1.045,427275,852 158,004 72934,245,9045,200 5,852 1.125,824284,632 129,696 78421,455,4245,197 4,632 0.894,607295,253 152,337 84127,594,0095,194 5,253 1.015,222304,897 146,910 90023,980,6095,191 Datos Historicos4,897 0.944,865315,143 159,433 96126,450,4495,188 473545556,1135,143 0.985,106325,245 167,840 102427,510,0255,185 463549695,4505,245 1.005,205334,878 160,974 108923,794,8845,182 551250395,8524,878 0.934,838344,927 167,518 115624,275,3295,179 613737174,6324,927 0.944,883354,977 174,195 122524,770,5295,176 658042505,2534,977 0.954,930365,225 188,100 129627,300,6255,173 606170664,8975,225 1.005,173666188,114.003,468,530.0016,206.001,018,756,962.00188,114.00594670665,143SUMA36 188,121 PROMEDIO 5,225.39413573515,2454958548542738244,878MESREGRESIONPROMEDIO458433234,92737ERROR:#REF!ERROR:#REF!498442504,977385,167 402573515,225395,164 405,161 415,158 425,155

0.0

PROMEDIO MOVIL SIMPLE

MESESCANTIDADESPRONOSTICOPRONOSTICOindiceP 2MP 3M14,735 0.906 24,635 0.887 35,512 4,685 1.055 4,942 46,137 4,961 5,074 1.174 5,959 5,826 56,580 5,428 5,825 1.259 7,334 6,835 66,061 6,076 6,359 1.160 7,375 7,048 75,946 6,259 6,321 1.138 7,192 7,123 84,135 6,196 6,004 0.791 4,751 4,903 95,427 5,381 5,041 1.039 5,235 5,588 104,584 5,169 4,781 0.877 4,194 4,535 114,984 4,715 5,006 0.954 4,774 4,498 124,025 4,998 4,784 0.770 3,685 3,850 134,555 4,531 4,505 0.872 3,927 3,950 144,969 4,521 4,290 0.951 4,080 4,299 155,039 4,516 4,762 0.964 4,592 4,355 163,717 4,854 5,004 0.711 3,560 3,453 174,250 4,575 4,378 0.813 3,561 3,721 187,066 4,335 3,984 1.352 5,387 5,862 197,066 5,011 5,658 1.352 7,651 6,776 207,351 6,127 7,066 1.407 9,940 8,620 213,824 7,161 7,209 0.732 5,275 5,241 223,323 6,080 5,588 0.636 3,553 3,867 234,250 4,833 3,574 0.813 2,906 3,931 247,351 3,799 3,787 1.407 5,327 5,344 256,113 4,975 5,801 1.170 6,786 5,820 265,450 5,905 6,732 1.043 7,021 6,158 275,852 6,305 5,782 1.120 6,475 7,061 284,632 5,805 5,651 0.886 5,009 5,146 295,253 5,311 5,242 1.005 5,270 5,339 304,897 5,246 4,943 0.937 4,632 4,916 315,143 4,927 5,075 0.984 4,995 4,850 325,245 5,098 5,020 1.004 5,039 5,117 334,878 5,095 5,194 0.934 4,849 4,756 344,927 5,089 5,062 0.943 4,772 4,798 354,977 5,017 4,903 0.952 4,669 4,778 365,225 4,927 4,952 1.000 4,952 4,927 666188,114 173,227 173,348 36 promedio5,225 8 MAX0.0MINIMOSMAX0.0MINIMOS

PROMEDIO MOVIL PONDERADOPONDERACION0.450.320.23PonderacionMesesCantidadesPronostico0.214,735 0.324,635 0.535,512 46,137 5,053 56,580 5,649 66,061 6,234 75,946 6,232 84,135 6,107 95,427 5,064 104,584 5,143 114,984 4,747 124,025 4,953 134,555 4,425 144,969 4,482 155,039 4,656 163,717 4,921 174,250 4,364 187,066 4,248 197,066 5,551 207,351 6,503 213,824 7,209 223,323 5,531 234,250 4,279 247,351 3,887 256,113 5,615 265,450 6,112 275,852 6,029 284,632 5,784 295,253 5,162 304,897 5,187 315,143 4,951 325,245 5,091 334,878 5,145 344,927 5,041 354,977 4,976 365,225 4,942 Sumatoria666188,114 173,269 MINIMOSMINIMOSDMA04032.45SIST RASTREO0.04223A= 2 / 36 + 1SUAVIZACION EXPONENCIALA= 2 / 37 A=0,05MESESCANTIDADESF(1) = F(0) + * (Do - Fo)PRONOSTindice estapronostico0.0514,735 4,635 0.91 4,200 24,635 4,640 4,640 0.89 4,116 35,512 4,640 4,640 1.05 4,894 46,137 4,683 4,683 1.17 5,500 56,580 4,756 4,756 1.26 5,989 66,061 4,847 4,847 1.16 5,622 75,946 4,908 4,908 1.14 5,585 84,135 4,960 4,960 0.79 3,925 95,427 4,919 4,919 1.04 5,108 104,584 4,944 4,944 0.88 4,337 114,984 4,926 4,926 0.95 4,698 124,025 4,929 4,929 0.77 3,797 134,555 4,884 4,884 0.87 4,257 144,969 4,867 4,867 0.95 4,628 155,039 4,872 4,872 0.96 4,699 163,717 4,881 4,881 0.71 3,472 174,250 4,823 4,823 0.81 3,922 187,066 4,794 4,794 1.35 6,483 197,066 4,907 4,907 1.35 6,636 207,351 5,015 5,015 1.41 7,056 213,824 5,132 5,132 0.73 3,756 223,323 5,067 5,067 0.64 3,222 234,250 4,980 4,980 0.81 4,050 247,351 4,943 4,943 1.41 6,954 256,113 5,064 5,064 1.17 5,924 265,450 5,116 5,116 1.04 5,336 275,852 5,133 5,133 1.12 5,748 284,632 5,169 5,169 0.89 4,582 295,253 5,142 5,142 1.01 5,169 304,897 5,147 5,147 0.94 4,824 315,143 5,135 5,135 0.98 5,054 325,245 5,135 5,135 1.00 5,155 334,878 5,141 5,141 0.93 4,799 344,927 5,128 5,128 0.94 4,835 354,977 5,118 5,118 0.95 4,874 365,225 5,111 5,111 1.00 5,110

b =n xy - (x * y)(n x) - (x)

DATOS

Meses (X)Cantidades (Y)xy x2 y2 regresionindice estac.pronostico15,437 5,437 1295609696026.67717717720.91328692215504.0854497249A =(y * x ) - (x * xy)26,700 13,400 4448900006022.47975117981.12544093776777.9452583314(n x) - (x)36,631 19,893 9439701616018.28232518231.11385057586703.4672331428B =n xy - (x * y)46,747 26,988 16455220096014.08489918491.13333582196815.9778520167(n x) - (x)55,814 29,070 25338025966009.88747318750.97661397195869.340075813466,259 37,554 36391750816005.690047191.05136340736314.162751232176,305 44,135 49397530256001.49262119261.05909031526356.122712062186,392 51,136 64408576645997.29519519521.07370424986439.321338382495,105 45,945 81260610255993.09776919780.8575188045139.1940313517105,960 59,600 100355216005988.90034320031.00113850585995.7187407243a =6030.8746031746116,521 71,731 121425234415984.70291720291.09537318736555.483109198126,093 73,116 144371246495980.50549120551.02347934836120.9238623572b =-4.1974259974135,906 76,778 169348808365976.30806520810.99206778785928.9027211794145,126 71,764 196262758765972.11063921060.86104630555142.2638016638155,320 79,800 225283024005967.91321321320.89363369985333.1283646326165,385 86,160 256289982255963.71578721580.90455215665394.4919768456176,582 111,894 289433227245959.51836121841.10561973916588.9611355541186,101 109,818 324372222015955.32093522091.02482315836103.1508096838195,241 99,579 361274680815951.12350922350.88036357535239.152369521205,361 107,220 400287403215946.92608322610.90052072645355.3301963377215,558 116,718 441308913645942.72865722870.93361204955548.2030812798225,572 122,584 484310471845938.53123123120.93596371715558.2497654638236,112 140,576 529373565445934.33380523381.02667089726092.6078119842246,394 153,456 576408832365930.13637923641.07404020236369.2048765288255,827 145,675 625339539295925.9389532390.97879766335800.295200073264,937 128,362 676243739695921.74152724150.82929879244910.8930976674275,028 135,756 729252807845917.54410124410.8445846324997.8668073219286,043 169,204 784365178495913.34667524671.01508053536002.5231084313296,489 188,181 841421071215909.14924924921.0899979476440.9605499432306,649 199,470 900442092015904.95182325181.11687414856595.0880392544316,465 200,415 961417962255900.75439725441.08596651676408.0216988792326,234 199,488 1024388627565896.5569712571.04716400086174.6621890917336,859 226,347 1089470458815892.35954525961.15214916296788.8771176845345,237 178,058 1156274261695888.16211926210.87969167025179.7671693608356,105 213,675 1225372710255883.96469326471.02549506346033.9767459532mesregresion365,821 209,556 1296338840415879.76726726730.97778980575749.176493866337-155.3047619048sumatorias666214316394853916206128688016221431636214327.49754253838-159.5021879022PROMEDIO 5,953 39-163.699613899640-167.89703989741-172.094465894542-176.2918918919