Montecarlo en Excel explicado con croissants

Montecarlo en Excel explicado con croissants

¿Se puede usar Excel para calcular croissants? Sí, y lo hacemos con el método Montecarlo.

¿Cuántos croissants tienes que comprar para el coffee break de un evento? Parece una pregunta trivial, pero esconde un problema lleno de incertidumbre: no sabes cuánta gente vendrá ni cómo se comportará. En este tutorial resolvemos ese reto aplicando el método Montecarlo en Excel, usando solo funciones que ya tienes a tu disposición. El planteamiento es divertido pero el aprendizaje es muy serio: distribuciones normales, distribuciones categóricas, emparejamientos aleatorios y simulaciones masivas.

El caso: croissants para un evento

Partimos de datos históricos que nos dan cinco cifras clave:

  • Asistencia media: 200 personas, con una desviación de 20.
  • Aplicaciones preferidas de los asistentes: Access 10%, Excel 60% y Power BI 30%.
  • En el coffee break la gente conversa por parejas. Si los dos comparten aplicación favorita, hablan mucho y comen poco: 2 croissants (uno cada uno). Si no la comparten, se distraen comiendo más: 6 croissants.

El objetivo es estimar cuántos croissants comprar para no quedarnos cortos, uno de los peores fallos al organizar un evento.

¿Qué es el método Montecarlo?

El método Montecarlo es una técnica matemática y estadística que consiste en generar datos aleatorios y simular un evento muchas veces. En lugar de resolver el problema con matemáticas complejas, defines un modelo de cómo funciona el caso concreto y lo repites miles de veces para obtener una visión estadística que te ayude a tomar decisiones. Para ello necesitamos entender dos tipos de distribución.

La distribución normal

La distribución normal nos dice que, si tomamos una muestra suficientemente grande, los resultados se reparten siguiendo la clásica campana de Gauss: la media representa el 50% de los casos y la curva será más ancha o más estrecha según la desviación.

En Excel la calculamos con DISTR.NORM.N, que indica qué probabilidad tiene cada salida posible. Sus argumentos son el valor, la media, la desviación y un último parámetro de acumulado:

  • Con FALSO devuelve la probabilidad puntual de cada valor (la campana).
  • Con VERDADERO devuelve la probabilidad acumulada, que llega al 100% y pasa por el 50% justo en la media.

Si aumentas la desviación, la curva se aplana; si la reduces, se estrecha y los resultados se concentran alrededor de la media.

Para la simulación nos interesa la operación inversa: dar un porcentaje y obtener el valor que le corresponde. Eso lo hace INV.NORM. Por ejemplo, con media 200 y desviación 20, el 50% devuelve exactamente 200 personas. Así estimamos cuánta asistencia esperar en cada repetición.

La distribución categórica

Para decidir si cada persona es de Access, Excel o Power BI respetando los porcentajes, usamos un truco: en vez de los porcentajes sueltos, generamos su acumulado con ESCANEAR (scan), partiendo de cero y sumando. Así obtenemos 10%, 70% y 100%.

Después:

  1. Generamos números aleatorios con MATRIZALEAT (entre 0 y 1).
  2. Con BUSCARX buscamos cada número dentro de la columna acumulada y devolvemos el nombre de la aplicación.
  3. La clave está en el modo de coincidencia: usamos 1 (valor exacto o siguiente elemento mayor), de modo que un 5% caiga en Access y un 15% en Excel.

Podemos comprobar el reparto con AGRUPARPOR y la función PORCENTAJE.DE. Cuantos más casos generamos (100, 1.000, 10.000), más se ajustan los resultados a los porcentajes definidos, porque se reduce el peso de la casualidad.

Montando la simulación del evento

Con las piezas listas, construimos el cálculo para un único escenario:

  1. INV.NORM con el porcentaje de entrada nos da la asistencia (empezamos con el 50% = 200 personas).
  2. MATRIZALEAT (envuelta en ENTERO) genera un número aleatorio por persona.
  3. BUSCARX asigna la aplicación preferida a cada asistente.
  4. AJUSTARFILAS agrupa a las personas en parejas. Con el parámetro de relleno marcamos a quien se quede solo.
  5. Con TOMAR comparamos la primera columna con la de al lado (-1) para ver si la pareja comparte aplicación.
  6. Un condicional SI asigna 2 croissants si coinciden y 6 si no (quien se queda solo come 6).
  7. Sumamos todo para obtener el total de croissants de ese escenario.

Cada vez que pulsamos F9 se regeneran los aleatorios y el total cambia, porque jugamos con dos fuentes de azar: cuánta gente viene y qué aplicación prefiere cada una.

Simulación masiva: el corazón de Montecarlo

Para aplicar Montecarlo de verdad, comprimimos todo el cálculo en una sola fórmula que depende de un porcentaje de entrada y la repetimos muchísimas veces. Generamos un vector de aleatorios con MATRIZALEAT (las repeticiones) y aplicamos la fórmula a cada fila con BYROW y una LAMBDA, de modo que cada iteración simule un evento completo.

Después agrupamos los resultados con AGRUPARPOR (dividiendo el total entre 10 para no dispersar), los contamos, extraemos columnas con ELEGIRCOLS y definimos nombres dinámicos para alimentar un gráfico. A medida que subimos las repeticiones (40, 100, 1.000, 10.000), el gráfico empieza a parecerse a una distribución normal: ese es el efecto de la simulación masiva.

El resultado: ¿cuántos croissants compro?

Con 10.000 repeticiones, el valor más probable se sitúa en torno a 410 croissants para cubrir el caso medio (50%). Pero como hablamos de probabilidad, conviene asegurar más cobertura. Calculando el porcentaje acumulado con ESCANEAR y dividiendo entre la suma total, vemos que para cubrir el 90% de los casos habría que comprar unos 470 croissants. Cubrir los casos extremos exigiría hasta 600, así que la decisión final depende del presupuesto.

Conclusión

Hemos resuelto un problema lleno de incertidumbre combinando distribuciones normales y categóricas, emparejamientos aleatorios y miles de simulaciones, todo con funciones nativas de Excel como INV.NORM, BUSCARX, MATRIZALEAT, AGRUPARPOR, BYROW y LAMBDA. El método Montecarlo demuestra que, con un buen modelo y mucha repetición, Excel puede ayudarte a tomar decisiones reales sin necesidad de estadística avanzada. Y sí: no volverás a ver un croissant de la misma manera.

Más contenido de Excel en InflueXcel