sábado, octubre 18, 2008

Manejo de escenarios con Excel

Uno de los usos frecuentes de Excel es crear modelos de predicción de resultados o presupuestos según van cambiando ciertas variables. En ingles existe el término "what if" para describir este tipo de análisis. En el proceso de escribir esta nota he buscado un equivalente en castellano a esta expresión. La ayuda en línea de Excel traduce el término a "y si", que por algún motivo me parece menos acertado que su equivalente literal en inglés. Tal vez sería mas expresivo usar "qué pasaría si", pero supongo que a los traductores les debe haber parecido excesivamente largo.
Mi inclinación personal es usar la expresión "análisis de escenarios" y precisamente Escenarios es el nombre de una las herramientas que Excel pone a disposición de los usuarios.

Según mi experiencia personal, esta herramienta es poco conocida y aún menos usada a pesar que puede ahorrarnos tiempo y errores.

De acuerdo a la ayuda de Excel "Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir automáticamente en la hoja de cálculo"



La funcionalidad se encuentra en el menú Herramientas, junto con Buscar Objetivo y Solver. Esto no es casualidad ya que estas herramientas también permiten realizar análisis de sensibilidad y de escenarios. Bajo el menú Datos se encuentra otra herramienta para realizar análisis de sensibilidad, las Tablas.

Antes de usar la herramienta Escenarios necesitamos construir un modelo cuyos resultados sean sensibles al cambio en una serie de variables. Esta herramienta acepta hasta 32 variables.

Veamos un uso posible de Escenarios con un ejemplo. Supongamos una empresa cuyos ingresos son en euros (todas sus ventas son en Europa), los materiales son adquiridos en dólares y el resto de los ingresos y gastos son en pesos. El cuadro de pérdidas y ganancias de semejante empresa será afectado por los cambios en las cotizaciones de las monedas, aún si no hay cambios en la actividad real de la empresa.

Queremos investigar cuáles serán los resultados del año para este empresa de acuerdo a distintos escenarios para las tasa de cambio. El modelo que construimos será éste:



En las celdas B2 y B3 tenemos las tasas de cambio actuales del euro y el dólar. Estas celdas serán las variables en nuestro modelo.
En el cuadro de pérdidas y ganancias proyectado tenemos los montos en la moneda original en la columna C y los montos convertidos a pesos en la columna D.

Con las tasas de cambio actuales, predecimos un porcentaje de beneficio bruto del 42%. ¿Que pasaría si esta tasa cambiaran? Supongamos tres posibilidades (escenarios) posibles:

1 - euro = 4.50; dólar = 3.30
2 - euro = 4.10; dólar = 3.10
3 - euro = 4.10; dólar = 3.30

Para crear los escenarios con la herramientas abrimos el menú Herramientas-Escenarios



Apretamos el botón Crear e ingresamos los datos. Al primer escenario lo llamaremos Devaluación, al segundo Revaluación y al tercero Mixto.



Al apretar el botón Aceptar se abre este formulario donde ingresamos los valores de las variables correspondientes al escenario



Apretamos aceptar y en el formulario que se abre volvemos a apretar Agregar para insertar el próximo escenario (Devaluación). Volvemos a realizar el proceso para agregar Mixto.



Cuando terminamos de ingresar los datos de todos los escenarios podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el escenario Revaluación y apretamos Mostrar, veremos en la hoja que el porcentaje de la ganancia bruto cae al 39%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja.
Hay que tener en cuenta que al cerrar el formulario, las celdas variables conservan el valor del último escenario elegido.
Pero la real utilidad de usar Escenarios se evidencia al apretar el botón Resumen. Se abre este formulario



En la ventanilla Celdas de Resultado ponemos las celdas que queremos que aparezcan en el resumen. Si queremos que aparezca todo el total de ingresos, el total de gastos y el beneficio bruto, elegimos las celdas D10, D16 y D17



Al apretar Aceptar Excel crea una nueva hoja donde podemos ver un cuadro que compara los tres escenarios con la situación actual



Uno de los inconvenientes es que Excel usa la dirección de las celdas como nombre de la fila, lo que hace que la tabla sea prácticamente ilegible, es especial si se trata de un modelo con muchas líneas.
La técnica para superar este inconveniente es poner nombres a las celdas que estamos interesados en mostrar


Beneficio_bruto = Hoja1!$D$17
Dólar = Hoja1!$B$3
Euro = Hoja1!$B$2
Total_de_Gastos = Hoja1!$D$16
Total_de_Ingresos = Hoja1!$D$10

Volvemos a generar el resumen y obtenemos



Escenarios permite consolidar escenarios de distintas hojas, a condición que los modelos sean idénticos. Para combinar escenarios hay que apretar el botón Combinar que abre un formulario donde ingresamos los libros y las hojas que queremos combinar.
Como habrán notado, la hoja Resumen usa Agrupación y Esquema para ocultar ciertas filas. Si mostramos estas filas podemos ver el nombre del creador del escenario y la fecha en que fue creado



Dado que Excel crea una hoja cada vez que activamos la opción Resumen, podemos crear varias hojas que guarden distintos escenarios.
También podemos crear una tabla dinámica del escenario usando la opción Informe de tabla dinámica en el formulario de Resumen.

Technorati Tags:

12 comentarios:

  1. Hola Jorge! Estupenda entrada la de escenarios. Pero cuando te refieres a :

    Uno de los inconvenientes es que Excel usa la dirección de las celdas como nombre de la fila, lo que hace que la tabla sea prácticamente ilegible, es especial si se trata de un modelo con muchas líneas.
    La técnica para superar este inconveniente es poner nombres a las celdas que estamos interesados en mostrar


    Beneficio_bruto = Hoja1!$D$17
    Dólar = Hoja1!$B$3
    Euro = Hoja1!$B$2
    Total_de_Gastos = Hoja1!$D$16
    Total_de_Ingresos = Hoja1!$D$10


    Donde escribo esta equivalencia, para que aparezca en la hoja "resumen de escenario". Gracias!

    ResponderBorrar
  2. Hola

    se trata de "nombres", una herramienta que hay en Excel para definir rangos.
    Para definir los nombres tienes que abrir el menú Herramientas--Nombres--Definir.

    ResponderBorrar
  3. Excelente artículo, me ayudo muchisimo en mi comprensión de Escenarios en Excel.

    No podía no dejar de agradecer tu enorme colaboración hacia los internautas... Muchas Gracias!

    Salu2

    Juan, un estudiante agradecido :)

    ResponderBorrar
  4. Salud amigos, en "mi EXCEL", nombres no esta bajo herramientas,sino en Insertar. Algo que noto, las variables tal y como estan definidas en los ejemplos: no trabajan

    ResponderBorrar
  5. No sólo en tu Excel, en el de todos! Se trata de un error de imprenta.
    El ejemplo fue desarrollado sobre un modelo que trabaja. Si logro encontrar el archivo, pondré un enlace para descargarlo.

    ResponderBorrar
  6. Buenas tartdes Jorge,
    Recorri su blogs y lo felicito por su buen apoyo para poder observar como cambia el escenario y la apilaccion es formilable.
    quiero preguntarte algo mas como halla el % para gastos de produccion lo demas te comprendi.
    Un abarzo

    ResponderBorrar
  7. Con una sencilla operación. Por ejemplo, la celda E8 contiene la fórmula
    =D8/D10

    ResponderBorrar
  8. Jorge,
    Excelente post sobre escenarios. Para hacer mi consulta, voy a utilizar tu ejemplo. Yo quiero en un gráfico ver como varia el beneficio con la cotización del dolar (o el euro, da lo mismo). Por lo tanto imagino una columna con muchas cotizaciones en pasos de, diagmos 5 centavos. Entonces a la derecha una segunda columna con el beneficio. De esta manera podría graficar la relacion entre ellos. El problea es que si quiero 100 cotizaciones, veo poco practico generar 100 escenarios. No se si quedo clara la consulta.
    Muchas gracias!!
    John Graue

    ResponderBorrar
  9. John,
    no me parece que usar escenarios tenga alguna ventaja sobre usar sencillamente una tabla de dos columnas y graficarla.

    ResponderBorrar
  10. Jorge, me parece que me expliqué mal. Lo que yo planteo es: Teniendo una función (beneficio) dependiendo de una o mas variables (Dolar o Euro) de una manera no muy directa (por ahi en este ejemplo la relación es bastante directa) lo que yo quiero es generar varias entradas al sistema (varia el dolar con unos 100 valores) y obtener las correspondientes salidas (100 valores de Beneficio). Para luego recien graficarlas. Entiendo que para 3 o 4 variaciones, es practico generar escenarios, pero que pasa cuando son muchos mas (digamos unos 100). No se como enviarte un archivo de ejemplo. Muchas gracias!!!!

    ResponderBorrar
  11. John,
    sigo sin entender por qué usar escenarios. Para generar un gráfico necesitamos una serie de datos y una tabla es la mejor forma de ordenar los datos.
    Para mandarme el archivo, fijate lo que escribo en el enlace Ayuda (en la parte superior del blog).

    ResponderBorrar
  12. En Excel encontramos cualquier cantidad de soluciones a problemas relacionados con el mundo de
    los negocios. Saber manejar en Excel Solver, Tabla de de datos, Escenarios y Buscar Objetivo es muy
    importante y nos sacará de muchos apuros y hará nuestro trabajo más efectivo y dinámico, al mismo
    tiempo que afianzará nuestra valía en este mundo del dinero y negocio empresarial.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.