sábado, 8 de octubre de 2011

Administrar escenarios en Excel


El "Administrador de escenarios" de Excel es una buena herramienta de análisis de datos con multiples aplicaciones en el ámbito de la Dirección Financiera o Comercial. Es aplicable a cuentas de resultados, presupuestos y en general, cualquier contexto en el que haya que operar con varios parámetros.



Para conocer su funcionamiento, vamos a descargar una plantilla sobre la que practicaremos:


Si vamos a la hoja "Escenarios", podemos ver una tabla similar a la de la imagen de abajo, con la cuenta de resultados previsional a 5 años (simplificada para el ejercicio) de una empresa. También podemos ver más arriba un cuadro con 3 parámetros supuestos referidos al crecimiento que se espera experimente la empresa y a la evolución del Índice de Precios al Consumo:


Las celdas del año 2 contienen fórmulas que calculan el crecimiento respecto de los valores de las celdas del año 1. Las celdas del año 3 contienen fórmulas que hacen lo propio respecto de los valores de las celdas del año 2..... y así sucesivamente. Por lo tanto las únicas celdas de la tabla de abajo que contienen valores (no fórmulas) son las del año 1.

Es una imagen muy frecuente en el departamento financiero de cualquier pyme o bien en el plan de viabilidad económica de cualquier emprendedor, pero imaginemos que el director financiero o el gerente de la empresa necesita valorar distintas situaciones que se pudieran dar debido a la coyuntura económica y/o a la propia empresa, variando los valores de algunos parámetros... ¿cómo se podría hacer esta simulación?.

Supongamos que las tablas que presentamos anteriormente conforman un "escenario moderado" y el gerente necesita estudiar otros posibles escenarios (uno optimista y otro pesimista) para poder prever una toma de decisiones en todos los casos:


1) Hagamos click en la "Ficha Datos", después en el botón "Análisis Y si" (que hay dentro del grupo"Herramientas de datos") y después en "Administrador de escenarios...". Obtendremos el cuadro de diálogo:


... donde tendremos que agregar uno a uno, los "escenarios" que queremos estudiar, empezando por el  moderado (que es el que aparece de partida en las tablas) y terminando con el pesimista y optimista.

2) Hacemos click en "Agregar" e introducimos en el siguiente cuadro de diálogo que emerge, los siguientes datos y referencias:

 

En el "Nombre de escenario" podemos poner libremente un nombre con el que queremos identificar el escenario.

En "Celdas cambiantes", una vez borramos la referencia que aparece por defecto, presionamos CTRL y con el ratón seleccionamos el rango de celdas H3:H5 y, sin haber dejado de hacer presión sobre CTRL, selesccionamos el rango de celdas D9;D10. El resultado que obtenemos son 2 rangos separados por punto y coma (Excel pone automáticamente el signo punto y coma y los signos de $).

3) Aceptamos y obtenemos el siguiente cuadro:


4) Vemos que lo que aparece son los datos que había en las celdas seleccionadas y que deseamos queden memorizadas como "Escenario moderado". Así pues, no tenemos que cambiar los datos y damos a "Agregar", obteniendo de nuevo el cuadro de diálogo donde se definen los escenarios (el nombre y las celdas cambiantes).

5) Repetimos exactamente el paso 2), pero en el "Nombre de escenario" , ponemos "Escenario pesimista" y aceptamos (las celdas cambiantes las pone Excel ya por defecto esta vez. Obteniendo de nuevo el pequeño cuadro de diálogo "Valores de escenario".

6) Esta vez, cambiamos los valores que nos presenta por defecto Excel, por los valores del "Escenario pesimista" de nuestra tabla de supuestos. Es decir:


... y damos a "Agregar".

7) Repetimos los pasos 5) y 6), pero esta vez poniendo como nombre de escenario "Escenario optimista" y en el cuadro de diálogo "Valores del escenario" ponemos los valores del escenario optimista de nuestros supuestos. Es decir:


...y esta vez, puesto que no tenemos que agregar más escenarios,hacemos click en "Aceptar" y obtenemos:


Ahora ya tenemos todos los escenarios configurados, sólo nos queda seleccionar el deseado en el panel "Escenarios" y hacer click en el botón "Mostrar". Podremos ver cómo se introducen automáticamente en las 5 celdas los datos de nuestro supuesto y todo queda recalculado según el escenario elegido.

Debemos apreciar que cuando hacemos clik en "Mostrar" el cuadro de diálogo no desaparece. Esto es para que podamos cambiar entre escenarios más rápidamente y poder apreciar los cambios con más facilidad.

En el cuadro de diálogo anterior ("Administrador de escenarios"), vemos que existe un botón llamado "Resumen..." que al hacer click sobre él obtenemos el siguiente cuadro, donde debemos seleccionar en el campo "Celdas de resultado:", el rango de celdas que consideramos como resultados importantes para nosotros y cuyo resultado para cada escenario, nos gustaría saber (vemos abajo que se han seleccionado arbitrariamente como rango de celdas importantes, las que reflejan el beneficio en porcentaje respecto de las ventas):


Si hacemos click en "Aceptar", obtenemos un cuadro - resumen que es totalmente modificable (y no se actualiza al volver a cambiar los datos en la tabla sobre la que hemos aplicado los escenarios). Una vez cambiamos las celdas en las que obtenemos resultados irrelevantes para nosotros, podemos obtener un interesante resumen como este:



Por último, comentar que en el cuadro de diálogo ("Resumen del escenario"), también disponemos de la opción de obtener una tabla dinámica de escenario (en vez de un resumen "estático" como hemos obtenido en nuestro ejemplo).

Análisis de datos en Excel
Tablas de datos en Excel (2 variables)
La utilidad "Buscar objetivo" en Excel


  José Manuel Pomares Medrano

4 comentarios: