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
np.random.seed(0)
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(self.env.now)} 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_qtyself.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(self.env.now)} 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:
self.obs_time.append(self.env.now)
self.inventory_level.append(self.inventory)
self.costslevel.append(self.balance)
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(self.env.now,2)}')
else:
self.balance += self.selling_price*self.inventory
self.inventory = 0
#print(f'{self.inventory} is out of stock at {round(self.env.now,2)}')
if self.inventory < self.reorder_level and self.num_ordered ==0:
self.env.process(self.handle_order())
def plot_inventory(self):
plt.figure()
plt.step(self.obs_time,self.inventory_level)
plt.xlabel('Time')
plt.ylabel('SKU level')
def plot_balance(self):
plt.figure()
plt.step(self.obs_time,self.costslevel)
plt.xlabel('Time')
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
else:
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.runner_setup())
simulation.env.process(simulation.observe())simulation.env.run(until=until)def 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)
run(s,run_time)
service_levels.append(s.service_level())
costs.append(s.avg_cost_of_inventory())
eoq.append(q)
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
else:
plt.plot(reorder_qty_proposals,service_levels)
return eoq[np.where(costs==np.min(costs[service_levels>=target_service_level]))][0]
plt.show()
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(' https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx',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.to_feather('OnlineRetail.feather')
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)
run(s,8)
s.plot_inventory()Now let's check our toolset for stock items with StockCode of ‘10002’
run_simulation_for_item('10002',stockcodeQty,avg_customer_rate)Full code available on github
