OBJETIVOS: Determinar los pronsticos de serie de tiempos y de regresin lineal, utilizando Excel y el complemento anlisis de datos.
ndice
1. Introduccin 2. Ejemplo de cmo usar "ANLISIS DE DATOS" Para pronsticos
1. Introduccin El Anlisis de datos es un programa de complemento (complemento: programa suplementario que
agrega funciones o comandos personalizados a Microsoft Office.) de Microsoft Office Excel que est disponible cuando instala Microsoft Office o Excel. Sin embargo, para utilizarlo en Excel primero lo debe cargar.
a) Introduccin al Anlisis de datos de Excel.
Instalacin Anlisis datos de Excel utilizando Microsoft Office 2003
Paso 1: Seleccione el menu Herramientas y luego acceda a la seccin Complementos
Administracin de la produccin I Anlisis de pronsticos
2
Paso 2: Marque la opcin Anlisis de datos y luego seleccione Aceptar.
Paso 3: Probablemente se le pedira autorizacin para instalar el complemento. Seleccione S.
Administracin de la produccin I Anlisis de pronsticos
3
Paso 4: Dirjase nuevamente al men Herramientas y si la instalacin fue correcta la opcin Anlisis de datos
deber estar disponible en el men.
Instalacin Anlisis de datos de Excel utilizando Microsoft Office 2007
Paso 1: Seleccione el botn Office en la esquina superior izquierda.
Administracin de la produccin I Anlisis de pronsticos
4
Paso 2: Seleccione Opciones de Excel.
Administracin de la produccin I Anlisis de pronsticos
5
Paso 3: En el men de la izquierda debe seleccionar Complementos y luego presionar el botn Ir
Paso 4: Marque la opcin Anlisis de datos y luego seleccione Aceptar.
Administracin de la produccin I Anlisis de pronsticos
6
Paso 5: Probablemente se le pedira autorizacin para instalar el complemento. Seleccione S.
Paso 6: Si la instalacin ha resultado satisfactoria el complemento Anlisis de datos deber estar disponible
en la seccin Datos de Excel.
2. Ejemplo de cmo usar "ANLISIS DE DATOS" para pronsticos
2.1 Promedio mvil
2.2 Suavizamiento exponencial de primer orden
2.3 Regresin lineal
Administracin de la produccin I Anlisis de pronsticos
7
2.1 Promedio mvil Autor:
Roger Ucan Leyton
Ing. Industrial por la Universidad Nacional de Trujllo
Master Direcc. Estratgica de RR.HH. - EOI Espaa
Una empresa desea conocer la tendencia que tendr para el siguiente periodo de la produccin del artculo XX,
para lo cual ha obtenido la informacin del sector dentro de su rea de influencia. Se solicita:
a. Hallar el pronstico mediante el promedio mvil.
b. Calcular el periodo tpico para el periodo 23.
c. Interpretar los resultados.
PERIODO 1 2 3 4 5 6 7 8 9 10 11
PRODUCCION 593 570 486 854 797 362 594 271 45 254 433
PERIODO 12 13 14 15 16 17 18 19 20 21 22
PRODUCCION 529 994 319 610 748 832 193 720 415 536 850
Traer a formato Excel:
Activar el paquete de Herramientas de Anlisis:
Seleccionar: Herramientas-Anlisis de datos
All seleccionar: Anlisis de Datos
Administracin de la produccin I Anlisis de pronsticos
8
Procedimiento de llenado de la caja de dilogo: Media Mvil.
Asumiendo un intervalo de n=5
El resultado ser el siguiente:
Administracin de la produccin I Anlisis de pronsticos
9
Note que el pronstico para el perodo 23 ser de 542,8 donde adems aparecer el grfico siguiente
Media mvil
0
200
400
600
800
1000
1200
1 3 5 7 9 11 13 15 17 19 21
Punto de datos
Valo
r
Real
Pronstico
Quedando el anlisis terminado con el error cuadrado medio. Hallamos el error cuadrado medio para los
pronsticos encontrados.
Donde el error del pronstico est dado por la frmula de:
Administracin de la produccin I Anlisis de pronsticos
10
Error = (Yt Ft)2
Donde:
Yt = dato histrico
Ft = pronstico hallado
Por ltimo promediaremos el error del pronstico para hallar el Error Cuadrado Medio (ECM) para compararlo
con el de otros intervalos.
Copiamos la frmula para las otras celdas y luego, procedemos a hallar el promedio de los errores de pronstico,
con la frmula
=PROMEDIO(D7:D23)
Es decir el promedio de los errores de pronstico al cuadrado, hallados.
Administracin de la produccin I Anlisis de pronsticos
11
Dando como resultado el ECM para n=5 en 92.270,64
Si acaso desarrollramos para un n=3 tendramos, siguiendo los pasos anteriores lo siguiente:
Administracin de la produccin I Anlisis de pronsticos
12
Quedando el pronstico as:
Luego hallamos el error cuadrado del pronstico
Administracin de la produccin I Anlisis de pronsticos
13
Por ltimo hallamos el Error cuadrado medio, que viene a ser el promedio de todos los errores cuadrados del
pronstico.
Resultando para n=3: El ECM= 83.743,63
As:
K E.C.M.
3 83.743,63
5 92.270,64
Luego aceptaramos el valor de n=3, por tener el menor valor de ECM. Vale decir que nuestro pronstico para el
perodo 23 ser de 600,33.
Administracin de la produccin I Anlisis de pronsticos
14
2.2 Suavizamiento exponencial de primer orden
Con los siguientes datos acerca de la ventas en miles de dlares de la Empresa D & M durante los ltimos 12
meses:
Meses Ventas
Sep. 6
Oct. 7
Nov. 6
Dic. 12
Ene. 7
Feb. 10
Mar. 6
Abr. 4
Mayo. 9
Jun. 7
Jul. 8
Ago. 6
Determine el pronstico para el mes de septiembre, utilizando el mtodo de suavizacin exponencial con = 0.3.
1. Posicionar el cursor en cualquier celda.
2. Seleccionar anlisis de datos y buscar el mtodo de suavizacin exponencial
Administracin de la produccin I Anlisis de pronsticos
15
3. Seleccionar rango de entrada, colocar el factor de suavizacin (1-), seleccionar el rango de salida (celda del primer dato de salida) y clic en crear grfico.
4. Clic en Aceptar
5. Desplegado de pronsticos y grfico.
6. Determinar el pronstico de septiembre, copiando el pronstico de agosto.
Administracin de la produccin I Anlisis de pronsticos
16
2.3 PRONSTICOS CORRELACIN Y REGRESIN
Anlisis de regresin: Tcnica estadstica usada para elaborar una ecuacin matemtica que muestra cmo se relacionan las variables. Diagramas de dispersin: Grafica que muestra la forma en que los puntajes de dos variables cualesquiera X y Y estn dispersas. Correlacin: Es la medida numrica de la intensidad de la relacin lineal entre dos variables. Grados o tipos de correlacin:
Para interpretar un coeficiente de correlacin hay que tener en cuenta por un lado su magnitud (relacin entre las variables) y por otro su signo (tipo de relacin).
Regresin: Consiste en predecir o pronosticar los valores de una variable Y conociendo los valores de otra variable X.La regresin se identifica con una lnea recta que se dibuja a travs del diagrama de dispersin.
Administracin de la produccin I Anlisis de pronsticos
17
EJEMPLO DE REGRESIN Y CORRELACIN UTILIZANDO MICROSOFT EXCEL 2007 A continuacin se presentan las ventas trimestrales en miles de dlares de diez restaurantes localizados cerca de una Universidad y la poblacin estudiantil en miles que acude a dichos restaurantes:
Calcule el coeficiente de correlacin, elabore la ecuacin de la regresin y pronostique las ventas trimestrales para una poblacin estudiantil de 10 Pasos: 1. Encontrar el coeficiente de correlacin a. Site el cursor en la celda donde desea que Excel proporcione el resultado.
Administracin de la produccin I Anlisis de pronsticos
18
b. Seleccione el men frmulas y clic en insertar funcin
c. Seleccione la categora de estadsticas y posteriormente COEF.DE.CORREL presione aceptar
d. En la matriz 1 elija la columna "x" y en la matriz 2 la columna "y" clic en aceptar
Administracin de la produccin I Anlisis de pronsticos
19
2. Encontrar los valores de la ecuacin de regresin. a. Site el cursor donde desea que aparezcan los valores.
Administracin de la produccin I Anlisis de pronsticos
20
b. Agregue las herramientas de anlisis de Excel si no las tiene activas para ello: site el cursor en la parte superior de los mens, clic derecho, elegir personalizar barra de herramientas de acceso rpido
c. Elegir Complementos, en la parte inferior elija complementos de Excel ir a
d. Elija herramientas para anlisis y clic en aceptar.
Administracin de la produccin I Anlisis de pronsticos
21
e. Clic en anlisis de datos, a continuacin elija regresin y clic en aceptar
f. En rango y de entrada elija la columna de datos "y" y en rango x de entrada elija la columna de datos "x" en rango de salida elija la celda donde desea el resultado y haga clic en aceptar.
Administracin de la produccin I Anlisis de pronsticos
22
g. En la imagen puede observar que obtuvo el valor de la correlacin y los valores de las variables b0 y b1 para la ecuacin de la regresin lineal en este caso los valores son: 60 y 5 respectivamente.
3. Pronostique para cada poblacin estudiantil incluyendo la solicitada que es 10.
Administracin de la produccin I Anlisis de pronsticos
23
4. Realice la grfica tomando en cuenta las ventas trimestrales y el valor estimado (pronstico)