Inventory Management
🛍️

Inventory Management

Retail inventory management is the backbone of any retail business, essentially enabling you to keep your business in order. It’s the system and processes you implement to keep a record of your stores' inventory. Whilst we can probably all agree that some type of inventory management process is crucial. Having the right automated inventory management system in place can make all the difference. Out of stock items equals frustrated customers and loss of sales which over time could damage a retailer’s reputation and lose future customers and sales.

Problem Statement

Using historical sales data, the latest order made by the retailer, and supplier's delivery info create the next order list to cover all the demand for the selected time period.

Dataset

Few datasets needed as input, with the following attributes and sample previews:
Sales Data
Variable
Datatype
Description
datetime
day of the year
int
shop identifier
int
product identifier
int
product category identifier
float
product price
int
total quantity sold
notion image
Items
Variable
Datatype
Description
int
unique product identifier
int
product category identifier
int
the number of days between day when order with the product was purchased and when product was received
int
the maximum lead time that may be required in case of supplier delays
int
number of items available in stock at the beginning
notion image
Orders
Variable
Datatype
Description
int
product identifier
int
shop identifier
int
total ordered number of items
datetime
day when order was purchased
datetime
day when order expected to be received
notion image
Order Items
Variable
Datatype
Description
int
product identifier
int
shop identifier
notion image
Supplier Restrictions
Variable
Datatype
Description
int
product identifier
int
minimum number of products that can be ordered
notion image

Datrics Pipeline

notion image
Here we can see four differentiated groups of bricks, all of them are going to be described in detail.

Build Demand Forecasting Model

The first group called "Train Model" are all about get historical data, filter data related only to the shop we are interested in (e.g. shop_id = 31), mark categorical variables using Typization Brick, and feed all that data into LGBM Regression, with the following configuration:
notion image
 
As a result, we get model that would be used to forecast product demand.
notion image

Predict Demand

The next group called "Predict Demand", uses the model trained in the previous section, and preprocessed data to forecast the product's demand.
notion image
Here we filter products only related to one shop (e.g shop_id = 31), generate future days period for prediction using Create New Column Brick,
notion image
and using Join Brick with the "cross join" options we get all possible combinations of product and day from the prediction period.
Other required data for the pre-trained model usage (e.g item price) are taken from the input historical data. As the last step in this section, we get a prediction for each product for the period from '2015-12-14' to '2015-12-30' and round it to get int values using Apply Function Brick.

Calculate Reorder Point and Safety Stock

The next group called "Get ROP, SafetyStock" relates to calculations we need to perform to get inventory management figures.
A reorder point (ROP) is the minimum unit quantity that a business should have in available inventory before they need to reorder more products. The formula to calculate the reorder point is the sum of lead time demand and safety stock in days.
Reorder points ensure that you don’t fall behind on your next batch of inventory. With an accurate reorder point for each SKU, you’ll always have enough stock on hand to satisfy customer demand — without tying up excess capital in inventory.
Reorder point formula
Reorder Point (ROP) = Demand during lead time + safety stock
Demand during lead time
Lead time is the number of days between when you place a purchase order with your manufacturer or supplier for a product and when you receive the product.
To find demand during lead time, we need to multiply the lead time (in days) for a product by the average number of units sold daily:
Lead time demand = lead time x average daily sales
notion image
Safety stock
It’s not enough to know the average demand for a product, as that demand can increase suddenly or problems with a supplier can prevent you from restocking as quickly as you expected. Safety stock, as the name suggests, is the extra “just in case” inventory you keep on hand to anticipate variability in demand or supply.
Safety stock level = (Max daily orders x max lead time) – (average daily orders x average lead time).
To find the proper safety stock level for a given product:
  1. Multiply the maximum number of daily orders by the maximum lead time that may be required in case of supplier delays.
  1. Multiply the average number of daily orders by the average lead time.
  1. Subtract the result of Step 2 from the result of Step 1.
Now, back to our reorder point formula: Just add together the lead time demand and safety stock calculation, and, — you’ve calculated ROP.
Back to platform capabilities, Lead Time and Max Lead Time are given as an input for each product in the Items dataset, while average and maximum daily sales are calculated using Data Aggregate Bricks from historical sales dataset. Using this data as an input and sequence of Apply Function Brick, we can easily calculate ROP and Safety Stock for each product. As a final step in this group, we filter out all intermediate results and unnecessary data that was needed for computation to get only ROP, Safety Stock, and Lead Time Demand for each item:
notion image

Generate Next Orders

Final group of brick called "Get Next Orders".
notion image
Firstly, we load the last orders made by the retailer, and using Join Data Brick combines that data with predicted products daily demand.
notion image
 
After that, we fill missing values and sum predicted product daily items to be sold using Rolling Function Brick in For Loop Container for each product to get rolling demand (highlighed brick below):
notion image
After joining data from the previous step with the data calculated in the section "Get ROP, Safety Stock" using Join Data Brick, we can add to the starting stock - items arrived on that day and subtract already calculated rolling demand, to get inventory level for each day in the predicted days period. Here we can see an intermediate result for some item (e.g. item_id=64):
notion image
To get a day for each item when the new order should be purchased we compare the daily level of inventory with Reorder Point using Compare Data Brick. For the day when these two equals, the amount of product that should be purchased from the supplier on that day are calculated using Apply Function Brick and the following formula:
order amount = safety stock + lead time demand
And finally, if items to order are smaller than the minimum possible quantity that the supplier can provide (or minimum cost-effective quantity), this amount is replaced with the minimum batch size of the purchase for that product using Compare Data, Filter Rows and Columns Bricks, and Union final result (highlighted brick below):
 
notion image
As a result, we get the list of orders with dates specified when these orders should be submitted to the supplier and the recommended number of each item:
notion image
 
undefined