Uso de la herramienta de búsqueda de objetivos de análisis hipotético de Excel

Aunque la larga lista de funciones de Excel es una de las características más atractivas de la aplicación de hoja de cálculo de Microsoft, hay algunas gemas infrautilizadas que mejoran estas funciones. Una herramienta que a menudo se pasa por alto es el análisis What-If.

La herramienta de análisis What-If(What-If Analysis) de Excel se divide en tres componentes principales. La parte discutida aquí es la poderosa función Goal Seek que le permite trabajar hacia atrás desde una función y determinar las entradas necesarias para obtener el resultado deseado de una fórmula en una celda. Siga leyendo para aprender a usar la herramienta de búsqueda de objetivos de análisis hipotético de Excel.(What-If Analysis Goal Seek)

Ejemplo de herramienta de búsqueda de objetivos de Excel

Suponga que desea obtener un préstamo hipotecario para comprar una casa y le preocupa cómo la tasa de interés del préstamo afectará los pagos anuales. El monto de la hipoteca es de $100,000 y pagará el préstamo en el transcurso de 30 años.

Con la función PMT(PMT) de Excel , puede averiguar fácilmente cuáles serían los pagos anuales si la tasa de interés fuera del 0%. La hoja de cálculo probablemente se vería así:

Un simple cálculo de pago de hipoteca en Excel

La celda en A2 representa la tasa de interés anual, la celda en B2 es la duración del préstamo en años y la celda en C2 es el monto del préstamo hipotecario. La fórmula en D2 es:

=PMT(A2,B2,C2)

y representa los pagos anuales de una hipoteca de $100,000 a 30 años al 0% de interés. Observe(Notice) que la cifra en D2 es negativa ya que Excel asume que los pagos son un flujo de efectivo negativo de su posición financiera.

Desafortunadamente, ningún prestamista hipotecario le prestará $ 100,000 al 0% de interés. Suponga(Suppose) que hace algunos cálculos y descubre que puede pagar $6,000 por año en pagos de hipoteca. Ahora se pregunta cuál es la tasa de interés más alta que puede aceptar para el préstamo para asegurarse de no terminar pagando más de $6,000 por año.

Muchas personas en esta situación simplemente comenzarían a escribir números en la celda A2 hasta que la cifra en D2 llegara a aproximadamente $6,000. Sin embargo, puede hacer que Excel haga el trabajo por usted utilizando la herramienta Búsqueda de objetivos de análisis hipotético . (Analysis Goal Seek)Esencialmente, hará que Excel trabaje hacia atrás desde el resultado en D4 hasta que llegue a una tasa de interés que satisfaga su pago máximo de $6,000.

Comience haciendo clic en la pestaña Datos en la (Data)cinta(Ribbon) y ubicando el botón Análisis hipotético en la sección (What-If Analysis)Herramientas de datos(Data Tools) . Haga clic en el botón Análisis de hipótesis y (What-If Analysis)seleccione Buscar objetivo(Goal Seek) en el menú.

Herramienta de búsqueda de objetivos de análisis hipotético de Excel

Excel abre una pequeña ventana y le pide que ingrese solo tres variables. La variable Establecer celda(Set Cell) debe ser una celda que contenga una fórmula. En nuestro ejemplo aquí, es D2 . La variable Hasta el valor(To Value) es la cantidad que desea que tenga la celda en D2 al final del análisis.

Para nosotros, es -6,000 . Recuerde que Excel ve los pagos como un flujo de caja negativo. La variable Al cambiar de celda(By Changing Cell) es la tasa de interés que desea que Excel encuentre por usted para que la hipoteca de $100,000 le cueste solo $6,000 por año. Entonces, usa la celda A2 .

Variables de búsqueda de objetivos de Excel

Haga clic en el botón Aceptar(OK) y puede notar que Excel muestra un montón de números en las celdas respectivas hasta que las iteraciones finalmente convergen en un número final. En nuestro caso, la celda en A2 ahora debería leer alrededor de 4.31%.

Resultados de un análisis de búsqueda de objetivos hipotéticos de Excel

Este análisis nos dice que para no gastar más de $6,000 por año en una hipoteca de $100,000 a 30 años, necesita asegurar el préstamo a no más del 4.31%. Si desea continuar haciendo análisis hipotéticos, puede probar diferentes combinaciones de números y variables para explorar las opciones que tiene al tratar de asegurar una buena tasa de interés en una hipoteca.

La herramienta de búsqueda de objetivos de análisis hipotético(What-If Analysis Goal Seek) de Excel es un poderoso complemento para las diversas funciones y fórmulas que se encuentran en la hoja de cálculo típica. Al trabajar hacia atrás a partir de los resultados de una fórmula en una celda, puede explorar las diferentes variables en sus cálculos con mayor claridad.



About the author

Ingeniero audiófilo y especialista en productos de audio con más de 10 años de experiencia. Me especializo en la creación de altavoces y auriculares para música de calidad de principio a fin. Soy un experto en la solución de problemas de audio, así como en el diseño de nuevos sistemas de altavoces y auriculares. Mi experiencia va más allá de hacer buenos productos; También me apasiona ayudar a otros a ser lo mejor posible, ya sea a través de la educación o el servicio comunitario.



Related posts