Análisis de riesgo de proyecto con simulación de Montecarlo en Excel
Este instructivo técnico está diseñado para ingenieros industriales, analistas de datos y gestores de proyectos que buscan cuantificar la incertidumbre en la planificación de sus iniciativas utilizando la potencia de la simulación de Montecarlo dentro de Microsoft Excel.
Definición del problema
En la gestión de proyectos, es común que los tiempos de ejecución, los costos de materiales o la demanda de mercado no sean valores fijos, sino variables sujetas a incertidumbre. Un enfoque determinista (usar un valor promedio) puede llevar a subestimar o sobreestimar drásticamente los recursos necesarios, resultando en retrasos costosos o desperdicio de capital.
Caso Práctico: Implementación de una Línea de Producción de Componentes Electrónicos.
Una empresa manufacturera está planificando la instalación de una nueva línea de producción para un componente electrónico especializado. El costo total del proyecto depende de tres variables clave: Costo de Materia Prima (CMP), Tiempo de Instalación de Maquinaria (TIM) y Costo de Mano de Obra (CMO). Estos valores no son exactos; el CMP fluctúa por la volatilidad del mercado de semiconductores, el TIM depende de la complejidad imprevista de la instalación, y el CMO puede variar según la disponibilidad de técnicos especializados. El objetivo es determinar la probabilidad de que el costo total del proyecto exceda un presupuesto límite de $500,000 USD.
Explicación técnica
La Simulación de Montecarlo es una técnica computacional que utiliza muestreo aleatorio para modelar el resultado de un proceso que depende de variables aleatorias. En lugar de calcular un único resultado (como el valor esperado), se ejecuta el modelo miles de veces, cada vez utilizando valores diferentes para las variables de entrada, extraídos de una distribución de probabilidad definida.
Conceptos Clave en Excel:
- Distribuciones de Probabilidad: En lugar de usar un número fijo, asignamos rangos a nuestras variables. Para este caso, utilizaremos la Distribución Normal (para costos y tiempos) o la Distribución Uniforme (si no se conoce la distribución exacta, pero se conocen los límites mínimo y máximo).
- Funciones Aleatorias: Se emplearán funciones como
=NORM.INV()(para generar valores aleatorios basados en una distribución normal) o=ALEATORIO()(para distribuciones uniformes). - Iteración: El proceso se automatiza mediante la copia masiva de fórmulas y la posterior ejecución de la simulación (a menudo forzando el recálculo de la hoja).
Guía paso a paso
Paso 1: Definición de Variables y Distribuciones
Primero, debemos definir las variables de entrada y sus parámetros estadísticos (media $mu$ y desviación estándar $sigma$ para la Normal, o mínimo/máximo para la Uniforme).
Acción: Crea una tabla de parámetros en la Hoja 1. Define CMP, TIM y CMO con sus respectivas distribuciones.
Resultado: Una tabla clara que define la incertidumbre de cada factor.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Variable | Distribución | Parámetro 1 ($mu$ o Min) | Parámetro 2 ($sigma$ o Max) |
| 2 | Costo Materia Prima (CMP) | Normal | 250,000 | 20,000 |
| 3 | Tiempo Instalación (TIM) | Normal | 45 | 5 |
| 4 | Costo Mano Obra (CMO) | Normal | 100,000 | 15,000 |
Paso 2: Generación de Muestras Aleatorias
Ahora, generaremos una columna para cada variable, donde cada celda contendrá un valor aleatorio basado en la distribución definida en el Paso 1. Usaremos la función NORM.INV(ALEATORIO(), media, desviación_estandar).
Acción: En la Hoja 2, crea columnas para CMP_Sim, TIM_Sim y CMO_Sim. En la celda A2 (CMP_Sim), introduce la fórmula:
=NORM.INV(ALEATORIO(), $B$2, $D$2) (Asumiendo que B2 es la media y D2 es la desviación estándar en la Hoja 1).
Por qué: ALEATORIO() genera un número entre 0 y 1, que es la entrada necesaria para NORM.INV() para mapear ese número a un valor específico de la distribución normal.
Resultado: La celda A2 mostrará un costo de materia prima diferente en cada recálculo.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Variable | CMP_Sim | TIM_Sim | CMO_Sim |
| 2 | Muestra 1 | =NORM.INV(ALEATORIO(), 250000, 20000) | =NORM.INV(ALEATORIO(), 45, 5) | =NORM.INV(ALEATORIO(), 100000, 15000) |
| 3 | Muestra 2 | =NORM.INV(ALEATORIO(), 250000, 20000) | =NORM.INV(ALEATORIO(), 45, 5) | =NORM.INV(ALEATORIO(), 100000, 15000) |
Paso 3: Cálculo del Resultado del Proyecto (Costo Total)
Acción: En la columna D (Costo Total), introduce la fórmula para la primera muestra (D2):
=A2 + (B2 * 100) + C2
Resultado: Se obtiene el costo total proyectado para esa iteración específica.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | CMP_Sim | TIM_Sim | CMO_Sim | Costo Total (CT) |
| 2 | $265,000 | 48 | $110,000 | =$A2 + (B2*100) + $C2 |
| 3 | $240,000 | 42 | $95,000 | =$A3 + (B3*100) + $C3 |
Paso 4: Ejecución de la Simulación (Iteración)
Para obtener una visión estadística robusta, necesitamos miles de iteraciones. Esto se logra copiando las fórmulas de las columnas A, B, C y D hacia abajo, por ejemplo, hasta la fila 5000.
Acción: Selecciona el rango A2:D2 y arrástralo hacia abajo hasta la fila 5001. Luego, fuerza el recálculo de toda la hoja presionando F9.
Resultado: 5000 filas, cada una representando un escenario de proyecto único y plausible, con su respectivo costo total.
Paso 5: Análisis de Resultados (Estadísticas)
Finalmente, analizamos la columna de Costo Total (Columna D) para responder a la pregunta inicial: ¿Cuál es la probabilidad de exceder $500,000?
Acción: Utiliza la función =CONTAR.SI() para contar cuántas veces el Costo Total es mayor o igual a $500,000. Luego, divide ese conteo entre el número total de simulaciones (5000).
Fórmula de Probabilidad: =CONTAR.SI(D2:D5001, ">=500000") / 5000
Resultado: Un porcentaje que indica el riesgo asociado al sobrecosto.
Ejercicio propuesto
Extienda el caso práctico. Suponga que el Costo de Mano de Obra (CMO) no sigue una distribución Normal, sino que es más probable que sea bajo o alto, con una probabilidad del 10% de ser extremadamente alto (un evento de «cuello de botella» de personal). Para modelar esto, cambie la distribución de CMO a una Distribución Triangular, especificando: Mínimo = $80,000, Modo (más probable) = $100,000, y Máximo = $150,000. Utilice la función =TRIANGULAR.INV(ALEATORIO(), min, modo, max) en lugar de NORM.INV() para esa variable y vuelva a ejecutar la simulación de 5000 iteraciones. ¿Cómo cambia la probabilidad de sobrecosto?
Errores habituales
- Error de Referencia Absoluta: Olvidar usar el signo de dólar ($) en las referencias a los parámetros de distribución (ej. usar
B2en lugar de$B$2al arrastrar la fórmula). Solución: Siempre fija las celdas de parámetros de entrada con referencias absolutas para que, al copiar la fórmula, siempre apunte a la media y desviación estándar correctas. - Recálculo Incompleto: Simplemente copiar la fórmula 5000 veces sin forzar el recálculo. Solución: Después de copiar, presiona
F9. Esto obliga a Excel a recalcular todas las funciones volátiles (comoALEATORIO()) en todas las celdas, generando un nuevo conjunto de muestras. - Confundir Distribuciones: Usar
NORM.INV()cuando la realidad del negocio es discreta o tiene límites claros (como en el ejercicio propuesto). Solución: Antes de codificar, valide con el equipo de expertos qué distribución se ajusta mejor a la naturaleza del riesgo (Normal, Uniforme, Triangular, etc.).
Deja una respuesta