Cómo utilizar la herramienta de análisis del Administrador de escenarios de Excel

El mes pasado, usamos Goal Seek para determinar el pago hipotecario más razonable dentro de un presupuesto específico. Este mes, continuamos con el ejemplo de hipoteca utilizando Scenario Manager. Generaremos un informe resumido que nos permitirá comparar todos los posibles términos hipotecarios al mismo tiempo. Luego, cambiaremos un poco el ejemplo calculando el costo total de la hipoteca y los compararemos.

Estoy usando Excel 2013 y Windows 7. Para su comodidad, puede descargar el archivo .xlsx o .xls de ejemplo (que también contiene los ejemplos del mes pasado). Excel 2003 no admite esta función, pero puede guardar el archivo.xlsx archivar como un .xls archivo y seguir viendo los resultados.

Revisión rápida

Las tres herramientas de análisis son herramientas hipotéticas. En otras palabras, puede ver el impacto de cambiar los valores de entrada sin cambiar los datos reales. El artículo del mes pasado le mostró cómo utilizar Goal Seek, una herramienta de análisis hipotético que le indica cómo deben cambiar los valores de entrada para lograr un objetivo específico. El Administrador de escenarios le permite sustituir los valores de entrada por varias celdas (hasta 32). De esta manera, puede ver los resultados de diferentes valores de entrada (o escenarios) al mismo tiempo.

Compare los términos de la hipoteca

Usaremos la calculadora de hipotecas simple que se muestra en Figura A.

Figura A

Usaremos esta sencilla calculadora de hipotecas para demostrar el Administrador de escenarios de Excel.

Referirse a Cuadro A para crear esta hoja si lo desea, o descargar el archivo de ejemplo. Con los valores de entrada actuales del 6%, 180 meses y un capital de $ 200,000, un pago mensual es de $ 1,687.71 y pagará un total de $ 303,788.46.

Cuadro A

Calculadora de hipotecas simple.

Ahora, usemos el Administrador de escenarios para comparar varios conjuntos de intereses y plazos para ver cómo cambia el pago mensual:

1. Seleccione B4: C4 (las celdas de entrada).
2. Haga clic en la pestaña Datos.
3. En el grupo Herramientas de datos, haga clic en el menú desplegable Análisis hipotético y seleccione Administrador de escenarios (Figura B). Figura B
   

   

4. Haga clic en Agregar y asigne un nombre al escenario, como BestCase (Figura C) y haga clic en Aceptar.
   Figura C
   

   

5. A continuación, ingrese los mejores términos que podría obtener para el préstamo, .01 y 120 (Figura D). Cuando desee ver solo un conjunto de valores, haga clic en Aceptar. En este caso, queremos agregar más valores de entrada, así que haga clic en Agregar.
   Figura D
   

   

6. Repita los pasos 4 y 5 para crear cuatro escenarios más (Figura E): Caso peor: .06; 360 AlmostWorstCase: .045; 360 GettingThere: .03; 240 CouldSwingIt: .03; 120
   Figura E
   

   

7. Después de agregar todos los escenarios que desea considerar, haga clic en Resumen.
8. En el cuadro de diálogo resultante, asegúrese de que el control de la celda de resultado muestre E4, la celda que contiene la función de hipoteca, y haga clic en Aceptar (Figura F).
   Figura F
   

   

Tomemos un minuto para revisar los nombres y los valores de entrada; son un poco subjetivos y, en este caso, definitivamente podrían actualizarse. Eso depende de tu perspectiva. Por ejemplo, su escenario BestCase puede ofrecer la tasa de interés más baja durante la menor cantidad de meses, pero el pago no es el más bajo. Si los nombres no son significativos, reconsidere sus objetivos: ¿está buscando el pago mensual más bajo o devolver la menor cantidad de dinero durante la vigencia del préstamo? Al final, los nombres no son tan importantes.

Comparar el pago total

CouldWingIt exige el pago mensual más alto, pero ¿pagará menos a largo plazo? Podemos usar el Administrador de escenarios nuevamente para averiguar:

1. Seleccione B4: C4, haga clic en Datos y luego elija Administrador de escenarios en el menú desplegable Análisis Y si … en el grupo Herramientas de datos.
2. En el cuadro de diálogo resultante, no cambie nada. Ya lo ha configurado todo. En su lugar, haga clic en Resumen.
3. En el cuadro de diálogo final, cambie la celda de resultado a F4 (la fórmula de recuperación) y haga clic en Aceptar (Figura H).
   Figura H
   

   

En este punto, tienes todas las posibilidades frente a ti y puedes tomar la mejor decisión para ti, que puede que no sea ni el mejor ni el peor de los casos, sino algo intermedio. El escenario BestCase tiene un pago mensual elevado, pero devolverá la menor cantidad de dinero. ¡El valor de recuperación del escenario del peor caso es más del doble! AlmostWorstCase tiene el pago más bajo pero una gran recuperación. GettingThere parece ofrecer un pago bajo y una de las recompensas más bajas.

La mayoría de los prestatarios elegirán el pago mensual más asequible para devolver la menor cantidad de dinero. Scenario Manager puede ayudarlo a determinar la mejor opción para su presupuesto si tiene un prestamista que esté dispuesto a negociar los términos.

Comparación rápida

Como mencioné, Excel ofrece tres herramientas de análisis hipotético. Goal Seek toma un resultado y proyecta un resultado para determinar los posibles valores de entrada que reducen ese resultado. Tanto el administrador de escenarios como las tablas de datos usan conjuntos de valores de entrada para proyectar un posible resultado.

A menudo hay muchas posibilidades a considerar. Por ejemplo, incluso con nuestro ejemplo simple, podría agregar el monto principal como un tercer valor de entrada. Quizás la abuela le dé un depósito grande; es posible que desee determinar cómo se suma, especialmente si desea poner parte de ese dinero en un fondo de emergencia en lugar de usarlo todo como depósito. Las posibilidades son casi infinitas, y es por eso que Scenario Manager es una gran herramienta para tener en su caja de herramientas de hipótesis.

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 tablas de datos de Excel.

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

Boletín semanal de Microsoft

Sea el conocedor de Microsoft de su empresa leyendo estos consejos, trucos y hojas de trucos de Windows y Office. Entrega lunes y miércoles

Nos vemos pronto!