Solving store inventory allocation problem of CPG

Sujoy Saha

Shinoj Zacharias

Shima GhassemPour

June 19th, 2023 3

Introduction

One of the major problems faced by Consumer Packaged Goods (CPG) companies is to estimate the true demand of its products from multiple channels. CPGs supply their products to different retailers to sell in their stores. The decision to host inventory of a particular CPG is usually made by the retailer based on several performance parameters of the CPG such as fill rate, days on hand, pricing, discount rate, demand of the CPG’s product/brand. and others.

While the CPG receives orders being placed from the retailer in bulk, usually they don’t have enough visibility of the customer demand from the customers. Having better visibility of actual sales and demand of its products in the retailer stores will enable the CPG to plan better and make better decisions on how many products need to be allocated to which retailers and to which stores. It will also help the CPG accumulate accurate demand signals for its production.

Let us explain the problem with a simplistic illustration as shown in the diagram below. In this diagram there is a factory, run by the CPG, which produces and ships products to CPG warehouses. The warehouses, in turn, supply the product to a retailer warehouse. This retailer’s warehouse then ships those products to three stores mapped to that warehouse. The number displayed in the table under inventory is the actual inventory shipped to that store, while demand is the actual demand of that product in that store. Since the CPG has no idea about the actual demand of its products in stores, it ships the products based on the value in the demand column. The illustration demonstrates that if the allocation could have happened based on actual demand, then the CPG could have saved on lost sales opportunity, inventory holding cost, reverse logistics cost, and production cost.

Working with a CPG giant in the healthcare industry, we sought to draft a solution that could:

Provide insights on forecasted demand of a CPG product at individual retailer store.
Aggregate the same at warehouse level.
Place the order accordingly.

SupplyChain

Objectives

To build a complete understanding of the inventory journey from the CPG warehouses to the retailer stores and validate if all required data assets in the journey are properly maintained in CPG IT.
To map and understand all the key players in CPG-retailer supply chain and identify key data assets, which are required to build a demand forecasting capability, and determine whether they are in place or not.
To build a demand forecasting capability at a store-product granularity to facilitate inventory management.
To operationalize the demand forecasting capability in production.
To build a visualization to recommend inventory count that needs to be maintained at the various retailer stores.

User personas

The main user personas for this product will be the Supply Chain analysts and planners of the CPG.

This report will also be useful for the demand planning and sales teams of the CPG.

Background

Like many other machine learning initiatives, this initiative also had three phases.

Diagnostics: — In the Diagnostics phase, we analyzed the current performance of inventory management, as driven by the CPG Sales and Supply chain teams. There was an existing set of Power BI (PBI) reports which were not performing at the optimal level. The team first optimized the performance of those set of reports by making significant changes at the modelling level of the reports. Then they added a set of new reports to provide better visibility of the supply chain.
Predictive: — In the Predictive phase, we understood the root cause of the stock out problems faced by the CPG. We then conducted two iterations of ML (Machine Learning) feasibility studies to check if the CPG has the required data elements to build a predictive capability to forecast demand of CPG products at a store-product level of granularity. We needed two iterations as the first iteration was not fully conclusive given the complexity of the problem landscape and due to low sales velocity of CPG products in the retailer stores. After the second ML feasibility study, we concluded that a demand forecasting capability at a store-product level of granularity is the need of the hour and also can be build confidently for a significant section of the CPG product portfolio. In the MLOPS (ML Operationalization) phase of the project, we scaled the ML models to CPG production.
Prescriptive: — The final most important layer of the initiative was to provide a visualization to the users so that they can use the insights generated from the ML models in their day to day job. Herein, we make a recommendation for the supply chain analysts of the CPG to plan for inventory movement to the different stores so that the forecasted demand of the different stores can be met.

Constraints

The initiative operated with a few constraints as detailed below:

The execution at retailer will happen at a segment level. There will be a segment allocated to a store-product combination from the retailer. However, this information will not flow to the CPG.
There will be no visibility available to any logistics information at retailer end which includes shipment information from the retailer warehouses to the retailer stores.
Order management will take place at a CPG Warehouse level for the different products and not at a store level.

Explorations + Decisions

RPRS

Data

The following data elements are required to build this solution. If the CPG can provide these datasets then the solution can be easily extended to

POS Sales: Historical sales of the CPG products in the several retailer stores will be directly used to forecast demand of those products in future.
Store Inventory data: Inventory information will be crucial to figure out stock outages faced in retail stores. The demand of the CPG products in the individual retailer stores will accordingly be adjusted for out-of-stock scenarios to understand the true demand of the CPG product in the retailer stores.
Promotions data: Product-specific promotional information will play a significant role in addressing the movement of product demand in the individual stores. It will be even better if we can get hold of store-specific promotional event information.
Weather information: Weather might play a significant role in the demand of a particular product. As an example, winter will help sales of sweaters. Cold and flu medicines are expected to sale more during flu season.
Store features: Sales patterns are often driven by the size of the retailer store. Larger format of the retailer store might be able to drive higher sales compared to a smaller format.
Holiday information: Holiday or calendar information might be very helpful in improving forecast accuracy of certain types of products. As an example, one might expect sales of TVs to peak during the Thanksgiving.
Product/category specific information: Certain products might have unique features which could drive its sales significantly. As an example, the travel pack size of toothpaste can be expected to sell more during holidays, as people travelling on holidays will prefer the travel pack size.
COVID data: Since this initiative took place during COVID times, the historical data used for the models were impacted significantly due to COVID. We used a public dataset on COVID incidences in US was used in this project.