A Premier CPG Company’s Distributor Leveraged an Inventory Forecasting Solution to Save An Estimated $2.1 Million

A CPG company’s sales operations team followed traditional demand forecasting methods, and monthly targets were set based on intuition for every product category sold by every distributor. With an improved efficiency of their supply chain, the distributors continued to lower their inventory levels. However, since the sales team’s targets didn’t account for these changing inventory levels, they missed their sales target often, and wanted to leverage CPG data analytics to optimize inventory forecasting.

A hybrid ML based solution was developed for an efficient inventory forecasting to predict the changing inventory levels of distributors for every product category. A Power BI based simulator was created to present the results. 

We followed a four-step process to help resolve challenges faced by the client. Through the process, the clients were able to maintain and plan a predictive inventory framework in a better manner, while making sales decisions that were more pertinent for an evolving market.

Step 1: Data Preparation

The existing sales targets were being set based on the intuition of the Customer Success Managers and using traditional demand forecasting methods. However, the customers (distributors) they were selling to, had dynamic demands at varying frequencies and not a consistent, monthly requirement. Therefore, the sell-in, which is the product that has been sold to the distributors, and the sell-out, which is what the distributors have sold to customers, had to be determined. Few select distributors were identified and prioritised for the inventory forecasting model.

Analytical data was collated for volume sold, under each specific category and factors like volume, macroeconomic data, promotions etc. were included. Exploratory data analysis was performed after treating missing values and outliers.

Step 2: Forecast Sell-out

Built a hybrid inventory forecasting model with historical sell-out data. Included variables such as weather, macroeconomic factors etc. Tested the model and validated the accuracy on a few out-of-time samples. Multiple forecasting algorithms used for the same – a mix of traditional (ARIMAX) and modern (Prophet) algorithms used to identify the right fit for a specific category of the client’s product.

Step 3: Inventory Analysis

Our CPG data analytics team analysed the Co-efficient of Variation of sell-out volume to estimate the safety stock that the distributor might have reserved. Performed exploratory data analysis to understand the mathematical relationship between lags/leads of sell-out and of sell-in.

The major challenge here was to define and standardize the unit of measurement for the sell-in and sell-out as they often varied, because while the client sold in bulk, more often than not, the distributor did not. The price variation from sell-in to sell-out also had to be factored in. After much research and understanding minimum and maximum margins, we established business rules and tested it out across products and the distributors. Sell-in and sell-out data was normalised. Using sample of few products, monthly trend of sell-in and sell-out was super-imposed.

Step 4: Predict Sell-in

By leveraging the sell-out forecasts, we estimated safety stocks of distributors and the variance between sell-in and sell-out to predict sell-in volume for the upcoming months. The biggest challenge in this regard, was determining the distributor’s frequency of restocking, and therefore, the predicting the sell-in volume for the coming months.

Initially, the client just wanted to evaluate if the problem can be solved, but we worked with them to also create a Power BI dashboard, which enabled them look at business trends for a particular category or analyse a particular distributor’s sell-in. Algorithms like Prophet, ARIMAX, UCM, LSTM, Random Forest, GBM, LGBM etc, used.

All the data collected was plugged into a power BI dashboard allowing customers to simulate levels of stock-outs at distributors under variable levels of sell-outs.