Este artículo ha sido adaptado de Microsoft Excel análisis de datos y modelado de negocios por Wayne L. Winston.

  • Quién usa la simulación de Montecarlo?

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

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

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

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

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 neto positivo actual (VPV)? ¿Cuál es el factor de riesgo de nuestra cartera de inversiones? La simulación de Montecarlo nos permite modelar situaciones que presentan incertidumbre y reproducirlas en un equipo miles de veces.

Nota:  El nombre de la simulación de Montecarlo proviene de las simulaciones de ordenador 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 detone funcione correctamente. Los físicos implicados en este trabajo eran grandes fanáticos del juego, por lo que le dieron a las simulaciones el nombre de código Montecarlo.

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

Muchas empresas usan la simulación de Montecarlo como parte importante de su proceso de toma de decisiones. Estos son algunos ejemplos.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb y Eli Lilly usan la simulación para estimar tanto el retorno medio como el factor de riesgo de los nuevos productos. En GM, el director general usa esta información para determinar qué productos se comercializan.

  • GM usa la 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 en la tasa de interés y fluctuaciones del tipo de cambio).

  • Lilly usa la simulación para determinar la capacidad óptima de cada uno de los medicamentos.

  • Proctor and Gamble usa la simulación para modelar y cubrir de forma óptima el riesgo cambiaria.

  • Sears usa la simulación para determinar cuántas unidades de cada línea de productos se deben pedir a los proveedores, por ejemplo, el número de pares de pantalones de Docker que se deben solicitar este año.

  • Las compañías de petróleo y medicamentos usan la simulación para valorar "opciones reales", como el valor de una opción para expandir, contratar o posponer un proyecto.

  • Los planificadores financieros usan la simulación de Montecarlo para determinar estrategias de inversión óptimas para la retirada de sus clientes.

Cuando escribe la fórmula =RAND() 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, debería 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 como mínimo 0,90, y así sucesivamente. Para demostrar cómo funciona la función RAND, consulte el 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 RAND siempre vuelve a calcular automáticamente los números que 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, copie de la celda C3 a C4:C402 la fórmula =RAND(). A continuación, asigne un nombre al rango C3:C402 Datos. A continuación, 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, los números aleatorios se recalculan. Observe que el promedio de los 400 números siempre es 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 en cuenta también 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 otros números aleatorios generados.

Supongamos 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 Excel reproducir o simular esta demanda de calendarios muchas veces? El truco es asociar cada valor posible de la función RAND con una posible demanda de calendarios. La siguiente tarea garantiza que una demanda de 10 000 se produzca el 10 por ciento del tiempo, y así sucesivamente.

Demanda

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 demanda, mire el archivo Discretesim.xlsx, que se muestra en la figura 60-2 en 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 tablas F2:G5 (búsqueda con nombre). Los números aleatorios mayores o iguales a 0 y inferiores a 0,10 darán una demanda de 10 000; los números aleatorios mayores o iguales a 0,10 y inferiores a 0,45 darán una demanda de 20 000; los números aleatorios mayores o iguales a 0,45 y inferiores a 0,75 darán una demanda de 40 000; y los números aleatorios mayores o iguales a 0,75 darán una demanda de 60 000. Para generar 400 números aleatorios, copie de C3 a C4:C402 la fórmula RAND(). Después, genera 400 ensayos o iteraciones de 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 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 volver a calcular los números aleatorios, las probabilidades simuladas se acercan a nuestras probabilidades de demanda asumidas.

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

Imagen del libro

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 nombrar 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 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 cerca de 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 mu media y un sigma de desviación estándar. Por ejemplo, el número aleatorio 0,77 en la celda C4 (vea 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 usar la simulación de Montecarlo como herramienta de toma de decisiones. Supongamos que la demanda de una tarjeta 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 de 1,50 $. Las tarjetas sobradas deben eliminarse con un coste de 0,20 $ por tarjeta. ¿Cuántas tarjetas se deben imprimir?

Básicamente, simulamos cada posible cantidad de producción (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 el archivo Valentine.xlsx, 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.

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 en la celda C3 con la fórmula BUSCARV(rand,búsqueda,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 de producción y demanda. En la celda C8, calcula nuestros ingresos con la fórmula MIN(producido,demanda)*unit_price. En la celda C9, calcula el costo total de producción con la fórmula producida*unit_prod_cost.

Si producimos más tarjetas de las que se demandan, el número de unidades que quedan sobre equivale a producción menos 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(producido>demanda, producido-demanda,0). Por último, en la celda C11, calculamos nuestros beneficios 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 nuestros beneficios esperados para cada cantidad. Esta situación es una en la que una tabla de datos de dos vías viene a nuestro rescate. (Vea el capítulo 15, "Análisis de confidencialidad 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.

Imagen del libro

En el rango de celdas A16:A1015, escriba los números de 1 a 1000 (correspondientes a nuestras 1000 pruebas). Una forma sencilla de crear estos valores es empezar por escribir 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 yseleccione 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 stop de 1000. En el área Series en, seleccione la opción Columnas y, a continuación, haga clic en Aceptar. Los números 1-1000 se introducirán en la columna A a partir de la celda A16.

Imagen del libro

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 (de 1 a 1000) y de cada cantidad de producción. Nos referimos a la fórmula de beneficio (calculada en la celda C11) en la celda superior izquierda de nuestra tabla de datos (A15) especificando =C11.

Ahora estamos listos para engañar a Excel para simular 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, a continuación, seleccione Tabla de datos. Para configurar una tabla de datos de dos vías, elija nuestra cantidad de producción (celda C1) como celda de entrada de fila y seleccione cualquier celda en blanco (hemos elegido 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, tenga en cuenta los valores colocados por la tabla de datos en el rango de celdas C16:C1015. Para cada una de estas celdas, Excel un valor de 20 000 en la celda C1. En C16, el valor de celda de entrada de columna de 1 se coloca en una celda en blanco y el número aleatorio de la celda C2 se vuelve a calcular. El beneficio correspondiente se registra en la celda C16. A continuación, el valor de entrada de la celda de columna de 2 se coloca en una celda en blanco y el número aleatorio en C2 vuelve a calcularse. 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 beneficio simulado promedio 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 adecuada.

El impacto del riesgo en nuestra decisión      Si producimos 20 000 tarjetas en lugar de 40 000 tarjetas, nuestro beneficio esperado disminuye aproximadamente 22 por ciento, pero nuestro riesgo (medido por la desviación estándar de beneficios) disminuye casi 73 por ciento. Por lo tanto, si somos extremadamente contrarios al riesgo, producir 20 000 tarjetas puede ser la decisión correcta. Por cierto, producir 10 000 tarjetas siempre tiene una desviación estándar de 0 tarjetas, ya que si producimos 10 000 tarjetas, siempre las venderemos todas sin ningún resto.

Nota:  En este libro, la opción Cálculo se establece en Automático excepto para tablas. (Use el comando Cálculo en el 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 que es una buena idea porque una tabla de datos grande ralentizará su trabajo si se vuelve a calcular cada vez que escriba algo en la hoja de cálculo. Tenga en cuenta que, en este ejemplo, siempre que presione F9, el beneficio medio cambiará. Esto ocurre 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 para hacer en esta situación es, ¿en qué intervalo estamos 95 por ciento seguros de que el beneficio medio verdadero va a caer? Este intervalo se denomina intervalo de confianza del 95 por ciento para el beneficio medio. La fórmula siguiente calcula un intervalo de confianza del 95 por ciento para la media de cualquier resultado de simulación:

Imagen del libro

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

Imagen del libro

Estamos 95 por ciento seguros de que nuestro beneficio medio cuando se ordenan 40 000 calendarios es de entre 56.687 y 62.589 $.

  1. Un distribuidor de GMC cree que la demanda de los 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 todos los enviados que no se venden a precio completo se pueden vender por 30.000 $. Está pensando en ordenar 200, 220, 240, 260, 280 o 300 enviados. ¿Cuántos debería pedir?

  2. Un pequeño supermercado está intentando determinar cuántas copias de la revista People deben solicitar 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 sin vender se puede devolver por 0,50 $. ¿Cuántas copias de Personas debe ordenar la tienda?

¿Necesita más ayuda?

Siempre puede preguntar a un experto en la Excel Tech Community u obtener soporte técnico en la Comunidad de respuestas.

¿Necesita más ayuda?

Ampliar sus conocimientos
Explorar los cursos
Obtener nuevas características primero
Unirse a Microsoft Office Usuarios de Insider

¿Le ha sido útil esta información?

¿Cuál es tu grado de satisfacción con la calidad del lenguaje?
¿Qué ha afectado a tu experiencia?

¡Gracias por sus comentarios!

×