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.

=+(B8*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.

11 comentarios:

  1. muy bueno el tutorial quisiera saber cual es el algoritmo en visual basic de distribucion triangular... gracias

    ResponderEliminar
    Respuestas
    1. Function TRIANGULARINV(ByVal probabilidad As Single, ByVal optimista As Single, ByVal masprobable As Single, ByVal pesimista As Single)
      On Error GoTo 18
      Dim x As Single
      If probabilidad > 1 Or probabilidad < 0 Then GoTo 18
      If optimista >= pesimista Then GoTo 18
      x = (masprobable - optimista) / (pesimista - optimista)
      If (x > 1 Or x < 0) Then GoTo 18
      If probabilidad <= x Then TRIANGULARINV = optimista + (probabilidad * (pesimista - optimista) * (masprobable - optimista)) ^ 0.5)
      If probabilidad > x Then TRIANGULARINV = pesimista - ((1 - probabilidad) * (pesimista - masprobable) * (pesimista - optimista)) ^ 0.5)
      Exit Function
      18 TRIANGULARINV = 0
      End Function

      Eliminar
  2. Hola, tengo una pregunta, el valor esperado en una distribución triangular, sería igual a mi media?

    ResponderEliminar
    Respuestas
    1. En cualquier distribución el valor esperado es igual al valor de la media de la distribución

      Eliminar
  3. Hay un problema con la propuesta para distribución triangular, el error surge del supuesto que se hace de que los dos triángulos tienen la misma área, lo cual solo es cierto cuando el valor más probable es equidistante con el valor optimista y con el pesimista.
    Una fórmula para generar aleatorios con distribución triangular es:
    =SI(U<=(b-a)/(c-a);a+RAIZ(U*(c-a)*(b-a));c-RAIZ((((b-a)/(c-a))-U)*(c-a)*(c-b)+POTENCIA(b-c;2)))
    Donde:
    U: Es el número aleatorio [0, 1] generado casi siempre con =ALEATORIO()
    a: Es el valor optimista
    b: Es el valor más probable
    c: Es el valor pesimista

    ResponderEliminar
  4. Todavía más sencilla la fórmula:
    =SI(U<=(b-a)/(c-a);a+RAIZ(U*(c-a)*(b-a));c-RAIZ((1-U)*(c-b)*(c-a)))

    ResponderEliminar
    Respuestas
    1. Hola.
      Gracias por el magnífico aporte. Se que a muchas personas que buscan precisamente esto les resultará de gran ayuda.
      Es magnífico ver como se enriquece esto para todos.
      Bien echo.

      Eliminar
    2. Está buena esta formula? la pruebo y me tira valores fuera del rango maximo, minimo...

      Eliminar
    3. El problema en tu caso debe estar en otro lugar y definitivamente no en la fórmula.
      He generado 50000 números usando esa fórmula y ninguno se salió del rango.
      Se me ocurre que esté haciéndolo con varios aleatorios diferentes, el valor de U debe ser el mismo para todas las ocurrencias en la fórmula.
      Como ve el problema estaba en otro lado.

      Eliminar
  5. Buenisimo el tutorial. Felicitaciones.

    ResponderEliminar
  6. Gracias por el tutorial, se podría realizar un análisis de sensibilidad en en este ejercicio??

    ResponderEliminar