Cómo utilizar la herramienta de análisis Goal Seek de Excel

Si me preguntas, las herramientas de análisis hipotético de Excel están infrautilizadas. A menudo respondo a los lectores con “¿Has probado una tabla de datos?” Invariablemente, no saben cómo implementar la función; a veces, ni siquiera saben qué es una tabla de datos. Entonces, durante los próximos meses, le presentaré las herramientas de análisis hipotético de Excel:

• Buscar objetivo
• Administrador de escenarios
• Tabla de datos

Revisaremos las opciones, discutiremos cuándo usarlas y luego implementaremos las tres herramientas. Cada ejemplo será simple, pero incluirán suficiente información para que pueda comenzar a usar estas funciones por su cuenta.

Es posible que se pregunte qué es una herramienta hipotética; estas herramientas le muestran el impacto de realizar un cambio sin afectar los datos reales. Este mes, estamos explorando Goal Seek. Esta herramienta le dice cómo deben cambiar las variables para lograr un objetivo específico.

Estoy usando Excel 2013 y Windows 7. Para su comodidad, puede descargar el ejemplo .xlsx o .xls archivo.

Ejemplo 1

Técnicamente, Goal Seek es un proceso de cálculo de un valor mediante la realización de análisis hipotéticos en un conjunto de valores dado. Para nuestros propósitos, la función Buscar objetivo de Excel le permite ajustar un valor utilizado en una fórmula para lograr un objetivo específico. O, dicho de otra manera, Goal Seek determina los valores de entrada necesarios para lograr un objetivo específico. Use Goal Seek cuando no tenga un valor exacto para usar.

Calcular los términos de un préstamo es un buen ejemplo de una situación hipotética. ¿Qué pasa si el pago que ofrece su prestamista es demasiado alto para su presupuesto? El uso de Goal Seek puede ayudar al evaluar la fórmula de pago y devolver nuevos valores para la tasa, el plazo e incluso el capital para ajustarse al pago deseado. Figura A muestra una calculadora de hipotecas simple donde se conocen todas las variables: para pagar un préstamo del 6% sobre $ 200,000 durante 15 años, su pago mensual debe ser de $ 1,687.71. (Los depósitos son importantes, pero no complicaremos este ejemplo con más variables de las que necesitamos).

Figura A

Una simple calculadora de hipotecas.

Quiere la casa, pero su pago ideal es de $ 1,200. Podría pasar mucho tiempo introduciendo nuevos valores de tasa y plazo hasta que tenga suerte. Una mejor solución es utilizar Goal Seek para determinar la tasa y el plazo necesarios para reducir su pago. Primero, calculemos la tasa necesaria, de la siguiente manera:

1. Seleccione la celda que contiene la fórmula. En este caso, eso es E4.
2. Haga clic en la pestaña Datos. En el grupo Herramientas de datos, haga clic en Análisis hipotético y elija Buscar objetivo (Figura B). En Excel 2003, Goal Seek está en el menú Herramientas.
   Figura B
   

   

3. En el cuadro de diálogo resultante, encontrará tres valores de entrada. El valor predeterminado de Establecer celda es la celda seleccionada, E4. (Esa es la celda que contiene la función PMT ()). El valor Hasta debe ser igual a su objetivo. En este caso, ingrese el pago que puede pagar, -1200 (el valor es negativo porque es un pago). Al cambiar el valor de la celda, apunta al valor de entrada que desea ajustar. Ingrese B4 (la tasa de interés) en ese control (Figura C).
   Figura C
   

   

4. Haga clic en Aceptar y Excel iterará (usando la configuración incorporada) hasta que encuentre el mejor resultado (Figura D). Necesita una tasa de interés del 1% para reducir su pago a $ 1,200.
   Figura D
   

   

Si Buscar objetivo no funciona, vuelva a visitar el valor Hasta en el paso 3. Específicamente, debe ingresar el pago deseado como un valor negativo. Para volver a los valores originales, haga clic en Cancelar en el paso 4 en lugar de hacer clic en Aceptar después de ver el resultado en el cuadro de diálogo. O puede hacer clic en Deshacer en la barra de herramientas de acceso rápido (QAT) después de hacer clic en Aceptar. Si Goal Seek no puede encontrar una solución para usted, Excel habilita los botones de Paso y Pausa, lo que le permite forzar más iteraciones para reducir la brecha entre los valores actuales y su objetivo. (Esta es una función más avanzada que no visitaremos en este artículo, pero debe saber que está disponible).

Ahora, probablemente sepa que también puede extender el plazo del préstamo para reducir el pago, así que usemos Goal Seek para determinar un nuevo plazo de la siguiente manera:

1. Seleccione E4, haga clic en la pestaña Datos, haga clic en Análisis hipotético y elija Buscar objetivo. En Excel 2003, elija Buscar objetivo en el menú Herramientas.
2. Introduzca -1200 en el control de valor A y C4 en el control de celda Cambiando.
3. Haga clic en Aceptar para ver los resultados (Figura E).
   Figura E
   

   

Este ajuste es tan drástico como el ajuste de tarifas. Para obtener el pago que desea con un interés del 6%, ¡debe duplicar la duración de su préstamo! Claramente, la mejor ruta es buscar una tasa de interés más baja si es posible. Si eso no es posible, una casa con un precio más bajo podría estar en su futuro. ¡Espere! Intentemos eso también:

1. Ingrese el valor del término de 180 en C4 (si es necesario).
2. Seleccione E4, haga clic en la pestaña Datos, haga clic en Análisis hipotético y elija Buscar objetivo. En Excel 2003, elija Buscar objetivo en el menú Herramientas.
3. Ingrese -1200 como el valor Para, ingrese D4 como el valor Cambiando la celda y haga clic en Aceptar (Figura F).
   Figura F
   

   

Goal Seek no le dio la solución, solo usted puede decidir qué hacer con la información que proporciona Goal Seek. Goal Seek ofrece una visión rápida.

Ejemplo 2

Veamos otro ejemplo. Es tan simple como el primero, pero se inclina más hacia una situación empresarial real que el primer ejemplo. Figura G muestra una simple hoja de ganancias trimestral, y la historia es un poco sombría: las cifras del verano han bajado y solo le queda una cuarta parte para cumplir su meta de $ 50,000 para mantenerse solvente.

Figura G

Hoja de ganancias trimestral con una meta de ganancia neta de $ 50,000.

Con Goal Seek, puede conocer rápidamente cuáles deben ser sus ingresos del cuarto trimestre para alcanzar su objetivo de ganancias de $ 50,000. Para lograr esto, haga lo siguiente:

1. Seleccione E7, la celda que contiene la fórmula (una función SUMA () simple) que expresa su objetivo de $ 50 000, aunque todavía no devuelve $ 50 000.
2. Haga clic en la pestaña Datos, haga clic en Análisis Y si … y luego elija Buscar objetivo. En Excel 2003, elija Buscar objetivo en el menú Herramientas.
3. En el cuadro de diálogo resultante, ingrese 50000 como el valor de Para.
4. Introduzca C6 como el valor de la celda Cambiando (Figura H).
   Figura H
   

   

5. Haga clic en Aceptar (Figura I).
   Figura I
   

   

Con la ayuda de Goal Seek, puede ver rápidamente que su último trimestre debe generar $ 76,667 para alcanzar su meta de $ 50,000. Saber de antemano lo que enfrenta puede ayudarlo a determinar estrategias nuevas, quizás más agresivas, para mejorar los ingresos.

Cumplir metas

La herramienta de análisis Goal Seek de Excel es fácil de usar y puede ayudarlo a tomar decisiones para el futuro basadas en los datos actuales. ¡Gracias a Goal Seek, el futuro no es tan impredecible como parece!

Envíame tu pregunta sobre Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantía. Cuando se comunique conmigo, sea lo más específico posible. Por ejemplo, “Solucione el problema de mi libro y corrija el problema” probablemente no obtenga una respuesta, pero “¿Puede decirme por qué esta fórmula no arroja los resultados esperados?” podría. Mencione la aplicación y la versión que está utilizando. TechRepublic no me reembolsa mi tiempo o experiencia, ni pido una tarifa a los lectores. Puede contactarme en [email protected].

Para obtener un artículo posterior sobre este tema, lea: Cómo utilizar la herramienta de análisis de Excel Scenario Manager.

Sigue navegando por nuestra web viendo más artículos.