Your customer due dates are wrong

You want to know when your money comes in. Logical question, and it looks like a matter of adding up: take your outstanding invoices, look at the due date, put them on a timeline.

Do that once and then compare it with what actually came in. Chances are your forecast was well off.

Why that field is no good

The due date in your ERP comes from a payment term attached to the customer record. Someone set it at some point, maybe when the customer was created, maybe carried over from a previous system.

And after that nobody ever looked at it again.

The result is that the field tells you what was once agreed, not what happens. Customers who structurally pay late still sit at thirty days. Customers who actually settle in cash sit at the default value because nobody adjusted them. And a chunk of them is simply empty.

In the model I built, the due dates on the customer side were in practice unusable for a forecast. The supplier side was reliable, and that is no coincidence: with purchases the term is set by the other party and printed on their invoice, so it arrives correctly.

What to do instead

You derive the term from behavior instead of from a field.

The simplest form: calculate per customer the average number of days between invoice date and payment date over the past twelve months. That is a calculation on data you already have and it tells you what actually happens.

For customers with little history that does not work, and then a grouping is better. In the model I worked with, the channel was the determining factor: whoever buys in the store pays immediately, a webshop order comes in within a few days, and a business customer roughly sticks to a one month term. Three groups, each with its own term, derived from what that group does historically.

Which grouping works for you depends on your business. Channel, customer segment, size of the invoice. The point is that you group on something that genuinely explains payment behavior, and not on a field someone once filled in.

What that gives you

A forecast you dare to show.

And something more useful: the gap between the agreed term and the actual term becomes visible. That is a number per customer, and it is usually a surprise. There are nearly always a few customers in there who deviate much further than anyone thought, and those are exactly the conversations you should be having.

Put a signal on that. Not on every late payment, because then you get too many and nobody looks anymore. Do it when a customer structurally deviates from their own pattern. Someone who always pays at thirty-five days and is now at sixty is telling you something, and you want to know that before your next big delivery.

What to be careful with

An average hides outliers. A customer who mostly pays at ten days and twice a year at ninety has a decent average and an annoying pattern. So look at the spread as well, not only at the average.

And seasonality plays a part. In some sectors everybody pays slower in summer and around the year end. If you do not correct for that, you will think something is going wrong every summer.

The claim

The field holding your payment term describes an agreement. Your cash flow is determined by behavior. As long as you treat those two as the same thing, you are building a forecast on an assumption nobody has checked in years.