Logistics & supply chain: MQL recipes

Recipes

Economic order quantity (Wilson)
Safety stock and reorder point
Promised delivery date in business days
Shipping tariff from past deliveries
SSCC numbers of pallet labels
Great-circle distances and headings of a delivery round
ABC analysis of the spare parts
Cartons per pallet and trailers for an order
Handling flags of an order and the vehicles that can carry it
Newsvendor order of a fresh product

executed on MentDB

Economic order quantity (Wilson)

An item has an annual demand of 12000 units, an order cost of 85 and a unit cost of 14.50 with a 22% holding rate. The script computes the Wilson EOQ, the order frequency, and checks the result by minimising the total ordering plus holding cost.

Pipeline

01 · holdingHolding cost per unitmath calc→02 · eoqsqrt(2DS/H)math calc→03 · checkMinimum of the total costnum minimize→04 · readOptimum from JSONjson select

Script

admin

#1. Annual demand, order cost, unit cost 14.5 and holding rate 22%;
-> "[D]" 12000;
-> "[S]" 85;
-> "[H]" (math calc "0.22*14.5");
#2. Economic order quantity of Wilson;
-> "[q]" (math calc "sqrt(2*[D]*[S]/[H])");
#3. Check it: minimum of the ordering plus holding cost;
json load "rx_eoq" (num minimize (concat [D] "*" [S] "/x+x/2*" [H]) "x" 100 5000);
concat "EOQ " (math round [q] 0) " units, " (math round (/ [D] [q]) 1) " orders a year, one every " (math round (/ 250 (/ [D] [q])) 1) " working days. Minimum annual cost " (math round (json select "rx_eoq" "/value") 2) " at x = " (math round (json select "rx_eoq" "/x") 1);

Result

mentdb

EOQ 800 units, 15 orders a year, one every 16.7 working days. Minimum annual cost 2551 at x = 799.7

Go further

Quantity discounts: add the purchase cost D*price(x) to the expression and minimise on each price band
Cost curve for a chart: num table of the total cost from 200 to 2000

executed on MentDB

Safety stock and reorder point

Daily demand averages 40 units with a standard deviation of 12 and the supplier lead time is 6 days. For a 97.5% cycle service level the script finds z, the safety stock and the reorder point, then the stock-out risk if only 40 units are kept.

Pipeline

01 · zNormal quantilestat dist_inv→02 · stockz sigma sqrt(L)math calc→03 · riskStock-out probabilitystat dist_cdf

Script

admin

#1. Daily demand: mean 40, standard deviation 12, lead time 6 days;
-> "[d]" 40;
-> "[sd]" 12;
-> "[L]" 6;
#2. Service level 97.5%: z of the normal law;
-> "[z]" (stat dist_inv "normal" "[0, 1]" 0.975);
#3. Safety stock and reorder point;
-> "[ss]" (math ceil (math calc "[z]*[sd]*sqrt([L])"));
-> "[rop]" (+ (* [d] [L]) [ss]);
#4. Stock-out risk if we keep only 40 units of safety stock;
-> "[risk]" (- 1 (stat dist_cdf "normal" "[0, 1]" (math calc "40/([sd]*sqrt([L]))")));
concat "z = " (math round [z] 3) ", safety stock " [ss] " units, reorder point " [rop] " units. With 40 units only, stock-out risk per cycle " (math round (* [risk] 100) 1) "%.";

Result

mentdb

z = 1.96, safety stock 58 units, reorder point 298 units. With 40 units only, stock-out risk per cycle 8.7%.

Go further

Variable lead time: use sqrt(L*sd^2 + d^2*sdL^2) in math calc
Estimate the demand parameters from history with stat mean and stat stddev

executed on MentDB

Promised delivery date in business days

An order arrives on Wednesday 18 November 2026 after the 14:00 cut-off. Processing starts the next business day, picking takes 1 business day and transport 2. The script gives each date and checks the delivery window promised to the customer.

Pipeline

01 · cut-offNext business daydate add_business_days→02 · lead timeCalendar daysdate nb_day→03 · promiseWithin the window?date between

Script

admin

#1. Order received on Wednesday 2026-11-18 at 16:30, after the 14:00 cut-off;
-> "[order]" "2026-11-18";
-> "[start]" (date add_business_days [order] 1);
#2. Picking 1 business day, transport 2 business days;
-> "[ship]" (date add_business_days [start] 1);
-> "[deliv]" (date add_business_days [ship] 2);
#3. Is the promised window met?;
concat "{\"processing_start\": \"" [start] "\", \"shipped\": \"" [ship] "\", \"delivered\": \"" [deliv] "\", \"calendar_days\": " (date nb_day [order] [deliv]) ", \"in_window\": " (date between [deliv] "2026-11-23" "2026-11-25") "}";

Result

mentdb

{"processing_start": "2026-11-19", "shipped": "2026-11-20", "delivered": "2026-11-24", "calendar_days": 6, "in_window": 1}

Go further

Cut-off from the real time: compare date hour of the order timestamp with 14
Public holidays are not skipped: add one business day when date between finds a holiday on the way

executed on MentDB

Shipping tariff from past deliveries

Delivery costs are explained by a fixed fee, the weight and the distance. The script fits cost = fee + a weight + b distance by multiple regression, compares the fee with its standard error, quotes a new delivery, and draws the residuals against the distance to check the linear tariff.

Pipeline

01 · loadLoad the deliveriespa rm load_from_json→02 · fitFee and ratespa rm estimate_regression_parameters→03 · errorsStandard errorspa rm estimate_regression_parameters_standard_errors→04 · quoteQuote 180 kg, 260 kmpa rm predict→05 · drawResidual plotplot points

Script

admin

#1. Cost of 8 deliveries in EUR against weight in kg and distance in km;
-> "[X]" "[[120, 350], [80, 120], [200, 500], [150, 80], [60, 640], [300, 220], [90, 410], [250, 300]]";
-> "[Y]" "[314.2, 110.3, 452.8, 84.9, 549.0, 226.1, 356.7, 287.4]";
pa rm load_from_json "rx_ship" [X] [Y];
#2. Tariff cost = fee + a weight + b distance, with the standard errors: is the fixed fee significant;
-> "[b]" (pa rm estimate_regression_parameters "rx_ship");
-> "[se]" (pa rm estimate_regression_parameters_standard_errors "rx_ship");
#3. Quote for 180 kg over 260 km;
-> "[q]" (pa rm predict "rx_ship" "[180, 260]");
#4. Residual plot against the distance, to check the linear tariff;
-> "[res]" (pa rm estimate_residuals "rx_ship");
pa rm close "rx_ship";
plot points "[350, 120, 500, 80, 640, 220, 410, 300]" [res]

Result

mentdb

The chart is shown in the Plot tab of the editor.

Go further

Return [b], [se] and [q] with concat to read the tariff (fee -4.3 +- 2.0 EUR, 0.145 EUR/kg, 0.853 EUR/km, quote 243.57 EUR).
A pattern in the residuals calls for a distance band tariff.

executed on MentDB

SSCC numbers of pallet labels

The warehouse prints GS1-128 pallet labels. The script builds the next three SSCC codes (extension digit, GS1 company prefix, serial padded to 17 digits) with the GS1 modulo 10 check digit, and checks a code read by a scanner with the same algorithm on the GS1 reference example.

Pipeline

01 · padSerial on 9 digitsstring lpad→02 · reverseWeights from the rightstring reverse→03 · digitEach digitstring mid→04 · checkModulo 10math mod→05 · scan18 digitstype is_matches_regex

Script

admin

#1. GS1 company prefix of the warehouse and the last pallet serial used;
-> "[prefix]" "3012345";
-> "[serial]" 4817;
-> "[labels]" "";
for (-> "[k]" 1) (<= [k] 3) (++ "[k]") {
	#2. 17 digits: extension digit 0, prefix, serial padded with zeros to 9 digits;
	-> "[body]" (concat "0" [prefix] (string lpad (+ [serial] [k]) "0" 9));
	#3. GS1 mod 10: weights 3 and 1 from the right, check = 10 - sum mod 10;
	-> "[rev]" (string reverse [body]);
	-> "[sum]" 0;
	for (-> "[i]" 0) (< [i] (string length [rev])) (++ "[i]") {
		if (== (math mod [i] 2) 0) {-> "[w]" 3;} {-> "[w]" 1;};
		-> "[sum]" (+ [sum] (* [w] (string mid [rev] [i] (+ [i] 1))));
	};
	-> "[sscc]" (concat [body] (math mod (- 10 (math mod [sum] 10)) 10));
	-> "[labels]" (concat [labels] "(00)" [sscc] " ");
};
#4. Same check on the GS1 reference example 106141411234567897, read by a scanner;
-> "[scan]" "106141411234567897";
-> "[rev]" (string reverse (string left [scan] 17));
-> "[sum]" 0;
for (-> "[i]" 0) (< [i] 17) (++ "[i]") {
	if (== (math mod [i] 2) 0) {-> "[w]" 3;} {-> "[w]" 1;};
	-> "[sum]" (+ [sum] (* [w] (string mid [rev] [i] (+ [i] 1))));
};
concat "labels: " (string trim [labels]) " | scan valid: " (and (type is_matches_regex [scan] "[0-9]{18}") (== (math mod (- 10 (math mod [sum] 10)) 10) (string right [scan] 1)))

Result

mentdb

labels: (00)030123450000048187 (00)030123450000048194 (00)030123450000048200 | scan valid: 1

Go further

The same check digit protects GTIN-13 barcodes and GLN location codes
Store the last serial in a sequence so two operators never print the same SSCC

executed on MentDB

Great-circle distances and headings of a delivery round

The transport planner estimates a delivery round on Reunion island from the GPS coordinates of the stops. For each leg the script computes the haversine distance with atan2, the initial heading in degrees, the straight-line total and a road estimate with a detour factor of 1.35.

Pipeline

01 · radiansDegrees to radiansmath deg_to_rad→02 · haversinesin and cos termsmath sin→03 · distance2R atan2math atan2→04 · headingBearing in degreesmath rad_to_deg

Script

admin

#1. Delivery round from the Le Port warehouse: latitude and longitude in degrees of each stop;
json load "rx_st" "[{\"n\": \"Le Port\", \"lat\": -20.9370, \"lon\": 55.2930}, {\"n\": \"Saint-Denis\", \"lat\": -20.8823, \"lon\": 55.4504}, {\"n\": \"Saint-Benoit\", \"lat\": -21.0339, \"lon\": 55.7128}, {\"n\": \"Saint-Pierre\", \"lat\": -21.3393, \"lon\": 55.4781}]";
-> "[R]" 6371; -> "[tot]" 0; -> "[legs]" "";
for (-> "[i]" 1) (< [i] (json count "rx_st" "/")) (++ "[i]") {
	-> "[p1]" (math deg_to_rad (json select "rx_st" (concat "/[" (- [i] 1) "]/lat")));
	-> "[p2]" (math deg_to_rad (json select "rx_st" (concat "/[" [i] "]/lat")));
	-> "[dl]" (math deg_to_rad (- (json select "rx_st" (concat "/[" [i] "]/lon")) (json select "rx_st" (concat "/[" (- [i] 1) "]/lon"))));
	#2. Haversine: a = sin2 of dphi/2 + cos phi1 cos phi2 sin2 of dlambda/2, d = 2R atan2 of sqrt a, sqrt 1-a;
	-> "[a]" (+ (math power (math sin (/ (- [p2] [p1]) 2)) 2) (* (* (math cos [p1]) (math cos [p2])) (math power (math sin (/ [dl] 2)) 2)));
	-> "[d]" (* (* 2 [R]) (math atan2 (math sqrt [a]) (math sqrt (- 1 [a]))));
	#3. Initial bearing atan2 of sin dl cos p2, cos p1 sin p2 - sin p1 cos p2 cos dl, in degrees 0-360;
	-> "[brg]" (math rad_to_deg (math atan2 (* (math sin [dl]) (math cos [p2])) (- (* (math cos [p1]) (math sin [p2])) (* (* (math sin [p1]) (math cos [p2])) (math cos [dl])))));
	if (< [brg] 0) {-> "[brg]" (+ [brg] 360);};
	-> "[tot]" (+ [tot] [d]);
	-> "[legs]" (concat [legs] (json select "rx_st" (concat "/[" (- [i] 1) "]/n")) " > " (json select "rx_st" (concat "/[" [i] "]/n")) " " (math round [d] 2) " km heading " (math round [brg] 0) "; ");
};
json unload "rx_st";
concat [legs] "straight-line total " (math round [tot] 2) " km, road estimate x1.35 = " (math round (* [tot] 1.35) 1) " km"

Result

mentdb

Le Port > Saint-Denis 17.44 km heading 70; Saint-Denis > Saint-Benoit 32.04 km heading 122; Saint-Benoit > Saint-Pierre 41.78 km heading 216; straight-line total 91.26 km, road estimate x1.35 = 123.2 km

Go further

The detour factor of an island road network is high: calibrate it on a few real trips
Order the stops by the nearest neighbour first, then compare the totals

executed on MentDB

ABC analysis of the spare parts

The stock manager classifies 10 SKUs by annual consumption value: A for the items making 80 pct of the value, B up to 95 pct, C for the rest. The script sorts the values, walks them from the largest, computes the cumulative share and assigns the class.

Pipeline

01 · valuesValues of the SKUsjson parse_obj→02 · totalTotal valuestat sum→03 · sortSorted valuesmath order_double→04 · classCumulative shareif

Script

admin

#1. Annual consumption value in EUR of the 10 SKUs of a spare parts store;
json load "rx_sku" "{\"BRG-6204\": 48200, \"FLT-0110\": 3150, \"BLT-A42\": 21800, \"SEL-3550\": 1240, \"PMP-M12\": 96500, \"VLV-DN50\": 12600, \"GSK-150\": 860, \"MTR-4K\": 7400, \"CPL-28\": 2300, \"SNS-PT100\": 5950}";
json load "rx_v" "[]";
json parse_obj "rx_sku" "/" "[k]" "[v]" {json iarray "rx_v" "/" [v] NUM;};
-> "[total]" (stat sum (json doc "rx_v"));
#2. Values sorted ascending, read from the end to rank the SKUs from the largest;
json load "rx_s" (math order_double (json doc "rx_v"));
-> "[cum]" 0; -> "[out]" "";
for (-> "[i]" (- (json count "rx_s" "/") 1)) (>= [i] 0) (-- "[i]") {
	-> "[v]" (json select "rx_s" (concat "/[" [i] "]"));
	-> "[cum]" (+ [cum] [v]);
	-> "[share]" (* (/ [cum] [total]) 100);
	#3. Class A up to 80 pct of the value, B up to 95 pct, C beyond;
	-> "[cls]" (if (<= [share] 80) {"A";} {if (<= [share] 95) {"B";} {"C";}});
	json parse_obj "rx_sku" "/" "[k]" "[x]" {if (== [x] [v]) {-> "[out]" (concat [out] [k] " " [cls] " " (math round [share] 1) "; ");};};
};
json unload "rx_sku"; json unload "rx_v"; json unload "rx_s";
concat [out] "total " [total] " EUR"

Result

mentdb

PMP-M12 A 48.3; BRG-6204 A 72.4; BLT-A42 B 83.3; VLV-DN50 B 89.6; MTR-4K B 93.3; SNS-PT100 C 96.2; FLT-0110 C 97.8; CPL-28 C 99; SEL-3550 C 99.6; GSK-150 C 100; total 200000 EUR

Go further

Count the A items weekly and the C items once a year: cycle counting follows the ABC classes
Cross it with an XYZ analysis on the demand variability with stat stddev / stat mean

executed on MentDB

Cartons per pallet and trailers for an order

The shipping office plans an order of 2500 cartons of 40 x 30 x 25 cm and 9.5 kg on Euro pallets. The script tests both carton orientations on the layer, computes the layers within the height, the weight limit, the volume fill, the gross weight and the pallets and trailers needed.

Pipeline

01 · layerCartons per layermath trunc→02 · bestBest orientationmath max→03 · limitHeight and weightmath min→04 · palletsPallets and trailersmath ceil

Script

admin

#1. Carton 40 x 30 x 25 cm, 9.5 kg, on a Euro pallet 120 x 80 cm, 15 cm high, max load height 180 cm and 1000 kg;
-> "[L]" 120; -> "[W]" 80; -> "[cl]" 40; -> "[cw]" 30; -> "[ch]" 25; -> "[kg]" 9.5;
#2. Cartons per layer in both orientations, the best one is kept;
-> "[o1]" (* (math trunc (/ [L] [cl])) (math trunc (/ [W] [cw])));
-> "[o2]" (* (math trunc (/ [L] [cw])) (math trunc (/ [W] [cl])));
-> "[layer]" (math max [o1] [o2]);
#3. Layers within the height, then limited by the weight;
-> "[layers]" (math trunc (/ (- 180 15) [ch]));
-> "[n]" (math min (* [layer] [layers]) (math trunc (/ 1000 [kg])));
-> "[fill]" (/ (* [n] (* (* [cl] [cw]) [ch])) (* (* [L] [W]) (- 180 15)));
#4. A 13.6 m trailer takes 33 Euro pallets, order of 2500 cartons;
-> "[pal]" (math ceil (/ 2500 [n]));
concat "per layer " [o1] " or " [o2] " -> " [layer] ", layers " [layers] ", cartons per pallet " [n] ", volume fill " (math round (* [fill] 100) 1) " pct, gross " (+ (* [n] [kg]) 25) " kg | 2500 cartons = " [pal] " pallets = " (math ceil (/ [pal] 33)) " trailer(s)"

Result

mentdb

per layer 6 or 8 -> 8, layers 6, cartons per pallet 48, volume fill 90.9 pct, gross 481 kg | 2500 cartons = 53 pallets = 2 trailer(s)

Go further

A mixed layer (some cartons turned) can fit 9 or 10 cartons: dedicated pallet software tests these patterns
Check the axle loads, not only the total weight, when the trailer is full

executed on MentDB

Handling flags of an order and the vehicles that can carry it

The warehouse system stores handling constraints as a bit mask (fragile, chilled, frozen, hazmat ADR, oversized, high value). The script builds the mask of an order with OR, keeps the flags each vehicle covers with AND, shows the missing flags with XOR and prints the masks in binary.

Pipeline

01 · maskOrder flagsmath bit_or→02 · coverCovered flagsmath bit_and→03 · missingMissing flagsmath bit_xor→04 · binaryBinary viewstring bin

Script

admin

#1. Handling flags of the WMS: 1 fragile, 2 chilled, 4 frozen, 8 hazmat ADR, 16 oversized, 32 high value;
-> "[order]" (math bit_or (math bit_or 1 2) 32);
#2. Capabilities of the vehicles as the same bit mask;
json load "rx_veh" "{\"VAN-12\": 1, \"REEFER-07\": 3, \"TRUCK-ADR\": 25, \"SECURE-02\": 35}";
-> "[out]" "";
json parse_obj "rx_veh" "/" "[v]" "[cap]" {
	#3. Covered flags = order AND capability, missing flags = order XOR covered;
	-> "[missing]" (math bit_xor [order] (math bit_and [order] [cap]));
	-> "[out]" (concat [out] [v] " caps " (string lpad (string bin [cap]) "0" 6) " missing " (string lpad (string bin [missing]) "0" 6) (if (== [missing] 0) {" OK";} {"";}) "; ");
};
json unload "rx_veh";
concat "order flags " [order] " = " (string lpad (string bin [order]) "0" 6) " | " [out]

Result

mentdb

order flags 35 = 100011 | TRUCK-ADR caps 011001 missing 100010; VAN-12 caps 000001 missing 100010; REEFER-07 caps 000011 missing 100000; SECURE-02 caps 100011 missing 000000 OK;

Go further

One integer column stores all the flags of a product: queries test them with a bitwise AND
Add a flag with OR, remove it with AND of the inverted mask

executed on MentDB

Newsvendor order of a fresh product

A shop orders fresh salads every morning: bought 1.10 EUR, sold 2.90 EUR, unsold units are lost, demand is normal with mean 400 and standard deviation 80. The newsvendor model orders up to the critical ratio quantile; the script computes it with the inverse error function and gives the service level reached.

Pipeline

01 · ratioCu / (Cu + Co)math round→02 · quantilez from erf_invmath erf_inv→03 · orderOrder quantitymath ceil→04 · serviceP(no stockout)math erfc

Script

admin

#1. Fresh salads bought 1.10 EUR, sold 2.90 EUR, unsold units given away: daily demand normal, mean 400, sd 80;
-> "[cost]" 1.10; -> "[price]" 2.90; -> "[salv]" 0; -> "[mu]" 400; -> "[sd]" 80;
#2. Newsvendor critical ratio Cu / Cu + Co, underage = lost margin, overage = cost - salvage;
-> "[cu]" (- [price] [cost]); -> "[co]" (- [cost] [salv]);
-> "[cr]" (/ [cu] (+ [cu] [co]));
#3. Normal quantile z = sqrt 2 erf_inv of 2 CR - 1, order quantity and its service level;
-> "[z]" (* (math sqrt 2) (math erf_inv (- (* 2 [cr]) 1)));
-> "[q]" (math ceil (+ [mu] (* [z] [sd])));
-> "[sl]" (* 0.5 (math erfc (/ (* -1 (/ (- [q] [mu]) [sd])) (math sqrt 2))));
concat "critical ratio " (math round [cr] 4) ", z " (math round [z] 4) ", order " [q] " salads, P no stockout " (math round (* [sl] 100) 2) " pct, safety stock " (- [q] [mu]) " units"

Result

mentdb

critical ratio 0.6207, z 0.3073, order 425 salads, P no stockout 62.27 pct, safety stock 25 units

Go further

If the unsold salads can be sold at 0.50 EUR, the overage cost falls and the order rises
For a discrete demand use the Poisson law with stat dist_inv