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
Items
Variable
Datatype
Description
int
the number of days between day when order with the product was purchased and when product was received
Orders
Variable
Datatype
Description
Order Items
Supplier Restrictions
Variable
Datatype
Description
Datrics Pipeline
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:
Â
As a result, we get model that would be used to forecast product demand.
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.
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,
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
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:
- Multiply the maximum number of daily orders by the maximum lead time that may be required in case of supplier delays.
- Multiply the average number of daily orders by the average lead time.
- 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:
Generate Next Orders
Final group of brick called "Get Next Orders".
Firstly, we load the last orders made by the retailer, and using Join Data Brick combines that data with predicted products daily demand.
Â
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):
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):
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):
Â
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:
Â