Cost-to-Serve Analysis: Calculating It From ERP Data

There is a specific conversation that happens in distribution businesses roughly once a year. Someone finally works out the true economics of the largest account, discovers it has been marginal or loss-making for a while, and nobody quite knows what to do with the information — because by then the relationship is deep, the volume props up the warehouse, and the salesperson who owns it is the best one you have.

The reason it takes a year to surface is that every routine report is built on gross margin, and gross margin is silent on the thing that makes big customers expensive: they don't just buy more, they buy differently.

What cost to serve actually means

Between "gross profit" and "profit" sits a set of costs that most systems pool at company level and never attribute to anyone:

• Order handling — someone processes the order, checks it, resolves queries on it
• Picking and packing — driven by number of lines, not order value
• Delivery — driven by drops and distance, not tonnage
• Returns and credits — disproportionately expensive, and concentrated in a few accounts
• Customer service — the accounts that call are not the accounts that spend
• Working capital — the customer who pays at 90 days is borrowing from you at your cost of capital

None of these correlate reliably with revenue. That's the entire point. If they did, gross margin would be a sufficient ranking and this analysis would be unnecessary.

Why gross margin misleads

Two customers, same product mix, same gross margin percentage, same annual revenue:

On every report you currently run, these two customers are identical. In reality Customer B consumes roughly thirteen times the order-handling and delivery resource, generates twenty times the credit note volume, and finances itself with your cash for an extra six weeks. Depending on your cost rates, B is somewhere between marginal and firmly loss-making, and A is one of the best accounts you have.

Choosing activity drivers

A full activity-based costing implementation is not worth it here. You need four to six drivers that explain most of the variation, and you need them to be countable from data you already have.

These five cover the majority of businesses:

If you have per-account customer service data — call minutes, ticket counts — add it, because it's usually highly skewed and it changes the answer. If you don't, don't invent it; four drivers well measured beat six with two guessed.

Extracting the drivers with SuiteQL

This produces one row per customer with every driver counted for the last twelve months.

-- Cost-to-serve activity drivers by customer, last 12 months.
WITH orders AS (
  SELECT t.entity AS cust,
         COUNT(DISTINCT t.id)   AS order_count,
         COUNT(tl.id)            AS line_count,
         SUM(tl.rate * ABS(tl.quantity))            AS revenue,
         SUM((tl.rate * ABS(tl.quantity)) - tl.costestimate) AS gross_profit
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  WHERE t.type = 'SalesOrd' AND t.voided = 'F'
    AND tl.mainline = 'F' AND tl.taxline = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
  GROUP BY t.entity
),
deliveries AS (
  SELECT t.entity AS cust, COUNT(DISTINCT t.id) AS delivery_count
  FROM transaction t
  WHERE t.type = 'ItemShip' AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
  GROUP BY t.entity
),
credits AS (
  SELECT t.entity AS cust,
         COUNT(DISTINCT t.id)      AS credit_count,
         SUM(ABS(t.foreigntotal)) AS credit_value
  FROM transaction t
  WHERE t.type = 'CustCred' AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
  GROUP BY t.entity
),
paydays AS (
  SELECT t.entity AS cust,
         AVG(NVL(t.closedate, CURRENT_DATE) - t.trandate) AS avg_days_to_pay
  FROM transaction t
  WHERE t.type = 'CustInvc' AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
  GROUP BY t.entity
)
SELECT
  BUILTIN.DF(o.cust)                AS customer,
  ROUND(o.revenue, 0)               AS revenue,
  ROUND(o.gross_profit, 0)          AS gross_profit,
  ROUND(o.gross_profit / NULLIF(o.revenue, 0) * 100, 1) AS gm_pct,
  o.order_count,
  o.line_count,
  NVL(d.delivery_count, 0)          AS deliveries,
  NVL(c.credit_count, 0)            AS credit_notes,
  ROUND(NVL(c.credit_value, 0), 0)   AS credit_value,
  ROUND(NVL(p.avg_days_to_pay, 0), 0) AS avg_days_to_pay,
  ROUND(o.revenue / NULLIF(o.order_count, 0), 0) AS avg_order_value
FROM orders o
LEFT JOIN deliveries d ON d.cust = o.cust
LEFT JOIN credits    c ON c.cust = o.cust
LEFT JOIN paydays    p ON p.cust = o.cust
WHERE o.revenue > 0
ORDER BY o.gross_profit DESC

closedate on an invoice approximates the settlement date; if your account closes invoices by other means, substitute the payment application date. As always, sanity-check a handful of customers you know well before trusting the whole table.

Putting a cost on each driver

Now the part people over-engineer. You do not need a perfect cost rate. You need one that's roughly right and consistently applied, because the output you care about is the ranking, and the ranking is remarkably insensitive to rate precision.

The method:

1. Take the relevant cost pool from your P&L. Warehouse wages and consumables for picking. Transport cost for delivery. Customer service salaries for order handling.
2. Divide by total driver volume for the year. Total warehouse cost ÷ total order lines picked = cost per line.
3. Apply to each customer's driver count.
4. For receivables: customer revenue × (avg days to pay ÷ 365) × your cost of capital.

Illustratively, for a mid-market distributor:

Those are illustrative figures, not benchmarks — derive your own from your own P&L. But note the shape: a credit note costing £50 to process makes the customer with 41 of them £2,050 worse before anything else is counted.

Reading the result: the four quadrants

Plot gross margin percentage against cost to serve as a percentage of revenue:

The finding that provokes most argument is a large, well-liked account landing in the bottom right. Have the transaction detail ready before you present it, because the first response will be that the model must be wrong.

What to actually do about it

Firing customers makes for a punchy consulting slide and is almost never the right first move. The cost base doesn't disappear when the revenue does, so exiting a marginal account often makes things worse before it makes them better.

In rough order of how often they work:

1. Change the behaviour, not the relationship. A minimum order value, a fixed weekly delivery day, or a small-order surcharge fixes most high-CTS accounts without a difficult conversation about price.
2. Reprice at the next review, with evidence. "Your average order is £774 against our £2,100 average, and each order costs us the same to process" is a conversation. "We need 3%" is a negotiation you'll lose.
3. Attack the credit note rate. High credits usually mean an upstream problem — wrong items, poor order accuracy, an unclear catalogue — that is yours to fix and cheap to fix.
4. Address payment behaviour separately. Often the largest single line and the most tractable. See reducing DSO with data you already have.
5. Exit, rarely, and last. Only where behaviour genuinely won't change and the capacity has somewhere better to go.

One last thing worth saying: cost to serve done once is a project, and projects age. Order patterns shift, a customer changes buyer, delivery costs move. The businesses that get sustained value from this recalculate it continuously and look at the movers, not the levels — because a customer whose cost to serve jumped 40% this quarter is a more actionable signal than one that has been marginal for three years.