Optimal SKU Allocation Service

Overview

This microservice implements an Integer Linear Programming (ILP) model to solve a stock allocation problem.

The model is designed to solve the trade-off between

• profitability
• customer demand

with supply and store capacity constraints.

The primary goal is to maximize the pure operational margin within a network of stores.

---

Problem Statement

Let

• $W$ be a Central Warehouse.
• $J$ a set of Retail Stores.
• $I$ a set of Products
• $SKU$ the category of products $I$.

The business needs is to allocate in the most profitable way the available stock from the central warehouse to the retail stores.

Objective

Total Incremental Operational Profit defines the pure, net financial gain expected from the allocation plan.

$$Z = \sum_{i \in I} \sum_{j \in J} R_{ij} \cdot x_{ij}$$

It is calculated as the Unit Revenue minus only the Unit Variable Costs (like COGS, shipping, or packaging) for every allocated item.

The model must focus exclusively on the marginal contribution of each unit given an allocation.

Constraints & Policies

The model incorporates two types of limits on allocation quantity:

1. Capacity Limits (C1 & C2): Based on logistical limits.
2. Market Limits (C3 & C4): Based on commercial forecast.

---

Analytical Model: Integer Linear Program (ILP)

This is classified as an ILP because all decision variables are restricted to being non-negative integers.

1. Sets and Parameters (Data Requirements)

Parameter	Description	Data Source
$\mathbf{I}$	Set of all available SKUs (products).	Product Attributes
$\mathbf{J}$	Set of all Retail Stores.	Store Capacity
$\mathbf{R_{ij}}$	Unit Incremental Profit (Revenue per unit minus Variable Costs) of allocating one unit of SKU $i \in I$ to Store $j \in J$.	Commercial Matrix
$\mathbf{V_{i}}$	The volume of one unit of SKU $i \in I$.	Product Attributes
$\mathbf{S_{i}}$	Total available supply/inventory of SKU $i \in I$ in the Central Warehouse.	Product Attributes
$\mathbf{N^{\text{Vol}}_{j}}$	Maximum total volume capacity of Store $j \in J$.	Store Capacity
$\mathbf{D^{\text{max}}_{ij}}$	Maximum forecasted demand of SKU $i$ at Store $j$. (Upper bound of the confidence interval).	Commercial Matrix
$\mathbf{D^{\text{min}}_{ij}}$	Minimum required allocation of SKU $i$ at Store $j$. (Lower bound of the confidence interval).	Commercial Matrix

2. Decision Variables

Variable	Description	Type
$\mathbf{x_{ij}}$	The number of units of SKU $i \in I$ allocated to Store $j \in J$.	$\mathbf{x_{ij} \in \mathbb{Z}_{\geq 0}}$ (Non-negative Integer)

3. Objective Function (Maximizing Total Incremental Operational Profit)

$$\text{Maximize } Z = \sum_{i \in I} \sum_{j \in J} R_{ij} \cdot x_{ij}$$

4. Constraints

ID	Constraint Name	Mathematical Formulation	Description
C1	Maximum Store Volume Capacity	$$\forall j \in J: \sum_{i \in I} V_i \cdot x_{ij} \leq N^{\text{Vol}}_{j}$$	Ensures the total volume of allocated units does not exceed the store's physical capacity.
C2	Warehouse Supply Capacity	$$\forall i \in I: \sum_{j \in J} x_{ij} \leq S_i$$	Ensures the total allocated quantity for any SKU does not exceed the central warehouse supply.
C3	Market Demand Cap (Upper Bound)	$$\forall i \in I, j \in J: x_{ij} \leq D^{\text{max}}_{ij}$$	Sets the maximum allocation to the highest value of the sales forecast confidence interval. This prevents overstocking.
C4	Minimum Demand Fulfillment (Lower Bound)	$$\forall i \in I, j \in J: x_{ij} \geq D^{\text{min}}_{ij}$$	Requires the allocation to meet the minimum required sales forecast. This guarantees high-demand items are prioritized to ensure customer satisfaction.

---

Solution Design

The problem falls in the category of event driven jobs. At every new event, eg, collection release, the business needs to make the decision and deliver the report to each store.

Data

As most businesses have different data solutions and techincal maturity, a simple google sheet with restriced access can be a good starting point with minimal costs.

A service account email is shared and placed in the allowed users for file accessibility.

3 main tables are necessary for the service to work:

• products

A table to define the attributes of the products

• stores

A dimension table to define the attributes of the store

• commercial_matrix

A operational table to define the operational gain of allocation given a pair sku-store

products schema:

• sku_id:VARCHAR - The SKU for the product
• unit_volume_m3:DOUBLE - the m3 space occupied by the package in store
• warehouse_supply_units:INTEGER - The sku quanity available in the warehouse to be allocated

stores schema:

• store_id:VARCHAR - identifier of the store
• max_volume_capacity_m3:DOUBLE - volume capacity of the store

commercial_matrix schema:

• sku_id:VARCHAR - identifier of sku
• store_id:VARCHAR - identifier of store
• unit_contribution_eur:DOUBLE - the operational gain from selling a unit in store
• max_forecast_demand_units:INTEGER - the max forecasted quantity for that sku in that store
• min_forecast_demand_units:INTEGER - the min forecasted quantity for that sku in that store

Request

As the event is not frequent and can fall within the free tier range of most cloud providers, the most efficient solution is a Serverless API with restriced priviliges.

Request payload:

curl -X POST 'http://<HOST>/api/v1/allocation/solve' \
  -H 'Content-Type: application/json' \
  -d '{
    "data_uri": "https://docs.google.com/spreadsheets/d/<IDGSHEET>/edit",
    "sheet_names": {
      "products": "SKU_ATTRIBUTES",
      "stores": "STORE_CAPACITY",
      "commercial_matrix": "DEMAND_AND_MARGINS"
    },
    "solver_config": {
      "solver_time_limit": 60
    }
}'

Response

the successful reponse will return a status 200 with the folowing payload:

{"status":"Optimal",
"solve_time_seconds":0.7456920146942139,
"total_expected_contribution_eur":39026.82,
"allocation_results":[
    {"sku_id":"1",
    "store_id":"1",
    "units_allocated":5,
    "total_contribution_eur":130.65
    },
    .
    .
    .
    ]}

Architecture