Supercharge your inventory modeling in Excel with SimPy

Management of inventory and stock items is one of the fundamental business processes in any organization, as many sub-processes are linked to inventory, optimizing the inventory process becomes crucial. Obviously, all businesses have different types of inventory management sub-processes based on the industry such as the optimum selection of stock items, setting reorder level, setting up warehouse procedures, etc. However, generally, Microsoft Excel is one of the most favored tools in keeping track of inventory and setting up inventory modeling scenarios especially for small-mid-sized businesses.

Continuing from my last article on inventory simulation, we will develop a python-based SimPy tool to simulate and forecast inventory item behaviors. Inventory simulation methods are widely used in the industry for the selection of optimum stock levels, and policies. Although machine-learning-based approaches are very popular this approach is based on DES(Discrete Event simulation) and is more geared toward small-business since it doesn’t require large datasets to develop ML models.

So to start we will import the SimPy Class we developed in the last blog. The Class has the following methods

handle_order — simulates a reordering process during stock out. I am using a constant lead-time of 2 days for the delivery of all items. However, this can be changed as required

generate_interarrival — generates customers entering your inventory or storeroom or shop

generate_demand — generates demand of customers when entering the store

observe — sets up an observer which logs in inventory levels

runner_setup — used to run and set up the simulation

service_level- returns simulated service level

avg_cost_of_invetory — returns avg cost of inventory

plot_inventory — plots inventory levels

plot_balance — plots cost levels buying and selling of items

The full class is shown below

import numpy as np
import simpy
import matplotlib.pyplot as plt
from math import ceil
class inventory_simulation:
def__init__(self,env:simpy.Environment,reorder_level:int,reorder_qty:int,purchase_cost:float,selling_price:float) -> None:
self.reorder_level = reorder_level
self.reorder_qty = reorder_qty
self.balance = 0
self.num_ordered = 0
self.inventory = self.reorder_qty
self.env = env
self.obs_time = []
self.inventory_level = []
self.demand =0
self.purchase_cost = purchase_cost
self.selling_price = selling_price
self.costslevel = []
def handle_order(self) -> None:

#print(f'at {round(} placed order for {self.num_ordered}')

self.num_ordered = self.reorder_level + 1 -self.inventory
self.num_ordered = ceil(self.num_ordered/self.reorder_qty)*self.reorder_qty
self.balance -= self.purchase_cost*self.num_ordered
yield self.env.timeout(2.0)
self.inventory += self.num_ordered
self.num_ordered = 0
#print(f'at {round(} recieved order for {self.num_ordered}')

def generate_interarrival(self) -> np.array:
return np.random.exponential(1./5)
def generate_demand(self) -> np.array:
return np.random.randint(1,5)
def observe(self):

while True:
yield self.env.timeout(0.1)
def runner_setup(self):
while True:
interarrival = self.generate_interarrival()
yield self.env.timeout(interarrival)
self.balance -= self.inventory*2*interarrival
self.demand = self.generate_demand()
if self.demand < self.inventory:
self.balance += self.selling_price*self.demand
self.inventory -= self.demand
#print(f'customer comes I sold {self.demand} at time {round(,2)}')
self.balance += self.selling_price*self.inventory
self.inventory = 0
#print(f'{self.inventory} is out of stock at {round(,2)}')
if self.inventory < self.reorder_level and self.num_ordered ==0:
def plot_inventory(self):
plt.ylabel('SKU level')

def plot_balance(self):
plt.ylabel('SKU balance USD')
def service_level(self):
__temp_level= np.array(self.inventory_level)
__temp_level1 = __temp_level[__temp_level==0]
if len(__temp_level1)==0:
return 1
return (1 - len(__temp_level1)/len(__temp_level))
def avg_cost_of_inventory(self):
__temp_level = np.array(self.inventory_level)*self.purchase_cost
return (__temp_level.mean())

Now let's add a runner to start the simulation and an EOQ search function to get the optimum EOQ based on the simulation

I will create a run function that takes in simulation and the number of iterations it will run.

def run(simulation:inventory_simulation,until:float):
simulation.env.process(simulation.observe()) eoq_sim_search(reorder_lvl_proposals:float,reorder_qty_proposals:np.array,purchase_cost:float,selling_price:float,run_time:int,target_service_level:float) -> float:
service_levels = []
costs = []
eoq = []

for q in reorder_qty_proposals:
s =inventory_simulation(simpy.Environment(),reorder_lvl_proposals,q,purchase_cost,selling_price)

service_levels = np.array(service_levels)
costs = np.array(costs)
eoq = np.array(eoq)
if (len(service_levels)<1) or (len(costs)<1) or (len(eoq)<1) or len(np.where(service_levels>=target_service_level)) < 1:
return False

return eoq[np.where(costs==np.min(costs[service_levels>=target_service_level]))][0]

Now that we have built our basic toolset let start working with excel files. So I will use the UCI dataset for a retail shop.

I will use pandas to get the data from xlsx file and load it into python.

import pandas as pd
import numpy as np
from Simulation import inventory_simulation
from runner import *
df_full = pd.read_excel('',dtype={'InvoiceNo':str,'StockCode':str,'Description':str,'Quantity':np.float32,'UnitPrice':np.float32,'CustomerID':str,'Country':str})
# I will use feather for a faster load
df_full = pd.read_feather('OnlineRetail.feather')

So next we will use pandas to do some aggregation on the dataset so that basically we have for each item, ROL, ROQ, mean demand, std. of demand, avg_customer rate.

#rate will simply be orders/ N where N is the total time
totaldays = (np.max(df_full['InvoiceDate']) - np.min(df_full['InvoiceDate'])).days
stockcodeQty = df_full.groupby("StockCode").size()
stockcodeQty = pd.DataFrame(stockcodeQty).reset_index()
stockcodeQty.columns = ['StockCode','Count']
stockcodeQty.Count =stockcodeQty.Count/totaldaysCustQty = df_full.groupby("CustomerID").size()CustQty = pd.DataFrame(CustQty).reset_index()
CustQty.columns = ['CustomerID','Count']
CustQty.Count = CustQty.Count/totaldays
avg_customer_rate = np.mean(CustQty.Count)
#I will randomly assign ROL and ROQ since it was not provided by the dataset
stockcodeQty['ROL'] = [int(np.random.uniform(10,20)) for x in np.arange(0,len(stockcodeQty))]
stockcodeQty['ROQ'] =[int(np.random.uniform(30,60)) for x in np.arange(0,len(stockcodeQty))]
stockcodeQty = stockcodeQty.merge(df_full[['StockCode','UnitPrice']],how='left',left_on='StockCode',right_on='StockCode')stockedQty2 = df_full.groupby("StockCode").agg({"Quantity":[np.mean,np.std]})
stockedQty2 = stockedQty2.reset_index()
stockcodeQty = stockcodeQty.drop_duplicates(subset=['StockCode'])
stockcodeQty = stockcodeQty.reset_index()
stockcodeQty['SellingPrice'] = stockcodeQty['UnitPrice']*3
stockcodeQty = stockcodeQty.merge(stockedQty2,how="left",left_on="StockCode",right_on="StockCode")
stockcodeQty.columns = ['index','StockCode','Count','ROL','ROQ','C.P','S.P','mean','std']
stockcodeQty = stockcodeQty.drop_duplicates(subset=['StockCode'])

Now let's write a function to create a simulation for the item and get the optimum EOQ

def run_simulation_for_item(item:str,df:pd.DataFrame,customer_rate:float):
ROL = df[df['StockCode']==item]['ROL'].values[0]
ROQ = df[df['StockCode']==item]['ROQ'].values[0]
CP = df[df['StockCode']==item]['C.P'].values[0]
SP = df[df['StockCode']==item]['S.P'].values[0]
mean = df[df['StockCode']==item]['mean'].values[0]
std = df[df['StockCode']==item]['std'].values[0]
s= inventory_simulation(simpy.Environment(),ROL,ROQ,CP,SP,customer_rate,mean,std)

Now let's check our toolset for stock items with StockCode of ‘10002’


Full code available on github

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store