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

Nota: Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Este artículo es una adaptación de análisis de datos de Microsoft Excel y modelaDo empresarial de Wayne L. Winston.

  • ¿Quién usa la simulación de Carlo de Monte?

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

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

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

  • ¿Cómo puede determinar la compañía de la tarjeta de felicitación Cuántas tarjetas quiere producir?

Queremos estimar de manera precisa las probabilidades de que haya inciertos eventos. Por ejemplo, ¿cuál es la probabilidad de que los flujos de efectivo de un nuevo producto tengan un valor neto actual positivo (NPV)? ¿Cuál es el factor de riesgo de nuestra cartera de inversiones? La simulación de Monte Carlo nos permite modelar situaciones que presenten incertidumbre y reproducirlas en un equipo miles de veces.

Nota:  La simulación de nombre Monte Carlo viene de las simulaciones informáticas realizadas durante la 1930s y 1940s para estimar la probabilidad de que la reacción de la cadena necesaria para que la detonación de la bomba Atom funcione correctamente. El Physicists implicado en este trabajo fueron grandes entusiastas de las apuestas, por lo que dieron a las simulaciones el nombre de código Monte Carlo.

En los cinco capítulos siguientes, verá ejemplos de cómo puede usar Excel para realizar simulaciones de Monte Carlo.

Muchas empresas usan la simulación de Monte Carlo como una parte importante de su proceso de toma de decisiones. A continuación se muestran algunos ejemplos.

  • General Motors, Proctor y apuesta, Pfizer, Bristol-Myers Squibb y Eli Lilly utilizan simulación para calcular tanto el retorno promedio como el factor de riesgo de nuevos productos. En GM, esta información es utilizada por el CEO para determinar los productos que se comercializan.

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

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

  • Proctor y la apuesta usa la simulación para modelar y mejorar el riesgo de los extranjeros en el extranjero.

  • Sears usa simulación para determinar cuántas unidades de cada línea de producto debe solicitarse a los proveedores, por ejemplo, el número de pares de trousers que se deben pedir este año.

  • Las empresas de drogas y petróleo usan una simulación para "opciones reales", como el valor de una opción para expandir, contraer o contraer un proyecto.

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

Cuando escribe la fórmula = Rand () en una celda, obtiene un número que es igualmente probable que se asuma cualquier valor comprendido entre 0 y 1. Por lo tanto, aproximadamente el 25 por ciento de las veces, deberías obtener un número menor o igual que 0,25; aproximadamente el 10 por ciento del tiempo, deberías obtener un número 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.

Imagen del libro

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 aleatorio siempre vuelve a calcular automáticamente los números que genera cuando se abre una hoja de cálculo o cuando se escribe nueva información en la hoja de cálculo.

En primer lugar, copie de la celda C3 a C4: C402 = Rand (). A continuación, se denomina el rango C3: C402 Data. Después, en la columna F, puede realizar un seguimiento del promedio de los números aleatorios de 400 (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 números de 400 es siempre de 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. Además, tenga 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 se comunica nada sobre los valores de los otros números aleatorios generados.

SuPongamos que la demanda de un calendario está regida por la siguiente variable aleatoria:

Petición

Probabilidad

10 000

0,10

20 000

0,35

40.000

0,3

60 000

0,25

¿Cómo se puede hacer que Excel juegue o simule esta demanda de calendarios varias veces? El truco es asociar cada valor posible de la función RAND con una posible demanda para calendarios. La siguiente asignación asegura que una demanda de 10.000 se producirá el 10 por ciento de la hora, y así sucesivamente.

Petición

Número aleatorio asignado

10 000

Menos de 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 de la página siguiente.

Imagen del libro

La clave de nuestra simulación es usar un número aleatorio para iniciar una búsqueda desde el rango de tabla F2: G5 (llamada consulta). Los números aleatorios mayores o iguales que 0 y menores que 0,10 generarán una demanda de 10.000; los números aleatorios mayores o iguales que 0,10 y menores que 0,45 producirán una demanda de 20.000; los números aleatorios mayores o iguales que 0,45 y menores que 0,75 producirán una demanda de 40.000; los números aleatorios mayores o iguales que 0,75 generarán una demanda de 60.000. Los números aleatorios de 400 se generan copiando de C3 a C4: C402 la fórmula Rand (). Después, puede generar pruebas de 400, o iteraciones, de la demanda del 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 genera una petición de 10.000, cualquier número aleatorio entre 0,10 y 0,45 genera una petición 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 las iteraciones de 400 que producen cada demanda. Cuando presione F9 para volver a calcular los números aleatorios, las probabilidades simuladas estarán cerca de nuestras probabilidades de demanda asumidas.

Si escribe una celda, la fórmula DISTR. Norm (), MU, Sigma, generará un valor simulado de una variable aleatoria normal con una media de MU y una Sigmade desviación estándar. Este procedimiento se muestra en el archivo Normalsim. xlsx, que se muestra en la figura 60-3.

Imagen del libro

Supongamos que queremos simular pruebas de 400, 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 a estas celdas media y Sigma, respectivamente). Copiar la fórmula = Rand () de C4 a C5: C403 genera 400 números aleatorios diferentes. Copiar de B4 a B5: B403 la fórmula NORMINV (C4, media, Sigma) genera 400 diferentes valores de prueba a partir de una variable aleatoria normal con una media de 40.000 y una desviación estándar de 10.000. Cuando presionemos la tecla F9 para volver a calcular los números aleatorios, la media seguirá cerca de 40.000 y la desviación estándar cercana a 10.000.

Esencialmente, para un número aleatorio x, la fórmula NORMINV (p, MU, Sigma) genera el percentil p-ésimo de una variable aleatoria normal con una media de MU y un Sigmade desviación estándar. Por ejemplo, el número aleatorio 0,77 de la celda C4 (consulte la figura 60-3) genera en la celda B4 aproximadamente el percentil 77th 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 usar la simulación de Monte Carlo como herramienta para la toma de decisiones. Suponga que la demanda de una tarjeta de día de San Valentín se rige por la siguiente variable aleatoria:

Petición

Probabilidad

10 000

0,10

20 000

0,35

40.000

0,3

60 000

0,25

La tarjeta de felicitación vende por $4,00 y el costo variable de producir cada tarjeta es de $1,50. Las cartas sobrantes deben ser eliminadas a un coste de $0,20 por tarjeta. ¿Cuántas tarjetas deben imprimirse?

En esencia, simulamos cada cantidad de producción posible (10.000, 20.000, 40.000 o 60.000) varias veces (por ejemplo, 1000 iteraciones). A continuación, determinamos la cantidad de pedidos que genera la ganancia media máxima a través de las iteraciones de 1000. Puede encontrar los datos para esta sección en el archivo San Valentín. xlsx, que se muestra en la figura 60-4. Asigne los nombres de rango en las celdas B1: B11 a las celdas C1: C11. Al rango de celdas G3: H6 se le asigna la búsquedade nombres. Nuestros parámetros de precio de venta y costo se especifican en las celdas C4: C6.

Imagen del libro

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 = Rand (). Como se ha descrito anteriormente, simula la demanda de la tarjeta de la celda C3 con la fórmula BUSCARV (Rand, lookup, 2). (En la fórmula de BUSCARV, Rand es el nombre de la celda asignada 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, calculará los ingresos con la fórmula mín (producido, demanda) * unit_price. En la celda C9, calcule el costo total de producción con la fórmula * unit_prod_cost.

Si se producen más tarjetas que las que se encuentran en la demanda, la cantidad de unidades restantes es igual a la producción menos la demanda; en caso contrario, no queda ninguna unidad. Calculamos el costo de la cancelación en la celda C10 con la fórmula unit_disp_cost * si (produced>demand, producida, Demand, 0). Por último, en la celda C11, calculamos nuestros beneficios como ingresos – total_var_cost-total_disposing_cost.

Nos gustaría tener una forma eficaz de pulsar F9 varias veces (por ejemplo, 1000) para cada cantidad de producción y calcular el beneficio previsto para cada cantidad. Esta situación es una en la que llega nuestro rescate una tabla de datos bidireccional. (Para obtener más información sobre las tablas de datos, consulte el capítulo 15, "Análisis de sensibilidad con tablas de datos"). La tabla de datos que se usa en este ejemplo se muestra en la figura 60-5.

Imagen del libro

En el rango de celdas A16: A1015, escriba los números del 1 al 1000 (correspondientes a nuestras pruebas de 1000). 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 , en el grupo edición , haga clic en rellenoy seleccione serie para mostrar el cuadro de diálogo serie . En el cuadro de diálogo serie , que se muestra en la ilustración 60-6, escriba un valor de paso de 1 y un valor Stop de 1000. En el área series en , 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, empezando por la celda A16.

Imagen del libro

A continuación, especificamos las 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 (1 a 1000) y cada cantidad de producción. En la celda superior izquierda de la tabla de datos (A15), hacemos referencia a la fórmula de beneficio (calculado en la celda C11) escribiendo = C11.

Ahora ya podemos engañar a Excel para simular 1000 iteraciones de demanda por cada cantidad de producción. Seleccione el rango de tabla (A15: E1014) y, a continuación, en el grupo herramientas de datos de la pestaña datos, haga clic en análisis si y, a continuación, seleccione Tabla de datos. Para configurar una tabla de datos bidireccionales, seleccione 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 la columna. Después de hacer clic en aceptar, Excel simula los valores de petición de 1000 para cada cantidad de pedido.

Para comprender por qué funciona esto, considere los valores colocados en la tabla de datos en el rango de celdas C16: C1015. Para cada una de estas celdas, Excel usará el valor 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 vuelve a calcular el número aleatorio de la celda C2. A continuación, se registra el beneficio correspondiente en la celda C16. Después, el valor de entrada de celda de la columna 2 se coloca en una celda en blanco y vuelve a calcularse el número aleatorio en C2. El beneficio correspondiente se especifica en la celda C17.

Copiando de la celda B13 a C13: e13 la fórmula Average (B16: B1015), calculamos el promedio de beneficio simulado para cada cantidad de producción. Copiando de la celda B14 a C14: E14 la fórmula stdev (B16: B1015), calculamos la desviación estándar de nuestros beneficios simulados para cada cantidad de pedido. Cada vez que pulsemos F9, se simularán 1000 iteraciones de demanda para cada cantidad de pedido. La creación de tarjetas de 40.000 siempre da como resultado el mayor beneficio esperado. Por lo tanto, parece que la creación de tarjetas de 40.000 es la decisión correcta.

El impacto del riesgo en nuestra decisión     Si fabricamos 20.000 en lugar de las tarjetas 40.000, nuestro resultado esperado es de aproximadamente un 22%, pero nuestro riesgo (medido por la desviación estándar de beneficios) reduce el 73 por ciento. Por lo tanto, si tenemos averses riesgos, la creación de tarjetas de 20.000 podría ser la decisión correcta. Por cierto, la creación de tarjetas de 10.000 siempre tiene una desviación estándar de 0 tarjetas, ya que si se producen 10.000 tarjetas, siempre nos venderemos todas sin restos.

Nota:  En este libro, la opción de cálculo se establece en automático excepto en las tablas. (Use el comando cálculo en el grupo cálculo de la pestaña fórmulas). Esta configuración garantiza que la tabla de datos no se recalcule a menos que pulse F9, lo cual es una buena idea porque una gran tabla de datos ralentizará su trabajo si se actualiza cada vez que escribe algo en la hoja de cálculo. Tenga en cuenta que en este ejemplo, cada vez que presione F9, cambiarán los beneficios medios. Esto sucede porque cada vez que presione F9, se usa una secuencia diferente de números aleatorios de 1000 para generar demandas para cada cantidad de pedido.

Intervalo de confianza para las ganancias medias     Una pregunta natural que se debe formular en esta situación es, en qué intervalo es 95 por ciento el porcentaje seguro de que se obtendrán ganancias reales. Este intervalo se denomina intervalo de confianza del 95 por ciento para los beneficios de media. Un intervalo de confianza del 95 por ciento para la media de cualquier salida de simulación se calcula mediante la siguiente fórmula:

Imagen del libro

En la celda J11, calcula el límite inferior para el intervalo de confianza de 95 por promedio de beneficio, cuando se generan los calendarios de 40.000 con la fórmula D13 – 1.96 * D14/raiz (1000). En la celda J12, calcule el límite superior para el intervalo de confianza de 95 por ciento con la fórmula D13 + 1.96 * D14/raiz (1000). Estos cálculos se muestran en la figura 60-7.

Imagen del libro

Estamos seguros de que nuestro 95 por ciento de la media de beneficios es de entre $56.687 y $62.589.

  1. Un distribuidor GMC cree que la demanda de envíos de 2005 se distribuirá normalmente con una media de 200 y una desviación estándar de 30. Su costo de recibir un envío es $25.000 y vende un envío para $40.000. La mitad de los envíos no vendidos a todo el precio se pueden vender por $30.000. Está pensando en el ordenamiento de envíos de 200, 220, 240, 260, 280 o 300. ¿Cuántos debe pedir?

  2. Un pequeño supermercado está intentando determinar cuántas copias de la revista de personas deben pedir cada semana. Creemos que su demanda de personas está regida por la siguiente variable aleatoria:

    Petición

    Probabilidad

    15

    0,10

    20

    0,20

    veinticinco

    0,30

    0,30

    0,25

    35

    0,15

  3. El supermercado paga $1,00 por cada copia de personas y la vende para $1,95. Cada copia no vendida puede ser devuelta por $0,50. ¿Cuántas copias de las personas deben almacenarse en la tienda?

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×