Cuedis

How to spot a supplier turning unreliable before it costs you

Most supplier problems do not start with a missed delivery. They start with a delivery that is a day late, then another that is two days late, then a short shipment that gets sorted out with a phone call. Each one on its own is easy to shrug off. Together they add up to empty shelves, rushed orders from a more expensive supplier, and customers who go elsewhere.

The good news is that a supplier turning unreliable almost always leaves a trail in your purchase records, weeks before it becomes a crisis. This post shows how to read that trail.

The three signs to watch

1. Lead time getting longer. Lead time is the number of days between placing an order and receiving it. Every supplier has a usual range. One might reliably deliver in 4 to 6 days, another in 10 to 14. What matters is not the number itself but whether it is drifting away from that supplier's usual range.

2. Lead time getting less predictable. A supplier who delivers in exactly 7 days is easier to plan around than one who delivers in anything from 3 to 15, even if the average is the same. Growing spread is often the first sign of trouble at the supplier's end: staff shortages, cash flow problems, or their own supplier letting them down.

3. Short or substituted deliveries. If your system records ordered and received quantities separately, the gap between them is a second signal. A supplier who used to fill 98% of your order and now fills 85% is struggling, even if the deliveries arrive on time.

Measuring lead time from your own data

Most POS and ERP systems record a purchase order with an order date and a goods-received note with a received date. Table and column names vary from system to system, but the idea is always the same: join the order to its receipt and count the days between them.

Here is an example for MySQL, assuming tables called purchase_orders and goods_received:

SELECT s.name AS supplier,
       COUNT(*) AS deliveries,
       ROUND(AVG(DATEDIFF(g.received_date, p.order_date)), 1) AS avg_days,
       MIN(DATEDIFF(g.received_date, p.order_date)) AS fastest,
       MAX(DATEDIFF(g.received_date, p.order_date)) AS slowest
FROM purchase_orders p
JOIN goods_received g ON g.po_id = p.id
JOIN suppliers s ON s.id = p.supplier_id
WHERE p.order_date >= CURDATE() - INTERVAL 90 DAY
GROUP BY s.name
ORDER BY avg_days DESC;

And the same for SQL Server. Note that DATEDIFF takes its arguments in a different order there:

SELECT s.name AS supplier,
       COUNT(*) AS deliveries,
       ROUND(AVG(CAST(DATEDIFF(day, p.order_date, g.received_date) AS float)), 1) AS avg_days,
       MIN(DATEDIFF(day, p.order_date, g.received_date)) AS fastest,
       MAX(DATEDIFF(day, p.order_date, g.received_date)) AS slowest
FROM purchase_orders p
JOIN goods_received g ON g.po_id = p.id
JOIN suppliers s ON s.id = p.supplier_id
WHERE p.order_date >= DATEADD(day, -90, CAST(GETDATE() AS date))
GROUP BY s.name
ORDER BY avg_days DESC;

Run it for the last 90 days, then again for the 90 days before that. Any supplier whose average or slowest delivery has grown noticeably deserves a closer look.

Why a single average is not enough

A table like the one above is a good start, but it has two weaknesses.

First, an average hides the trend inside the period. A supplier who was fine for two months and terrible for the last three weeks can still have a respectable 90-day average. Looking at deliveries in date order, or comparing the last few deliveries with the supplier's usual range, catches the change much sooner.

Second, normal is different for every supplier. Nine days is excellent for an overseas supplier and alarming for a local wholesaler. Comparing all suppliers against one fixed target produces either too many warnings or too few. The useful question is always "is this unusual for this supplier?"

A practical rule of thumb is to treat a delivery as late when it takes noticeably longer than that supplier's usual range. Then watch how many late deliveries there are in a row, or in a short period. One late delivery is noise. Several in a few weeks is a pattern.

Things that look like unreliability but are not

Before you call the supplier, rule out the usual false alarms:

  • Public holidays and festive seasons. Deliveries around Chinese New Year, Hari Raya or year end are often slower across the board. Compare with the same period last year.
  • Your own receiving. If goods arrive on time but are only entered into the system days later, the data will show a late delivery that never happened. Check whether the received date is the delivery date or the data-entry date.
  • Order changes. An order that was amended or split will often look late in the data.
  • A one-off event. Port congestion or a road closure that affected everyone is not a sign that one supplier is failing.

What to do when you see it

When the data shows a supplier slipping, you have time to act calmly instead of in a panic:

  1. Talk to them early, with specific dates: "Your last six deliveries took between 8 and 12 days; it used to be 4 to 6. What has changed?" Specific evidence gets a straighter answer than a general complaint.
  2. Increase safety stock for that supplier's fast-moving items while you find out more.
  3. Line up an alternative for the products that would hurt most if they ran out.
  4. Keep watching. If things improve, note when. If they do not, you have the history to justify a change.

Doing this without spreadsheets

Running these queries every week works, but it depends on someone remembering to do it and knowing what normal looks like for each supplier. That is exactly the job we built Cuedis for. It reads new deliveries as they are recorded, learns each supplier's usual lead time, and when one starts slipping it forms a belief with a date and evidence, for example "Kelvin Electronics has been unreliable since 7 April: 24 late deliveries, usually 4 to 6 days." It then tells you in the morning briefing. When the supplier improves, it changes its mind and keeps a record of why.

Whichever way you do it, the main lesson is the same: your purchase records already know which supplier is letting you down. You only need to look at them before the shelves do the talking.