Predicción de valores futuros con gráficos de Excel

Excel puede ayudarlo a hacer predicciones sobre valores futuros o ayudarlo a detectar una tendencia lineal. Lo que haremos en esta sección es configurar algo llamado Trendline. Usaremos un cuadro de dispersión X, Y para esto. Echaremos un vistazo a las predicciones de ingresos futuros basadas en lo que se ganó en años anteriores. Si está un poco confundido, no se preocupe: todo se aclarará a medida que avancemos.

 
Escriba los siguientes títulos en las celdas A1 a C1:
 
Año
Años desde 2006
Ingresos


Formatee las celdas, si lo prefiere. Su hoja de cálculo se verá así:
 
Encabezados celulares


Ingrese los años 2006 a 2019 en las celdas A2 a A15:
 
Valores de año en la columna A


Como eje X para nuestra tabla, podemos tener los años desde 2006. Estos valores se usarán en una fórmula posterior. En las celdas B2 a B15 ingrese los valores 0 a 13:
 
La columna B


Ahora necesitamos algunos valores de ingresos para los años 2006 a 2013. Esto es el ingreso que realmente se ha ganado, en lugar del ingreso que se podría ganar en el futuro. Luego usaremos estos datos duros para predecir los valores futuros. Ingrese algunos valores de ingreso, entonces, en las celdas C2 a C9. Compusimos los siguientes valores:
 
Valores de ingreso agregados a la Columna C


Ahora estamos listos para insertar un gráfico X, Y Scatter.
 
Resalta las celdas B1 a C9:
 
Celdas B1 a B9 resaltadas


Estos serán los datos de nuestra tabla.
 
Desde la parte superior de Excel, haga clic en Insertar cinta. En el panel Gráficos , ubique y haga clic en el ícono Scatter charter. El ícono se ve así:
 
Icono Gráfico de dispersión de Excel


Seleccione el primer elemento para obtener un gráfico con solo puntos:
 
Varios gráficos de dispersión en Excel


(Si no puede ver el ícono arriba, haga clic en Gráficos recomendados . Cambie a la pestaña Todos los gráficos , luego seleccione XY Scatter ).
 
Aparecerá un nuevo gráfico en su hoja de cálculo. Debe tener un aspecto como este:
 
Un gráfico de dispersión agregado a una hoja de cálculo de Excel


Las cifras en la parte inferior, el eje X, son nuestros años desde 2006. Las cifras en el Eje Y son nuestros valores de ingresos. El primer punto, el del extremo izquierdo, nos dice que hicimos algo más de 12000 en el año 0 (el año 0 es 2006). En el año 1 (2007) hicimos poco menos de 16000. En el año 2 (2008) hicimos algo más de 14000, y así sucesivamente.
 
Todos estos puntos parecen formar una línea suelta que sube desde la izquierda. Puede agregar una línea usted mismo usando el elemento Shapes en el panel de ilustraciones. Lo que habrá hecho entonces es crear una regresión lineal.
 
En lugar de agregar la línea nosotros mismos, sin embargo, Excel puede agregar la línea para nosotros. No solo eso, puede darnos la fórmula que usó para crear la línea. Podemos usar esa fórmula para predecir los ingresos futuros.
 
Haga clic en su gráfico para resaltarlo. Debería ver tres iconos a la derecha, en Excel 2013 y 2016. (Consulte a continuación para Excel 2007 y Excel 2010.) Haga clic en el símbolo Más y marque la casilla de Trendline:
 
La opción Trendline en excel 2013


Cuando marques Trendline , deberías ver una línea aparecer en tu gráfico:
 
Un gráfico de Excel con una línea de tendencia


Para obtener la línea en Excel 2007 y 2010, seleccione su gráfico y luego haga clic en la pestaña Diseño . Desde el panel Análisis , haga clic en la opción Línea de tendencia . Desde el menú Trendline, selecciona Linear Trendline .
 
La línea representa el mejor ajuste de Excel para una regresión lineal. Está tratando de poner todos los puntos que pueda tan cerca de la línea como sea posible.
 
Para ver la ecuación utilizada por Excel, haga clic nuevamente en el símbolo Plus (Excel 2013 y Excel 2016). Luego haga clic en la flecha a la derecha de Trendline. Aparece un nuevo menú. Seleccione Más opciones en la parte inferior:
 
Más opciones de Trendline


Debería ver un panel abierto a la derecha de Excel, como el de la imagen siguiente.
 
Para usuarios de Excel 2007 y 2010, haga clic en la pestaña Diseño nuevamente. Luego haga clic en Trendline en el panel de Análisis . Desde el menú Trendline esta vez, selecciona Más opciones de línea de tendencia . A continuación, verá un cuadro de diálogo con opciones iguales a las de la imagen a continuación.
 
El panel de diálogo Formato Trendline en Excel 2013


La opción Trendline que hemos elegido es Linear . Mire la parte inferior y marque la casilla junto a Mostrar ecuación en el gráfico.
 
Cuando marque la casilla, debería aparecer la siguiente ecuación en su tabla:
 
y = 564.88x + 13604
 
Esto es algo llamado la Ecuación Slope-Intercept. Si recuerdas las lecciones de Matemáticas de la escuela, la ecuación generalmente se escribe así (la "b" al final puede ser una letra diferente, dependiendo de en qué parte del mundo te hayan enseñado Matemáticas):
 
y = mx + b
 
En esta fórmula, la letra "m" es la pendiente (gradiente) de la línea, y la letra "b" es el primer valor en el eje y. La x es un valor en el eje X. Una vez que tenga la pendiente de la línea, un valor para el eje X y el punto de inicio de la línea, puede extender la línea y calcular otros valores en ella. Esta será la letra "y" en la ecuación.
 
Excel ya ha calculado dos valores para nosotros, la "m" y la "b". La "m" (la pendiente) es 564.88 y la "b" es un valor de ingreso de 13604.
 
Para calcular los valores de y, solo necesitamos una "x". La "x" para nosotros será aquellos "Años desde 2006" en nuestra columna B.
 
Haga clic dentro de la celda C10 en su hoja de cálculo, luego. Introduzca la siguiente:
 
= 564.88 * B10 + 13604
 
Presiona la tecla enter y deberías encontrar que Excel tiene un valor de 18123.04. Este es el ingreso predicho para el año 2014. Use Autocompletar para las celdas B11 a B15. El resto de los valores predichos se completará en:
 
Valores futuros agregados con la ecuación Slope-Intercept


Así que Excel está prediciendo que ganaremos 18123.04 en 2014. Para el año 2019, está pronosticando que ganaremos 20947,44.
 
En la siguiente parte de este tutorial, verá cómo extender la línea de tendencia para que estos valores se agreguen a su gráfico.

ANTERIOR  |  INDICE  |  SIGUIENTE