Distribución normal de pagos

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)

Gráficos de distribución normal (Gauss) con Excel.

Hace unos días me preguntaba un lector cómo se puede crear con Excel un gráfico tipo campana de Gauss para representar un informe de desviaciones estándar.
Empecemos por citar a Wikipedia:

…la llamada distribución normal…[se utiliza] como modelo en multitud de variables (peso, altura, calificaciones...), en cuya distribución los valores más usuales se agrupan en torno a uno central y los valores extremos son escasos.

La representación gráfica de la distribución normal se ve así:



Lo que nos dice este gráfico es que en la distribución normal los miembros de la muestra se concentran alrededor del promedio y son más escasos a medida que nos vamos a los extremos. En la distribución normal cerca del 68% de los miembros de la muestra se ubican a en el área definida por un desvío estándar en cada sentido

En esta nota veremos cómo crear gráficos de distribuciones normales con las herramientas que nos provee Excel.
Para definir una distribución normal necesitamos una colección de datos de los cuales calcularemos el promedio y el desvío estándar. Por ejemplo, para crear este gráfico



hemos definido el desvío estándar en la celda B1 (1) y el promedio en la celda B2 (0), en el rango A5:A29 hemos puesto una serie de datos que va de -3 a +3, es decir de menos tres desvíos estándar a más tres desvíos estándar



Los valores en el rango B5:B29 están calculados con la fórmula

=DISTR.NORM(A5;Media;DesvEst;FALSO)

donde Media  DesvEst es el nombre de la celda B1 y Media el de la celda B2. Los valores del rango A5:A29 son el resultado de tomar 3 desvíos estándar de “izquierda a derecha” (-3 x DesvEst = -3).
También podemos representar la distribución normal con un gráfico de área



En resumen, para crear un gráfico de distribución normal necesitamos la serie que de datos que queremos analizar, obviamente, la función PROMEDIO, la función DESVEST para calcular el desvío estándar y la función DISTR.NORM para calcular la distribución normal para la media y desviación estándar de cada dato de la serie.


Para ampliar un poco más la explicación pongamos por ejemplo una serie de 500 datos.



Los datos los hemos puesto en el rango Datos (A2:A501); en E1 hemos calculado el promedio de los datos y en E2 el desvío estándar.
En el rango C5:C25 creamos una serie de grupos, es decir, intervalos de frecuencia de los datos. Para calcular cuántos datos caen en cada grupo usamos la función FRECUENCIA

={FRECUENCIA(datos;grupos)}

Esta es una función matricial y la introducimos pulsando Ctrl+Mayúsculas +Enter luego de haber seleccionado todo el rango.
Finalmente calculamos al distribución normal con

=DISTR.NORM(C5;$E$1;$E$2;FALSO)

en el rango E5:E25

Con estos datos podemos crear un histograma (también pueden consultar esta nota) y en el mismo gráfico agregar la distribución normal de los datos con un gráfico de línea (usando un eje secundario)




Este gráfico nos permite comparar el histograma creado a partir de los datos con la distribución normal teórica.

El archivo de este ejemplo se puede descargar aquí.



Technorati Tags: MS Excel