viernes, 13 de noviembre de 2009

Escenarios multi-hipótesis

Descargar el fichero: escenariosmulti.xlsx


Los escenarios multi-hipótesis: permiten efectuar análisis de sensibilidad de múltiples variables de entrada, frente a múltiples variables de salida. Vamos a realizar un caso con 5 variables de entrada y 2 variables de salida. Generaremos tres escenarios posibles: pesimista, medio y optimista. También veremos análisis de sensibilidad utilizando tablas tipo I (una variable de entrada) y tablas tipo II (dos variables de entrada).

Hoja 1

En la Hoja1 efectuamos el Análisis de Sensibilidad respecto a una variable de entrada. Creamos una modelo que nos permite ilustrar este caso. Se trata de la cuenta de resultados de una empresa que efectúa previsiones para los próximos años: 2010, 2011, 2012, 2013 y 2014.




La 'Facturación' (fila 7) se prevé para el año 2010 que será de 150.000 €, incrementándose para los próximos años en un porcentaje que viene expresado por la celda amarilla (B7). Es este caso, suponemos que la facturación experimentará un incremento acumulado del 5% cada año. La celda amarilla es sobre la que efectuaremos el análisis de sensibilidad. Es nuestra variable de entrada. La celda E7 nos proporciona el incremento, y al copiarla hacia la derecha este incremento se convierte en acumulado afectando a los sucesivos años. Su expresión es la siguiente:

=+D7*(1+$B$7)

Los ingresos 'Atípicos' (fila 8) parten de un importe inicial de 30.000 € y se reducen cada año un 3%. Comprobarlo en la celda E8 que se copiará hacia la derecha.

=+D8*(1-0,03)

En la fila 6 sumamos los INGRESOS con la función SUBTOTALES.


=SUBTOTALES(núm_función;valor1, valor2, ...)

Esta función es muy versátil y según el valor que demos a núm_función nos permitirá calcular diferentes operaciones. Veamos algunos valores de núm_función:
  • sumar 9
  • contar 2
  • calcular el máximo 4
  • calcular el mínimo 5
  • calcular el promedio 1
La ventaja de sumar con la función SUBTOTALES en lugar de hacerlo con la función SUMA, es que si en el rango de valores de la función SUBTOTALES se incluye una fila que también emplea esta función, no la incluye dentro del cálculo, evitando duplicidades. En nuestro caso en la fila 15 calculamos el Beneficio como suma de los Ingresos y de los Gastos (con signo negativo). Al emplear en el cálculo del Beneficio la función SUBTOTALES y hacerlo también en el cálculo de los Ingresos y de los Gastos, no se incluyen estos, evitando duplicar los importes.

Obsérvese la necesidad de considerar los ingresos negativos ya que la función SUBTOTALES sabe sumar, pero no restar.

Para los Gastos hemos creado únicamente tres posibles conceptos: personal, amortización y alquileres. Cada uno de ellos contiene unos importe previstos para el ejercicio 2010 y unos incrementos acumulados para los próximos años.

En la fila 15 calculamos el Beneficio. Por ejemplo en D15 la expresión es:

=SUBTOTALES(9;D6:D14)

Observe que, tal y como hemos comentado, el cálculo es correcto pese a utilizar el rango D6:D14 que incluye la fila de Ingresos y la de Gastos. Esto es debido a que no se computan en la suma debido a que las propias filas de Ingresos y Gastos se calcularon con la función SUBTOTALES.

Creamos un nombre de rango denominado 'beneficio' que es el rango D15:H15, cuyo ámbito lo hemos restringido únicamente a la Hoja1. Para hacer esto seguimos estos pasos:
  1. Seleccionamos el rango D15:H15
  2. En el cuadro de nombres escribimos: "Hoja1!beneficio" (sin las comillas). Al anteponer al nombre de rango el nombre de la hoja y una admiración hacia abajo (!), conseguimos definir el nombre de rango exclusivamente en el ámbito de actuación de la Hoja1.
  3. Pulsar ENTER. Esto es importante ya que si después de escrito el nombre de rango pulsamos cualquier celda con el ratón, sin haber pulsado Enter, el nombre de rango no quedará grabado.


Un método alternativo para nombrar rangos en Excel 2003 se hace con Insertar, Nombre, Definir. En Excel 20007 el método alternativo para asignar un nombre de rango se consigue con: Fórmulas, Asignar nombre a un rango. Se obtiene la siguiente ventana:


Observe que en 'Ámbito:' hemos indicado Hoja1. Esto hace que el nombre de rango se restrinja exclusivamente a la Hoja1. A esta ventana también podríamos haber llegado en Excel 2007 con Fórmulas, Administrador de nombres, Nuevo.

Fila 17: ISS (Impuesto de Sociedades). Consideramos que es un 35% sobre el Beneficio. Observe la fórmula de la celda D17, y las de esa fila.

=35%*beneficio

Para crear la fila 17 seguimos estos pasos:

  1. Seleccionamos las celdas D17:H17
  2. Escribimos la fórmula: =35%*beneficio
  3. No pulsamos Enter. Pulsamos Ctrl + Enter. De esta forma conseguimos que la fórmula se aplique al todas las celdas del rango seleccionado.
Ahora, nombramos el rango D17:H17 con el nombre de rango ISS restringido al ámbito exclusivo de la Hoja1.

Fila 18. BDI (Beneficio Después de Impuestos). Para crear esta fila seguimos estos pasos:

  1. Seleccionamos las celdas D18:H18
  2. Escribimos la fórmula: =beneficio-ISS
  3. No pulsamos Enter. Pulsamos Ctrl + Enter. De esta forma conseguimos que la fórmula se aplique al todas las celdas del rango seleccionado.
Fila 19. Dividendos. Consideramos que todo el beneficio después de impuestos se reparte en dividendos.

Fila 22. Flujos de Caja. Supongamos que este proyecto de inversión para el accionista un desembolso de 100.000 € (celda C22) que se indican con signo negativo por ser un desembolso, y unas recuperaciones positivas que son los dividendos que se prevé obtener en los próximos años. Sobre estos flujos de caja posteriormente calcularemos el VAN y la TIR que serán las variables de salida del modelo, y sobre las que efectuaremos el análisis de sensibilidad.

Primero vamos a crear el análisis de sensibilidad para analizar la TIR, y de momento no calcularemos el VAN. Seguiremos estos pasos:

Paso 1

Celda D24. En esta celda calculamos la TIR de la operación. La fórmula empleada es:

=TIR(C22:H22)

El resultado obtenido es 7,35% anual, que representa la rentabilidad de este proyecto bajo el supuesto de que la celda amarilla (incremento de la facturación) sea del 5% anual acumulado.

Paso 2

En el rango C25:C35 creamos una serie entre el 0% y el 10% que representan los posibles valores que consideramos puede llegar a tomar la celda amarilla, que es el incremento de la facturación esperada para los próximos años. La celda D24 y el rango C24:C35 es imprescindible que se encuentren en esas posiciones relativas uno respecto al otro, ya que vamos a crear una tabla para efectuar el análisis de sensibilidad.

Paso 3

Seleccionamos la tabla que ocupará el rango C24:D35. Observar que queda seleccionada también la celda verde (D24).

Paso 4

En Excel 2003 vamos al menu: Datos, Tabla.
En Excel 2007 vamos a Datos, Análisis Y si, Tabla de datos.

Obtenemos una ventana que nos pide que introduzcamos:
  • Celda de entrada (fila)
  • Celda de entrada (columna)
La celda de entrada (fila) la dejamos vacía, y en la celda de entrada (columna) introducimos la celda amarilla (B7), que es la celda amarilla sobre la que deseamos hacer el análisis de sensibilidad. Al encontrarnos en el caso de Tablas Tipo I únicamente analizamos una variable de entrada. Únicamente tenemos una celda amarilla. En las tablas tipo II que veremos en la Hoja2 veremos que se rellenan ambas (la celda de entrada fila y la celda de entrada columna).

Paso 5

Pulsamos Enter y obtenemos la siguiente tabla, con los valores de la TIR para diferentes tasas de incremento de la facturación. Aplicamos a los valores obtenidos de la TIR un formato de porcentaje de dos decimales.

Paso 6

A nivel estético queda horrible ver en la celda verde (D24) un número en lugar de un rótulo que nos indique que esa columna de la tabla nos proporciona la TIR. Vamos a utilizar un truco para que sin borrar la fórmula de la TIR de la celda D24, ya que es imprescindible que este ahí, podamos ver el rótulo de la TIR.

Nos situamos en D24 y pulsamos con el botón derecho del ratón eligiendo 'Formato de celdas', pestaña Número, Categoría Personalizada, y en Tipo escribimos la palabra TIR entrecomillada:

"TIR"




Con este truco veremos que en la celda verde D24 pone TIR, y que en realidad sigue existiendo la fórmula.

Paso 7

Intente borrar una única celda obtenida, por ejemplo la celda D31, y obtendrá un mensaje de error que le avisa que no se puede eliminar una única celda. Si quiere borrarlo debe borrar el rango D25:D35 completo. La fórmula D31 tiene la siguiente expresión:

{=TABLA(;B7)}

Expresión que no podemos manejar.



Si cambiamos un valor en el rango C25:C35 se recalcula automáticamente la TIR correspondiente. Por ejemplo, en C35 cambie el 10% por un 12% y observe como cambia la TIR de la celda D35.

Ya hemos efectuado el análisis de sensibilidad de una variable de entrada frente a una variable de salida.

  • Variable de entrada: la celda amarilla B7. Incremento de la facturación.
  • Variable de salida: la TIR. Se obtiene en el rango D25:D35.
Ahora podemos plantearnos incorporar una nueva variable de salida, el VAN. Pero hemos de ser conscientes de que seguimos estando en Tablas Tipo I, ya que continuamos con una única variable de entrada. Tenemos una única celda amarilla, aunque ahora vamos a conseguir dos variables de salida (la TIR y el VAN).


Paso 8

Para añadir una nueva variable de salida debemos previamente borrar el rango D25:D35. Luego añadimos en la celda E24 una fórmula que calcula el VAN de la operación al 6%.

=+VNA(6%;D22:H22)+C22

Obtenemos un valor de 3.411,20 €.


Sin borrar la fórmula del VAN de la celda E24 aplicamos el mismo truco que utilizamos antes con la TIR, pero en esta ocasión para conseguir que en esa celda se vea la palabra VAN.


Paso 9

Seleccionamos el rango C24:E35


Paso 10

Lanzamos la tabla de datos y en Celda de entrada (fila) no ponemos nada, y en Celda de entrada (columna) seleccionamos la celda amarilla (B7). Recordemos que aunque tengamos ahora dos variables de salid (TIR y VAN) seguimos estando en Tablas Tipo I (una única celda amarilla).



Paso 11

Pulsamos Enter y obtenemos la tabla que pretendíamos con la TIR y el VAN.




Hoja 2

Las tablas tipo I nos permite efectuar el análisis de sensibilidad respecto a una variable de entrada, siendo el resultado es una columna de datos (una dimensión). Las tablas tipo II nos permiten analizar dos variables de entrada, siendo el resultado es una tabla de doble entrada.

En la Hoja2 realizamos un modelo de cuenta de resultados similar al de la Hoja1, pero en este caso tenemos dos celdas amarillas. Esto es, el modelo dispone de dos variables de entrada respecto a las que vamos a efectuar el análisis de sensibilidad.

Las variables de entrada (celdas amarillas) son:

  • Celad B7. Incremento de la facturación
  • Celda B11. Incremento de los gastos de personal.




El procedimiento es análogo al descrito en la Hoja1, con la salvedad de que ahora la celda verde que contiene la fórmula de la TIR debe encontrarse en el vértice de la tabla (celda C24).

=TIR(C22:H22)

Su importe es del 7,35%.




Creamos una tabla de doble entrada con las dos variables sobre las que deseamos efectuar el análisis de sensibilidad:

  1. En la columna C (rango C25:C35), creamos una serie entre el 0% y el 10% para la variable que representa el incremento porcentual de la facturación.
  2. En la fila 24 (rango D24:H24) creamos una serie con varios porcentajes entre el -3% y el 10% que representan la variación de los gastos de personal.
Ahora se debe seleccionar toda la tabla (rango C24:H35) y lanzar las Tablas de Datos. En esta ocasión si rellenamos tanto la 'Celda de entrada (fila)' como la 'Celda de entrada (columna)'.

  • La Celda de entrada (fila) es la celda B11
  • La Celda de entrada (columna) es la celda B7


Finalmente en la celda verde empleamos el truco visto para poner la palabra TIR sin borrar la fórmula, y hacemos un gráfico.




Resumiendo el Análisis de Sensibilidad:

  1. Tablas Tipo I. Una variable de entrada. Se obtiene una columna de datos. R1.
  2. Tablas Tipo II. Dos variables de entrada. Se obtiene una tabla de doble entrada. R2.
  3. Tablas Tipo III. Tres variables de entrada. Se obtendría un cubo. R3.
En Excel no existen las Tablas Tipo III. En su día se podían hacer con una hoja de cálculo anterior a Excel que se llamaba Lotus 123. Esta hoja de cálculo reinó durante muchos años como el lider indiscutible hasta que llegó Excel y la desplazó definitivamente. En Lotus las Tablas Tipo III se conseguian utilizando varias hojas de un libro: Hoja1, Hoja2, Hoja3,..., así se obtenia un cubo en tres dimensiones. Podíamos general por ejemplo un libro de Bonos, donde las tres variables serían: cupón, precio y plazo frente a rentabilidad.

Pese a que en Excel las Tablas Tipo III no existen, rápidamente llegaríamos al límite de variables de entrada a utilizar, ya que más allá de un cubo (R3), no existe figura geométrica con la que poder representar cuatro variables de entrada (R4), ni mucho menos cinco variables (R5), etc.

La metodología de las Tablas tiene un límite geométrico y no podríamos pasar más allá de tres variables de entrada. Excel ha optado por utilizar una herramienta denominada ESCENARIOS, que soporta múltiples variables de entrada y de salida, por lo que se puede considerarse multi-hipótesis.


Hoja 3

En la Hoja3 trabajaremos con Escenarios, una herramienta de Excel que nos permite utilizar múltiples variables de entrada y de salida.

En esta ocasión disponemos en la columna B de cinco celdas amarillas que serán las variables de entrada. Representan los incrementos porcentuales acumulados de la facturación, los ingresos atípicos, los gastos de personal, amortizaciones y alquileres. Pretendemos estudiar dos variables de salida: la TIR y el VAN calculado al 6%.

Definimos tres posibles escenarios:

  1. Escenario optimista
  2. Escenario medio
  3. Escenario pesimista
Para cada escenario definimos el valor que tendrían las variables de entrada. Esto se puede ver en el cuadro de las filas 27 a 30.



Por tanto, disponemos de 5 celdas amarillas (variables de entrada) y dos celdas verdes (variables de salida). El modelo esta creado de tal forma que las variables de entrada y las de salida están vinculadas por fórmulas. Esto quiere decir que si cambiamos manualmente el valor de alguna o varias de las celdas amarillas el valor calculado en las celdas verdes cambia automáticamente.

Para lanzar la herramienta de Escenarios en Excel 2003 elegimos: Herramientas, Escenarios.

Para lanzar la herramienta de Escenarios en Excel 2007 elegimos: Datos, Análisis Y si, Administrador de escenarios.




En la ventana que surge del Administrador de escenarios elegimos Agregar. Como nombre de escenario escribimos 'Optimista'. Donde dice 'Celdas cambianates' se deben poner las amarillas, que al estar separadas se indican seleccionándolas con el ratón mientas se mantiene presionada la tecla Control.




Seguidamente se indican los valores que toman las celdas amarillas para este escenario concreto.




Pulsamos sobre el botón 'Agregar' y procedemos de igual manera con el escenario Medio y con el escenario Pesimista. Al finalizar la introducción de los valores de las variables para este último escenario pulsamos sobre el botón 'Aceptar'.



Pulsamos sobre el botón 'Resumen' y en la siguiente ventana indicamos como 'Celdas de resultado' las dos celdas verdes. Como tipo de informe elegimos Resumen.




Esto genera un informe en una hoja nueva denominada 'Resumen de escenario'. Duplicamos esta hoja para mejorar su aspecto. Para duplicarla pulsamos la tecla Control mientras arrastramos la pestaña de la hoja que deseamos duplicar. Esto generará una hoja denominada 'Resumen de escenario (2)'.


En esta hoja duplicada eliminamos la columna de valores actuales, ya que no nos interesa tener a la vista los valores que actualmente tienen las celdas amarillas. Otra mejora consiste en cambiar los nombres de las celdas por sus conceptos: Facturación, Atípicos, .... Y finalmente realizamos un gráfico que ilustre los tres escenarios considerados.



Excel nos ayuda a estudiar los escenarios, pero ha de ser el usuario el que defina para cada escenario el valor que toman las diferentes variables de entrada. Esto es lo verdaderamente complicado e interesante, y afortunadamente aún el ser humano no ha sido desplazado en esa faceta.

13 comentarios:

  1. Gracias por sus aportes, excelente, muy didáctico... si por casualidad tienes información sobre como usar Solver, específicamente ¿q significan "métodos de resolución", sus opciones?, ¿conoces programas alternativos de análisis de hipótesis en internet?

    cualquier inf te agradecería me la enviaras a entrepreneur.zambra@gmail.com
    gracias

    ResponderEliminar
  2. BUENOS DIAS, YA HICE TODO EL MONTAJE Y DEMASIADO INTERESANTE, PERO CUANDO HAGO LA ULTIMA PARTE CON CINCO ENTRADAS EN FILA Y DOS EN COLUMNA EN EXCEL 2010 DEJA DE FUNCIONAR, SOLO SE PUEDE HACER SOLO CALCULANDO LA TIR, EL VAN SOLO TAMBIEN DEJA FUNCIONAR, EN 2007 TRABAJA PERFECTAMENTE, LO HEMOS HECHO EN INFINIDAD DE EXCEL 2010 Y CON VARIOS SISTEMAS OPERATIVOS Y EN TODOS PASA LO MISMO.

    CESAR ARENAS
    arenassalazarcesaraugusto@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Cesar.
      Es cierto que en ocasiones Excel 2010 "se cuelga" y deja de funcionar, aunque no siempre. Por ello lo mejor es grabar el fichero antes de lanzar la herramienta de Administrador de Escenarios, por si no sucede esto no perder todo el trabajo realizado.
      Un saludo.

      Eliminar
  3. Una consulta Adolfo, no puedo hacer el grafico de excel cuando tengo dos variables, facturacion y personal que varían. Me podría ayudar para hacer el gráfico.
    Muchas Gracias
    La explicación es genial

    ResponderEliminar
    Respuestas
    1. Hola.
      Para hacer el gráfico es importante saber si lo queremos de tipo "Líneas" o de tipo "Dispersión". Los de líneas llevan en el eje horizontal una variable X que son atributos, por ejemplo, optimista, medio, pesimista, o bien otro ejemplo sería: Madrid, París, Londres. Por el contrario, si el gráfico es de "Dispersión" el eje X representa una magnitud numérica, donde el valor que adquiere la variable X del eje horizontal si se representa en escala, por ejemplo, 1990, 2000, 2015. En el ejemplo anterior, si fueran años, puede observarse que entre el primer valor y el segundo han transcurrido 10 años, y entre el segundo y el tercero han transcurrido 15 años. Estos valores del eje X se representarían a escala.
      Tanto si se trata de un gráfico de Líneas o de Dispersión lo que se ha de hacer en primer lugar es poner tres columnas con los datos. En la primera columna se ponen los valores del eje X, en la segunda columna se ponen los valores de la primera variable a representar,por ejemplo, en tu caso, 'Facturación' y en la tercera columna se pone la otra variable que deseamos representar, en tu caso, 'Personal'. Luego eliges el tipo de gráfico, y sigues los pasos hasta el final. El gráfico ya estaría hecho y ahora solo queda, si lo deseas, maquetarlo un poco, para ponerlo bonito, a tu gusto.
      Suerte con ello.
      Un saludo.

      Eliminar
  4. Hola Adolfo,
    necesito realizar al menos 1000 escenarios para un trabajo con VAN, imagino que habrá alguna manera para crear esos escenarios a partir del botón sin tener que ponerlos uno a uno.
    Muchas grcaias

    ResponderEliminar
    Respuestas
    1. Hola.
      Cuando se necesitan realizar operaciones repetitivas que a mano sería imposible realizar o muy tedioso lo mejor es recurrir al uso de las macros en Excel programando un procedimiento en VBA.
      Un saludo.

      Eliminar
  5. Que excelente aporte y muy claro....muchas gracias Adolfo

    ResponderEliminar
  6. Hola,
    Una pregunta en el ejercicio de partida cuando calcula los flujos considera de partida un flujo de -100.000 esto a que corresponde, ya que el resto de flujos consideran el dividendo de cada año, pero el año 0 al poner -100.000 a que corresponde al desembolso que ha realizado ??

    ResponderEliminar
    Respuestas
    1. Si, el importe negativo en t=0 se considera el desembolso inicial de la inversión. Flujo de caja negativo supone que el inversor paga y flujo de caja positivo supone que el inversor cobra.
      Un saludo.

      Eliminar
  7. Hola, tengo una consulta. Se puede usar de valor de entrada un valor que se encuentra en una hoja diferente a la que estamos usando para la tabla de sensibilidad?

    ResponderEliminar
    Respuestas
    1. Hola.
      Imagino que si es posible. Pruébalo de forma directa o bien otro método sería vincular la celda amarilla (input) de tu hoja con la celda de la otra hoja.
      Un saludo.

      Eliminar
  8. Hola Adolfo, has probado a usar escenarios distintos en dos hojas distintas de un mismo libro? Gracias

    ResponderEliminar