Cómo crear hojas de trabajo de pronóstico en Excel 2019

La función Hoja de pronóstico en Excel 2019 hace que sea muy fácil convertir una hoja de trabajo que contiene datos financieros históricos en una hoja de trabajo de pronóstico visual notable. Todo lo que debe hacer es abrir la hoja de cálculo de Excel con sus datos históricos , colocar el cursor de la celda en una de sus celdas y luego hacer clic en el botón Hoja de pronóstico en la pestaña Datos de la cinta (Alt + AFC).

Excel luego selecciona todos los datos históricos en la tabla de la hoja de trabajo circundante (que generalmente incluye una columna para las fechas y otra para los datos financieros relacionados) mientras que al mismo tiempo abre el cuadro de diálogo Crear hoja de trabajo de pronóstico.

Definir las opciones para un nuevo gráfico de líneas que muestre las ventas históricas y proyectadas en el cuadro de diálogo Crear una hoja de cálculo de previsión.

De forma predeterminada, Excel 2019 selecciona un gráfico de líneas para representar visualmente las tendencias en su pronóstico, pero, si lo desea, puede hacer que el programa use un gráfico de columnas en su lugar. Simplemente haga clic en el icono Gráfico de columnas a la derecha inmediata del icono Gráfico de líneas en la parte superior del cuadro de diálogo Crear hoja de cálculo de previsión.

Además de seleccionar el tipo de gráfico que se utilizará en la hoja de cálculo de pronóstico, también puede controlar la fecha en la que finaliza el gráfico de pronóstico. Para ello, seleccione una nueva fecha en el cuadro de texto Fin del pronóstico que aparece debajo de la vista previa del gráfico de líneas o columnas incrustado. Por ejemplo, en la imagen a continuación, la tabla de datos históricos incluye las ventas anuales de los años 2000 a 2015, y Excel usa automáticamente estos datos para extender la fecha de finalización del pronóstico cuatro años hasta 2019. Si desea extender el pronóstico más allá de este final fecha, simplemente ingrese el año nuevo en el cuadro de texto Fin del pronóstico (o seleccione uno con los botones giratorios).

Hoja de trabajo con gráfico de líneas incrustado que muestra las ventas históricas y previstas creadas con la función Hoja de pronóstico.

Debajo del cuadro de texto Fin de pronóstico en el cuadro de diálogo Crear hoja de trabajo de pronóstico, encontrará un botón Opciones que, al hacer clic en él, expande el cuadro de diálogo para incluir las siguientes opciones:

• Inicio de pronóstico le permite seleccionar una fecha de inicio en los datos históricos posterior a la que Excel selecciona automáticamente (que es la primera fecha en la tabla de la hoja de trabajo).
• El intervalo de confianza le permite seleccionar un nuevo grado de confianza que Excel usa para establecer la línea Límite de confianza superior e inferior en el pronóstico cuando se usa el gráfico de líneas predeterminado.
• Cambios estacionales de la opción Detectar automáticamente a Establecer manualmente donde puede ingresar o seleccionar un nuevo valor que indique el número de puntos en el rango de valores de su tabla de datos que son parte de un patrón estacional recurrente. Cuando Excel no puede detectar automáticamente la estacionalidad en los datos de su hoja de trabajo, aparece una advertencia que le advierte y le sugiere que seleccione el botón de opción Establecer manualmente (y deje la configuración predeterminada en cero) para obtener mejores resultados en el pronóstico.
• Incluir estadísticas de pronóstico tiene Excel incluye una tabla de métricas de precisión de pronóstico y factores de suavizado en la hoja de cálculo de pronóstico resultante.
• El rango de la línea de tiempo modifica el rango de celdas que contiene los valores de fecha en sus datos históricos (de manera predeterminada, Excel selecciona todos estos valores que identifica en la tabla de la hoja de trabajo actual).
• Rango de valores modifica el rango de celdas que contiene los valores financieros en sus datos históricos (de forma predeterminada, Excel selecciona todos estos valores que identifica en la tabla de la hoja de trabajo actual).
• Rellenar puntos faltantes Se utiliza para que Excel rellene automáticamente los puntos de datos faltantes que encuentre en la tabla de la hoja de trabajo en el gráfico de previsión mediante interpolación o ceros.
• Agregar duplicados El uso modifica la función estadística.

Después de seleccionar todas las opciones deseadas en el cuadro de diálogo Crear hoja de trabajo de pronóstico, solo tiene que hacer clic en el botón Crear para que Excel cree una nueva hoja de trabajo de pronóstico. Esta nueva hoja contiene una tabla de datos formateada que combina sus datos históricos con los valores de pronóstico, así como un gráfico de líneas o columnas incrustado (según su selección) que representa la tendencia en los valores históricos y de pronóstico.

La imagen de arriba muestra la nueva hoja de trabajo de pronóstico que la función Hoja de pronóstico de Excel creó utilizando los datos de ventas históricos originales que se muestran arriba después de que se seleccionó el botón de opción Establecer manualmente con la configuración en cero y se seleccionó la casilla de verificación Incluir estadísticas de pronóstico antes de hacer clic en Crear en el Cuadro de diálogo Crear hoja de trabajo de previsión. Como puede ver, Excel colocó esta nueva hoja de trabajo de pronóstico (llamada Hoja1) antes de la hoja de trabajo que contiene los datos históricos (llamada Ventas anuales).

Tenga en cuenta que para mostrar todos los valores de pronóstico en la nueva tabla de datos formateada en la imagen de arriba, el gráfico de líneas incrustado se reposicionó y se cambió de tamaño para que encaje perfectamente en la esquina inferior derecha antes de tomar la captura de pantalla. Si lo desea, puede mover este gráfico a su propia hoja de gráficos antes de guardar la nueva tabla de pronóstico y el gráfico como parte del libro de trabajo Ventas históricas y previstas.