consolidado forecasting

Download Consolidado Forecasting

Post on 14-Dec-2015

6 views

Category:

Documents

0 download

Embed Size (px)

DESCRIPTION

Jorge Gonzales Esan

TRANSCRIPT

DATASERIE DE TIEMPO DE VENTAS DE GASOLINASEMANAVENTAS (Miles de Galones)117221319423518616720818922102011151222

PROMEDIOS MOVILESSERIE DE TIEMPO DE VENTAS DE GASOLINACON EXCELSEMANAVENTAS (Miles de Galones)PRONOSTICO CON PROMEDIOS MOVILESERROR DE PRONSTICOERROR ABSOLUTOERROR DE PRONSTICO AL CUADRADOERROR:#N/A117ERROR:#N/A221193192142319.004.004.002051821.00-3.003.001961620.00-4.004.001872019.001.001.001881818.000.000.02092218.004.004.0020102020.000.000.019111520.00-5.005.0019122219.003.003.000.0024.000.002.670.00Error0.00SEMANAVENTAS (Miles de Galones)PRONOSTICO CON PROMEDIOS MOVILES (4)PRONOSTICO CON PROMEDIOS MOVILES (3)ERROR DE PRONSTICOERROR ABSOLUTOERROR DE PRONSTICO AL CUADRADO11722131942319.0051820.0021.00-2.002.0061620.2520.00-4.254.2572019.0019.001.001.0081819.2518.00-1.251.2592218.0018.004.004.00102019.0020.001.001.00111520.0020.00-5.005.00122218.7519.003.253.25-3.2521.75-0.412.720.00Error0.00

PROMEDIOS MOVILES PONDERADOSSERIE DE TIEMPO DE VENTAS DE GASOLINAF(t+1) = fc(t)xY(t) + fc(t-1)xY(t-1) + fc(t-2)xY(t-2)

FACTOR T0.50FACTOR T-10.33FACTOR T-20.17SEMANAVENTAS (Miles de Galones)PRONOSTICO CON PROMEDIOS MOVILESERROR DE PRONSTICOERROR DE PRONSTICO AL CUADRADO11722131942319.333.673.6751821.33-3.333.3361619.83-3.833.8372017.832.172.1781818.33-0.330.3392218.333.673.67102020.33-0.330.33111520.33-5.335.33122217.834.174.1726.83Error2.98

SUAVIZAMIENTO EXPONENCIALSERIE DE TIEMPO DE VENTAS DE GASOLINAF(t+1) = (Yt) + (1-)F(t)erro simpleFACTOR 0.10error absolutoYFCON EXCELSEMANAVENTAS (Miles de Galones)PRONOSTICO CON SUAVIZAMIENTO EXPONENCIALERROR DE PRONSTICOERROR DE PRONSTICO AL CUADRADOERROR:#N/A11717.0017.0022117.004.004.0017.8031917.421.581.5818.0442317.595.415.4119.0351818.15-0.150.1518.8361618.14-2.142.1418.2672017.912.092.0918.6181818.13-0.130.1318.4992218.123.883.8819.19102018.531.471.4719.35111518.68-3.683.6818.48122218.293.713.71ERROR ACUMULADO16.0428.25ERROR PROMEDIO1.462.57SIMPLEABSOLUTOFACTOR Error0.0011.910.0510.040.109.250.158.980.208.980.259.120.309.350.359.630.409.960.4510.310.5010.70PTIMO0.170.5511.11Error8.960.6011.550.6512.020.7012.520.7513.050.8013.610.8514.210.9014.860.9515.541.0016.27

TENDENCIASSERIE DE TIEMPOS DE VENTA DE BICICLETAST(t) = B(0) + B(1) x tT(t) = 20.4 + 1.1 x tCON EXCEL

AO (t)Ventas (Miles)tYtt x YttAO (t)Ventas (Miles)PronsticoResumen121.6121.621.61121.68.3222.9245.891.64222.90Estadsticas de la regresin325.5376.5229.59325.5-72.6Coeficiente de correlacin mltiple0.8745261666421.9487.6350.416421.90Coeficiente de determinacin R^20.7647960161523.95119.5597.525523.90R^2 ajustado0.7353955181627.5616599036627.50Error tpico1.9589538024731.57220.51543.549731.50Observaciones10829.78237.61900.864829.70928.69257.42316.681928.60ANLISIS DE VARIANZA1031.41031431401001031.40Grados de libertadSuma de cuadradosPromedio de los cuadradosFValor crtico de FRegresin199.82599.82526.0130293160.0009295092tYtt x YttResiduos830.73.8375551545.511181.5385Total9130.525

t promedioY PromedioCoeficientesError tpicoEstadstico tProbabilidadInferior 95%Superior 95%Inferior 95.0%Superior 95.0%5.5154.55Intercepcin20.41.338220211115.24412785810.0000003417.31405866223.48594133817.31405866223.485941338t promedioY promedioAO (t)1.10.21567371545.10029698310.00092950920.60265552081.59734447920.60265552081.5973444792B(1)32.5Pendiente

B(0)-24.2IntercepcinAnlisis de los residuales

ObservacinPronstico Ventas (Miles)Residuos121.50.1222.60.3323.71.8424.8-2.9funcion cuando tiene poca variabilidad y tendencia marcada525.9-26270.5728.13.4829.20.5930.3-1.71031.4-0

TENDENCIAS (2)SERIE DE TIEMPOS DE VENTA DE BICICLETAST(t) = B(0) + B(1) x tT(t) = 20.4 + 1.1 x tCON EXCEL

AO (t)Ventas (Miles)tYtt x YttAO (t)Ventas (Miles)PronsticoResumen119944.314.34.3114.323.616219955.6211.222.4425.60Estadsticas de la regresin319967.8323.470.2937.869.624Coeficiente de correlacin mltiple0.8745261666419979.2436.8147.21649.20Coeficiente de determinacin R^20.7647960161519989.7548.5242.52559.70R^2 ajustado0.7353955181000Error tpico1.9589538024000Observaciones10000000ANLISIS DE VARIANZA5000Grados de libertadSuma de cuadradosPromedio de los cuadradosFValor crtico de FRegresin199.82599.82526.0130293160.0009295092tYtt x YttResiduos830.73.837520124.2486.655Total9130.525

t promedioY PromedioCoeficientesError tpicoEstadstico tProbabilidadInferior 95%Superior 95%Inferior 95.0%Superior 95.0%424.84Intercepcin20.41.338220211115.24412785810.0000003417.31405866223.48594133817.31405866223.485941338t promedioY promedioAO (t)1.10.21567371545.10029698310.00092950920.60265552081.59734447920.60265552081.5973444792B(1)0.408Pendiente

B(0)23.208IntercepcinAnlisis de los residuales

ObservacinPronstico Ventas (Miles)Residuos121.50.1222.60.3323.71.8424.8-2.9funcion cuando tiene poca variabilidad y tendencia marcada525.9-26270.5728.13.4829.20.5930.3-1.71031.4-0

Hoja1Resumen

Estadsticas de la regresinCoeficiente de correlacin mltiple0.8745261666Coeficiente de determinacin R^20.7647960161R^2 ajustado0.7353955181Error tpico1.5574148722Observaciones10ANLISIS DE VARIANZAGrados de libertadSuma de cuadradosPromedio de los cuadradosFValor crtico de FRegresin163.095671327363.095671327326.0130293160.0009295092Residuos819.40432867272.4255410841Total982.5

CoeficientesError tpicoEstadstico tProbabilidadInferior 95%Superior 95%Inferior 95.0%Superior 95.0%Intercepcin-12.88986784143.6391263533-3.54202261480.0075973863-21.2817082606-4.4980274222-21.2817082606-4.4980274222Variable X 10.69526910550.13631933745.10029698310.00092950920.38091614971.00962206140.38091614971.0096220614

MODELO MULTIPLICATIVOlo minimo es 3 AnosAoTrimestreVentas (Miles)Promedio Movil (4 Trimestres)Promedio Movil Centrado114.8

24.15.3536.05.485.6046.55.745.88215.85.986.0825.26.196.3036.86.336.3547.46.406.45316.06.546.6325.66.686.7337.56.766.8047.86.846.88416.36.947.0025.97.087.1538.05.5848.4

MODELO MULTIPLICATIVO (2)dividir las ventas entre los indicestYtt x YttT(t) = 5.100 + 0.147 x tAoTrimestreVentas (Miles)Promedio Movil (4 Trimestres)Promedio Movil CentradoRatio Valor Irregular y/o EstacionaltrimestreVentas DesestacionalizadasAoTrimestreTiempoPronstico de Tendenciandice EstacionalPronstico Trimestral (miles)Pronstico Trimestral (un)periodoventaspronostico114.80.932200477315.155.151.0051177.600.937.09708614.824.15.350.837759204224.899.794.002187.750.846.49649124.136.05.605.481.0961.093348842235.4916.469.003197.901.098.6386323646.55.885.741.133INDICE ESTACIONAL1.143305142745.6922.7416.004208.041.149.19919546.5215.86.085.980.971TRIMESTRE 10.930.932200477356.2231.1125.0061218.190.937.63763455.825.26.306.190.840TRIMESTRE 20.840.837759204266.2137.2436.002228.340.846.98698465.236.86.356.331.075TRIMESTRE 31.091.093348842276.2243.5449.003238.481.099.28927676.847.46.456.401.156TRIMESTRE 41.141.143305142786.4751.7864.004248.631.149.87986887.4316.06.636.540.9180.932200477396.4457.9381.009625.66.736.680.8390.8377592042106.6866.84100.00105.637.56.806.761.1091.0933488422116.8675.46121.00117.547.86.886.841.1411.1433051427126.8281.87144.00127.8416.37.006.940.9080.9322004773136.7687.86169.00136.325.97.157.080.8340.8377592042147.0498.60196.00145.938.01.0933488422157.32109.75225.0015848.41.1433051427167.35117.55256.00home168.4177.0856262443tYtt x Ytt186.4910479383136101.6046268755913.66649183441496198.6322577406209.1948994576t PromedioY Promedio217.63427736898.56.3502891797226.9841151924239.2757535191249.8677972597B(1)0.147Pendiente

B(0)5.100Intercepcin

RegresinRestaurante iPoblacin de Estudiantes (Miles)Ventas trimestrales (000 US$)1258261053888481185121176161377201578201699221491026202Resumen

Estadsticas de la regresinCoeficiente de correlacin mltiple0.9501229552Coeficiente de determinacin R^20.90273363R^2 ajustado0.8905753338Error tpico13.8293166859Observaciones10ANLISIS DE VARIANZAGrados de libertadSuma de cuadradosPromedio de los cuadradosFValor crtico de FRegresin1142001420074.24836601310.0000254887Residuos81530191.25Total915730

CoeficientesError tpicoEstadstico tProbabilidadInferior 95%Superior 95%Inferior 95.0%Superior 95.0%Intercepcin609.22603480976.50333553230.000187444138.724725594981.275274405138.724725594981.2752744051Poblacin de Estudiantes (Miles)50.5802652388.61674915570.00002548873.66190596276.33809403733.66190596276.3380940373Anlisis de los residuales

ObservacinPronstico Ventas trimestrales (000 US$)Residuos170-12290153100-124100185120-36140-37160-3816099170-211019012

Regresin MultipleIngresos Brutos semanales (000 US$)Anuncios en TV (000 US$)Anuncios en Peridicos (000 US$)Resumen965.01.5902.02.0Estadsticas de la regresin954.01.5Coeficiente de correlacin mltiple0.9586634444922.52.5Coeficiente de determinacin R^20.9190355997953.03.3R^2 ajustado0.8866498396943.52.3Error tpico0.6425873026942.54.2Observaciones8943.02.5ANLISIS DE VARIANZAGrados de libertadSuma de cuadradosPromedio de los cuadradosFValor crtico de FRegresin223.435407792511.717703896228.37776838790.0018652423Residuos52.06459220750.4129184415Total725.5

CoeficientesError tpicoEstadstico tProbabilidadInferior 95%Superior 95%Inferior 95.0%Superior 95.0%Intercepcin83.23009169011.573868951752.88247893710.000000045779.184332752687.275850627779.184332752687.2758506277Anuncios en TV (000 US$)2.29018362090.30406455617.53189931320.00065323171.50856079653.07180644531.50856079653.0718064453Anuncios en Peridicos (000 US$)1.30098909830.3207015974.05669666210.00976079780.47659939852.1253787980.47659939852.125378798Anlisis de los residuales

ObservacinPronstico Ingresos Brutos semanales (000 US$)Residuos196.6324934421-0.6324934421290.4124371285-0.4124371285394.34230982120.6576901788492.2080234881-0.2080234881594.39390657710.6060934229694.2380092893-0.2380092893794.4197049551-0.4197049551893.35311529850.6468847015