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 poco utilizadas que mejoran estas funciones. Una herramienta que a menudo se pasa por alto es el análisis de qué pasa si.
La herramienta de análisis What-If de Excel se divide en tres componentes principales. La parte discutida aquí es la poderosa función de búsqueda de objetivos que le permite trabajar hacia atrás desde una función y determinar las entradas necesarias para obtener la salida deseada de una fórmula en una celda. Sigue leyendo para aprender a usar la herramienta de búsqueda de objetivos del análisis What-If de Excel.
Ejemplo de la 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 usted pagará el préstamo en el transcurso de 30 años.
Usando la función PMT de Excel, puede determinar 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 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 por $ 100, 000 a un interés del 0%. 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 a un interés del 0%. Supongamos que hace algunas averiguaciones y descubre que puede pagar $ 6, 000 por año en pagos de hipotecas. 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 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 alcance aproximadamente $ 6, 000. Sin embargo, puede hacer que Excel haga el trabajo por usted mediante el uso de la herramienta de búsqueda de objetivos del análisis Qué pasa si. Esencialmente, hará que Excel trabaje hacia atrás desde el resultado en D4 hasta que alcance 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 de qué-si en la sección Herramientas de datos . Haga clic en el botón Análisis de " Qué pasa si " y elija Búsqueda de objetivos 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 To Value es la cantidad que desea que la celda en D2 esté al final del análisis.
Para nosotros, es -6, 000 . Recuerde que Excel ve los pagos como un flujo de efectivo negativo. La variable Por cambio de celda es la tasa de interés que desea que Excel encuentre para usted, por lo que la hipoteca de $ 100, 000 le costará solo $ 6, 000 por año. Por lo tanto, utilice la celda A2 .
Haga clic en el botón Aceptar y podrá observar que Excel muestra varios números en las celdas respectivas hasta que las iteraciones finalmente converjan 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 a 30 años, $ 100, 000, usted necesita asegurar el préstamo a no más de 4.31%. Si desea continuar con los análisis de simulación, puede probar diferentes combinaciones de números y variables para explorar las opciones que tiene cuando intenta obtener una buena tasa de interés sobre una hipoteca.
La herramienta de búsqueda de objetivos What-If Analysis es un complemento poderoso de 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.
