1. The problem
A wholesale distributor of consumer electronics across Latin America was making stocking decisions on 4,307 SKUs using a spreadsheet and intuition. Their ERP held 4.5 years of order history — roughly 290,000 raw order lines — but nobody had ever asked the data a simple question:
Which SKUs can actually be forecast, and which ones can't?
That question sounds technical. It isn't. It's the difference between a supply chain that plans and a supply chain that guesses. If you treat a lumpy, contract-driven SKU the same way you treat a steady, high-volume SKU, you will either overstock (cash locked in inventory) or understock (lost sales, unhappy customers). Both are expensive. Both are avoidable.
The brief I set myself was not "build a forecasting model." It was:
Build a tool that takes a raw ERP export and tells an analyst, honestly, which SKUs they can trust a forecast on — and which ones need a phone call to the customer instead.
That distinction is the entire project.
2. The data
The dataset is a real ERP export from a working distributor. Not synthetic, not scraped, not a Kaggle competition. Real order lines with real mess in them.
| Metric | Value |
|---|---|
| Raw order lines | ~290,000 |
| Clean order lines after filtering | 84,741 |
| Distinct customers | 872 |
| Distinct SKUs | 4,307 |
| Date range | 2023-01-02 to 2026-08-31 |
| Total net units | ~5.78M |
The first finding came before any modeling. Roughly 21% of raw rows were not sales. They were discount adjustments, returns, and cancellations posted as negative-quantity transactions. If you naively sum the quantity column, you get a number that looks like demand but isn't. Every downstream forecast is wrong if this step is wrong.
So the pipeline does three things before any model sees the data:
- Separates gross demand from returns. Net demand (gross minus returns) is the forecast target. Gross demand and returns remain visible for sensitivity analysis.
- Removes exact duplicates. ERP exports frequently double-post. Detected and removed.
- Establishes a completeness cutoff. The last month in the data is often partial. The pipeline uses the last complete month as the forecasting anchor. This sounds trivial. It isn't. Forecasting off a partial month produces a fake trend and a fake seasonality signal, and both errors propagate into every SKU.
After cleaning, the app reports its own data quality: rows, customers, SKUs, invalid rows removed, exact duplicates removed, and a flag for any rows above the 99th percentile of absolute quantity. On the pilot training set, the app retained 103 outlier rows for investigation rather than silently dropping them. Silent outlier removal is how forecasters lie to themselves.
3. The finding: 0.27%
Of the 872 customers and 4,307 SKUs, roughly 34,031 customer × SKU pairs ever had positive demand. The question is: how many have enough monthly history to forecast?
| Filter | Pairs remaining | % of total |
|---|---|---|
| Any positive demand ever | 34,031 | 100% |
| At least 12 months of monthly history | 92 | 0.27% |
| At least 24 months of monthly history | 5 | 0.015% |
And on the SKU side: 79% of SKUs had fewer than 6 months of activity.
Read that again. Less than one-third of one percent of customer × SKU pairs have enough monthly history for a classical time-series model to even attempt a forecast. Fifteen thousandths of a percent have enough history for a model to estimate seasonality with any confidence.
This is not an anomaly. This is what wholesale distribution actually looks like. A reseller orders a bulk shipment in March, another in July, nothing for four months, then a spike on a new SKU launch. That is not monthly demand. That is a reorder-probability problem wearing a time-series costume.
The insight the project produced is not a forecast. It is this:
This dataset has a small forecastable core and a large unforecastable tail, and treating them the same is the actual risk.
4. What I built
Given the finding, the tool had to do three things well:
- Forecast honestly on the forecastable core. Three models — Naive (repeat last year), Seasonal Naive (repeat the same month last year), and ETS (exponential smoothing with trend and seasonality). Selected per SKU via expanding-window backtesting, not a global choice.
- Refuse to forecast on the tail. If a pair has insufficient history, the tool does not produce a confident number. It produces a clear "Insufficient history — validate with customer" label and a written reason.
- Explain itself in plain language. Every SKU gets a reliability tier, a pattern label, a demand-type label, and a business action.
The business actions are the point:
| Action | Meaning |
|---|---|
| Validate with Customer | Do not act on the number until you've confirmed it with the customer. |
| Increase Stocking | Model is reliable, direction is up. Raise the order. |
| Maintain Current Stocking | Model is reliable, direction is flat. Don't overthink it. |
| Evaluate for Reduction | Model is reliable, direction is down. Cut the order. |
The engine is built on Pandas, Statsmodels, and a custom reliability layer. The UI is Streamlit. The output is a decision workbook in Excel with live formulas, a pivot-table guide, and a documented methodology sheet. 46 pytest tests cover the pipeline end-to-end.
5. Validation result — and it didn't work the way we expected
I tested the app on 92 forecastable customer × SKU pairs. Training window: 2023-01 to 2025-08. Holdout: 2025-09 to 2026-08. Twelve months held back, never seen by the model.
| Metric | Value |
|---|---|
| Pairs attempted | 92 |
| Pairs that produced a forecast | 57 |
| Pairs with non-zero demand in the holdout | 31 |
| Model beats Naive on | 18 of 31 (58% win rate) |
| Median absolute % error — model | 208.4% |
| Median absolute % error — naive | 1900.0% |
| Volume-weighted WAPE — model | 178.7% |
| Volume-weighted WAPE — naive | 65.5% |
Two readings, both true:
Reading 1 (favourable): the model beat naive on 58% of pairs. Median error dropped from 1900% to 208% — a 9× improvement. On the long tail of small, low-volume SKUs, the model is genuinely better than "repeat last year."
Reading 2 (honest, and this is the true one): volume-weighted WAPE — which is what actually matters for stock decisions — is 178.7% for the model vs 65.5% for naive. On the SKUs that carry the business, the model is 3× worse than the naive baseline.
The 58% win rate counts pairs equally, including 20-unit tail SKUs. The volume-weighted number counts the big SKUs, which is where stockouts and overstock actually cost money. If you are running a warehouse and you have to choose between the two numbers, you choose the second one.
What went wrong — the diagnosis
Look at the top 10 pairs by holdout volume. Every single one is OMNI CHANNEL C/O AMAZON US. That's the concentration problem. And for each of them, the model over-forecast.
For the top SKU, WALL-CHARGR-25W:
| Quantity | Value |
|---|---|
| Forecast (ETS) | 58,148 |
| Actual holdout | 22,791 |
| Naive (last 12m training) | 37,021 |
The ETS model saw growth in the training window, extrapolated it, and forecast 58k. Actual was 22.8k — a 155% over-forecast. Naive said 37k — a 62% over-forecast. Both wrong, both over. Naive was closer.
Why is naive closer than ETS here? Because ETS with additive trend on lumpy wholesale data extrapolates noise. When the training window contains a big spike (a bulk Amazon order, a launch stocking, a promotion), ETS thinks that spike is the trend and projects it forward. Naive just says "repeat last year" and stays grounded.
This is a real, documented, common failure mode of trend-following models on wholesale data. It is not a bug in the code. It is the physics of the problem.
What the app got right
Here is the part that matters for the interview.
The app correctly flagged the SKUs where the model would fail. It did not confidently produce a wrong number. It produced a number and a warning:
- On
WALL-CHARGR-25W, the SKU Decision page shows Reliability: Medium, Selected model: ETS, Action: Validate with Customer, and a yellow warning: "Forecast grows +57.1% vs last 12 months (unusually high). 6-month backtest selects Naive, while the 12-month selection is ETS; inspect both because the forecast horizon is 12 months." - Across the portfolio, 84 of 92 pairs were assigned Low reliability, most with the reason "Best model WAPE is ≥100%" or "History is only N months (<24)."
- The action label on every one of those pairs is "Validate with Customer."
The model was wrong. The app knew the model was wrong. The app said so. That is working as designed.
The most important output of this forecasting app was telling the user not to trust the forecast without validation.
6. From forecast to replenishment — v2
v1 answered the question "what will demand be?" and was honest that the answer was usually "we don't know." That is a useful tool. It is not a complete tool. A supply chain lead cannot take "we don't know" to a customer. They need a next action.
The first version of the replenishment layer produced this on the pilot portfolio: 85 SKUs, every one receiving action = "Validate with Customer", every one with recommended_order_qty = 0. The trust rule was working correctly — 84 of the 85 SKUs are Low reliability, and the one Medium-reliability SKU had a Review trend-check. Both are legitimate blocks.
But a supply chain lead cannot act on "validate 85 things." That's the same as "do nothing."
The fix was not to loosen the trust rule. It was to produce two numbers instead of one:
recommended_order_qty— the dispatchable quantity. Zero when the trust rule blocks. This is what the app would order autonomously.recommended_order_qty_if_validated— the quantity to order once the customer confirms the forecast. This is always computed, regardless of reliability tier.
The second number is the product. It's what the supply chain lead walks into the customer call with. Every SKU also gets a validation_urgency bucket derived from days of cover versus lead time:
| Urgency | Rule | Meaning |
|---|---|---|
| Call today | days of cover < lead time | Cannot recover if the call waits. |
| Call this week | days of cover < 1.5 × lead time | Buffer is thin but recoverable. |
| Monitor | days of cover < lead time + review period | Inside the reorder window; watch next cycle. |
| No action | everything else | Cover is comfortably above the reorder window. |
SKUs are ranked 1..N: first by urgency bucket, then by days of cover ascending within each bucket. Rank 1 is always the SKU with the smallest margin between its current cover and its lead time.
The pilot worklist
| Rank | SKU | Supplier | Urgency | Days of cover | Order if validated |
|---|---|---|---|---|---|
| 1 | R400-SOM-BDFEGA | China | Call today | 0.1 | 9,869 |
| 2 | WALL-CHARGR-25W | Boss | Call today | 0.2 | 9,198 |
| 3 | XT2311-3M128BKU | Xiomi | Call today | 7.7 | 439 |
| 4 | IPAD9-64SIL-WIF | Apple | Call today | 17.1 | 50 |
| 5 | R400-SBUDSFEBKU | China | Call today | 22.0 | 50 |
The top of the list is R400-SOM-BDFEGA, with 2.4 hours of cover against a 25-day lead time. Rank 2 is WALL-CHARGR-25W — the SKU from v1 — with 5.3 hours of cover and a Medium reliability tier whose decision-layer verdict was "Validate with Customer, driven by OMNI CHANNEL C/O AMAZON US, 100% share."
The app will not dispatch an order for either. It will tell the analyst to call China about 9,869 units and Boss about 9,198 units, in that order, before lunch.
The wall charger, revisited
v1 flagged WALL-CHARGR-25W for three independent reasons: Medium reliability, a Review trend-check, and a decision-layer verdict starting with "Validate." Each was sufficient on its own. The app zeroed the order and told the analyst to call the customer.
v2 keeps the same block, but adds the number the analyst takes into the call:
Call today — validate & order. Days of cover 0.2. Stockout risk High. Per policy no order is dispatched until the signal is validated with the customer. If the customer confirms, order 9,198 units from Boss.
That final sentence is the whole difference between v1 and v2. v1 said "don't trust this forecast." v2 says "don't trust this forecast, and here is exactly what to do when you've verified it."
7. What this does not mean
- It does not mean the app is broken. The app correctly assigned Medium/Low reliability. It fired the model-horizon disagreement warning. It returned "Validate with Customer" as the action. If the app had confidently returned a number with High reliability, that would be a bug. It didn't.
- It does not mean the synthetic-data demo was wrong. On synthetic data with stable seasonality, Seasonal Naive won and the reliability tiers correctly sorted the SKUs. On lumpy wholesale data, ETS over-extrapolates and the tiers correctly sort into Low. Both findings are correct for their respective datasets.
- It does not mean the app should be rebuilt. A tool that says "I'm not sure about this SKU — validate before acting" is more valuable than one that confidently says the wrong number. The rebuild would be to add reorder-probability modelling for the 99.73% of pairs where monthly time-series is the wrong frame. That is a v3, not a v1 fix.
- The v2 pilot's on-hand inventory is a synthesized snapshot. The distributor's ERP export did not include a live inventory feed, so the on-hand numbers used to demonstrate v2 are illustrative. The logic is production-ready; the specific quantities would change with a real inventory source.
8. Recommendation
For the 33,939 customer × SKU pairs that cannot be forecast monthly, monthly time-series is the wrong tool. The right recommendations are:
- Customer reorder-probability modelling. Instead of predicting how much a customer will order next month, predict whether they will order. The right output is a probability and a reorder window, not a point forecast.
- Aggregate customer-level forecasting. Roll the SKU-level chaos up to the customer level. A customer that orders 400 SKUs irregularly still has a fairly stable total monthly demand. Forecast the aggregate, allocate down proportionally.
- New-SKU launch curves. For SKUs with under 6 months of activity, you cannot forecast monthly demand. You can forecast against a launch curve — either a category-level curve or a curve from a comparable prior SKU.
For the 92-pair forecastable core: keep the three-model selection with expanding-window backtest, and promote both guardrails from warnings to hard rules:
- Cap the model's trend extrapolation when the SKU's recent 3-month volume is below the trailing-12-month average. This specifically prevents the
WALL-CHARGR-25Wfailure mode. - Force "Validate with Customer" whenever the 6-month and 12-month backtests select different models. The disagreement is the signal.
9. What I took away
Three things, in order of importance:
The finding is the deliverable. "0.27% of pairs are forecastable" is a more useful sentence than any forecast number this project produced. That sentence changes what the business does next. The forecast numbers change what the business orders next month. The first is strategy. The second is tactics. Strategy is worth more.
Honest metrics beat flattering metrics. I could have reported "58% win rate" and stopped. That number is true and it flatters the model. But the volume-weighted WAPE is the number a supply chain lead needs to make a decision, and that number says the model is 3× worse than a naive baseline on the SKUs that matter. Reporting the second number, unprompted, is the difference between an analyst and a vendor.
A forecasting app's most important feature is knowing when not to forecast. This project started as "build a demand forecasting engine." It ended as "build a tool that tells you which demand is forecastable — and what to do about it." Those are different products, and the second one is more useful. The 84 of 92 Low reliability labels are not a failure of the model. They are the product.
10. The stack
| Layer | Tool |
|---|---|
| Data pipeline | Python, Pandas, NumPy |
| Models | Statsmodels (ETS), custom Naive / Seasonal Naive |
| Backtesting | Expanding-window cross-validation, custom |
| Reliability + decision layer | Custom Python |
| Replenishment layer | Custom Python (inventory_decision.py) |
| UI | Streamlit |
| Workbook export | openpyxl |
| Tests | pytest (46 tests, all passing) |
| Data | Real ERP export — 84,741 cleaned order lines, 872 customers, 4,307 SKUs |
Engineering log with the full technical narrative: engineering-log-4.html →
Want to see the voice agent log? Read Voice AI · Log 03 →