Pricing Optimization for EFI — Feasibility Analysis

The Short Answer

Yes, we can build this. EFI's data is rich enough to support a profit-maximizing LP/MIP model, and there are production-quality solvers that run natively in Node.js via WebAssembly. CPLEX is overkill (and has no Node.js bindings) — HiGHS is the right solver. Our problem size (36 products × ~10 customers × 6 months = low thousands of variables) solves in milliseconds.

---

Table of Contents

1. EFI's Business as an Optimization Problem
2. The Mathematical Model
3. Data Availability
4. Solver Choice: HiGHS (not CPLEX)
5. Yield Management Concepts
6. Implementation Approach

---

EFI's Business as an Optimization Problem

EFI is fundamentally an arbitrage business: buy palm ingredients FOB in SE Asia, pay to ship them to the US, sell at a markup. The margin per ton is:

Margin = SellingPrice - LandedCost

Where landed cost stacks up as:

LandedCost = FOB_price                          (negotiated per supplier/PO)
           + OceanFreight                        (contract or spot rate per container)
           + Customs_broker_fees                 (~$175/container)
           + Tariffs                             (% of product value)
           + Drayage                             (port → warehouse trucking)
           + Handling_&_Storage                  (terminal fees)
           + Demurrage                           (if containers sit too long)

All of these components exist in our data. The ref_landed_cost_estimates table (1,564 rows) has the full cost breakdown per destination port per month. The supplier_purchase_orders table (781 rows) has historical FOB prices by supplier. Airtable has selling prices (Sales Price on Sales Orders) and contract terms.

---

The Mathematical Model

Objective Function

Maximize total margin across all products, customers, contract types, and months:

Maximize Z = Σ (over product p, customer c, contract type k, month t):
    [ SellingPrice(p,c,k,t) - LandedCost(p,supplier,port,t) ] × Qty(p,c,k,t)

Decision Variables

Variable	Type	Description
qty[p,c,k,t]	Continuous ≥ 0	Tons of product p to sell to customer c via contract type k in month t
buy[p,s,t]	Continuous ≥ 0	Tons of product p to purchase from supplier s in month t
inv[p,t]	Continuous ≥ 0	Inventory of product p at end of month t
ship[p,port,t]	Continuous ≥ 0	Tons shipped through each port in month t

Where:

• p ∈ {MagnaPalm, MagnaMaxx, MagnaFat, MagnaBlend, ...} (from products table, 36 active)
• c ∈ {customer list} (from Airtable Customers, ~10 active)
• k ∈ {Forward, Conversion, Spot} (contract types)
• t ∈ {Mar 2026, Apr 2026, ..., Aug 2026} (6-month horizon)
• s ∈ {supplier list} (from suppliers table, ~48, filtered to active)
• port ∈ {BAL, OAK, SAV, TAC} (from ref_efi_ports)

Constraints

1. Demand satisfaction — can't sell more than the customer needs, must honor contract minimums:

DemandMin[p,c,t] ≤ Σk qty[p,c,k,t] ≤ DemandMax[p,c,t]

Source: Airtable Contract Line Items (Qty (ST)) for forward commitments, historical Sales Orders for demand estimates

2. Supply capacity — can't buy more than suppliers can deliver:

buy[p,s,t] ≤ SupplierCapacity[p,s,t]

Source: historical supplier_purchase_orders grouped by supplier × product × month — use max historical volume as capacity proxy

3. Inventory balance — what's in the warehouse flows forward:

inv[p,t] = inv[p,t-1] + Σs buy[p,s,t] - Σc,k qty[p,c,k,t]

Source: Airtable Inventory Availability for initial inventory, then flow conservation

4. Port throughput — shipping through each port has practical limits:

ship[p,port,t] ≤ PortCapacity[port,t]

5. Contract type timing — Forward purchases must happen within sourcing windows, Spot only in delivery month:

If k=Forward:  buy must occur ≥ 6 weeks before month t
If k=Conversion: buy occurs 2-4 weeks before
If k=Spot: buy occurs in month t

Source: ref_shipment_windows (23,448 rows) — half-month windows

6. Transit time feasibility — orders placed must arrive in time:

OrderDate + TransitDays[origin,dest] ≤ DeliveryMonth_start

Source: ref_otw_transit_times (84 port-pair routes)

7. Landed cost linkage — the cost depends on port choice:

LandedCost[p,port,t] = FOB[p,s,t] + OF_rate[port,t] + Tariff[port] + Drayage[port] + Handling[port]

Source: ref_landed_cost_estimates has all of these per port per month

LP vs MIP — When Do We Need Integer Variables?

The core problem — how many tons to allocate where — is pure LP (all continuous variables, linear constraints, linear objective). LP problems solve in milliseconds.

You only need MIP (binary/integer variables) for:

Decision	LP or MIP?	Reason
How many tons to sell to each customer	LP	Continuous variable (metric tons)
How many tons to buy from each supplier	LP	Continuous variable
Inventory levels	LP	Continuous variable
Accept/reject a customer order	MIP	Binary decision (yes=1, no=0)
Which supplier to use (if minimum order quantities)	MIP	Binary selection
Volume discount tiers	MIP	Binary tier activation
Ship/don't ship in a window	MIP	Binary decision
Fixed costs (vessel booking fee)	MIP	Fixed charge formulation

Recommendation: Start pure LP. Add binary variables only when needed. HiGHS handles both.

---

Data Availability

Already Available

Parameter	Source	Table/Field
FOB prices (historical)	Postgres	supplier_purchase_orders.price_per_mton
Ocean freight rates	Postgres	ref_landed_cost_estimates.of_contract_rate, of_spot_rate
Customs/broker/drayage	Postgres	ref_landed_cost_estimates.customs_broker_fees, drayage_charges, handling_storage
Tariffs	Postgres	ref_landed_cost_estimates.custom_tariffs
Demurrage	Postgres	ref_landed_cost_estimates.demurrage
Transit times	Postgres	ref_otw_transit_times.transit_time (84 port-pair routes)
Selling prices	Airtable	Sales Orders.Sales Price, FOB/ST
Contract commitments	Airtable	Contract Line Items.Qty (ST), Contract Type, FOB/ST
Customer demand history	Airtable	Sales Orders by customer × product × month
Inventory on-hand	Airtable	Inventory Availability.Total Inventory For Month
Product catalog	Postgres	products (36 rows)
Supplier list	Postgres	suppliers (48 rows, with supplier_type, supplier_status, payment_terms)
Port costs by destination	Airtable	Contract Line Items.Port of Discharge — BAL/SAV=$3k, OAK/TAC=$2k/container
Shipment windows	Postgres	ref_shipment_windows (23,448 rows)
CRD penalties	Airtable	Contract Line Items.CRD Late Fees
Book/reference prices	Airtable	Pricing.Book Price, COGS/ST

Detailed Cost Structure from ref_landed_cost_estimates

The ERP has a complete per-port, per-month cost breakdown (1,564 rows):

Column	Description
of_contract_rate	Contracted ocean freight rate per FEU/container
of_spot_rate	Spot market ocean freight rate
customs_broker_fees	Clearance cost (~$175/container)
custom_tariffs	Tariff duty (varies by origin/commodity)
drayage_charges	Port-to-warehouse trucking
handling_storage	Warehouse/terminal fees
demurrage	Container overage fees
efi_port_id	FK → destination port (BAL, OAK, SAV, TAC)
month	Price snapshot date (time-series)

Contract Types and Pricing Tiers

From Airtable, the four contract types map to different pricing:

Type	Lead Time	Price Level	Risk	Optimizer Treatment
Forward	6+ weeks	Lowest FOB	Low (locked early)	Guaranteed margin, highest priority
Conversion	2-4 weeks	Medium	Medium	Fill gaps, moderate margin
Spot	Same month	Highest FOB	High (reactive)	Premium margin but uncertain volume
Side-by-Side	N/A	Adjustment	N/A	Tracked separately

Gaps to Fill

Parameter	Needed For	Source Option
Current FOB quotes	Today's buying cost	Manual input or MPOB API
Forward demand forecasts	Future month demand	Airtable Forecast table (stub) or historical pattern
Supplier capacity limits	Supply constraints	Infer from historical PO max volumes
Customer price elasticity	Price optimization	Start with fixed prices, add later
Currency rates (MYR/IDR→USD)	Accurate cost calc	External API (exchangerate-api.com)

The gaps are not blockers — we can start with historical averages and add live feeds incrementally.

---

Solver Choice: HiGHS (not CPLEX)

Why Not CPLEX?

• No Node.js bindings. IBM only supports Python, Java, C/C++. You'd need to shell out to a subprocess.
• ~$12K/year license. Designed for problems with millions of variables.
• EFI's problem (~2K variables) is far below the threshold where commercial solvers matter.

Recommended Stack

lp-model  →  programmatic model building API (like Python's PuLP)
    ↓
  HiGHS   →  production-grade LP/MIP solver (C++ compiled to WASM)

npm install lp-model highs

Why HiGHS?

• University of Edinburgh C++ solver compiled to WebAssembly
• Handles LP, MIP, and quadratic programming
• Same performance class as CPLEX/Gurobi for problems under 100K variables
• Solves EFI's problem (~2K variables) in <10ms
• MIT licensed, zero cost
• Works in Node.js with no native compilation

Code Example

import { Model } from 'lp-model';

const model = new Model();

// Decision: tons of MagnaPalm to sell to Farm A via Forward contract in March
const qty_palm_farmA_fwd_mar = model.addVar({
  lb: 0,
  ub: 200,  // demand max from Airtable
  name: "qty_MagnaPalm_FarmA_Forward_Mar2026"
});

// Margin coefficient = selling price - landed cost
// $1,200/ST selling - $980/ST landed = $220/ST margin
model.setObjective(
  [[220, qty_palm_farmA_fwd_mar], /* ... more terms ... */],
  "MAXIMIZE"
);

// Supply constraint
model.addConstr(
  [qty_palm_farmA_fwd_mar, /* other allocations */],
  "<=",
  500
);

// Solve
const highs = await require("highs")();
await model.solve(highs);
console.log(`Optimal margin: $${model.ObjVal}`);

Alternative Solvers (backup options)

Solver	npm Package	Speed	MIP?	Notes
HiGHS	highs	Fastest	Yes	Recommended
GLPK	glpk.js	Good	Yes	Solid backup, more verbose API
jsLPSolver	javascript-lp-solver	OK	Yes	Pure JS, zero deps, good for prototyping
YALPS	yalps	Good	Yes	TypeScript native, 2-3x faster than jsLPSolver

---

Yield Management Concepts

Contract Types as Fare Classes

EFI's contract types map directly to yield management fare classes:

Airlines	EFI Equivalent
Economy (early booking, cheap)	Forward contract (6-week lead, lowest price)
Business (flexible, mid-price)	Conversion (2-4 week lead, mid price)
First class / walk-up fare	Spot (same month, highest price)

The optimizer decides: how much supply to reserve for Spot vs. commit to Forward contracts?

Reserving inventory for Spot buyers captures premium margins, but risks unsold inventory. Committing too much to Forward loses upside. This is the classic revenue management tradeoff — and LP solves it exactly.

The model handles this through opportunity cost: every ton committed to a Forward contract at $1,100/ST means that ton can't be sold Spot at $1,200/ST. The optimizer balances the guaranteed Forward margin against the probabilistic Spot margin.

Time-Based Pricing

EFI's sourcing windows directly affect cost. Buying earlier (forward) vs. later (spot) creates different landed costs:

earlyBuy[p,t] at FOB_early[p,t]  (placed 3-4 months ahead, lower cost)
lateBuy[p,t]  at FOB_late[p,t]   (placed 0-1 months ahead, higher cost)

The model optimizes the mix of early vs. late procurement to minimize total cost while meeting all demand constraints.

Volume Discounts (MIP extension)

Tiered pricing requires binary variables for tier selection:

For customer c buying product p:
  Tier 1: 0-100 MT at $1,200/MT
  Tier 2: 101-300 MT at $1,150/MT
  Tier 3: 301+ MT at $1,100/MT

Variables:
  qty_tier1[p,c,t], qty_tier2[p,c,t], qty_tier3[p,c,t]   (continuous)
  y_tier1[p,c,t], y_tier2[p,c,t], y_tier3[p,c,t]          (binary: is this tier active?)

Constraints:
  qty_tier1 ≤ 100 × y_tier1
  y_tier2 ≤ y_tier1  (can only enter tier 2 if tier 1 is full)
  total_qty = qty_tier1 + qty_tier2 + qty_tier3

---

Implementation Approach

Proposed Package Structure

packages/pricing/                    # @efi/pricing
├── package.json
├── tsconfig.json
└── src/
    ├── models/
    │   ├── optimization-model.ts    # LP/MIP model definition
    │   └── parameters.ts            # Input parameter types
    ├── fetchers/
    │   ├── cost-parameters.ts       # PG: landed cost components
    │   ├── demand-parameters.ts     # AT: customer demand + contract commitments
    │   ├── supply-parameters.ts     # PG: supplier capacity, FOB prices
    │   └── inventory-parameters.ts  # AT: current inventory levels
    ├── services/
    │   ├── optimizer.ts             # Build + solve the LP model
    │   ├── scenario-runner.ts       # Run multiple what-if scenarios
    │   └── sensitivity.ts           # Shadow prices, reduced costs analysis
    └── web/
        ├── server.ts                # Express on port 3003
        └── views/
            ├── layout.ts
            ├── optimization-result.ts   # Recommended allocations table
            ├── margin-analysis.ts       # Margin by product/customer/month
            └── scenario-comparison.ts   # Side-by-side scenario results

Phased Rollout

Phase 1 — Deterministic LP (start here)

• Fixed selling prices (from existing contracts)
• Historical FOB costs as parameters
• Landed cost from ref_landed_cost_estimates
• Maximize margin given known demand + supply
• Output: "Here's the optimal allocation and the resulting margin"

Phase 2 — Scenario Analysis

• Run the same model under 3-5 scenarios (FOB +15%, demand -20%, freight spike)
• Compare margins across scenarios
• Output: "If palm prices rise 15%, margin drops $X — hedge by..."

const scenarios = [
  { name: 'base', fobMultiplier: 1.0, demandMultiplier: 1.0 },
  { name: 'high_cost', fobMultiplier: 1.15, demandMultiplier: 1.0 },
  { name: 'low_demand', fobMultiplier: 1.0, demandMultiplier: 0.8 },
  { name: 'freight_spike', freightAdder: 50, demandMultiplier: 1.0 },
];

Phase 3 — Price Optimization (MIP)

• Selling price becomes a decision variable (discretized tiers)
• Add binary variables for accept/reject orders, supplier selection
• Volume discount modeling
• Output: "Set MagnaPalm at $1,180/ST for Farm A, $1,150/ST for Farm B"

Phase 4 — Live Integration

• Real-time MPOB/currency feeds
• Weekly re-solve on schedule
• Alert when current pricing deviates from optimal by >5%

Re-Solve Frequency

Frequency	What Changes	Action
Daily	Spot commodity prices, freight rates, FX rates	Update landed cost parameters, re-run allocation
Weekly	Customer order pipeline, supplier availability	Re-run full model with updated demand/supply
Monthly	New sourcing window opens, contracts roll	Re-solve with new time period, update forward commitments
Quarterly	Contract renewals, strategic planning	Run scenario analysis (what-if on prices, demand)

For EFI's current scale, weekly re-solving is sufficient, with the ability to do ad-hoc runs when market conditions shift.

Handling Uncertainty (future)

Three approaches in order of complexity:

1. Scenario analysis (Phase 2) — define 3-5 scenarios, solve each independently, compare
2. Robust optimization — assume parameters lie within uncertainty sets (+/- 10%), optimize for worst case
3. Stochastic programming — model uncertainty as probability distributions, maximize expected margin

Per research on commodity hedging, combining operational hedging (inventory, timing) with data-driven optimization yields 3-5% cost savings on average. For EFI's volume, even 3% on procurement costs is material.

Recommendation: Start with deterministic LP + scenario analysis (Phases 1-2). This gives 80% of the value with 20% of the complexity.

---

References

• HiGHS solver — University of Edinburgh, MIT licensed
• lp-model — programmatic LP modeling for JS
• highs npm package — HiGHS compiled to WASM
• Roland Berger: AI-driven commodity price optimization
• ResearchGate: Commodity pricing and sales optimization using LP
• ScienceDirect: Data-driven approach for optimal commodity hedging