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: MS Excel