Introducción a la simulación Monte Carlo en Excel

• ¿Quién utiliza la simulación Monte Carlo?

• ¿Qué sucede cuando escribe =ALEAT() en una celda?

• ¿Cómo se pueden simular valores de una variable aleatoria discreta?

• ¿Cómo se pueden simular valores de una variable aleatoria normal?

• ¿Cómo puede una empresa de tarjetas de felicitación determinar cuántas tarjetas producir?

Nos gustaría estimar con precisión las probabilidades de eventos inciertos. Por ejemplo, ¿cuál es la probabilidad de que los flujos de caja de un nuevo producto tengan un valor neto actual positivo (VNA)? ¿Cuál es el factor de riesgo de nuestra cartera de inversión? La simulación Monte Carlo nos permite modelar situaciones que presentan incertidumbre y luego jugar en un ordenador miles de veces.

En los próximos cinco capítulos, verá ejemplos de cómo puede usar Excel para realizar simulaciones montecarlo.

Muchas compañías utilizan la simulación Monte Carlo como parte importante de su proceso de toma de decisiones. Estos son algunos ejemplos.

• General Motors, Proctor and Gamers, Pfizer, Bristol-Myers Squibb y Eli Lilly utilizan simulación para estimar tanto el retorno medio como el factor de riesgo de los nuevos productos. En GM, esta información es utilizada por el CEO para determinar qué productos llegan al mercado.

• GM utiliza simulación para actividades tales como la previsión de ingresos netos para la empresa, la predicción de costos estructurales y de compra, y la determinación de su susceptibilidad a diferentes tipos de riesgo (tales como cambios en los tipos de interés y fluctuaciones de los tipos de cambio).

• Lilly utiliza simulación para determinar la capacidad óptima de la planta para cada fármaco.

• Proctor y Gamers utiliza simulación para modelar y cubrir de forma óptima el riesgo de cambio de divisas.

• Sears utiliza simulación para determinar cuántas unidades de cada línea de productos deben solicitarse a los proveedores, por ejemplo, el número de pares de pantalones Dockers que deben solicitarse este año.

• Las compañías petroleras y farmacéuticas utilizan simulación para valorar "opciones reales", como el valor de una opción para expandir, contraer o posponer un proyecto.

• Los planificadores financieros utilizan la simulación Monte Carlo para determinar estrategias de inversión óptimas para la jubilación de sus clientes.

Cuando escribe la fórmula =ALEATORIO() en una celda, obtiene un número que es igualmente probable que asuma cualquier valor entre 0 y 1. Por lo tanto, alrededor del 25 por ciento del tiempo, debe obtener un número menor o igual que 0,25; alrededor del 10 por ciento del tiempo debería obtener un número que sea al menos 0,90, y así sucesivamente. Para demostrar cómo funciona la función RAND, eche un vistazo al archivo Randdemo.xlsx, que se muestra en la figura 60-1.

En primer lugar, copie de la celda C3 a C4:C402 la fórmula =ALEATORIO(). A continuación, asigne un nombre al rango C3:C402 Datos. Después, en la columna F, puede realizar un seguimiento del promedio de los 400 números aleatorios (celda F2) y usar la función CONTAR.SI para determinar las fracciones entre 0 y 0,25, 0,25 y 0,50, 0,50 y 0,75, y 0,75 y 1. Al presionar la tecla F9, se vuelven a calcular los números aleatorios. Observe que el promedio de los 400 números es siempre aproximadamente 0,5 y que alrededor del 25 por ciento de los resultados están en intervalos de 0,25. Estos resultados son coherentes con la definición de un número aleatorio. Tenga también en cuenta que los valores generados por RAND en celdas diferentes son independientes. Por ejemplo, si el número aleatorio generado en la celda C3 es un número grande (por ejemplo, 0,99), no nos indica nada sobre los valores de los demás números aleatorios generados.

Suponga que la demanda de un calendario se rige por la siguiente variable aleatoria discreta:

Demanda	Probabilidad
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

¿Cómo podemos hacer que Excel reproduzca o simule esta demanda de calendarios muchas veces? El truco consiste en asociar cada valor posible de la función ALEATORIO a una posible demanda de calendarios. La siguiente asignación garantiza que una demanda de 10 000 se producirá el 10 por ciento del tiempo, y así sucesivamente.

Demanda	Número aleatorio asignado
10 000	Menor que 0,10
20 000	Mayor o igual que 0,10 y menor que 0,45
40,000	Mayor o igual que 0,45 y menor que 0,75
60 000	Mayor o igual que 0,75

Para demostrar la simulación de la demanda, mire el archivo Discretesim.xlsx, que se muestra en la figura 60-2 en la página siguiente.

La clave de nuestra simulación es utilizar un número aleatorio para iniciar una búsqueda desde el rango de la tabla F2:G5 ( búsqueda con nombre). Los números aleatorios mayores o iguales que 0 y menores que 0,10 producirán una demanda de 10.000; los números aleatorios mayores o iguales a 0,10 y menores que 0,45 producirán una demanda de 20.000; los números aleatorios mayores o iguales a 0,45 y menores que 0,75 producirán una demanda de 40.000; y los números aleatorios mayores o iguales a 0,75 producirán una demanda de 60.000. Para generar 400 números aleatorios, copie de C3 a C4:C402 la fórmula ALEATORIO(). Después, se generan 400 pruebas o iteraciones de la demanda de calendario copiando de B3 a B4:B402 la fórmula BUSCARV(C3,búsqueda,2). Esta fórmula garantiza que cualquier número aleatorio menor que 0,10 genere una demanda de 10.000, cualquier número aleatorio entre 0,10 y 0,45 genera una demanda de 20.000, y así sucesivamente. En el rango de celdas F8:F11, use la función CONTAR.SI para determinar la fracción de nuestras 400 iteraciones que producen cada demanda. Cuando presionamos F9 para volver a calcular los números aleatorios, las probabilidades simuladas se aproximan a nuestras probabilidades de demanda asumidas.

Si escribe en cualquier celda la fórmula DISTR.NORM.INV(rand(),mu,sigma), generará un valor simulado de una variable aleatoria normal que tenga una media mu y una desviación estándar sigma. Este procedimiento se ilustra en la Normalsim.xlsx del archivo, que se muestra en la figura 60-3.

Supongamos que queremos simular 400 ensayos o iteraciones para una variable aleatoria normal con una media de 40 000 y una desviación estándar de 10 000. (Puede escribir estos valores en las celdas E1 y E2, y denominar estas celdas media y sigma, respectivamente). Copiar la fórmula =ALEATORIO() de C4 a C5:C403 genera 400 números aleatorios diferentes. Al copiar de B4 a B5:B403, la fórmula DISTR.NORM.INV(C4,media,sigma) genera 400 valores de prueba diferentes a partir de una variable aleatoria normal con una media de 40.000 y una desviación estándar de 10.000. Cuando presionamos la tecla F9 para volver a calcular los números aleatorios, la media permanece cercana a 40 000 y la desviación estándar cerca de 10 000.

Básicamente, para un número aleatorio x, la fórmula DISTR.NORM.INV(p,mu,sigma) genera el percentil pth de una variable aleatoria normal con una media mu y una desviación estándar sigma. Por ejemplo, el número aleatorio 0,77 en la celda C4 (vea la figura 60-3) genera en la celda B4 aproximadamente el percentil 77 de una variable aleatoria normal con una media de 40.000 y una desviación estándar de 10.000.

En esta sección, verá cómo se puede utilizar la simulación Monte Carlo como una herramienta de toma de decisiones. Supongamos que la demanda de una tarjeta del Día de San Valentín se rige por la siguiente variable aleatoria discreta:

Demanda	Probabilidad
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

La tarjeta de felicitación se vende por $4.00, y el costo variable de producir cada tarjeta es $1.50. Las tarjetas sobradas deben eliminarse a un costo de $0.20 por tarjeta. ¿Cuántas tarjetas deben imprimirse?

Básicamente, simulamos cada cantidad de producción posible (10 000, 20 000, 40 000 o 60 000) muchas veces (por ejemplo, 1000 iteraciones). A continuación, determinamos qué cantidad de pedido produce el beneficio promedio máximo sobre las 1000 iteraciones. Puede encontrar los datos de esta sección en la Valentine.xlsx de archivos, que se muestra en la figura 60-4. Asigne los nombres de rango de las celdas B1:B11 a las celdas C1:C11. Al rango de celdas G3:H6 se le asigna la búsqueda de nombres. Nuestros parámetros de precio de venta y costo se introducen en las celdas C4:C6.

Puede especificar una cantidad de producción de prueba (40 000 en este ejemplo) en la celda C1. A continuación, cree un número aleatorio en la celda C2 con la fórmula =ALEATORIO(). Como se describió anteriormente, simule la demanda de la tarjeta en la celda C3 con la fórmula BUSCARV(aleatorio,búsqueda,2). (En la fórmula BUSCARV, aleatorio es el nombre de celda asignado a la celda C3, no la función ALEAT).

El número de unidades vendidas es el menor de nuestra cantidad y demanda de producción. En la celda C8, calcula nuestros ingresos con la fórmula MIN(producido,demanda)*unit_price. En la celda C9, calcula el costo de producción total con la fórmula producida*unit_prod_cost.

Si producimos más tarjetas de las demandas, el número de unidades que quedan equivale a producción menos demanda; de lo contrario, no hay unidades restantes. Calculamos nuestro costo de eliminación en la celda C10 con la fórmula unit_disp_cost*SI(produjo>demanda,producción-demanda,0). Por último, en la celda C11, calculamos nuestros beneficios como ingresos total_var_cost-total_disposing_cost.

Nos gustaría una manera eficiente de presionar F9 muchas veces (por ejemplo, 1000) por cada cantidad de producción y contar nuestros beneficios esperados para cada cantidad. Esta situación es una en la que una tabla de datos bidireccional llega a nuestro rescate. (Consulte el Capítulo 15, "Análisis de sensibilidad con tablas de datos", para obtener más información sobre las tablas de datos). La tabla de datos usada en este ejemplo se muestra en la figura 60-5.

En el rango de celdas A16:A1015, escriba los números 1–1000 (correspondientes a nuestras 1000 pruebas). Una forma sencilla de crear estos valores es comenzar escribiendo 1 en la celda A16. Seleccione la celda y, a continuación, en la pestaña Inicio del grupo Edición , haga clic en Rellenar y seleccione Serie para mostrar el cuadro de diálogo Serie . En el cuadro de diálogo Serie , que se muestra en la figura 60-6, escriba un valor de paso de 1 y un valor de detención de 1000. En el área Serie en , seleccione la opción Columnas y, a continuación, haga clic en Aceptar. Los números del 1 al 1000 se introducirán en la columna A a partir de la celda A16.

A continuación, especificamos nuestras posibles cantidades de producción (10.000, 20.000, 40.000 y 60.000) en las celdas B15:E15. Queremos calcular los beneficios de cada número de prueba (del 1 al 1000) y de cada cantidad de producción. Nos referimos a la fórmula de beneficios (calculada en la celda C11) en la celda superior izquierda de nuestra tabla de datos (A15) escribiendo =C11.

Ahora estamos listos para engañar a Excel para que simula 1000 iteraciones de demanda para cada cantidad de producción. Seleccione el rango de la tabla (A15:E1014) y, a continuación, en el grupo Herramientas de datos de la pestaña Datos, haga clic en Análisis Y si y, a continuación, seleccione Tabla de datos. Para configurar una tabla de datos bidireccional, elija nuestra cantidad de producción (celda C1) como celda de entrada de fila y seleccione cualquier celda en blanco (elegimos la celda I14) como celda de entrada de columna. Después de hacer clic en Aceptar, Excel simula 1000 valores de demanda para cada cantidad de pedido.

Para comprender por qué funciona esto, considere los valores colocados por la tabla de datos en el rango de celdas C16:C1015. Para cada una de estas celdas, Excel usará un valor de 20.000 en la celda C1. En C16, el valor de la celda de entrada de la columna 1 se coloca en una celda en blanco y se actualiza el número aleatorio de la celda C2. A continuación, se registra el beneficio correspondiente en la celda C16. A continuación, el valor de entrada de la celda de columna 2 se coloca en una celda en blanco y el número aleatorio en C2 se vuelve a calcular. El beneficio correspondiente se introduce en la celda C17.

Al copiar de la celda B13 a C13:E13 la fórmula PROMEDIO(B16:B1015) calculamos el promedio de beneficios simulados para cada cantidad de producción. Al copiar de la celda B14 a C14:E14 la fórmula DESVEST(B16:B1015) calculamos la desviación estándar de nuestros beneficios simulados para cada cantidad de pedido. Cada vez que presionamos F9, se simulan 1000 iteraciones de demanda para cada cantidad de pedido. Producir 40.000 tarjetas siempre produce el mayor beneficio esperado. Por lo tanto, parece que producir 40.000 tarjetas es la decisión correcta.

El impacto del riesgo en nuestra decisión      Si produjimos 20.000 tarjetas en lugar de 40.000, nuestro beneficio esperado cae aproximadamente 22 por ciento, pero nuestro riesgo (medido por la desviación estándar de beneficios) cae casi 73 por ciento. Por lo tanto, si somos extremadamente inversos al riesgo, producir 20.000 tarjetas podría ser la decisión correcta. Por cierto, la producción de 10.000 tarjetas siempre tiene una desviación estándar de 0 tarjetas porque si producimos 10.000 tarjetas, siempre las venderemos todas sin sobras.

Intervalo de confianza para las ganancias medias      Una pregunta natural para preguntar en esta situación es, en qué intervalo estamos 95 por ciento seguro de que el verdadero beneficio medio caerá? Este intervalo se denomina intervalo de confianza del 95 por ciento para el beneficio medio. Un intervalo de confianza del 95 por ciento para la media de cualquier salida de simulación se calcula con la siguiente fórmula:

En la celda J11, calcula el límite inferior para el intervalo de confianza del 95 por ciento en el beneficio medio cuando se producen 40 000 calendarios con la fórmula D13–1,96*D14/SQRT(1000). En la celda J12, calcula el límite superior de nuestro intervalo de confianza del 95 por ciento con la fórmula D13+1,96*D14/SQRT(1000). Estos cálculos se muestran en la Figura 60-7.

Estamos 95 % seguros de que nuestro beneficio medio cuando se ordenan 40.000 calendarios está entre 56.687 y 62.589 $.

1. Un distribuidor de GMC cree que la demanda de Enviados de 2005 se distribuirá normalmente con una media de 200 y una desviación estándar de 30. Su costo de recibir un Enviado es de $25,000, y vende un Enviado por $40,000. La mitad de los Enviados que no se venden a precio total se pueden vender por 30.000 dólares. Está considerando ordenar 200, 220, 240, 260, 280 o 300 Enviados. ¿Cuántos debería ordenar?

2. Un pequeño supermercado está tratando de determinar cuántas copias de Personas revista deben pedir cada semana. Creen que su demanda de Personas se rige por la siguiente variable aleatoria discreta:

   Demanda	Probabilidad
   15	0,10
   20	0,20
   25	0.30
   30	0,25
   35	0,15

3. El supermercado paga $1.00 por cada copia de Personas y lo vende por $1.95. Cada copia no vendida puede devolverse por 0,50 $. ¿Cuántas copias de Personas debe pedir la tienda?