Distribución normal de pagos

sábado, diciembre 06, 2014

Hace ya algún tiempo una lectora publicó este comentario en el post sobre como crear una distribución normal con Excel

Estoy tratando de hacer un flujo de caja donde tengo montos de dinero que debo distribuir en el tiempo y que se debe distribuir en forma normal (Gauss). Los datos que tengo son: el monto y el número de meses. Entonces, lo que quiero obtener es, el monto parcial para cada mes con una distribución normal. Es decir, el mes 1 y el mes final tendrán montos bajos y el mes del medio tendrá el mayor valor. La suma total debe los montos parciales debe ser igual al monto total.
Es decir, tenemos que crear primero una distribución normal (Gauss) y luego distribuir el monto total del dinero de acuerdo a esta distribución. Las variables son el monto y el número de meses.

Empecemos por crear la distribución normal para lo cual necesitamos una serie de  12 valores (los doce meses que requiere nuestra lectora) distribuidos normalmente.

Ponemos los datos tal como figuran en la imagen abajo


Seleccionamos la celda A5 (más adelante explicaré por qué su valor es -11) y abrimos el menú Inicio-Modificar-Rellenar-Series; marcamos la opción Columnas, Lineal, en Incremento ponemos el valor 2 (será explicado más adelante) y en límite 11 (ídem)


Apretamos "Aceptar" y Excel crea esta serie

La regla que usamos para crear esta serie simétrica de datos es:

  • valor inicial: número de cuotas menos 1 por -1 (en nuestro ejemplo: (12 - 1) x (-1) = 11)
  • incremento: 2
  • valor final (límite): valor inicial positivo

Ahora usamos la función DIST.NORM para calcular la distribución normal de acuerdo a la media y a la desviación estándar. En la celda B5 ponemos la fórmula =DISTR.NORM(A5,$B$1,$B$2,FALSO) y la copiamos al resto del rango


Ahora podemos usar los valores de la columna B para distribuir los pagos; en la celda C5 ponemos esta fórmula

=$B$3*(B5/SUMA($B$5:$B$16))

que copiamos al resto del rango


Como podemos ver, hemos dividido el monto a pagar en cuotas distribuidas aproximadamente según una distribución normal


Para variar la distribución de las cuotas, podemos cambiar el valor de la desviación estándar (la celda B2) o generar una serie de índices (el rango A6:A17) con un incremento más pequeño.

En esta animación podemos ver como va cambiando la distribución de los pagos a medida que cambiamos el valor del desvío estándar


Para generar una serie de índices con un incremento de 1, empezamos del valor -5.5 (la mitad del número de cuotas menos 1)



2 comments:

Anónimo,  08 diciembre, 2014 16:07  

Un articulo muy intersante, como deberiamos proceder si quisieramos una dsitribucion de tipo pico inicial?

Jorge Dunkelman 08 diciembre, 2014 17:33  

No estoy seguro a que se refiere "pico inicial", pero en el ejemplo de la nota podrías modificar el calor de la media (la celda B1) a -2.5 (o el número negativo que se adecue al propósito), lo que daría como resultado una serie de pagos decrecientes. También se puede "jugar" con las dos variables (media y desvío estándar) hasta obtener la distribución deseada.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP