miércoles, 2 de enero de 2013

La media móvil en Excel.

Hablaremos en esta ocasión de una de las herramientas de análisis que encontramos en Excel, en particular de la Media Móvil.
Esta es una herramienta muy específica del mundo estadístico, por lo que su interpretación y uso requiere de ciertas nociones de esta ciencia.
Podemos sin embargo leer algo al respecto en nuestra amiga Wikipedia sobre la media móvil.
Concretaremos diciendo, por tanto, que la media móvil es una técnica de predicción o pronóstico que simplifica el análisis de tendencias suavizando las fluctuaciones que aparecen en las medidas registradas-históricas- a lo largo de un período.
Estas fluctuaciones pueden ser causadas por factores aleatorios que a menudo derivan de la técnica de medición; (por ejemplo, las medidas de litros de lluvia recogida pueden variar según la exactitud del medidor). Aunque estas fluctuaciones también puede provenir de otras circunstancias temporales capaces de influir en las mediciones, por ejemplo, el control de volumen de ventas mensuales de una empresa, que puede variar según el número de días laborables del mes, o por la ausencia de algún buen comercial, etc... esto es, por causas extraordinarias. Para corregir el problema, se puede tomar una serie de medidas y suavizarlas a lo largo del tiempo, es decir, emplear una Media Móvil.


Para ejemplificar esta herramienta, dispondremos de los datos de unidades vendidas de un producto concreto de una empresa durante un periodo histórico de 12 meses:

La media móvil en Excel.


Este informe muestra una gran variación en las cantidades finales de unidades vendidas de un mes a otro (Junio y Noviembre especialmente), lo que podría indicar alguna carencia en la fuerza de ventas, lo que no es bueno para nuestra empresa. Con unos simples pasos, se puede usar esta información para administrar de forma más precisa los recursos comerciales o de ventas de este año.
Antes de comenzar a trabajar, debemos asegurarnoss que tenemos correctamente instalado el complemento de Análisis de datos, para ello, para instalar las Herramientas de análisis, en el menú Herramientas, haremos clic en Complementos, activaremos la casilla Herramientas de análisis y, a continuación, clic en Aceptar.

Recuerda que esta herramienta de análisis Media móvil proyecta los valores previstos para el periodo de previsión basándose en el valor medio de la variable de un número específico de periodos anteriores. Una media móvil proporciona información sobre tendencias que con una media simple de todos los datos del historial quedaría oculta. En nuestro ejemplo se usan los datos del producto XY para predecir un nivel de ventas para el año siguiente.
El primer paso será llamar a la herramienta, por lo que desde la ficha Datos > grupo Análisis > botón Análisis de datos abriremos la ventana desde la que buscaremos Media Móvil:

La media móvil en Excel.


Esto nos abrirá el cuadro diálogo de nuestra herramienta:

La media móvil en Excel.


Es sencillo completar este cuadro diálogo.
En el Rango de entrada seleccionaremos el rango de datos históricos registrados, con especial cuidado de marcar, si procede, el tick de 'Rótulos en la primera fila'.
El campo Intervalo es el más importante, ya que el valor que introduzcamos aquí, es el número de puntos de datos utilizado para calcular la media móvil, por tanto, cuanto mayor sea el intervalo, más suave será la línea de la media móvil; cuanto menor sea el intervalo, más se verá afectada la media móvil por las fluctuaciones de los puntos de datos individuales.
En nuestro ejemplo he seleccionado 3, que significa considerar el promedio aritmético de tres en tres datos.
En el cuadro Rango de salida, escribimos la dirección de la celda en la que deseamos que comiencen los resultados.
Por último, activaremos la casilla Crear gráfico para ver un gráfico que compare los niveles de ventas reales y previstas.

El resultado tras Aceptar será:

La media móvil en Excel.


El gráfico resultante da una mejor idea de los niveles de ventas ideales, representa la media móvil a tres meses. La herramienta media móvil produce los datos de la columna C, que se han utilizado para crear una de las líneas del gráfico (la de Pronóstico).
Si analizamos el resultado obtenido, y podemos hacerlo directamente en el Gráfico, observamos como la línea de pronóstico ofrece una línea de ventas más suavizada, sin fluctuaciones 'salvajes' (como teníamos en los datos reales), lo que a la hora de predecir modelos nos da cierta estabilidad.

Nótese que las dos primeras celdas del rango de salida contienen el valor de error #N/A. Lógico, ya que hemos seleccionado como intervalo el valor 3, por lo que para los dos primeros valores no existe media.

2 comentarios:

  1. Hola amigo, algun otro metodo para calcular pronostico de volumenes en excel?

    ResponderEliminar
    Respuestas
    1. Hola Gabriel,
      para pronosticar datos, en base a históricos, puedes emplear diferentess herramientas:
      la fución matricial ESTIMACION.LINEAL y agregar la línea de tendencia en un gráfico son las más sencillas y habituales.
      Sldos cordiales

      Eliminar