Shelf life on real demand: FIFO, expiry and forecast targets on M5 data¶

ShelfLife tracks stock by delivery date, sells the oldest first (FIFO) and removes units that reach their shelf life. Notebook 05e introduces it on a small synthetic example. This notebook applies it to real demand and asks how the target and the shelf life interact.

The notebook shows:

• 20 daily demand series from the M5 retail data, with targets from an external forecaster (Smooth's ETS model) and from a simple history rule;
• opening stock given as dated deliveries, and expiry recorded in the event table as expired_units;
• nine runs: three target sources crossed with shelf lives of 3, 5 and 8 days;
• a custom metric, the waste rate, next to built-in metrics and costs, including purchase_cost and waste_cost.

M5 supplies only item identifiers, dates and daily unit sales, which we use as demand. Lead time, review period, shelf lives, opening stock and costs are assumptions made for this example.

In [1]:

import numpy as np
import pandas as pd

# Show every number with two decimals.
pd.options.display.float_format = "{:.2f}".format

import numpy as np import pandas as pd # Show every number with two decimals. pd.options.display.float_format = "{:.2f}".format

1. Demand: 20 M5 items¶

The prepared sample (data/m5/sample_1.csv, source and terms in data/m5/README.md) has one row per item and day, with demand in y. The forecast origin is 28 February 2016: the 180 days up to it are history, and the 56 days after it are the demand the simulation replays. We keep the 20 items that sold on the most history days, ranked on history only, so the choice does not depend on the future.

In [2]:

data = pd.read_csv("data/m5/sample_1.csv", parse_dates=["date"])

origin = pd.Timestamp("2016-02-28")
history_days, n_days = 180, 56
history_start = origin - pd.Timedelta(days=history_days - 1)
future_end = origin + pd.Timedelta(days=n_days)

history_pool = data[data["date"].between(history_start, origin)]
selling_days = (
    history_pool.assign(sold=history_pool["y"].gt(0)).groupby("unique_id")["sold"].sum()
)
items = selling_days.sort_values(ascending=False, kind="stable").index[:20].tolist()

history = history_pool[history_pool["unique_id"].isin(items)]
demand = data[
    data["unique_id"].isin(items)
    & data["date"].between(origin, future_end, inclusive="right")
].sort_values(["unique_id", "date"])
demand = demand.assign(
    period=demand.groupby("unique_id").cumcount(),
    y=demand["y"].astype(float),
)[["unique_id", "date", "period", "y"]]
demand[demand["unique_id"].eq(items[0])].head()

data = pd.read_csv("data/m5/sample_1.csv", parse_dates=["date"]) origin = pd.Timestamp("2016-02-28") history_days, n_days = 180, 56 history_start = origin - pd.Timedelta(days=history_days - 1) future_end = origin + pd.Timedelta(days=n_days) history_pool = data[data["date"].between(history_start, origin)] selling_days = ( history_pool.assign(sold=history_pool["y"].gt(0)).groupby("unique_id")["sold"].sum() ) items = selling_days.sort_values(ascending=False, kind="stable").index[:20].tolist() history = history_pool[history_pool["unique_id"].isin(items)] demand = data[ data["unique_id"].isin(items) & data["date"].between(origin, future_end, inclusive="right") ].sort_values(["unique_id", "date"]) demand = demand.assign( period=demand.groupby("unique_id").cumcount(), y=demand["y"].astype(float), )[["unique_id", "date", "period", "y"]] demand[demand["unique_id"].eq(items[0])].head()

Out[2]:

	unique_id	date	period	y
160636	FOODS_1_085_CA_1	2016-02-29	0	9.00
160637	FOODS_1_085_CA_1	2016-03-01	1	5.00
160638	FOODS_1_085_CA_1	2016-03-02	2	4.00
160639	FOODS_1_085_CA_1	2016-03-03	3	19.00
160640	FOODS_1_085_CA_1	2016-03-04	4	10.00

In [3]:

import matplotlib.dates as mdates
import matplotlib.pyplot as plt

DEMAND_COLOR = "#4C78A8"

# Four items across the selling-days ranking: 1st, 7th, 13th and 20th.
example_items = [items[i] for i in [0, 6, 12, 19]]
recent = data[
    data["unique_id"].isin(example_items)
    & data["date"].between(origin - pd.Timedelta(days=41), future_end)
]
fig, axes = plt.subplots(2, 2, figsize=(11, 5.5), sharex=True, layout="constrained")
for ax, item in zip(axes.flat, example_items):
    rows = recent[recent["unique_id"].eq(item)]
    ax.plot(
        rows["date"],
        rows["y"],
        marker="o",
        markersize=2,
        linewidth=1,
        color=DEMAND_COLOR,
    )
    ax.axvline(origin, color="black", linestyle="--", linewidth=1)
    ax.set(title=item, ylabel="Units")
    ax.set_ylim(bottom=0)
    ax.grid(axis="y", alpha=0.2)
    ax.spines[["top", "right"]].set_visible(False)
    ax.xaxis.set_major_formatter(mdates.DateFormatter("%d %b"))
fig.suptitle("Daily demand of four items; the dashed line is the forecast origin")
plt.show()

import matplotlib.dates as mdates import matplotlib.pyplot as plt DEMAND_COLOR = "#4C78A8" # Four items across the selling-days ranking: 1st, 7th, 13th and 20th. example_items = [items[i] for i in [0, 6, 12, 19]] recent = data[ data["unique_id"].isin(example_items) & data["date"].between(origin - pd.Timedelta(days=41), future_end) ] fig, axes = plt.subplots(2, 2, figsize=(11, 5.5), sharex=True, layout="constrained") for ax, item in zip(axes.flat, example_items): rows = recent[recent["unique_id"].eq(item)] ax.plot( rows["date"], rows["y"], marker="o", markersize=2, linewidth=1, color=DEMAND_COLOR, ) ax.axvline(origin, color="black", linestyle="--", linewidth=1) ax.set(title=item, ylabel="Units") ax.set_ylim(bottom=0) ax.grid(axis="y", alpha=0.2) ax.spines[["top", "right"]].set_visible(False) ax.xaxis.set_major_formatter(mdates.DateFormatter("%d %b")) fig.suptitle("Daily demand of four items; the dashed line is the forecast origin") plt.show()

2. Targets: an ETS forecast and a history rule¶

Every run reviews stock every R = 2 days with a lead time of L = 2 days, so an order protects L + R = 4 days of demand. The order-up-to level S is a target for that 4-day total. We use three sources:

• ETS 90% and ETS 95%: Smooth's ES("ANN") fitted on each item's history, and the 90% or 95% quantile of 4-day total demand (cumulative=True), rounded up to whole units;
• History rate: four times the item's mean daily demand over the last 28 days of history, rounded up. It has no probability, so its policy states service_level=None.

The targets are computed once, at the origin, and stay fixed for the 56 days.

In [4]:

import math

from smooth import ES

lead_time, review_period = 2, 2
horizon = lead_time + review_period
target_end = origin + pd.Timedelta(days=horizon)

target_rows = []
for item in items:
    y = history.loc[history["unique_id"].eq(item), "y"].astype(float)
    model = ES("ANN")
    model.fit(y.reset_index(drop=True))
    for probability in [0.90, 0.95]:
        forecast = model.predict(
            h=horizon,
            interval="prediction",
            level=probability,
            side="upper",
            cumulative=True,
        )
        target_rows.append(
            {
                "unique_id": item,
                "source": f"ETS {probability:.0%}",
                "target": float(math.ceil(forecast.upper.iloc[0, 0])),
            }
        )
    recent_mean = y.iloc[-28:].mean()
    target_rows.append(
        {
            "unique_id": item,
            "source": "History rate",
            "target": float(math.ceil(horizon * recent_mean)),
        }
    )
targets = pd.DataFrame(target_rows).assign(end=target_end)
targets.pivot(index="unique_id", columns="source", values="target").loc[
    example_items, ["History rate", "ETS 90%", "ETS 95%"]
]

import math from smooth import ES lead_time, review_period = 2, 2 horizon = lead_time + review_period target_end = origin + pd.Timedelta(days=horizon) target_rows = [] for item in items: y = history.loc[history["unique_id"].eq(item), "y"].astype(float) model = ES("ANN") model.fit(y.reset_index(drop=True)) for probability in [0.90, 0.95]: forecast = model.predict( h=horizon, interval="prediction", level=probability, side="upper", cumulative=True, ) target_rows.append( { "unique_id": item, "source": f"ETS {probability:.0%}", "target": float(math.ceil(forecast.upper.iloc[0, 0])), } ) recent_mean = y.iloc[-28:].mean() target_rows.append( { "unique_id": item, "source": "History rate", "target": float(math.ceil(horizon * recent_mean)), } ) targets = pd.DataFrame(target_rows).assign(end=target_end) targets.pivot(index="unique_id", columns="source", values="target").loc[ example_items, ["History rate", "ETS 90%", "ETS 95%"] ]

Out[4]:

source	History rate	ETS 90%	ETS 95%
unique_id
FOODS_1_085_CA_1	56.00	69.00	74.00
FOODS_1_087_CA_1	19.00	24.00	27.00
FOODS_1_042_CA_1	15.00	25.00	28.00
FOODS_1_041_CA_1	5.00	10.00	11.00

The sources differ most where it matters for expiry. For the fastest item, FOODS_1_085_CA_1, the 4-day target is 56 units from the history rule, 69 at ETS 90% and 74 at ETS 95%. The history rule is a 4-day mean, so it holds no safety stock; the ETS quantiles add a margin for uncertainty, and the 95% margin is the larger one.

In [5]:

from stockcast.policies import OrderUpToPolicy

# The probability each target states; the history rule states none.
service_levels = {"ETS 90%": 0.90, "ETS 95%": 0.95, "History rate": None}
sources = list(service_levels)
policies = {}
for source in sources:
    source_targets = targets[targets["source"].eq(source)]
    policy = OrderUpToPolicy(
        lead_time=lead_time,
        review_period=review_period,
        freq="D",
        service_level=service_levels[source],
        allow_backorders=False,
        date_column="end",
    )
    policy.fit(
        source_targets,
        target_column="target",
        forecast_origin=origin,
    )
    policies[source] = policy

from stockcast.policies import OrderUpToPolicy # The probability each target states; the history rule states none. service_levels = {"ETS 90%": 0.90, "ETS 95%": 0.95, "History rate": None} sources = list(service_levels) policies = {} for source in sources: source_targets = targets[targets["source"].eq(source)] policy = OrderUpToPolicy( lead_time=lead_time, review_period=review_period, freq="D", service_level=service_levels[source], allow_backorders=False, date_column="end", ) policy.fit( source_targets, target_column="target", forecast_origin=origin, ) policies[source] = policy

3. Opening stock as dated deliveries¶

Expiry depends on age, so the opening stock needs dates. Each item opens with two days of its recent mean demand (at least 3 units), received in three deliveries: 20% two days before the origin, 30% one day before, and the rest on the origin date. ShelfLife takes them with its opening_lots argument (Stockcast's API calls a dated delivery a lot) and checks that each item's deliveries add up to its stock on hand.

In [6]:

from stockcast.core import InventoryStateDataFrame

recent_mean = (
    history[history["date"] > origin - pd.Timedelta(days=28)]
    .groupby("unique_id")["y"]
    .mean()
)
opening_units = np.maximum(3, np.ceil(lead_time * recent_mean)).reindex(items)

opening = InventoryStateDataFrame.from_observed(
    pd.DataFrame({"unique_id": items, "on_hand": opening_units.to_numpy()}),
    opening_date=origin,
)

delivery_rows = []
for item, units in opening_units.items():
    oldest = max(1.0, math.floor(0.2 * units))
    middle = max(1.0, math.floor(0.3 * units))
    for days_old, quantity in [(2, oldest), (1, middle), (0, units - oldest - middle)]:
        delivery_rows.append(
            {
                "unique_id": item,
                "received_date": origin - pd.Timedelta(days=days_old),
                "quantity": quantity,
            }
        )
opening_deliveries = pd.DataFrame(delivery_rows)
opening_deliveries[opening_deliveries["unique_id"].eq(items[0])]

from stockcast.core import InventoryStateDataFrame recent_mean = ( history[history["date"] > origin - pd.Timedelta(days=28)] .groupby("unique_id")["y"] .mean() ) opening_units = np.maximum(3, np.ceil(lead_time * recent_mean)).reindex(items) opening = InventoryStateDataFrame.from_observed( pd.DataFrame({"unique_id": items, "on_hand": opening_units.to_numpy()}), opening_date=origin, ) delivery_rows = [] for item, units in opening_units.items(): oldest = max(1.0, math.floor(0.2 * units)) middle = max(1.0, math.floor(0.3 * units)) for days_old, quantity in [(2, oldest), (1, middle), (0, units - oldest - middle)]: delivery_rows.append( { "unique_id": item, "received_date": origin - pd.Timedelta(days=days_old), "quantity": quantity, } ) opening_deliveries = pd.DataFrame(delivery_rows) opening_deliveries[opening_deliveries["unique_id"].eq(items[0])]

Out[6]:

	unique_id	received_date	quantity
0	FOODS_1_085_CA_1	2016-02-26	5.00
1	FOODS_1_085_CA_1	2016-02-27	8.00
2	FOODS_1_085_CA_1	2016-02-28	15.00

4. Nine runs: three targets × three shelf lives¶

A shelf life of d days means a delivery received on day D can be sold up to day D + d − 1 and expires at the start of day D + d, before that day's receipts, order and demand. Each run gets a fresh ShelfLife with its own shelf life; the policy, demand and opening stock are shared, and the engine copies the opening state for every run.

In [7]:

from stockcast.core import ShelfLife, SimulationEngine

shelf_lives = [3, 5, 8]
results = {}
for shelf_life_days in shelf_lives:
    for source in sources:
        results[(shelf_life_days, source)] = SimulationEngine().run(
            policy=policies[source],
            demand_source=demand,
            inventory=opening,
            processes=[
                ShelfLife(
                    shelf_life_days=shelf_life_days, opening_lots=opening_deliveries
                )
            ],
        )

from stockcast.core import ShelfLife, SimulationEngine shelf_lives = [3, 5, 8] results = {} for shelf_life_days in shelf_lives: for source in sources: results[(shelf_life_days, source)] = SimulationEngine().run( policy=policies[source], demand_source=demand, inventory=opening, processes=[ ShelfLife( shelf_life_days=shelf_life_days, opening_lots=opening_deliveries ) ], )

The event table records expiry in its own column. For the first item, under ETS 95% with a 3-day shelf life, the first days look like this:

In [8]:

first_events = results[(3, "ETS 95%")].to_event_frame()
first_events.loc[
    first_events["unique_id"].eq(items[0]),
    [
        "date",
        "starting_on_hand",
        "expired_units",
        "received_units",
        "demand",
        "fulfilled_units",
        "order_quantity",
        "ending_on_hand",
    ],
].head(6)

first_events = results[(3, "ETS 95%")].to_event_frame() first_events.loc[ first_events["unique_id"].eq(items[0]), [ "date", "starting_on_hand", "expired_units", "received_units", "demand", "fulfilled_units", "order_quantity", "ending_on_hand", ], ].head(6)

Out[8]:

	date	starting_on_hand	expired_units	received_units	demand	fulfilled_units	order_quantity	ending_on_hand
0	2016-02-29	28.00	5.00	0.00	9.00	9.00	51.00	14.00
20	2016-03-01	14.00	0.00	0.00	5.00	5.00	0.00	9.00
40	2016-03-02	9.00	9.00	51.00	4.00	4.00	23.00	47.00
60	2016-03-03	47.00	0.00	0.00	19.00	19.00	0.00	28.00
80	2016-03-04	28.00	0.00	23.00	10.00	10.00	23.00	41.00
100	2016-03-05	41.00	18.00	0.00	21.00	21.00	0.00	2.00

Reading the rows. On 29 February the delivery received on 26 February is three days old, and its 5 units expire before demand. The policy then orders 51 units, which arrive on 2 March and can be sold on 2, 3 and 4 March. Demand on those days takes 33 of them (4 + 19 + 10), so the remaining 18 expire on 5 March.

This is the core interaction. The order was sized to protect L + R = 4 days of demand at 95%, but each delivery can be sold for only 3 days. Whatever part of the target is not sold within the shelf life expires, however good the forecast is.

5. Scoring the runs, with a custom metric¶

InventoryEvaluator turns each event table into metrics. Built-in metrics cover service, stock and the cost components; a metric that is not built in is any function of the event table. The waste rate below is the share of the stock leaving the shelf that expired instead of being sold: expired / (expired + fulfilled).

The costs are assumptions: 1.00 per unit purchased, 0.25 per unit expired, 2.00 per unit of lost sales, 0.02 per unit held per day and 0.50 per order line.

In [9]:

from stockcast.evaluation import (
    InventoryEvaluator,
    avg_on_hand,
    fill_rate,
    holding_cost,
    ordering_cost,
    purchase_cost,
    shortage_cost,
    total_cost,
    waste_cost,
)


def waste_rate(events, context=None):
    """Share of the stock leaving the shelf that expired instead of being sold."""
    expired = events["expired_units"].sum()
    fulfilled = events["fulfilled_units"].sum()
    return float(expired / (expired + fulfilled))


costs = {
    "cost_components": ["purchase", "waste", "shortage", "holding", "ordering"],
    "purchase_cost_per_unit": 1.00,
    "waste_cost_per_unit": 0.25,
    "shortage_cost_per_unit": 2.00,
    "holding_cost_per_unit_period": 0.02,
    "order_cost_per_sku_line": 0.50,
    "order_cost_per_unit": 0.0,
}
score_rows = []
for (shelf_life_days, source), result in results.items():
    evaluator = InventoryEvaluator()
    evaluator.fit(result)
    scores = evaluator.evaluate(
        [
            fill_rate,
            waste_rate,
            avg_on_hand,
            purchase_cost,
            waste_cost,
            shortage_cost,
            holding_cost,
            ordering_cost,
            total_cost,
        ],
        groupby=[],
        context=costs,
    )
    score_rows.append(scores.assign(shelf_life=shelf_life_days, source=source))
scores = pd.concat(score_rows, ignore_index=True)

from stockcast.evaluation import ( InventoryEvaluator, avg_on_hand, fill_rate, holding_cost, ordering_cost, purchase_cost, shortage_cost, total_cost, waste_cost, ) def waste_rate(events, context=None): """Share of the stock leaving the shelf that expired instead of being sold.""" expired = events["expired_units"].sum() fulfilled = events["fulfilled_units"].sum() return float(expired / (expired + fulfilled)) costs = { "cost_components": ["purchase", "waste", "shortage", "holding", "ordering"], "purchase_cost_per_unit": 1.00, "waste_cost_per_unit": 0.25, "shortage_cost_per_unit": 2.00, "holding_cost_per_unit_period": 0.02, "order_cost_per_sku_line": 0.50, "order_cost_per_unit": 0.0, } score_rows = [] for (shelf_life_days, source), result in results.items(): evaluator = InventoryEvaluator() evaluator.fit(result) scores = evaluator.evaluate( [ fill_rate, waste_rate, avg_on_hand, purchase_cost, waste_cost, shortage_cost, holding_cost, ordering_cost, total_cost, ], groupby=[], context=costs, ) score_rows.append(scores.assign(shelf_life=shelf_life_days, source=source)) scores = pd.concat(score_rows, ignore_index=True)

In [10]:

SHELF_COLORS = {3: "#E45756", 5: "#F2CF5B", 8: "#54A24B"}

fig, axes = plt.subplots(1, 3, figsize=(13, 3.6), layout="constrained")
x = np.arange(len(sources))
width = 0.26
for ax, column, title in [
    (axes[0], "fill_rate", "Fill rate"),
    (axes[1], "waste_rate", "Waste rate: expired / (expired + sold)"),
    (axes[2], "avg_on_hand", "Average stock per item and day"),
]:
    for offset, shelf_life_days in enumerate(shelf_lives):
        values = (
            scores[scores["shelf_life"].eq(shelf_life_days)]
            .set_index("source")
            .loc[sources, column]
        )
        bars = ax.bar(
            x + (offset - 1) * width,
            values,
            width,
            color=SHELF_COLORS[shelf_life_days],
            label=f"{shelf_life_days}-day shelf life",
        )
        ax.bar_label(bars, fmt="%.2f", fontsize=7, padding=2)
    ax.set(title=title)
    ax.set_xticks(x, sources)
    ax.set_ylim(0, ax.get_ylim()[1] * 1.08)
    ax.grid(axis="y", alpha=0.2)
    ax.spines[["top", "right"]].set_visible(False)
fig.legend(
    *axes[0].get_legend_handles_labels(),
    frameon=False,
    ncol=3,
    loc="outside upper center",
)
plt.show()

SHELF_COLORS = {3: "#E45756", 5: "#F2CF5B", 8: "#54A24B"} fig, axes = plt.subplots(1, 3, figsize=(13, 3.6), layout="constrained") x = np.arange(len(sources)) width = 0.26 for ax, column, title in [ (axes[0], "fill_rate", "Fill rate"), (axes[1], "waste_rate", "Waste rate: expired / (expired + sold)"), (axes[2], "avg_on_hand", "Average stock per item and day"), ]: for offset, shelf_life_days in enumerate(shelf_lives): values = ( scores[scores["shelf_life"].eq(shelf_life_days)] .set_index("source") .loc[sources, column] ) bars = ax.bar( x + (offset - 1) * width, values, width, color=SHELF_COLORS[shelf_life_days], label=f"{shelf_life_days}-day shelf life", ) ax.bar_label(bars, fmt="%.2f", fontsize=7, padding=2) ax.set(title=title) ax.set_xticks(x, sources) ax.set_ylim(0, ax.get_ylim()[1] * 1.08) ax.grid(axis="y", alpha=0.2) ax.spines[["top", "right"]].set_visible(False) fig.legend( *axes[0].get_legend_handles_labels(), frameon=False, ncol=3, loc="outside upper center", ) plt.show()

What to notice:

• Shelf life: a shorter shelf life lowers the fill rate and raises waste for every target source. Under ETS 95%, the fill rate is 0.90 with 3 days against 0.97 with 8 days, and the waste rate 0.39 against 0.10. Expired units leave the shelf before they can be sold, and the stock that replaces them arrives two days later.
• Probability: with a 3-day shelf life, raising the quantile from 90% to 95% lifts the fill rate only from 0.89 to 0.90 but raises waste from 0.35 to 0.39. The extra safety stock mostly expires.
• History rule: the lowest waste and stock, but a fill rate of only 0.78 to 0.81, because a 4-day mean has no safety stock.

6. Where the cost comes from¶

total_cost is the sum of the components listed in cost_components. Evaluating each component separately shows what drives it.

In [11]:

COMPONENTS = {
    "purchase_cost": ("Purchase", "#4C78A8"),
    "waste_cost": ("Waste", "#B279A2"),
    "shortage_cost": ("Lost sales", "#E45756"),
    "holding_cost": ("Holding", "#72B7B2"),
    "ordering_cost": ("Order lines", "#F2CF5B"),
}
ordered = scores.set_index(["shelf_life", "source"]).loc[
    [(days, source) for days in shelf_lives for source in sources]
]
labels = [f"{source}\n{days} days" for days, source in ordered.index]

fig, ax = plt.subplots(figsize=(11, 4.2), layout="constrained")
bottom = np.zeros(len(ordered))
for column, (label, color) in COMPONENTS.items():
    ax.bar(labels, ordered[column], bottom=bottom, color=color, label=label)
    bottom += ordered[column].to_numpy()
for position, total in enumerate(ordered["total_cost"]):
    ax.text(position, total, f"{total:.0f}", ha="center", va="bottom", fontsize=8)
ax.set(title="Total cost by component", ylabel="Cost")
ax.tick_params(axis="x", labelsize=8)
ax.set_ylim(0, ordered["total_cost"].max() * 1.1)
ax.grid(axis="y", alpha=0.2)
ax.spines[["top", "right"]].set_visible(False)
ax.legend(frameon=False, ncol=5, fontsize=8, loc="upper right")
plt.show()

COMPONENTS = { "purchase_cost": ("Purchase", "#4C78A8"), "waste_cost": ("Waste", "#B279A2"), "shortage_cost": ("Lost sales", "#E45756"), "holding_cost": ("Holding", "#72B7B2"), "ordering_cost": ("Order lines", "#F2CF5B"), } ordered = scores.set_index(["shelf_life", "source"]).loc[ [(days, source) for days in shelf_lives for source in sources] ] labels = [f"{source}\n{days} days" for days, source in ordered.index] fig, ax = plt.subplots(figsize=(11, 4.2), layout="constrained") bottom = np.zeros(len(ordered)) for column, (label, color) in COMPONENTS.items(): ax.bar(labels, ordered[column], bottom=bottom, color=color, label=label) bottom += ordered[column].to_numpy() for position, total in enumerate(ordered["total_cost"]): ax.text(position, total, f"{total:.0f}", ha="center", va="bottom", fontsize=8) ax.set(title="Total cost by component", ylabel="Cost") ax.tick_params(axis="x", labelsize=8) ax.set_ylim(0, ordered["total_cost"].max() * 1.1) ax.grid(axis="y", alpha=0.2) ax.spines[["top", "right"]].set_visible(False) ax.legend(frameon=False, ncol=5, fontsize=8, loc="upper right") plt.show()

What to notice: purchase is the largest component, because every ordered unit is paid, including the units that later expire. With a 3-day shelf life, ETS 95% is the most expensive run (8,432): it buys the most and wastes the most. The history rule is the cheapest run at every shelf life (6,553, 5,845 and 5,592) because it buys the least, even though its lost-sales cost is the largest. At 2.00 per lost sale against 1.00 per unit purchased, running short is cheap; a higher shortage cost would change the ranking.

7. Expiry in the event table¶

The last figure follows one item under ETS 95% with the shortest and the longest shelf life. The line is the stock at the end of each day; the markers are the day's receipts, expired units and lost sales.

In [12]:

detail_item = items[0]
MARKERS = [
    ("received_units", "^", "#54A24B", "Received"),
    ("expired_units", "X", "#B279A2", "Expired"),
    ("lost_sales_units", "o", "#E45756", "Lost sales"),
]

fig, axes = plt.subplots(
    2,
    1,
    figsize=(11, 6),
    sharex=True,
    sharey=True,
    layout="constrained",
)
for ax, shelf_life_days in zip(axes, [3, 8]):
    events = results[(shelf_life_days, "ETS 95%")].to_event_frame()
    rows = events[events["unique_id"].eq(detail_item)]
    ax.step(
        rows["date"],
        rows["ending_on_hand"],
        where="mid",
        color=DEMAND_COLOR,
        linewidth=1.5,
        label="Stock at end of day",
    )
    for column, marker, color, label in MARKERS:
        flows = rows[rows[column].gt(0)]
        ax.scatter(
            flows["date"],
            flows[column],
            marker=marker,
            color=color,
            s=30,
            zorder=3,
            clip_on=False,
            label=label,
        )
    ax.set(
        title=f"{detail_item}, ETS 95%, {shelf_life_days}-day shelf life",
        ylabel="Units",
    )
    ax.set_ylim(bottom=0)
    ax.grid(axis="y", alpha=0.2)
    ax.spines[["top", "right"]].set_visible(False)
fig.legend(
    *axes[0].get_legend_handles_labels(),
    frameon=False,
    ncol=4,
    loc="outside upper center",
)
axes[1].xaxis.set_major_formatter(mdates.DateFormatter("%d %b"))
plt.show()

detail_item = items[0] MARKERS = [ ("received_units", "^", "#54A24B", "Received"), ("expired_units", "X", "#B279A2", "Expired"), ("lost_sales_units", "o", "#E45756", "Lost sales"), ] fig, axes = plt.subplots( 2, 1, figsize=(11, 6), sharex=True, sharey=True, layout="constrained", ) for ax, shelf_life_days in zip(axes, [3, 8]): events = results[(shelf_life_days, "ETS 95%")].to_event_frame() rows = events[events["unique_id"].eq(detail_item)] ax.step( rows["date"], rows["ending_on_hand"], where="mid", color=DEMAND_COLOR, linewidth=1.5, label="Stock at end of day", ) for column, marker, color, label in MARKERS: flows = rows[rows[column].gt(0)] ax.scatter( flows["date"], flows[column], marker=marker, color=color, s=30, zorder=3, clip_on=False, label=label, ) ax.set( title=f"{detail_item}, ETS 95%, {shelf_life_days}-day shelf life", ylabel="Units", ) ax.set_ylim(bottom=0) ax.grid(axis="y", alpha=0.2) ax.spines[["top", "right"]].set_visible(False) fig.legend( *axes[0].get_legend_handles_labels(), frameon=False, ncol=4, loc="outside upper center", ) axes[1].xaxis.set_major_formatter(mdates.DateFormatter("%d %b")) plt.show()

What to notice: with a 3-day shelf life, expiry (purple crosses) follows most deliveries, because part of each delivery does not sell within three days. Between 11 and 17 March the item also runs out before the next delivery (red dots), so the same weeks show both waste and lost sales. With 8 days, the same kind of orders sell before they expire, and only two small expiries remain, in mid-April.