Montecarlo es un método de simulación estadístico numérico usado para aproximar expresiones matemáticas complejas, como puede ser el caso de plazo o coste de un proyecto. Dada su complejidad, es una simulación que requiere computación algo avanzada y específica, por lo que nos puede resultar algo costoso desde la perspectiva del usuario casual.
Aun cuando Excel es posiblemente la herramienta de modelado más usada en el mundo, es posible incrementar sus capacidades y lograr este tipo de simulaciones de Montecarlo avanzadas con xlwings de Python.
Modelado de datos para Montecarlo en Excel.
En este pequeño proyecto tenemos un simple estado de resultados en nuestro libro de Excel. Este estado de resultados contiene datos históricos del 2020 y nos encontramos haciendo una predicción de ganancias para el 2021 en base a unas cuantas asunciones, como lo son crecimiento de ganancia, costo de los bienes vendidos y tasa de impuestos efectiva.
Este modelo simple nos da una salida para nuestros ingresos netos en 2021. Ahora, supongamos que queremos ver un rango de resultados en base a algunas asunciones sobre cómo nuestro crecimiento de ganancia podría resultar en el año.
Código Xlwings Python
Si asumimos que los resultados potenciales para el crecimiento de ganancia son normalmente distribuidos, podemos tomar muestras de esa distribución y obtener diferentes resultados en ingresos netos en base a cada escenario. Esta es la idea tras una simulación Montecarlo, y para hacer esto debemos ejecutar el siguiente código Python que utiliza la librería xlwings:
import xlwings as xw
import numpy as np
def main():
#app1 = xw.App(visible=False)
wb = xw.Book('C:/Users/aplan/Desktop/estado_resultados_montecarlo.xlsx')
sh = wb.sheets['resultados']
mean = sh.range('b16').value
std = sh.range('b19').value
row = 2
col = 5
offset = 1
escenarios(sh,mean,std,row,col,offset)
def escenarios(sh, mean, std, row, col, offset):
"""
sh = una hoja de Excel especifica
mean = Promedio de la distribucion normal
std = Distribucion estandar
row = la fila donde inicia nuestro calculo de salida
col = la columna donde inicia nuestro calculo de salida
offset = el num de filas a desplazarse
"""
dist = distribution(mean,std)
input_cell = 'b16'
output_cell = 'c2'
target_cell = 'c12'
for x in dist:
sh.range(input_cell).value = x
sh.range(row,col).value = int(sh.range(target_cell).value)
sh.range(row,col+1).value = int(sh.range(output_cell).value)
sh.range(row,col+2).value = x
row += offset
sh.range(input_cell).value = mean
def distribution(mean,std):
"""
mean = Promedio de la distribucion normal
std = Distribucion estandar
"""
dist = np.random.normal(mean,std,100) return dist
main()Entre las cosas más importantes a destacar en este código tenemos que:
- Se hacen cien simulaciones de nuestro estado de resultados para 2021
- Asumimos una tasa de crecimiento medio del 10% y una desviación estándar del 3% en nuestra distribución normal de tasas de crecimiento proyectadas
Podemos ejecutar nuestro código de Python con nuestro libro de Excel abierto. A continuación podemos ver cómo el código coloca los resultados de la simulación en nuestro libro de trabajo. Los gráficos están vinculados al área con las entradas para que sean actualizados conforme los datos sean introducidos en el libro de Excel.
Una entrada simple, pero efectiva sobre como usar Excel y xlwings para estudios Montecarlo. Xlwings se esta convirtiendo en uno de mis favoritos 😀
Cualquier duda nos leemos abajo.
