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 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.
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.
Usando la función 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í:
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:
=PAGO(A2,B2,C2)
Y representa los pagos anuales de una hipoteca de €100,000 a 30 años al 0% de interés. Observe 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 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. 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 cinta de opciones y localice el botón Análisis hipotético en la sección Herramientas de datos. Haga clic en el botón Análisis de hipótesis y seleccione Buscar objetivo en el menú.
Excel abre una pequeña ventana y le pide que ingrese solo tres variables. La variable Establecer celda debe ser una celda que contenga una fórmula. En nuestro ejemplo aquí, es D2. La variable Hasta el valor 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 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.
Haga clic en el botón aceptar 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%.
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 garantizar 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 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.
