Previsión en Excel, prónostico de datos futuros.

Escrito por Iván Antona en 3 agosto, 2020. Publicado en Excel Avanzado, Excel Intermedio, Herramientas Integradas.

Previsión en Excel

La previsión en Excel nos ayuda a pronosticar con un cierto grado de certeza (intervalo de confianza) un valor en base a una serie de datos históricos.
Necesitaremos entonces un listado con datos históricos donde deben aparecer de forma obligatoria dos variables necesarias, la variable fecha y la variable que pretendemos pronosticar, podría ser por ejemplo las ventas, los elementos de stock de un almacén, el número de clientes, etc…

Ejemplo de manejo de Previsión en Excel

Supongamos que tenemos un listado con el promedio de las ventas de 2019 de forma mensualizada y queremos mediante la herramienta de Previsión en Excel pronosticar como se comportaran las ventas en enero, febrero y marzo de 2020.

Seleccionaremos todos los datos de la serie incluyendo las fechas y las ventas y seguidamente dentro de la pestaña de datos iremos a la opción “Previsión” dentro encontraremos la opción de “previsión” y haciendo clic sobre ella automáticamente aparecerá un asistente que nos pinta por defecto la gráfica con le previsión en Excel.

Haciendo clic en la opción crear obtendremos el resultado de la previsión, así como la gráfica de esta. Para este caso en enero de 2020 pronosticamos unas ventas de 1,34. Para febrero y marzo, 1,35 y 1,36 respectivamente. Además, podemos observar los límites superiores e inferiores en nuestra gráfica, pero también en la tabla.
Estos límites están íntimamente relacionados con el intervalo de confianza o el nivel de certeza. Por defecto la herramienta de previsión en Excel establece un intervalo de confianza del 95%

Opciones de la previsión en Excel.

Cuando hacemos clic en “Opciones” dentro del asistente de previsión en Excel y tras haber seleccionado la serie de datos nos encontramos con diferentes opciones:
Final de pronóstico: Fecha hasta la cual queremos pronosticar. En nuestro ejemplo se trataba de marzo de 2020.

Inicio de pronósticos: Fecha a partir de la cual comenzamos a pronosticar.
Intervalo de confianza: El asistente lo mantiene por defecto en 95%. Esto significa que en base a los cálculos y el histórico esperamos que al menos el 95% de los puntos futuros pronosticados caerán dentro de ese intervalo o predicción. La base de este razonamiento es la distribución normal.
Estacionalidad: Por defecto se calcula automáticamente y se refiere al patrón que sigue la serie temporal. Por ejemplo, en nuestro caso como tratamos una serie de 12 meses el valor de la estacionalidad sería 12. Microsoft recomienda que cuando modifiquemos esta variable nunca sea menor a 3 para que el algoritmo funcione correctamente.
Intervalo de escala de tiempo: Se refiere a la columna que contiene las fechas en las que se miden los acontecimientos que posteriormente se utilizarán para la previsión en Excel.
Intervalo de valores: Se refiere a los valores en sí. Debe coincidir obviamente con la serie temporal o dicho de otra manera debe existir una relación entre cada elemento de la columna con un elemento adyacente de la columna de “escala de tiempo”
Rellenar los puntos que faltan con: Esta función nos permite que la herramienta de previsión en Excel rellene los valores faltantes con un promedio ponderado de los datos vecinos, esto es lo que se denomina interpolación. Para que Excel rellene mediante interpolación datos de la serie temporal que faltan se debe cumplir que no falten mas del 30% de los datos. Otra función que se nos permite es rellenar los datos faltantes con el valor “0”.
Agregar duplicados con: En aquellos casos en los que para una misma fecha tengamos varios valores, Excel realizará el promedio de los valores existentes. Otros cálculos adicionales para esta funcionalidad están disponibles como por ejemplo la mediana o el conteo. Dependerá del caso así lo trataremos.
Incluir estadísticas de previsión: Al activar esta casilla Excel en la hoja resultado añade información estadística adicional. Mas info sobre estos estadísticos aquí.

Si quieres ampliar información sobre las herramientas de previsión no dejes de consultar los análisis de hipótesis de:

Tabla de datos en Excel manejar variables con tabla de doble entrada

Escrito por Iván Antona en 3 julio, 2020. Publicado en Excel Básico.

Mediante la tabla de datos en Excel podremos realizar simulaciones con dos variables para obtener resultados en una tabla de doble entrada de forma muy sencilla. Deberemos tener un cálculo en el que las dos variables que vamos a simular en nuestra tabla de datos intervengan. Estas dos variables como su nombre indican cambian su valor para cada uno de los escenarios de la simulación.

Supuesto para el manejo de tabla de datos en Excel

Vimos en artículos anteriores el manejo del administrador de escenarios en donde habíamos ejemplificado el pago de la letra de un vehículo que adquirimos en el concesionario. En aquel ejemplo habíamos definido la siguiente tabla:

Estábamos calculando la cuota mensual que tendremos que pagar en base a los valores iniciales:

Coste del vehículo = 20.000€
Tasación (monto que nos descuentan del coste inicial por la entrega de nuestro vehículo viejo) = 1.000€
Plazo en meses (duración del préstamo) = 72 meses
Tipo de interés (interés que pagamos al banco que nos presta) = 7%

En base a esto datos calculamos el valor de la cuota de forma muy sencilla dividiendo el coste del vehículo menos la tasación entre el número de cuotas. Al resultado le aplicamos el interés.
Hasta aquí nada nuevo…¿Qué ocurre si vamos a visitar varios bancos y tenemos diferentes ofertas de tipo de interés? ¿Nos podría interesar un préstamo a menos tiempo pero que tenga una tipo de interés inferior? En esta situación existen dos variables que alterarán la cuota mensual:

Plazo en meses
Tipo de interés.

Veamos entonces como montar una tabla de datos en Excel que nos ayude de un vistazo a simular diferentes escenarios con los diferentes valores que hemos recuperado al visitar varios bancos.

¿Cómo defino mi tabla de datos en Excel con la información anterior?

Tendremos que crear una tabla de doble entrada. En nuestro ejemplo hemos puesto la variable “Tipo de interés” en la columna F (Rango F8:F11) y la variable “Plazo meses” en la fila 7 (Rango G7:I7). También necesitaremos que exista un cálculo en la celda F7 precisamente es el cálculo de la cuota que habíamos definido como =((D7-D8)/D9)*(1+D10).

Lo que hacemos en esta celda F7 es decir que es igual a la celda D11 que refleja el cálculo descrito. ¡Ojo! Este detalle es clave para que nuestro simulador con tabla de datos en Excel funcione correctamente.

Nuestro simulador utilizará las variables que hemos definido en F8:F11 y G7:I7 y los colocará dentro del cálculo de forma transparente para nosotros y nos presentará el resultado en el espacio G8:I11.

Para ello debemos seleccionar todo el rango incluyendo el cálculo es decir el rango F7:I11 y haremos clic en la pestaña Datos-Previsión-Análisis de hipótesis-Tabla de datos. Se nos presenta un asistente muy sencillo que nos pregunta:

Celda de entrada (fila): ¿Qué variable definimos en nuestra tabla en la fila 7? La variable “Plazo meses”
Celda de entrada (columnas): ¿Qué variable definimos en nuestra tabla en la columna F? La variable “Tipo de interés”

Nótese que hemos seleccionado entonces las celdas:

Esto es así porque en nuestro cálculo original así están definidas.

Finalmente y tras hacer clic en “aceptar” nuestra tabla de datos se rellena automáticamente con los resultados de la sustitución de las variables dentro del cálculo. Ahora ya podemos comenzar a analizar los resultados para tomar una decisión con respecto a la entidad con la que queremos negociar nuestro préstamo.

Descarga el ejemplo

Ejemplo_Tabla_datos_en_excel

Etiqueta: previsión