Área de aplicación: Toma de decisiones bajo incertidumbre, gestión de inventarios, análisis de riesgos en cadenas de suministro y planificación de la producción.
Funciones de Excel a Utilizar
A continuación, se detallan las funciones clave para este ejercicio de simulación Montecarlo en Excel:
1. RAND
Sintaxis: =RAND()
Para qué sirve: Genera un número aleatorio entre 0 y 1. Es la base para simular valores aleatorios en cualquier rango.
Notas:
- El valor cambia cada vez que se recalcula la hoja (presionando
F9). - Se usa para simular variables aleatorias, como la demanda en este ejercicio.
2. ALEATORIO.ENTRE
Sintaxis: =ALEATORIO.ENTRE(inferior; superior)
Para qué sirve: Genera un número entero aleatorio entre inferior y superior (inclusive).
Ejemplo: =ALEATORIO.ENTRE(10; 100) genera un número entre 10 y 100.
3. PERCENTIL
Sintaxis: =PERCENTIL(rango; k)
Para qué sirve: Devuelve el valor del percentil k (entre 0 y 1) de un rango de datos. Esencial para analizar resultados de la simulación.
Ejemplo: =PERCENTIL(A2:A1001; 0.9) devuelve el percentil 90 de los datos en A2:A1001.
4. SI.ERROR
Sintaxis: =SI.ERROR(valor; valor_si_error)
Para qué sirve: Maneja errores en fórmulas. Si valor genera un error, devuelve valor_si_error.
5. REDONDEAR
Sintaxis: =REDONDEAR(número; número_de_dígitos)
Para qué sirve: Redondea un número al número de dígitos especificado. Útil para ajustar resultados de la simulación.
Datos de Ejemplo
Supongamos que queremos simular la demanda de un producto en una fábrica. Tenemos las siguientes estimaciones:
| Parámetro | Valor |
|---|---|
| Demanda pesimista (unidades) | 50 |
| Demanda optimista (unidades) | 200 |
| Número de simulaciones | 1000 |
Desarrollo Paso a Paso
Paso 1: Generar Números Aleatorios con RAND
En el rango A2:A1001, genera 1000 números aleatorios entre 0 y 1 usando RAND():
=RAND() Resultado (primeras 5 filas):
| A |
|---|
| Número Aleatorio |
| 0.456 |
| 0.123 |
| 0.789 |
| 0.321 |
| 0.654 |
Paso 2: Escalar los Números Aleatorios al Rango de Demanda
En el rango B2:B1001, convierte los números aleatorios a valores de demanda entre 50 (pesimista) y 200 (optimista). Usa la fórmula:
=50 + (200 - 50) * A2 Resultado (primeras 5 filas):
| A | B |
|---|---|
| Número Aleatorio | Demanda Simulada (unidades) |
| 0.456 | 123.8 |
| 0.123 | 61.15 |
| 0.789 | 184.45 |
| 0.321 | 101.05 |
| 0.654 | 148.7 |
Nota: Arrastra la fórmula hacia abajo hasta la fila 1001 para generar las 1000 demandas.
Paso 3: Redondear los Resultados
En el rango C2:C1001, redondea los valores de demanda a números enteros:
=REDONDEAR(B2; 0) Resultado (primeras 5 filas):
| A | B | C |
|---|---|---|
| Número Aleatorio | Demanda Simulada | Demanda Redondeada |
| 0.456 | 123.8 | 124 |
| 0.123 | 61.15 | 61 |
| 0.789 | 184.45 | 184 |
| 0.321 | 101.05 | 101 |
| 0.654 | 148.7 | 149 |
Paso 4: Calcular Percentiles
En el rango E2:E6, calcula los percentiles clave (10, 25, 50, 75, 90) de las demandas simuladas:
| D | E |
|---|---|
| Percentil | Valor (unidades) |
| 10% | =PERCENTIL(C2:C1001; 0.1) |
| 25% | =PERCENTIL(C2:C1001; 0.25) |
| 50% | =PERCENTIL(C2:C1001; 0.5) |
| 75% | =PERCENTIL(C2:C1001; 0.75) |
| 90% | =PERCENTIL(C2:C1001; 0.9) |
Resultado aproximado (ejemplo):
| Percentil | Valor (unidades) |
|---|---|
| 10% | 65 |
| 25% | 98 |
| 50% | 125 |
| 75% | 152 |
| 90% | 187 |
Nota: Los valores pueden variar ligeramente al recalcular la hoja (F9).
Variantes del Ejercicio
Variante 1: Usar ALEATORIO.ENTRE para Demandas Enteras
En lugar de RAND(), usa ALEATORIO.ENTRE para generar demandas enteras directamente entre 50 y 200:
=ALEATORIO.ENTRE(50; 200) Resultado (primeras 5 filas):
| A |
|---|
| Demanda Simulada (unidades) |
| 123 |
| 61 |
| 184 |
| 101 |
| 149 |
Luego, calcula los percentiles igual que en el paso 4.
Variante 2: Manejo de Errores con SI.ERROR
Si el rango de percentiles incluye valores inválidos (ej: 0% o 100%), usa SI.ERROR para evitar errores:
=SI.ERROR(PERCENTIL(C2:C1001; 1.1); "Percentil invalido") Resultado: Si el percentil es inválido (ej: 110%), mostrarya el mensaje "Percentil invalido" en lugar de un error.
Ejemplo con percentiles válidos y no válidos:
| Percentil | Formula | Resultado |
|---|---|---|
| 50% | =PERCENTIL(C2:C1001; 0.5) | 125 |
| 110% | =SI.ERROR(PERCENTIL(…); «Percentil invalido») | Percentil invalido |
Ejercicio Propuesto
Una empresa estimo que la demanda de su producto puede variar entre 30 y 150 unidades por dia. Se desea realizar una simulacion Montecarlo en Excel para analizar el riesgo de stock.
Tareas:
- Genera 500 demandas aleatorias usando
RAND()y escalalas al rango 30-150. - Redondea los resultados a numeros enteros.
- Calcula los percentiles 10%, 25%, 50%, 75% y 90% de las demandas simuladas.
- Usa
SI.ERRORpara manejar un percentil invalido (ej: 110%). - Repite la simulacion usando
ALEATORIO.ENTRE(30; 150)y compara los resultados. - Crea un grafico de histograma para visualizar la distribucion de las demandas simuladas.
Deja una respuesta