Introducción a Montecarlo simulación 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.

En este artículo es una adaptación de análisis de datos de Microsoft Excel y modelado de negocio Wayne L. Winston.

  • ¿Quién usa Montecarlo simulación?

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

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

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

  • ¿Cómo puede determinar cuántas tarjetas para generar una compañía de la tarjeta de felicitación?

Nos gustaría estimar las probabilidades de eventos seguras de forma precisa. Por ejemplo, ¿cuál es la probabilidad de que los flujos de efectivo de un producto nuevo tendrá un valor actual neto positivo (NPV)? ¿Qué es el factor de riesgo de nuestra cartera de inversión? Simulación de Montecarlo nos permite a situaciones modelo que presentan incertidumbre y, a continuación, reproducción en un equipo miles de veces.

Nota: El nombre de simulación Montecarlo proviene de las simulaciones equipo realizadas durante la década de 1930 y 1940s para estimar la probabilidad de que la reacción de cadena necesaria para que una bomba atom para la detonación funcionarían correctamente. El físicos trabajan en este eran entusiastas de azar, por lo que le ha proporcionado las simulaciones por el nombre de código Montecarlo.

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

Muchas empresas usan Montecarlo simulación como una parte de su proceso de toma de decisiones importante. Aquí se muestran algunos ejemplos.

  • Motores de general, Proctor y apuesta, Pfizer, Bristol-Myers Squibb y Eli Lilly simulación de uso para calcular el promedio retorno y el factor de riesgo de nuevos productos. En GM, esta información se usa por el consejero Delegado para determinar qué productos se incluyen en el mercado.

  • GM usa simulación de actividades como la previsión de los ingresos netos para la empresa, predecir los costos de compras y estructurales y determinar su sensibilidad a diferentes tipos de riesgo (por ejemplo, los cambios de tasa de interés y las variaciones de tipo de cambio).

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

  • Proctor y apuesta usa simulación modelar y protegerse de manera óptima riesgo divisas.

  • Sears usa simulación para determinar el número de unidades de cada línea de producto debe estar ordenado de proveedores, por ejemplo, el número de pares de pantalones Dockers que se deben pedir este año.

  • Empresas de petróleo y fármaco use simulación valor "opciones reales", como el valor de una opción para expandir, contraer o posponer un proyecto.

  • Finanzas planificadores use Montecarlo simulación para determinar estrategias de inversión óptima para la jubilación de sus clientes.

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

Imagen del libro
Figura 60-1, que muestra la función aleatorio

Nota: Abra el archivo Randdemo.xlsx, no verá los mismos números aleatorios que se muestra en la figura 60-1. La función RAND siempre automáticamente vuelve a calcular los números que se genera cuando se abre una hoja de cálculo o cuando se introduce información nueva en la hoja de cálculo.

En primer lugar, copiar de la celda C3 a C4:C402 la fórmula = RAND(). A continuación, nombre del rango de datosde C3:C402. A continuación, en la columna F, puede realizar un seguimiento de la media de los números aleatorios 400 (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 siempre es de aproximadamente 0,5, así que alrededor 25% de los resultados en intervalos de 0,25. Estos resultados son coherentes con la definición de un número aleatorio. Tenga en cuenta que los valores generados por aleatorio en celdas diferentes son independientes. Por ejemplo, si el número aleatorio generado en la celda C3 es un gran número (por ejemplo, 0,99), nos dice nada acerca de los valores de los otros números aleatorios generados.

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

Petición

Probabilidad

10 000

0,10

20.000

0,35

40.000

0,3

60 000

0,25

¿Cómo podemos tenemos Excel reproducir o simular este petición de calendarios muchas veces? El truco es asociar cada valor posible de la función aleatorio con una petición de posible para los calendarios. La siguiente asignación garantiza que una petición de 10.000 se producen 10 por ciento del tiempo y así sucesivamente.

Petición

Número aleatorio asignado

10 000

Inferior a 0,10

20.000

Mayor o igual a 0,10 y 0,45 inferior

40.000

Mayor o igual a 0,45 e inferior a 0,75

60 000

Mayor o igual a 0,75

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

Imagen del libro
Figura 60-2 simular una variable aleatoria discreta

La clave para nuestro simulación es utilizar un número aleatorio para iniciar una búsqueda de un rango de la tabla F2:G5 (denominado búsqueda). Números aleatorios mayores o iguales a 0 y menor que 0,10 arrojará una petición de 10.000; números aleatorios mayores o iguales a 0,10 y menor que 0,45 arrojará una petición de 20.000; números aleatorios mayores o iguales a 0,45 y menor que 0,75 arrojará una petición de 40.000; y números aleatorios mayores o iguales a 0,75 arrojará una petición de 60.000. Generar números aleatorios 400 copiando desde C3 a C4:C402 la fórmula RAND. A continuación, generar 400 ensayos o iteraciones de petición de calendario copiando desde B3 a B4:B402 la fórmula VLOOKUP(C3,lookup,2). Esta fórmula asegura 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 nuestras 400 iteraciones generando cada petición. Cuando se presione F9 para recalcular los números aleatorios, son las probabilidades simuladas cerca nuestras probabilidades de petición supuesto.

Si escribe en cualquier celda de la fórmula NORMINV(rand(),mu,sigma), se generará un valor de una variable aleatoria normal tiene una media de mu y la desviación estándar sigmasimulado. Este procedimiento se muestra en el archivo Normalsim.xlsx, que se muestra en la figura 60-3.

Imagen del libro
Figura 60-3 simular una variable aleatoria normal

Supongamos que deseamos 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 asigne un nombre a estas celdas significa y sigma, respectivamente.) Copiar la fórmula = RAND() de C4 a C5:C403 genera 400 distintos números aleatorios. Copiar desde B4 a B5:B403 la fórmula NORMINV(C4,mean,sigma) genera 400 distintos valores de prueba de una variable aleatoria normal con una media de 40.000 y una desviación estándar de 10.000. Cuando se presiona la tecla F9 para recalcular los números aleatorios, la media permanece cerca 40.000 y la desviación estándar cerca de 10.000.

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

En esta sección, se muestra cómo puede utilizarse simulación de Montecarlo como una herramienta de toma de decisiones. Supongamos que la demanda de una tarjeta de San Valentín se rige por la variable aleatoria discreta siguiente:

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 coste de variable de producir cada tarjeta es $1,50. Tarjetas sobrantes deben eliminarse a un coste de 0,20 USD por tarjeta. ¿Cuántos tarjetas se deben imprimir?

Básicamente, es posible simular cada cantidad de producción posibles (10.000, 20.000, 40.000 o 60.000) muchas veces (por ejemplo, 1.000 iteraciones). A continuación, se determinar qué cantidad del pedido da como resultado el máximo beneficio promedio sobre 1.000 iteraciones. Puede encontrar los datos para esta sección en el archivo Valentine.xlsx, que se muestra en la figura 60-4. Asignar los nombres de rango de celdas B1:B11 a C1:C11 de celdas. El rango de celdas G3:H6 se asigna la búsquedade nombres. Nuestro precio de venta y los parámetros de costo se introducen en las celdas C4:C6.

Imagen del libro
Simulación de tarjeta de San Valentín figura 60-4

Puede especificar una cantidad de prueba de producción (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 describió anteriormente, simular petición de la tarjeta en la celda C3 con la fórmula VLOOKUP(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 valor más pequeño de la cantidad de producción y petición. En la celda C8, calcular nuestros ingresos con la fórmula MIN (producido, demanda) * unit_price. En la celda C9, calcular los costos de producción total con la fórmula fabrican * unit_prod_cost.

Si se producen más cartas en demanda, el número de unidades izquierda sobre producción es igual al menos demanda; en caso contrario, ningún unidades quedan. Se calcula el nuestro costo de eliminación en la celda C10 contiene la fórmula unit_disp_cost * IF (fabrican > demanda, fabrican: demanda, 0). Por último, en la celda C11, se calcula el nuestro beneficio como los ingresos: total_var_cost-total_disposing_cost.

Nos gustaría un modo eficaz presione F9 muchas veces (por ejemplo, 1000) para cada cantidad de producción y calcular nuestro beneficio previsto para cada cantidad. Esta situación es uno en el que una tabla de datos bidireccional viene a nuestro rescate. (Para obtener más información acerca de las tablas de datos, consulte "Sensibilidad análisis con tablas de datos," capítulo 15). La tabla de datos usada en este ejemplo se muestra en la figura 60-5.

Imagen del libro
Tabla de datos bidireccional figura 60-5 para simulación de tarjeta de felicitación

En el rango de celdas A16:A1015, escriba los números 1-1000 (correspondiente a nuestros ensayos 1000). Es una forma sencilla de crear estos valores iniciar, escriba 1 en la celda A16. Seleccione la celda y, a continuación, en la ficha Inicio en el grupo Edición, haga clic en relleno y seleccione serie para mostrar el cuadro de diálogo de la serie. En el cuadro de diálogo de serie, que se muestra en la figura 60-6, escriba un valor del paso 1 y dejar el valor 1000. En el área De serie, seleccione la opción de columnas y, a continuación, haga clic en Aceptar. Los números 1-1000 serán especificado en la columna inicial en la celda A16.

Imagen del libro
Figura 60-6 usando el cuadro de diálogo de serie para rellenar en los números de prueba 1 y 1000

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

Estamos listos para truco Excel en simulación de 1.000 iteraciones de petición para cada cantidad de producción. Seleccione el rango de tabla (A15:E1014) y, a continuación, en el grupo Herramientas de datos en la ficha datos, haga clic en análisis de What If y, a continuación, seleccione la tabla de datos. Para configurar una tabla de datos bidireccional, elija la cantidad de producción (celda C1) como la celda de entrada de fila y seleccione una celda en blanco (elegimos celda I14) como la columna de la celda de entrada. Después de hacer clic en Aceptar, Excel simula valores de petición de 1000 para cada cantidad de pedido.

Para entender por qué esto funciona, tenga en cuenta los valores incluidos en 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 celda de entrada de la columna 1 se coloca en una celda en blanco y el número aleatorio en la celda que C2 vuelve a calcular. A continuación, se registra el beneficio correspondiente en la celda C16. A continuación, el valor de entrada de celda de columna de 2 se coloca en una celda en blanco y, a continuación, vuelva a vuelve a calcular el número aleatorio en C2. Las ganancias correspondiente se escribe en la celda C17.

Copiando desde la celda B13 a C13:E13 la fórmula AVERAGE(B16:B1015), se calcula el promedio beneficio simulada para cada cantidad de producción. Al copiar de la celda B14 para C14:E14 la fórmula STDEV(B16:B1015), se calcula la desviación estándar de nuestros beneficios simuladas para cada cantidad de pedido. Cada vez que presiona F9, 1.000 iteraciones de petición están simuladas para cada cantidad de pedido. Producir 40.000 tarjetas siempre da como resultado el mayor beneficio previsto. Por lo tanto, parece que producir 40.000 tarjetas es la decisión correcta.

El impacto de los riesgos en nuestra decisión     Si se produce 20.000 en lugar de 40.000 cartas, nuestro beneficio previsto cae 22 por ciento, pero no nuestro riesgo (medida según la desviación estándar de beneficio) es prácticamente 73 por ciento. Por lo tanto, si somos muy desean riesgo, producir 20.000 tarjetas posible la decisión correcta. Por cierto, producir tarjetas de 10.000 siempre tiene una desviación estándar de 0 tarjetas porque si se producen tarjetas de 10.000, se siempre vendemos todos ellos sin ningún leftovers.

Nota: En este libro, la opción de cálculo se establece en Automático excepto tablas. (Utilice el comando de cálculo en el grupo de cálculo en la ficha fórmulas). Esta configuración garantiza que la tabla de datos no actualizará a menos que se presiona F9, que es una buena idea porque una tabla de datos de gran tamaño se ralentiza su trabajo si vuelve a calcular cada vez escriba algo en la hoja de cálculo. Tenga en cuenta que en este ejemplo, cuando presiona F9, las ganancias Media cambia. Esto ocurre porque cada vez que presiona F9, una secuencia diferente de números aleatorios de 1000 se usa para generar las peticiones de cada cantidad de pedido.

Intervalo de confianza para la intención beneficio     ¿Es una pregunta natural en esta situación, en intervalo de qué estamos 95 por ciento que pertenezca a la categoría los beneficios reales de Media? Este intervalo se denomina el intervalo de confianza de 95 por ciento de beneficio Media. Un intervalo de confianza del 95% de la media de los resultados de simulación se calcula mediante la siguiente fórmula:

Imagen del libro

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

Imagen del libro
Intervalo de confianza 95 por ciento figura 60-7 para las ganancias Media cuando se ordenan los 40.000 calendarios

Estamos 95 por ciento Asegúrese de que está nuestro beneficio Media cuando se ordenan los 40.000 calendarios entre 56,687 $ y 62,589 $.

  1. Un distribuidor GMC cree que la demanda de 2005 enviados se distribuye normalmente con una media de 200 y desviación estándar de 30. Su coste de recibir un envío es $25.000 y vende un envío para 40.000 $. La mitad de todos los envíos no vendidos al precio total pueden ser vendida por 30.000 $. Está a punto de ordenación 200, 220, 240, 260, 280 o 300 enviados. ¿Cuántos debe pedir?

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

    Petición

    Probabilidad

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. El supermercado paga 1,00 $ para cada copia de personas y vende para 1.95 $. Cada copia no vendido se puede devolver de 0,50 USD. ¿Cuántas copias de las personas debe el almacén de orden?

¿Necesita 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.

×