miércoles, 25 de noviembre de 2009

Ultimo valor. Una aplicación de DESREF

Descargar el fichero: ultimo_valor.xlsx

Ultimo valor nos permite calcular el último valor de una columna. Para ello se utiliza la función DESREF y COINCIDIR. Utilizaremos un caso que nos permite gestionar las participaciones de un Fondo de Inversión.

Disponemos de tres tablas y un gráfico.


En las celdas amarillas se introducen los datos y en las verdes se calculan las fórmulas.

La tabla central contiene el valor de una participación en diferentes fechas. Con esta información creamos el gráfico que nos muestra el valor cotizado a lo largo del tiempo de una participación del fondo de inversión. Esta tabla central irá creciendo hacia abajo a medida que se introduzcan nuevos valores en diferentes fechas.

La tabla de la izquierda permite ir anotando los movimientos (compras y ventas) que se producen en la cartera de este fondo de inversión en concreto. Las compras suponen un número positivo de participaciones y las ventas implican un número negativo de participaciones. A medida que se vayan produciendo nuevas compras y ventas de participaciones esta tabla irá creciendo hacia abajo, anotándose una nueva línea por cada operación realizada.


El precio de la celda E5 se obtiene con la expresión:

=DESREF(H4;COINCIDIR(B5;H:H)-4;1)

La función COINCIDIR tiene la siguiente sintaxis:
=COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia])

Y la función DESREF utiliza los siguientes argumentos:

=DESREF(ref;filas;columnas;alto;ancho)

A la función COINCIDIR tenemos que restarle 4 porque las fechas de la columna H comienzan en la fila 5. Esto supone que los datos deben comenzar siempre en la fila 5, no pudiéndose insertar filas.

En la celda K5 determinamos las participaciones en cartera sumando todos los valores de la columna D. Esto se hace con la expresión:

=SUMA(D:D)
Esto supone que no se deben introducir valores numéricos ni fechas en las celdas
D1 a D4.

La tabla de la derecha contiene tres fórmulas, y es una tabla que no crece. Esto es, no es una base de datos como las dos anteriores sino que esta constituida por tres fórmulas de resultados. En ella se determina el valor actual de la cartea de participaciones de este fondo de inversión concreto. Este valor actual se calcula como el producto del número de participaciones existentes en el momento actual, multiplicado por el último valor conocido de la participación.

La celda L5 se calcula con la expresión:

=DESREF(I4;COINCIDIR(MAX(H:H);H:H)-4;0)
Al ser la columna H la de fechas, su máximo nos da el valor de la fecha correspondiente a la fecha más actual, la más tardía. La función COINCIDIR nos indica cuantas fechas hay en la columna H. Restamos 4 ya que los datos comienzan en la fila 5. Por eso no se deben insertar o eliminar filas antes de la fila 5. La función DESREF nos lleva hasta el último precio de la participación anotado en la columna I. Por tanto la celda L5 contiene el último precio conocido de la participación.

En M5 calculamos el Valor Actual de la cartera simplemente multiplicando el número de participaciones que tenemos en cartera por el último precio conocido de una participación.

=K5*L5

jueves, 19 de noviembre de 2009

Lotería Primitiva


Dispone de un archivo de Excel que puede descargar: loteria.xlsm

Veamos un caso de simulación del juego de la Lotería Primitiva. Se trata de simular un gran número de jugadas y comprobar cuantos aciertos de 3, 4, 5, y 6 se producen. La macro no pretende ayudar a ganar dinero con este juego de azar. Sus pretensiones son formativas, ya que aprenderemos a programar macros con matrices y funciones. A la vista de los pobres resultados alcanzados, posiblemente la consecuencia sea que usted no vuelva a jugar a la lotería primitiva.


Reglas

Disponemos de 49 números, entre 1 y 49. Se deben elegir 6 de ellos, sin repetición. La jugada ganadora esta compuesta por otros 6 números, igualmente elegidos entre los 49 disponibles. Se compara nuestra jugada con la ganadora. Reciben premio los jugadores que tienen una combinación de números que coincide con la ganadora en 3 o 4 o 5 o 6. El premio mayor se obtiene si tienes 6 coincidencias, y el menor si tienes 3 coincidencias. La cuantía del premio varia en función del número de acertantes existentes en cada categoría.

Inicialmente estas fueron las reglas. Posteriormente añadieron la existencia de un número complementario, que da lugar a un premio intermedio entre el de acertar 5 y 6. Se trata de acertar 5 más el número complementario. La existencia de esta nueva regla no la vamos a considerar en nuestra simulación para simplificar el caso.




Función Genera

Hemos creado una función que genera 6 números aleatorios sin repetición entre 1 y 49.


Observar que se incluye la instrucción

Randomize

que en Visual Basic permite inicializar la semilla de los números aleatorios. Equivale a barajar las cartas de una baraja para sacar nuevas cartas al azar.


Código:

Private Function Genera() As Byte()
Dim A(6) As Byte
Dim i As Long
Dim j As Byte
Dim U As Byte
Dim repe As Boolean
Randomize
A(1) = Int(49 * Rnd + 1)
For i = 2 To 6
Do
U = Int(49 * Rnd) + 1
repe = False
For j = 1 To i - 1
If U = A(j) Then repe = True: Exit For
Next j
Loop While repe
A(i) = U
Next i
Genera = A
End Function


Macro

Disponemos de una macro que es la que dirige el flujo del programa. Llama a la función Genera inicialmente para solicitarle la combinación ganadora, y posteriormente cada vez que se realiza una jugada.



Código:

Sub primitiva_loto()
Dim i As Byte
Dim j As Double
Dim k As Byte
Dim s As Byte 'acumula las acertadas
Dim n As Double
Dim B(6) As Long 'B(3), B(4), B(5), B(6)
'acumulan las jugadas ganadas de 3,4,5,6
Dim G() As Byte 'combinación ganadora
Dim Z() As Byte
G = Genera()
n = InputBox("¿Núm. jugadas?", , 1000000)
For j = 1 To n 'j es la jugada
Z = Genera()
s = 0
For i = 1 To 6
  For k = 1 To 6
      If G(i) = Z(k) Then s = s + 1: Exit For
  Next k
Next i
For i = 3 To 6
  If s = i Then B(i) = B(i) + 1
Next i
Next j
For i = 1 To 6 'Imprimimos la Ganadora
Cells(i + 4, "B") = G(i)
Next i
For i = 3 To 6 'Imprimimos los resultados
Cells(i + 2, "E") = B(i)
Next i
Range("E10") = n
End Sub

Se comparan todos los elementos de la jugada con la ganadora, y se determina el número de aciertos. Si los aciertos son 3, o 4 o 5 o 6, se llevan a una matriz B() que acumula el número de aciertos de cada categoría.
  • B(3) acumula los acierto de 3
  • B(4) acumula los acierto de 4
  • B(5) acumula los acierto de 5
  • B(6) acumula los acierto de 6
B(1) y B(2) no se utilizan para nada.


Continua

Una variante sobre la macro anterior es otra macro que trabaja de forma continua y que no para hasta que el usuario la detiene, por ejemplo pulsando la tecla Esc. Despues de pulsar Esc sale una pantalla avisando de la interrupción de la macro, y en ella debe pulsas sobre 'Finalizar'.

Esta macro cada 100.000 jugadas realizadas muestra en pantalla los resultados alcanzados. Esto nos permite ver la evolución de los acierto.

Código:

Option Explicit
Private Function Genera() As Byte()
Dim A(6) As Byte
Dim i As Long
Dim j As Byte
Dim U As Byte
Dim repe As Boolean
Randomize
A(1) = Int(49 * Rnd + 1)
For i = 2 To 6
Do
   U = Int(49 * Rnd) + 1
   repe = False
   For j = 1 To i - 1
       If U = A(j) Then repe = True: Exit For
   Next j
Loop While repe
A(i) = U
Next i
Genera = A
End Function
Sub primitiva_continua()
Dim i As Byte
Dim j As Double
Dim k As Byte
Dim s As Byte 'acumula las acertadas
Dim B(6) As Long 'B(3), B(4), B(5), B(6) acumulan las jugadas ganadas de 3,4,5,6
Dim G() As Byte 'combinación ganadora
Dim Z() As Byte
G = Genera()
For i = 1 To 6 'Imprimimos la Ganadora
Cells(i + 4, 2) = G(i)
Next i
Do
j = j + 1 'j es la jugada
Z = Genera()
s = 0
For i = 1 To 6
   For k = 1 To 6
       If G(i) = Z(k) Then s = s + 1: Exit For
   Next k
Next i
For i = 3 To 6
   If s = i Then B(i) = B(i) + 1
Next i
If Int(j / 100000) - (j / 100000) = 0 Then
   For i = 3 To 6 'Imprimimos los resultados
       Cells(i + 2, 5) = B(i)
   Next i
   Cells(10, 5) = j
End If
Loop
End Sub


Tras dejar funcionando mi portatil durante ocho horas y media, conseguí algo más de cuatro mil millones de jugadas (4.126.700.000) siendo los aciertos obtenidos los siguientes.



De 6 aciertos no se consiguió ningún acierto. Esto probablemente no sea muy representativo debido a que estamos manejando números pseudoaleatorios, que no son verdaderamente aleatorios. Es posible que se mejoraran los resultados obtenidos utilizando no una única jugada ganadora, sino una ganadora por cada apuesta realizada. Pero, independientemente de este caso, lo que se ve con claridad es que no es muy rentable, en términos medios, jugar a la lotería. Aunque siempre nos quedará el consuelo de pensar que ... "a alguien le toca".

miércoles, 18 de noviembre de 2009

Simulación del Máximo Beneficio

Archivo de Excel utilizado en el vídeo: MaxB.xlsm

Maximizar el beneficio empresarial es habitualmente el objetivo que se persigue en una compañía. Pueden existir muchos otros objetivos, pero cuando se trata de optimización uniobjetivo el beneficio generalmente es la variable elegida. En este caso vamos a diseñar un modelo empresarial muy sencillo, (pocas ecuaciones) y vamos a utilizar la simulación de Monte Carlo para la determinación del beneficio esperado.

Una empresa efectúa una previsión del beneficio que obtendrá el próximo ejercicio en base a un modelo, y a información histórica que establece las diversas distribuciones de probabilidad de las variables que intervienen en el modelo.



Modelo

El modelo es el siguiente.

B=I-G
I=pq
G=CF+CV
CV=cq

1ª Ecuación: B=I-G
El beneficio es igual a los ingresos menos los gastos.

2ª Ecuación: I=pq
Los ingresos son el producto de precio unitario por cantidad vendida.
Para simplificar suponemos que todo lo que se produce se vende. No existe almacén.

3ª Ecuación: G=CF+CV
Los gastos se calculan como suma de Costes Fijos más Costes Variables.
En este caso se confunden gastos con costes. Admitamos la equivalencia.

4ª Ecuación: CV=cq
Los Costes Variables son el producto de costes unitarios por cantidad producida.
Para simplificar suponemos que todo lo que se produce se vende. No existe almacén.

El precio se distribuye como una normal de media 100 y desviación típica 20.
Los coste fijos son una uniforme entre 120.000 y 160.000 euros.
El coste unitario se distribuye según una triangular de entre 50 y 70, con media en 60.
La cantidad vendida es una logonormal de media 10.000 unidades y desviación típica 1,2.


Variables

Existen cuatro variables de entrada INPUTS:
  • Precio
  • Coste Fijos
  • Coste Unitario
  • Cantidad (q)

Y dos variables de salida OUTPUTS:
  • Beneficio
  • Desviación Típica

Las variables de entrada vienen dadas por distribuciones de probabilidad conocidas en base a la información histórica, o a la experiencia del experto que está realizando el modelo.




Distribuciones

Precio

Se calcula en la celda C18 con la siguiente expresión

=REDONDEAR(INV.NORM(ALEATORIO();100;20);2)

Para versiones 2007 y anteriores usar la siguiente fórmula.
=REDONDEAR(DISTR.NORM.INV(ALEATORIO();100;20);2)

Se trata de una distribución normal de media 100 y desviación 20.

Costes Fijos

Se calculan en la celda C19 con la siguiente expresión

=ALEATORIO.ENTRE(120000;160000)

Es una distribución uniforme entre 120.000 y 160.000.


Coste Unitario

Se calcula en la celda C20 con la siguiente expresión.



Siendo D20 un número aleatorio uniforme entre cero y uno, que se obtiene con =ALEATORIO().

La explicación de esta expresión se encuentra en la Hoja2 donde se trabaja con una distribución triangular entre 50 y 70 con media en 60. Para determinar la distribución triangular se han de conseguir las ecuaciones de las dos rectas que la definen. Dividimos el triángulo en dos triángulos. El primero va entre 50 y 60 y sabemos que en él se encuentra el 50% de la probabilidad total. El segundo triángulo va entre 60 y 70 y en él se encuentra el otro 50% de la probabilidad total.

Para determinar la altura del triángulo consideramos que en primer triángulo la probabilidad es 0,5, y la base es 60-50. ¿Quíen será la altura?.

Superficie=base*altura/2

0,5=(60-50)*altura/2

De este expresión deducimos que la altura es 10.

Estamos en la Hoja 2. En la celdas C5 a C15 creamos una serie entre 50 y 70, de dos en dos. Vamos a deducir ahora los valores de la columa B que corresponde a cada valor de la columna C.

En 50 la probabilidad es cero.
En 60 la probabilidad es 0,5.
¿Cuál será la probabilidad en 58?.
Para contestar a esta pregunta hemos de calcular el área del triángulo hasta 58. Su área es:

Area= Base*Altura/2
La base es: Base= (58-50)
Al ser la altura igual a la base el área es: (58-50)^2/2

La celda B9 calcula esta expresión dividiendo entre 100 ya que se trata de probabilidad y la probabilidad total ha de ser 1.

=+(C9-50)^2/2/100

En la celda D9 calculamos el coste unitario que corresponde a la probabilidad anterior. Esto se consigue planteando la expresión anterior como una ecuación matemática y ahora se despeja la probabilidad en función del coste unitario.

=+(B9*200)^0,5+50

Esto que hemos visto se hace para el primer triángulo y se ha de hacer lo propio para el segundo triángulo. De forma que la expresión que finalmente nos de el coste unitario llevará un condicional (SI) que aplicará una ecuación u otra en función de si el número aleatorio que se utiliza en la simulación es menos o mayor que 0,5.





Cantidad (q)

Se calcula en la celda C21 con la siguiente expresión.
=REDONDEAR(INV.LOGNORM(ALEATORIO();LN(10000);LN(1,2));0)
Para versiones 2007 y anteriores usar la siguiente fórmula.
=REDONDEAR(EXP(DISTR.NORM.INV(ALEATORIO();LN(10000);LN(1,2)));0)
Se trata de una distribución Logonormal o Lognormal de media 10.000 y desviación 1,2.

Las distribuciones lognormales se utilizan preferiblemente a las normales, ya que la campana de Gauss va desde menos infinito hasta más infinito, y no son válidas para trabajar con variables que no pueden tomar valores negativos. Por ejemplo, para precios, número de personas, distancia en kilómetros, coste, entre otras muchas no podemos, o mejor dicho, no deberíamos utilizar distribuciones normales ya que no estamos exentos de obtener valores negativos. En estos casos son preferibles las distribuciones lognormales.

En Excel la media y la desviación se han de introducir con la función LN, que es la de logaritmo neperiano.


Cálculo del Beneficio

  • Calculamos el Coste Variable en la celda C24.
  • Calculamos los Gastos en la celda C25.
  • Calculamos los Ingresos en la celda C26.
  • Calculamos el Beneficio en la celda C27.

Ya hemos calculado el Beneficio. Pulse la tecla F9 y compruebe que varía. Incluso en algunas ocasiones se obtiene un beneficio negativo. Cada una de estas concreciones de la variable Beneficio se supone que es un hipotético próximo año. Para hacer la simulación debemos generar un gran número de posibles beneficios y luego estudiar la distribución a la que se ajustan.

Sub ciclos()
Dim i As Long, c As Long
Dim Beneficio As Double
c = Val(InputBox("iteraciones?", , 1000))
Columns("K:K").Clear
Range("A1").Select
For i = 1 To c
    Beneficio = Range("C27")
    Range("I14") = i
    Cells(i, "K") = Beneficio
Next i
End Sub


Los beneficios obtenidos en cada iteración se apuntan en la columna K, con pegado especial valores. Esto se hace con una macro. Observar que hemos creado una sencilla macro que nos pregunta el número de iteraciones que deseamos, por ejemplo 5.000 y seguidamente se lanza a simular la variable Beneficio hasta completar los 5.000 valores de la columna K.



Histograma

Con los 5.000 valores del beneficio de la columna K, creamos un Histograma o Diagrama de Frecuencias. Para aprender a hacer histogramas con la función matricial FRECUENCIA, se puede ve en este mismo Blog u Post que lo explica: Histograma.




El histograma se asemeja a una campana de Gauss. No es muy perfecta debido a que hemos tomado únicamente 5.000 valores del beneficio, y en simulación se deben realizar muchas iteraciones para que los valores queden bien definidos. Sería interesante realizar 100.000 iteraciones, o un millón. Para efectuar un gran número de iteraciones es aconsejable utilizar una macro que maneje matrices. De esta forma evitamos utilizar la hoja de cálculo para anotar resultados intermedios, con lo que ganamos en velocidad y evitamos el límite del número de filas disponibles. También sería interesante diseñar una macro que haga el histograma.

lunes, 16 de noviembre de 2009

Pasar matrices a una función

Pasar matrices a una función en programación de macros para Excel es de lo que nos vamos a ocupar en este Post. También veremos el caso en el que la función devuelve una matriz. Disponemos de un procedimiento (Sub) que llama a una función (Function), la cual devuelve o bien un valor, o bien una matriz.
Primer caso
La función recibe una matriz y devuelve un único valor.
Código:
Sub principal()
Dim A(2)
A(1) = 1
A(2) = 2
MsgBox sumatorio(A)
End Sub

Private Function sumatorio(B)
Dim s
s = B(1) + B(2)
sumatorio = s
End Function
Segundo caso
La función no recibe nada y devuelve una matriz.
Es importante definir la función Genera escribiendo el tipo y unos paréntesis detras del tipo de variable:
Function Genera() As Byte()
Estos últimos paréntesis son imprescindibles.
Código:
Sub Ejecuta()
Dim Z() As Byte
Z = Genera()
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Genera() As Byte()
Dim A(2) As Byte
A(1) = 1: A(2) = 2
Genera = A
End Function
Otro ejemplo que se ajustaría a este segundo caso se da cuando se pide a la matriz que genere ciertos números aleatorios. Por ejemplo, la función podría generar números aleatorios sin repetición en cierto rango de valores enteros.
Tercer caso
La función recibe una matriz y devuelve otra matriz.
Código:
Sub Transforma()
Dim Z() As Byte
Dim p, q
p = 4: q = 5
Z = Dobla(p, q)
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Dobla(x, y) As Byte()
Dim A(2) As Byte
A(1) = x * 2
A(2) = y * 2
Dobla = A
End Function

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.