Unlocking Efficiency: Exploring Querri AI for Inventory Management, Cash Conversion Cycle, and Accounts Receivable Analysis

Inventory management is key to using cash effectively

Too much inventory means more cash tied up that could be put to use elsewhere and potentially higher obsolescence costs as parts in storage fall out of use. Too little inventory results in work stoppages for a manufacturer or lost sales for a retailer. Finding the balance is challenging, particularly since lead times and usage vary between parts and over time.

Using Querri to understand and forecast inventory

Taking an inventory database from Kaggle, let’s explore how to use Querri to understand and forecast this inventory.

The explanation on Kaggle doesn’t provide a lot of detail, so like with most data sets, the first step is to understand the data we are working with. Using natural language, I started with, “Given this is an inventory database, help me understand what the columns are?” Querri responded with a summary of each column and suggestions for further analysis.

From this, we can see that the data set is a list of orders for products, along with the components needed and lead times for those components. We’ll get into building a model, but first, let’s look a little closer at the data. Getting a summary of the dataset improves our understanding of the data and helps us check for missing or corrupted data.

Summary of the Dataset:

No missing values is good, but it seems odd that the number of unique values for Comp_Item_ID/Item_Desc and Product_ID/Product_Name do not match. I looked at this some more, but without talking to the data originator, I was unable to resolve the differences.

The suggested data conversion step is important before moving on to modeling. Looking to test natural language as much as possible, I simply asked Querri to convert the listed columns to be more suitable for analysis and let it determine what types to convert them to. Querri is still in the early demonstration stages and gave me an error during processing, but completed it correctly.

With the data in a better shape, let’s take a look at some of the trends with the data. First, MRP Forecasted Quantity (MRP_FCST_QTY) over Time.

We can see the trend upwards towards late 2016, then a slide-off going forward from there. This makes sense when we consider that it’s based on a combination of actuals and the forecast, but what it tells us about the forecast here is that it is driven by a sales pipeline without adjusting for speculative business. It could be hoped that the levels would continue more consistently into the future. A highly accurate long-term forecast for inventory demand would require a better sales forecast, but for the purposes of maintaining an efficient operating balance, this may be sufficient unless lead times are extreme. Querri also provided a distribution of the lead times:

From this, we see that if the purpose of the forecast is to ensure we have needed inventory on hand without carrying excess inventory, we don’t need to forecast further than 24 days ahead, and in 75% of the cases, 17 days would be sufficient.

Moving on to the forecasting process, I’ve used linear regression and Long Short-Term Memory (LSTM) neural networks for forecasting time series data, but my experience with it is limited. Linear regression doesn’t fit time series well, and LSTMs, while powerful, can require a lot of computation. In this case, rather than specifying a model, I just asked Querri to build a forecast. It chose SARIMAX (Seasonal Autoregressive Integrated Moving Average with eXogenous factors). This is a powerful algorithm specifically for forecasting time series data, which is part of the Python statsmodels library. Having the AI find a solution I was not aware of shows just how helpful it can be.

Based on the model, it produced a series of output charts.

Unfortunately, the decomposition is a bit of a mess. The forecasts seem better, and we can clearly see where new inventory would need to be ordered and the spikes in ordering in the data. However, these raise additional questions about the data, and further analysis would be needed.

Conclusion

This blog was a process of discovery for me. Sometimes data analysis can feel like bear wrestling, and this was one of those times for me. The use of AI in forecasting inventory demand accurately is valuable in part because that forecast is challenging, even more so when there are questions about the underlying data. Querri was able to help me along the way, in at least one case bringing to my attention a tool I had not used during 9 months of data analysis and machine learning training. However, a better understanding of the data or better underlying data is needed to build a really solid forecast.