Este artículo fue adaptado de Microsoft Excel Data Analysis and Business Modeling por Wayne L. Winston.
Información general
- ¿Quién usa la simulación de Monte Carlo?
- ¿Qué ocurre cuando escribe =RAND() en una celda?
- ¿Cómo se pueden simular los valores de una variable aleatoria discreta?
- ¿Cómo se pueden simular los 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 efectivo de un nuevo producto tengan un valor actual neto (VAN) positivo? ¿Cuál es el factor de riesgo de nuestra cartera de inversiones? La simulación de Monte Carlo nos permite modelar situaciones que presentan incertidumbre y luego reproducirlas en una computadora miles de veces.
Nota
El nombre de simulación de Monte Carlo proviene de las simulaciones por computadora realizadas durante las décadas de 1930 y 1940 para estimar la probabilidad de que la reacción en cadena necesaria para que una bomba atómica detonara funcionara con éxito. Los físicos involucrados en este trabajo eran grandes fanáticos de los juegos de azar, por lo que le dieron a las simulaciones el nombre en clave de Monte Carlo.
En los próximos cinco capítulos verá ejemplos de cómo puede usar Excel para realizar simulaciones de Monte Carlo.
¿Quién usa la simulación de Monte Carlo?
Muchas empresas utilizan la simulación de Monte Carlo como una parte importante de su proceso de toma de decisiones. Estos son algunos ejemplos.
- General Motors, Procter and Gamble, Pfizer, Bristol-Myers Squibb y Eli Lilly utilizan la simulación para estimar tanto el rendimiento promedio como el factor de riesgo de los nuevos productos. En GM, el CEO utiliza esta información para determinar qué productos llegan al mercado.
- GM utiliza la simulación para actividades como pronosticar ingresos netos para la corporación, predecir costos estructurales y de compra y determinar su susceptibilidad a diferentes tipos de riesgo (como cambios en las tasas de interés y fluctuaciones del tipo de cambio).
- Lilly utiliza la simulación para determinar la capacidad óptima de la planta para cada medicamento.
- Procter and Gamble utiliza la simulación para modelar y cubrir de manera óptima el riesgo de tipo de cambio.
- Sears utiliza la simulación para determinar cuántas unidades de cada línea de productos se deben pedir a los proveedores, por ejemplo, la cantidad de pares de pantalones Dockers que se deben pedir este año.
- Las compañías petroleras y farmacéuticas utilizan la 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 de Monte Carlo para determinar las estrategias de inversión óptimas para la jubilación de sus clientes.
¿Qué ocurre cuando escribe =RAND() en una celda?
Al escribir la fórmula =RAND() en una celda, obtiene un número con el que es igualmente probable que asuma cualquier valor entre 0 y 1. Por lo tanto, alrededor del 25 por ciento de las veces, debe obtener un número menor o igual a 0,25; alrededor del 10 por ciento de las veces debe obtener un número que sea de 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.
Nota
Al abrir el archivo Randdemo.xlsx, no verá los mismos números aleatorios que se muestran en la Figura 60-1. La función ALEAT siempre recalcula automáticamente los números que genera cuando se abre una hoja de cálculo o cuando se introduce nueva información en la hoja de cálculo.
En primer lugar, copie de la celda C3 a C4:C402 la fórmula =RAND(). A continuación, asigne al rango el nombre C3:C402 Data. 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 que están 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 consistentes con la definición de un número aleatorio. Además, tenga en cuenta que los valores generados por RAND en distintas celdas 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 dice nada sobre los valores de los otros números aleatorios generados.
¿Cómo se pueden simular los valores de una variable aleatoria discreta?
Supongamos que la demanda de un calendario está gobernada 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 RAND con una posible demanda de calendarios. La siguiente asignación garantiza que se producirá una demanda de 10 000 el 10 por ciento de las veces, 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, observe el archivo Discretesim.xlsx, que se muestra en la Figura 60-2 de la página siguiente.
La clave de nuestra simulación es usar un número aleatorio para iniciar una búsqueda en el rango de tabla F2:G5 (denominado búsqueda). 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. Se generan 400 números aleatorios copiando de C3 a C4:C402 la fórmula RAND(). A continuación, generará 400 pruebas o iteraciones de petición de calendario copiando de B3 a B4:B402 la fórmula BUSCARV(C3;BUSCAR,2). Esta fórmula garantiza que cualquier número aleatorio menor que 0,10 genera 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 recalcular los números aleatorios, las probabilidades simuladas están cerca de nuestras probabilidades de demanda asumidas.
¿Cómo se pueden simular los valores de una variable aleatoria normal?
Si escribe en cualquier celda la fórmula NORMINV(rand(),mu,sigma), generará un valor simulado de una variable aleatoria normal que tiene una media mu y una desviación estándar sigma. Este procedimiento se ilustra en el archivo Normalsim.xlsx, 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 denominarlas media y sigma, respectivamente). Si copia la fórmula =RAND() de C4 a C5:C403, se generarán 400 números aleatorios diferentes. Copiando de B4 a B5:B403, la fórmula NORMINV(C4,mean,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 recalcular los números aleatorios, la media permanece cerca de 40,000 y la desviación estándar cerca de 10,000.
Esencialmente, para un número aleatorio x, la fórmula NORMINV(p,mu,sigma)genera elp-ésimo percentil 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 (ver 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.
¿Cómo puede una empresa de tarjetas de felicitación determinar cuántas tarjetas producir?
En esta sección, verá cómo se puede utilizar la simulación de Monte Carlo como herramienta para la toma de decisiones. Supongamos que la demanda de una tarjeta del Día de San Valentín está gobernada 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 de $ 1.50. Las tarjetas sobrantes deben desecharse a un costo de $0.20 por tarjeta. ¿Cuántas tarjetas se deben imprimir?
Básicamente, simulamos cada cantidad de producción posible (10,000, 20,000, 40,000 o 60,000) muchas veces (por ejemplo, 1000 iteraciones). Luego, determinamos qué cantidad de pedido produce la ganancia promedio máxima en las 1000 iteraciones. Puede encontrar los datos de esta sección en el archivo Valentine.xlsx, que se muestra en la figura 60-4. Los nombres de rango de las celdas B1:B11 se asignan a las celdas C1:C11. Al rango de celdas G3:H6 se le asigna la búsqueda de nombres. Nuestros parámetros de precio y coste de venta se introducen en las celdas C4:C6.
Puede introducir 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 =RAND(). Como se ha descrito anteriormente, se simula la demanda de la tarjeta en la celda C3 con la fórmula BUSCARV(rand,lookup,2). (En la fórmula BUSCARV, rand es el nombre de celda asignado a la celda C3, no la función RAND).
El número de unidades vendidas es el menor de nuestra cantidad y demanda de producción. En la celda C8, se calculan nuestros ingresos con la fórmula MIN(producido,demanda)*unit_price. En la celda C9, se calcula el coste total de producción con la fórmula producida*unit_prod_cost.
Si producimos más tarjetas de las que están en demanda, el número de unidades sobrantes es igual a la producción menos la demanda; de lo contrario, no quedan unidades. Calculamos nuestro costo de eliminación en la celda C10 con la fórmula unit_disp_cost*SI(demanda producida,demanda>producida–0). Finalmente, en la celda C11, calculamos nuestra ganancia como ingresos: total_var_cost-total_disposing_cost.
Nos gustaría una forma eficiente de presionar F9 muchas veces (por ejemplo, 1000) para cada cantidad de producción y contar nuestra ganancia esperada para cada cantidad. Esta situación es una en la que una tabla de datos bidireccional viene a nuestro rescate. (Consulte el Capítulo 15, "Análisis de sensibilidad con tablas de datos", para obtener detalles sobre las tablas de datos). La tabla de datos utilizada en este ejemplo se muestra en la Figura 60-5.
En el rango de celdas A16:A1015, escriba los números del 1 al 1000 (correspondientes a nuestras 1000 pruebas). Una manera fácil de crear estos valores es empezar 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 parada de 1000. En el área Entrada de la serie , seleccione la opción Columnas y, a continuación, haga clic en Aceptar. Los números del 1 al 1000 se escribirán en la columna A, a partir de la celda A16.
A continuación, introducimos nuestras posibles cantidades de producción (10.000, 20.000, 40.000, 60.000) en las celdas B15:E15. Queremos calcular las ganancias para cada número de prueba (del 1 al 1000) y cada cantidad de producción. Nos referimos a la fórmula de ganancias (calculada en la celda C11) en la celda superior izquierda de nuestra tabla de datos (A15) ingresando =C11.
Ahora estamos listos para engañar a Excel para que simule 1000 iteraciones de demanda para cada cantidad de producción. Seleccione el rango de tablas (A15:E1014) y, a continuación, en el grupo Herramientas de datos de la pestaña Datos, haga clic en Análisis y 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 entender por qué funciona esto, considere los valores colocados por la tabla de datos en el rango de celdas C16:C1015. En 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 columna 1 se coloca en una celda en blanco y se vuelve a calcular el número aleatorio de la celda C2. El beneficio correspondiente se registra en la celda C16. A continuación, el valor de entrada de celda de columna 2 se coloca en una celda en blanco y el número aleatorio de C2 vuelve a calcularse. El beneficio correspondiente se especifica en la celda C17.
Al copiar de la celda B13 a C13:E13 la fórmula PROMEDIO(B16:B1015), calculamos el beneficio promedio simulado para cada cantidad de producción. Al copiar de la celda B14 a C14:E14 la fórmula STDEV(B16:B1015), calculamos la desviación estándar de nuestras ganancias simuladas 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 la mayor ganancia esperada. Por lo tanto, parece que producir 40,000 tarjetas es la decisión adecuada.
El impacto del riesgo en nuestra decisión Si producimos 20,000 en lugar de 40,000 tarjetas, nuestra ganancia esperada cae aproximadamente un 22 por ciento, pero nuestro riesgo (medido por la desviación estándar de la ganancia) cae casi un 73 por ciento. Por lo tanto, si somos extremadamente reacios al riesgo, producir 20,000 tarjetas podría ser la decisión correcta. Por cierto, producir 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.
Nota
En este libro, la opción Cálculo está establecida en Automático excepto para tablas. (Use el comando Cálculo del grupo Cálculo de la pestaña Fórmulas). Esta configuración garantiza que nuestra tabla de datos no se recalculará a menos que presionemos F9, lo cual es una buena idea porque una tabla de datos grande ralentizará el trabajo si se vuelve a calcular cada vez que escriba algo en la hoja de cálculo. Tenga en cuenta que en este ejemplo, cada vez que presione F9, cambiará la ganancia media. Esto sucede porque cada vez que presiona F9, se usa una secuencia diferente de 1000 números aleatorios para generar demandas para cada cantidad de pedido.
Intervalo de confianza para beneficio medio Una pregunta natural en esta situación es, ¿en qué intervalo estamos 95 por ciento seguros de que caerá la verdadera ganancia media? 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 resultado de simulación se calcula mediante la siguiente fórmula:
En la celda J11, se calcula el límite inferior para el intervalo de confianza del 95 por ciento sobre 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 para 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 por ciento seguros de que nuestra ganancia media cuando se piden 40,000 calendarios está entre $ 56,687 y $ 62,589.
Problemas
Un concesionario de GMC cree que la demanda de Envoys 2005 se distribuirá normalmente con una media de 200 y una desviación estándar de 30. Su costo de recibir un Envoy es de $ 25,000 y vende un Envoy por $ 40,000. La mitad de todos los Envoys que no se venden a precio completo se pueden vender por $ 30,000. Está considerando pedir 200, 220, 240, 260, 280 o 300 Enviados. ¿Cuántos debería pedir?
Un pequeño supermercado está tratando de determinar cuántas copias de la revista People deben pedir cada semana. Creen que su demanda de People 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 El supermercado paga $ 1.00 por cada copia de People y la vende por $ 1.95. Cada copia no vendida se puede devolver por $ 0.50. ¿Cuántas copias de People debe pedir la tienda?
¿Necesitas más ayuda?
Siempre puede preguntar a un experto en Excel Tech Community u obtener soporte técnico en Comunidades.