Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. In our previous Use Case, we took a massive leap into machine learning and built a simple sales forecasting model in KNIME. You can now predict how much revenue you will generate next month.
But as a DX manager who has worked closely with supply chain teams, I can tell you that predicting high sales is only half the battle. If a customer tries to buy your product and it is out of stock, that revenue drops to zero. On the other hand, if you buy too much inventory “just in case,” your company’s cash is trapped in a warehouse.
To balance this perfectly, inventory managers use a calculation called Safety Stock. Doing this manually in Excel for 10 products is easy. Doing it for 10,000 SKUs (Stock Keeping Units) every week is a nightmare.
In this tutorial, I will show you how to automate your inventory management by calculating safety stock across your entire product catalog instantly using KNIME.
What is the Safety Stock Formula?
Safety stock acts as an emergency buffer against sudden spikes in demand or delays from your suppliers. The most common formula used by supply chain professionals is:
(Max Daily Sales × Max Lead Time) − (Average Daily Sales × Average Lead Time)
To calculate this in KNIME, we just need to break it down into three simple nodes: aggregating the sales, joining the supplier data, and doing the math.
Step 1: Calculate Daily Sales (GroupBy Node)
First, we need to know the Maximum and Average daily sales for every single SKU. Let’s assume you have a dataset showing two years of daily order data.
- Drag the GroupBy node onto your canvas and connect your daily sales data. (If you need a refresher, read my guide on how to use the GroupBy node).
- In the “Groups” tab, select Product ID (or SKU).
- In the “Manual Aggregation” tab, select the “Quantity Sold” column. Change the aggregation method to Maximum.
- Select the “Quantity Sold” column again, but this time change the aggregation method to Mean (Average).
Execute the node. You now have a clean list of every product, showing the absolute most you ever sold in a single day, and the average amount you sell on a normal day.
Step 2: Bring in Supplier Lead Times (Joiner Node)
Next, we need the supplier data. “Lead time” is simply how many days it takes for your supplier to deliver the goods after you order them. You usually have a separate Excel file containing the “Average Lead Time” and “Max Lead Time” for each Product ID.
To blend this with our sales data, we use the Joiner node. Connect your GroupBy output to the top port, and your Supplier Excel file to the bottom port. Match them by “Product ID”.
(If you have never done this, check out my tutorial on how to do VLOOKUP in KNIME using the Joiner node).
You now have a master table containing all four numbers required for our formula: Max Sales, Avg Sales, Max Lead Time, and Avg Lead Time.
Step 3: Calculate Safety Stock (Math Formula Node)
Finally, we do the math. Add the Math Formula node to your workflow and connect it to your Joiner output.
Double-click to open it. In the Expression box, simply recreate the formula by double-clicking your columns and typing the math symbols:
($Max(Quantity Sold)$ * $Max Lead Time$) - ($Mean(Quantity Sold)$ * $Avg Lead Time$)
Check the “Append Column” box, name it “Safety Stock Requirement”, click OK, and execute!
Conclusion: Your Next Steps
Congratulations! Your KNIME workflow has instantly calculated the exact number of buffer units you need to keep in the warehouse for every single product in your catalog.
Next week, when your new sales data arrives, you don’t have to recalculate anything. Just click “Execute All,” and KNIME will dynamically adjust your safety stock levels based on the latest trends.
We have now optimized marketing and supply chain operations using internal numbers. But what about external, unstructured data? How do you measure what people are actually saying about your brand online?
Are you ready to dive into text analytics? Join me in our next exciting Use Case where we explore Social media sentiment analysis without coding in KNIME!
