Microsoft

Cómo usar la herramienta de análisis de búsqueda de objetivos de Excel

Si me pregunta, las herramientas de análisis hipotético de Excel están infrautilizadas. A menudo respondo a los lectores con «¿Has probado las hojas de datos?» No siempre saben cómo implementar esa función; a veces, ni siquiera saben qué es una hoja de datos. Entonces, en los próximos meses, les presentaré las herramientas de análisis hipotético de Excel:

  • búsqueda de objetivos
  • director de escena
  • ficha de datos

Revisaremos estas opciones, discutiremos cuándo usarlas y luego implementaremos las tres herramientas. Cada ejemplo es simple, pero contendrán suficiente información para que pueda comenzar a usar las funciones usted mismo.

Puede preguntar qué son las herramientas de análisis hipotético; estas herramientas le muestran el impacto de realizar cambios sin afectar los datos reales. Este mes, estamos explorando Goal Seek. La 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 documento.

Ejemplo 1

Técnicamente, Goal Seek es un proceso de cálculo de valores mediante la realización de un análisis hipotético en un conjunto de valores determinado. Para nuestros propósitos, la función de búsqueda de objetivos de Excel le permite ajustar los valores utilizados en las fórmulas para lograr un objetivo específico. O, en otras palabras, Goal Seek determina los valores de entrada necesarios para lograr un objetivo específico. Use Buscar objetivo cuando no tenga el valor exacto para usar.

Calcular las condiciones de un préstamo es un buen ejemplo de una situación hipotética. ¿Qué pasa si el pago de su prestamista es demasiado alto para su presupuesto? Use Goal Seek para ayudar a evaluar las fórmulas de pago y devolver nuevos valores para la tasa de interés, el plazo e incluso el capital para adaptarse a su pago deseado. Figura A Se muestra una calculadora de hipoteca simple donde se conocen todas las variables: Para pagar un préstamo del 6% sobre $200,000 durante 15 años, sus pagos mensuales deben ser de $1,687.71. (El depósito es importante, pero no complicaremos este ejemplo con más variables de las que necesitamos).

LEER  Windows PowerToys: hoja de trucos

Figura A

20150650

Una sencilla calculadora de hipotecas.

Quiere una casa, pero el pago ideal es $1,200. Puede pasar mucho tiempo insertando nuevas tasas de interés y valores de plazo hasta que tenga suerte. Una mejor solución es usar Goal Seek para determinar la tasa y el plazo requerido para reducir los pagos. Primero, calculemos las tasas necesarias de la siguiente manera:

  1. Seleccione la celda que contiene la fórmula. En este caso, 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 Búsqueda de destino (Figura B). En Excel 2003, Goal Seek está en el menú Herramientas.
    Figura B
    20150651
  3. En el cuadro de diálogo resultante, encontrará tres valores de entrada. Establece el valor de la celda por defecto en la celda E4 seleccionada. (Esta es la celda que contiene la función PMT()). El valor Para debe ser igual a su objetivo. En este caso, ingrese el pago que puede pagar, -1200 (el valor es negativo porque es un pago). Señale el valor de entrada que desea ajustar cambiando el valor de la celda. Introduzca B4 (tasa de interés) en este control (Figura C).
    Figura C
    20150652
  4. Haga clic en Aceptar y Excel iterará (usando la configuración integrada) 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
    20150653

Si Goal Seek no funciona, vuelva a visitar el valor Para en el paso 3. Específicamente, debe ingresar el pago requerido como un valor negativo. Para volver al valor original, haga clic en Cancelar en el paso 4 en lugar de hacer clic en Aceptar después de ver los resultados en el cuadro de diálogo. Como alternativa, 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 Paso y Pausa, que le permiten forzar más iteraciones para cerrar la brecha entre el valor actual y el objetivo. (Esta es una función más avanzada que no trataremos en este artículo, pero debe saber que está disponible).

Ahora, probablemente sepa que también puede extender el plazo de su préstamo para reducir sus pagos, así que usemos Goal Seek para determinar un nuevo plazo como este:

  1. Seleccione E4, haga clic en la pestaña Datos, haga clic en Análisis hipotético y seleccione Búsqueda de destino. En Excel 2003, elija Búsqueda de destino en el menú Herramientas.
  2. Ingrese -1200 en el control Hasta el valor y C4 en el control Por celda de cambio.
  3. Haga clic en Aceptar para ver los resultados (Figura E).
    Figura E
    20150654

Este ajuste es tan drástico como un ajuste de la tasa de interés. Para obtener el pago que desea al 6% de interés, ¡debe duplicar el plazo del préstamo! Obviamente, la mejor manera es buscar tasas de interés más bajas tanto como sea posible. Si eso no es posible, una casa menos costosa puede estar en su futuro. ¡Espere! Probemos también:

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

Goal Seek no tiene una solución para usted: solo usted puede decidir cómo utilizar la información proporcionada por Goal Seek. Goal Seek proporciona información rápida.

Ejemplo 2

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

Figura G

20150656

Estado de resultados trimestral con una meta de utilidad neta de $50,000.

Con Goal Seek, puede ver rápidamente qué ingresos del cuarto trimestre deben alcanzar el objetivo de ganancias de $50,000. Para hacer esto, haga lo siguiente:

  1. Seleccione E7, la celda que contiene la fórmula (una función SUM() simple) representa su objetivo de $50,000, aunque no haya devuelto $50,000.
  2. Haga clic en la pestaña Datos, haga clic en Análisis hipotético y seleccione Búsqueda dirigida. En Excel 2003, elija Búsqueda de destino en el menú Herramientas.
  3. En el cuadro de diálogo que aparece, ingrese 50000 para el valor Hasta.
  4. Ingrese C6 como el valor de la celda cambiando (Figura H).
    Figura H
    20150657
  5. Haga clic en Aceptar (Figura 1).
    Figura 1
    20150658

Con la ayuda de Goal Seek, puede ver rápidamente que se tuvieron que generar $ 76,667 el último trimestre para alcanzar el objetivo de $ 50,000. Conocer de antemano la situación a la que te enfrentas puede ayudarte a identificar estrategias nuevas y quizás más agresivas para aumentar tus ingresos.

Lograr objetivos

La herramienta de análisis Goal Seek de Excel es fácil de usar y lo ayuda a tomar decisiones para el futuro en función de los datos actuales. ¡Gracias a Goal Seek, el futuro no es tan impredecible como podrías pensar!

Enviarme preguntas sobre Office

Intentaré responder a las preguntas de los lectores lo mejor que pueda, pero no hay garantías. Cuando se comunique conmigo, sea lo más específico posible. Por ejemplo, «Por favor, resuelva mi libro de trabajo y solucione el problema» podría no obtener una respuesta, pero «¿Puede decirme por qué esta fórmula no devuelve el resultado esperado?», sí. Mencione la aplicación y la versión que está utilizando. Tecnopedia no me reembolsará por mi tiempo o experiencia, ni se me facturará a los lectores. Puede ponerse en contacto conmigo en [email protected].

Para un artículo de seguimiento sobre este tema, lea: Cómo usar la herramienta de análisis del administrador de escenarios de Excel.

LEER  Cómo concatenar valores en una sola columna de Excel en una sola fila

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Botón volver arriba