Optimizing Procurement and Sales Strategies for a Retail Chain with Supplier Payment Schemes#
Introduction#
In the competitive landscape of retail, managing procurement and sales strategies effectively is critical for profitability. This model addresses the complexities of retail operations by optimizing procurement plans, balancing supplier payment schemes, and accounting for variable demand, storage constraints, and losses. By incorporating a detailed financial framework, the model aims to maximize the retail chain’s profit while adhering to operational and contractual requirements.
Tags: amplpy, mip, inventory-management, cash-flow-management, payment-schemes, discounting, multi-period-planning, cost-minimization, cbc, open-source
Notebook author: Mikhail Riabtsev <mail@solverytic.com>
1. Problem Statement#
The retail chain “YourRetail” operates by offering a wide range of products sourced from suppliers and manufacturers. To maintain its competitive edge, “YourRetail” applies a standard trade markup of 25%. However, managing inventory, cash flow, and overall profitability is challenging due to the dynamic nature of demand, which is influenced by seasonal elasticity.
Key Features of the Problem#
Product Assortment#
“YourRetail” offers 50 product types, each with different purchase prices depending on the payment scheme used.
Prices are categorized as follows:
Price A: Cost under Scheme A.
Price B: Cost under Scheme B (5% higher than Price A).
Price C: Cost under Scheme C (10% higher than Price A).
Payment Schemes#
“YourRetail” employs three distinct payment schemes, each impacting cash flow and procurement costs:
Scheme A: Full payment is made upon receipt of goods.
Scheme B: Payment is split into two installments:
50% upon delivery.
Remaining 50% after one month, with a 5% price increase compared to Scheme A.
Scheme C: Goods are accepted on consignment, with full payment made one month after delivery. The purchase price increases by 10% compared to Scheme A.
Demand and Planning Horizon#
Storage Capacity and Costs#
“YourRetail” operates a warehouse with a total capacity of 2,000 square meters.
Weekly storage cost is $0.10 per square meter.
Financial Constraints#
Initial working capital: $15,000.
Discount rate: 5% annually, representing the time value of money.
Objective#
The goal is to maximize total profit over the 27-week planning horizon, considering the following factors:#
Revenue generated from product sales.
Costs incurred due to procurement, storage, and delayed payments under different schemes.
Constraints on cash flow, warehouse capacity, and operational feasibility.
2. Download Necessary Extensions and Libraries#
Let’s start by downloading the necessary extensions and libraries
# Install dependencies
%pip install -q amplpy pandas
import pandas as pd # Loading panda to work with pandas.DataFrame objects (https://pandas.pydata.org/)
import numpy as np # Loading numpy to perform multidimensional calculations numpy.matrix (https://numpy.org/)
import math
# Google Colab & Kaggle integration
from amplpy import AMPL, ampl_notebook
ampl = ampl_notebook(
modules=["cbc", "highs", "gurobi"], # modules to install
license_uuid="default", # license to use
) # instantiate AMPL object and register magics
3. AMPL Model Formulation#
%%writefile supplier_payment_schemes.mod
reset;
# Model Name: Retail Chain Optimization
# Version: 1.0
# Last Updated: January 2025
### SETS
param T >= 0; # Number of weeks in the planning horizon
set PROD; # Products, each modeled separately
set CHAIN_ATTR; # Attributes of the retail chain (e.g., storage cost, warehouse space)
set PAYMENT_SCHEME; # Different payment schemes ('A', 'B', 'C') with specific terms
set PAYMENT_ATTR; # Attributes of payment schemes (e.g., fraction paid upfront, payment delay)
set LINKS within {PROD, 1..T}; # Links between products and weeks for demand
### PARAMETERS
param demand {LINKS} >= 0; # Weekly demand for each product
param area {PROD} default 1; # Area occupied by each product
param cost {PROD, PAYMENT_SCHEME} >= 0; # Cost per product under each payment scheme
param chain_attr {CHAIN_ATTR} >= 0; # Retail chain attributes (e.g., storage cost, initial cash)
param scheme_attr {PAYMENT_SCHEME, PAYMENT_ATTR} >= 0; # Attributes of each payment scheme
param discount_rate >= 0; # Annual discount rate for time value of money
param bigM = 10e4; # Large constant for constraint modeling
### VARIABLES
var Units_Purchased {PROD, 1..T, PAYMENT_SCHEME} >= 0; # Units purchased for each scheme in each period
var IsScheme_Selected {PROD, 1..T, PAYMENT_SCHEME} binary; # Binary indicator for chosen scheme per product and period
var Units_Sold {(p,t) in LINKS, s in PAYMENT_SCHEME} >= 0; # Units sold per product, time, and payment scheme
var Inventory_Level {PROD, 1..T, PAYMENT_SCHEME}; # Inventory level per product, scheme, and time
var Cash_Flow {1..T} >= 0; # Available cash flow per period
### OBJECTIVE
# Maximize total profit (revenues - costs - storage costs, discounted over time)
maximize TotalProfit:
sum {t in 1..T, s in PAYMENT_SCHEME} (
sum {(p,t) in LINKS} Units_Sold[p,t,s] * cost[p,s] * chain_attr['markup'] # Revenue
- sum {p in PROD} Inventory_Level[p,t,s] * chain_attr['storage_cost']) # Storage cost
* (1 / (1 + discount_rate)^t); # Discount factor
### CONSTRAINTS
# 1. Inventory dynamics: Update inventory levels based on purchases and sales
s.t. InventoryDynamics {p in PROD, t in 1..T, s in PAYMENT_SCHEME}:
Inventory_Level[p,t,s] =
(if t = 1 then 0 else Inventory_Level[p,t-1,s]) # Previous period's inventory
+ Units_Purchased[p,t,s] # Current period purchases
- sum {(pp,tt) in LINKS: pp=p && tt=t} Units_Sold[p,t,s]; # Units sold in current period
# 2. Cash flow dynamics: Update cash flow based on revenue, costs, and storage expenses
s.t. CashFlowConstraint {t in 1..T}:
Cash_Flow[t] =
(if t = 1 then chain_attr['start_money'] else Cash_Flow[t-1]) + # Previous period's cash or initial cash
sum {(p,t) in LINKS, s in PAYMENT_SCHEME} (
Units_Sold[p,t,s] * cost[p,s] * (1 + chain_attr['markup']) # Revenue
- Units_Purchased[p,t,s] * cost[p,s] * scheme_attr[s, 'payment_fraction_now'] # Immediate payment
- sum {tt in 1..T: tt = t - scheme_attr[s, 'payment_delay'] and tt >= 1}
Units_Purchased[p,tt,s] * cost[p,s] * (1 - scheme_attr[s, 'payment_fraction_now']) # Delayed payment
) - sum {p in PROD, s in PAYMENT_SCHEME} Inventory_Level[p,t,s] * chain_attr['storage_cost']; # Storage cost
# 3. Scheme selection: Only one payment scheme can be selected for a product in each period
s.t. SingleSchemeSelection {p in PROD, t in 1..T}:
sum {s in PAYMENT_SCHEME} IsScheme_Selected[p,t,s] = 1;
# 4. Warehouse capacity: Total inventory across all products and schemes must fit within warehouse space
s.t. WarehouseCapacity {t in 1..T}:
sum {p in PROD, s in PAYMENT_SCHEME} Inventory_Level[p,t,s]
<= chain_attr['warehouse_space'];
# 5. Demand satisfaction: Total sales cannot exceed demand for each product in each period
s.t. DemandSatisfaction {(p,t) in LINKS}:
sum {s in PAYMENT_SCHEME} Units_Sold[p,t,s] <= demand[p,t];
# 6. Purchase balance: Ensure purchases stay within available cash flow
s.t. PurchaseDynamics {t in 1..T}:
sum {p in PROD, s in PAYMENT_SCHEME} Units_Purchased[p,t,s] * cost[p,s]
<= Cash_Flow[t];
# 7. Sales balance: Sales cannot exceed available inventory
s.t. SalesBalance {(p,t) in LINKS, s in PAYMENT_SCHEME}:
Units_Sold[p,t,s] <= (if t = 1 then 0 else Inventory_Level[p,t-1,s]);
# 8. Link purchases to selected schemes: Enforce purchases to match selected schemes
s.t. PurchaseSchemeLink {p in PROD, t in 1..T, s in PAYMENT_SCHEME}:
Units_Purchased[p,t,s] - bigM * IsScheme_Selected[p,t,s] <= 0;
Overwriting supplier_payment_schemes.mod
4. Load data#
ampl.read('supplier_payment_schemes.mod') # Load the AMPL model from the file
# Load product data from CSV into pandas DataFrames
df_1 = pd.read_csv('Price.csv') # Load product price data
df_2 = pd.read_csv('Retail.csv') # Load retail demand data
# Define the CHAIN_ATTR set with retail-specific attributes
ampl.set['CHAIN_ATTR'] = ['start_money', 'warehouse_space', 'storage_cost', 'markup']
# Define the PAYMENT_SCHEME set with payment schemes 'A', 'B', 'C'
ampl.set['PAYMENT_SCHEME'] = ['A', 'B', 'C']
# Define the PAYMENT_ATTR set with payment-related attributes
ampl.set['PAYMENT_ATTR'] = ['payment_fraction_now', 'payment_delay']
# Set the total number of periods based on the number of weeks in the retail data
ampl.param['T'] = max(df_2['week'])
# Assign product IDs to the PROD set in AMPL
ampl.set['PROD'] = set(df_1['prod_id'])
ampl.set['LINKS'] = set(tuple(x) for x in df_2[['prod_id', 'week']].dropna().to_records(index=False))
# Prepare and assign demand data with integer keys (product ID, week) and demand values
demand_data = {(int(row['prod_id']), int(row['week'])): int(row['demand']) for _, row in df_2.iterrows()}
ampl.param['demand'] = demand_data
# Create the price data dictionary with product-price pairs for each scheme (A, B, C)
price_data = {
(int(row['prod_id']), scheme): float(round(row[f'price {scheme}'], 2))
for _, row in df_1.iterrows()
for scheme in ['A', 'B', 'C']}
ampl.param['cost'] = price_data
# Assign specific retail chain parameters (e.g., start money, warehouse space, storage cost)
ampl.param['chain_attr']['start_money'] = 15000
ampl.param['chain_attr']['warehouse_space'] = 2000
ampl.param['chain_attr']['storage_cost'] = 0.1
ampl.param['chain_attr']['markup'] = 0.25
# Define payment scheme attributes (payment fractions and delays)
ampl.param['scheme_attr'] = {
('A', 'payment_fraction_now'): 1,
('A', 'payment_delay'): 0,
('B', 'payment_fraction_now'): 0.5,
('B', 'payment_delay'): 4,
('C', 'payment_fraction_now'): 0,
('C', 'payment_delay'): 4
}
# Define the discount rate as a weekly rate (annual discount rate divided by 52 weeks)
ampl.param['discount_rate'] = 0.05 / 52
#ampl.display('LINKS', 'CHAIN_ATTR', 'PROD', 'PAYMENT_SCHEME', 'PAYMENT_ATTR', 'T', 'demand', 'cost', 'chain_attr', 'scheme_attr' )
5. Solve problem#
# Set the solver type for use in solving the problems
solver = 'cbc' # Use CBC solver for optimization tasks
ampl.option['show_stats'] = 1 # Show problem size statistics (default: 0)
ampl.option['display_1col'] = 0 # Disable single-column data display
ampl.option['omit_zero_rows'] = 1 # Hide rows with zero values
ampl.option['omit_zero_cols'] = 1 # Hide columns with zero values
ampl.option['mp_options'] = 'outlev=1 lim:time=20' # Configure CBC options (output level and time limit)
ampl.solve(solver=solver, verbose=False) # Solve the optimization problem using CBC solver
6. Display results#
# Display results for key variables
ampl.display('Units_Sold', 'Units_Purchased', 'IsScheme_Selected', 'Inventory_Level', 'Cash_Flow')
7. Retrieve solution in Python#
# Initialize an empty dictionary to store AMPL variable data
amplvar = dict()
# Prepare a list of AMPL variables
list_of_ampl_variables = [item[0] for item in ampl.get_variables()]
# Iterate over each variable name in the list
for key_ampl in list_of_ampl_variables:
# Skip certain variables that are not to be processed (these variables won't be included in the output)
if key_ampl not in ['']:
# Convert the AMPL variable data to a pandas DataFrame
df = ampl.var[key_ampl].to_pandas()
# Filter the DataFrame to include only rows where the variable's value is greater than a small threshold (1e-5)
filtered_df = df[df[f"{key_ampl}.val"] > 1e-5]
# Round the values in the DataFrame to two decimal places
rounded_df = filtered_df.round(2)
# Convert the filtered DataFrame to a dictionary and add it to the amplvar dictionary
amplvar[key_ampl] = rounded_df #.to_dict(orient='records')
print (amplvar[key_ampl])
Units_Sold.val
index0 index1 index2
11704 2 B 7.0
3 A 2.0
4 B 5.0
5 B 5.0
6 C 14.0
... ...
14561 23 C 20.0
24 C 6.0
25 C 13.0
26 C 9.0
27 C 1.0
[1183 rows x 1 columns]