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, existen algunas gemas subutilizadas que mejoran estas funciones. Una herramienta que a menudo se pasa por alto es el Análisis Qué-si.
La herramienta de Análisis Qué-si de Excel se divide en tres componentes principales. La parte discutida aquí es la poderosa característica de búsqueda de objetivos 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 de Qué-si 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.
Utilizando la función PMT de Excel, puede calcular fácilmente cuáles serían los pagos anuales si la tasa de interés fuera 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 longitud del préstamo en años, y la celda en C2 es la cantidad del préstamo hipotecario. La fórmula en D2 es:
= PMT (A2, B2, C2)
y representa los pagos anuales de una hipoteca a 30 años y $ 100,000 en 0% de interés. Observe que la cifra en D2 es negativa, ya que Excel asume que los pagos son un flujo de caja negativo de su posición financiera.
Desafortunadamente, ningún prestamista hipotecario le prestará $ 100,000 al 0% de interés. Supongamos que calcula y descubre que puede permitirse pagar $ 6,000 por año en pagos de hipoteca. Ahora se está preguntando cuál es la tasa de interés más alta que puede tomar para el préstamo, para asegurarse de que no termine 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 alcanzó aproximadamente $ 6,000. Sin embargo, puede hacer que Excel haga el trabajo por usted utilizando la herramienta Buscar objetivo de análisis de si ... 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 Datosen la Cinta de opciones y ubicando el botón Análisis de qué ocurre sien la sección Herramientas de datos. Haga clic en el botón Análisis de qué ocurre siy seleccione Búsqueda de objetivosen el menú.
Excel abre una pequeña ventana y le pide que ingrese solo tres variables. La variable Establecer celdadebe ser una celda que contenga una fórmula. En nuestro ejemplo aquí, es D2. La variable A valores la cantidad que desea que la celda en D2esté al final del análisis.
Para nosotros, es -6,000. Recuerde que Excel considera los pagos como un flujo de caja negativo. La variable Al cambiar la celdaes la tasa de interés que desea que Excel encuentre para usted, de modo que la hipoteca de $ 100,000 le costará solo $ 6,000 por año. Por lo tanto, use la celda A2.
Haga clic en el botón Aceptary puede observar 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 aproximadamente 4.31%.
Este análisis nos dice que para no gastar más de $ 6,000 por año en una hipoteca a 30 años y $ 100,000, necesita asegurar el préstamo a no más de 4.31%. Si desea continuar haciendo análisis de y si, puede probar diferentes combinaciones de números y variables para explorar las opciones que tiene cuando intenta asegurar una buena tasa de interés en una hipoteca.
El análisis de Qué-si de Excel La herramienta Goal Seek es un poderoso complemento para las diversas funciones y fórmulas que se encuentran en la típica hoja de cálculo. Al trabajar hacia atrás desde los resultados de una fórmula en una celda, puede explorar las diferentes variables en sus cálculos con mayor claridad.