A step-by-step guide to computing cost to serve per customer in Excel: choosing the cost pools and their drivers, counting drops, order lines, visits and return lines per customer with COUNTIFS, computing a rate per driver from the ledger, allocating cost with SUMPRODUCT, contribution after cost to serve, the ratio of cost to serve to gross margin, the largest driver per loss-making customer, and the check that allocated cost equals the pools. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Cost to serve is what a customer costs beyond the goods. In Excel it is driver counts, rates and one SUMPRODUCT. This guide gives the formulas and the check.
| Sheet | One row per | Columns used |
|---|---|---|
Deliveries |
Drop | Customer, date |
OrderLines |
Order line | Customer, date, channel: Keyed or Portal |
Visits |
Logged visit | Customer, date |
Returns |
Return line | Customer, date |
Sales |
Customer | Customer, revenue, gross margin, for the period |
On Calc, period start B1 and end B2. Cost pools in a small table:
| A | B | C | D | |
|---|---|---|---|---|
| 5 | Pool | Cost | Total driver count | Rate |
| 6 | Delivery | from ledger | formula | formula |
| 7 | Keyed order lines | from ledger | formula | formula |
| 8 | Portal order lines | from ledger | formula | formula |
| 9 | Visits | from ledger | formula | formula |
| 10 | Returns | from ledger | formula | formula |
Total drops, C6:
=COUNTIFS(Deliveries!B:B,">="&$B$1,Deliveries!B:B,"<="&$B$2)
Keyed lines, C7:
=COUNTIFS(OrderLines!C:C,"Keyed",OrderLines!B:B,">="&$B$1,OrderLines!B:B,"<="&$B$2)
And so on. Rate, D6 down:
=B6/C6
On Sales, beside each customer in column A, drops in D2:
=COUNTIFS(Deliveries!A:A,A2,Deliveries!B:B,">="&Calc!$B$1,Deliveries!B:B,"<="&Calc!$B$2)
Keyed lines E2, portal lines F2, visits G2, return lines H2, each the same pattern on its own sheet.
I2. The rates run down Calc!D6:D10 and the counts run across D2:H2, so transpose:
=SUMPRODUCT(D2:H2,TRANSPOSE(Calc!$D$6:$D$10))
In older Excel enter it with Ctrl+Shift+Enter, or write it out:
=D2*Calc!$D$6+E2*Calc!$D$7+F2*Calc!$D$8+G2*Calc!$D$9+H2*Calc!$D$10
Contribution, J2:
=C2-I2
Cost to serve over gross margin, K2:
=IF(C2>0,I2/C2,"no margin")
Sort descending on K. Above 100 percent is loss-making after cost to serve. Cost to serve over revenue, =I2/B2, is worth a column and is the weaker ranking: a 17 percent cost to serve is fine on a 40 percent margin and fatal on a 15.
Put each driver's cost in its own column, M2 to Q2, headed Delivery, Keyed lines, Portal lines, Visits and Returns in M1:Q1: =D2*Calc!$D$6, =E2*Calc!$D$7, and so on. Then the largest driver, L2:
=INDEX($M$1:$Q$1,MATCH(MAX(M2:Q2),M2:Q2,0))
Add average drop value, =B2/D2, beside it. A customer whose largest driver is delivery and whose average drop is a twentieth of the business's is a minimum-order-value conversation.
=SUM(Sales!I:I)-SUM(Calc!B6:B10)
Zero, to rounding. If it is not, some deliveries or order lines belong to customers missing from the Sales sheet; find them with a COUNTIF of the delivery customers against the sales list.
Customer identifiers differ between systems. The delivery system's customer code is not the ledger's. Build the mapping first; every unmatched drop is cost that lands on nobody.
Pools include fixed cost. Warehouse rent allocated by drops makes small customers look worse than they are. Keep to cost that moves with activity.
Credit cost forgotten. A customer paying sixty days late costs real money: average overdue balance times the cost of capital, as a sixth pool.
Customers dropped from the result. The table shows behaviour to change, not customers to lose. Most loss-making accounts are one delivery day away from profit.
An annual cost-to-serve study is a classic spreadsheet project, and that is its weakness: it is done once, presented, and not refreshed, while the customers' behaviour changes monthly. Five exports joined on a customer map every month is where it strains; see the four signs a spreadsheet is no longer enough. For what the figure should be, see what is a good cost to serve; for how it differs by desk, the cost to serve by industry hub. Covirage computes the same table from the same exports every month, with the allocation check built in.
The ledger, for the same period as the driver counts. Delivery is fleet, fuel, drivers and third-party carriage. Order processing is the customer service team's cost. Sales is field sales salaries and travel. Take only the part that varies with activity, and write down what is in each pool so the figure can be rebuilt next year.
Because every customer then has the same cost to serve percentage and the analysis shows nothing. The point of the driver is that a customer taking five small deliveries a week carries five deliveries' cost. The allocation is the measure.
Less than you think. Whether a drop costs 35 or 42, the customer taking 250 drops for 90,000 of revenue is in the same place on the list. Get the drivers right and the pools roughly right; refine the rates later if a decision depends on it.