Cómo crear una previsión en Excel

Cómo es lógico, estudiar la previsión de ventas en una empresa es de máxima importancia. Una previsión nos puede ayudar a predecir aspectos, como las ventas futuras, los requisitos de inventario o las tendencias de los consumidores. Uno de los factores más analizados son las ventas. Conocer cual será la previsión de ventas de un trimestre o un año nos puede dar unas pistas para crear un escenario hipotético en el que establecer unas directrices.  Aprende cómo crear una previsión de ventas en Excel con la herramienta previsión y sin necessidad de utilizar ningúna función de Microsoft Excel. 

PREVISIÓN, la herramienta de pronóstico en Microsoft Excel

Para poder analizar los datos y crear una previsión de ventas, Microsoft Excel tiene una herramienta especializada en la previsión. Esta herramienta la podemos encontrar en la pestaña de datos.

Esta herramienta engloba una serie de funciones estadísticas de Excel para que nos sea mucho más fácil crear los gráficos y las tablas de previsión, ahorrándonos mucho tiempo. Unas de las funciones con las que se crearán nuestros nuevos datos son, por ejemplo, PREVISION.ETS o PREVISION.ETS.CONFINT. La herramienta de previsión analizará los datos históricos para generar un modelo predictivo.

Puntos importantes antes de crear un pronóstico de ejemplo

Antes de empezar, es muy importante tener preparada la tabla de datos que queramos usar para la previsión. Esta herramienta es muy estricta con los intervalos de tiempo y si no ordenamos las fechas como nos dicen, será imposible crear un análisis de previsión. Para crear nuestro análisis solo necesitamos dos tipos de datos que se correspondan entre sí:

• Una columna con fechas ordenadas con una lógica temporal, es decir: una columna de años, meses, semanas, etc.
• Una columna con los valores correspondientes que queremos analizar, por ejemplo: ventas, cantidades y otras.

También es importante fijarse en la escala de tiempo. Nosotros recomendamos ordenar la columna de fechas con intervalos mensuales en el día 1 de cada mes, intervalos anuales o intervalos numéricos. Si ponemos intervalos diarios nos saldrá un error al intentar crear la previsión.

La forma más fácil para preparar la tabla es creando una tabla dinámica ordenada por meses con los valores correspondientes. Cuando la tengamos es importante quitar los totales y la separación en años que aparecen por defecto. También es muy importante aplicar el formato correcto en las fechas.

Si queréis, aquí os dejamos el archivo que han usado nuestros consultores para realizar el ejemplo práctico que veréis a continuación.

Cómo hacer una previsión de ventas

Para este ejemplo usaremos una tabla que nos muestra el resumen de las ventas totales de 2017 y 2018 en meses, y 5 meses de 2016. Esta tabla la hemos sacado de una tabla dinámica para separar los valores que nos interesan. Hemos ordenado por fecha y año la tabla, y la hemos copiado quitando los años que aparecen como separador en una tabla dinámica.

Nos interesa conocer cual es la previsión de las ventas en los próximos 5 meses. Para ello primero seleccionaremos nuestra tabla ya preparada y elegiremos la opción de previsión. Una vez le hayamos dado «clic» en la opción de previsión; rápidamente nos aparecerá un cuadro de diálogo con un gráfico predictivo creado por defecto. El primer paso es decidir si queremos un gráfico de líneas o uno de columnas. Después debemos poner la fecha final de la previsión, en nuestro ejemplo 5 meses (de enero a mayo de 2019).

Opciones adicionales en la previsión

Justo debajo del gráfico tenemos las opciones avanzadas donde lo podremos personalizar un poco. En las opciones avanzadas encontraremos algunos detalles explicados a continuación:

Intervalo de confianza

El valor predeterminado de Excel es de 95%. El intervalo de confianza es el intervalo aproximado a cada valor de predicción, en el que se espera que se incluyan el 95 % de los puntos futuros, en función de la previsión (con distribución normal). El intervalo de confianza funciona como una variable de la precisión en la predicción. Un intervalo menor implica más confianza en la predicción para el punto específico. Es decir, si tenemos un intervalo del 95%, indica que el 95% de los valores futuros se producirán dentro de los máximos y mínimos marcados.

Estacionalidad

Esta opción indica la longitud del gráfico a partir de X número de puntos. La estacionalidad por defecto es de 0 (lineal). Como nuestra tabla está ordenada por meses, tenemos 12 puntos por cada ciclo. Por lo tanto, nosotros podremos un 12 de estacionalidad. Para cambiarla tendremos que marcar la opción de añadirla manualmente.

Intervalo de escala de tiempo

Esta opción sirve para cambiar el intervalo que usamos en la escala temporal. Este intervalo necesita coincidir con el Intervalo de valores.

Intervalo de valores

Esta opción sirve para cambiar el intervalo que usamos en valores. Este intervalo necesita ser idéntico al Intervalo de la escala de tiempo.

Rellenar los puntos que faltan

Para identificar los puntos que faltan, Excel utiliza la interpolación. Esto significa que los puntos que falten se completarán como el promedio del punto anterior. Esto siempre y cuando se hayan perdido menos del 30 % de los puntos. Si queremos tratar los puntos que faltan como un cero, debemos elegir la opción de “Cero”.

Agregar duplicados

Te permite elegir distintas opciones de cálculo para los valores.

Incluir estadísticas de previsión

Esta opción te permite generar automáticamente 7 medidas estadísticas que aportarán un mejor detalle sobre la previsión de tus datos. Se agregará una tabla con las siguientes medidas: coeficiente de suavizado (Alpha, beta y gamma), MASE, SMAPE, Mae y ECM).

Una vez elegida nuestra personalización, ya podemos crear el gráfico. Automáticamente nos aparecerán tres nuevas columnas: el límite de confianza superior, el inferior y la previsión (media entre las dos). Este sería el resultado final.

¿Ya conoces al completo el potencial de Microsoft Excel? Somos consultores especializados en las herramientas de Microsoft, participa en nuestro curso completo y presencial y descubre todo el potencial que ofrece Excel.