Distribution equation

[PhilipLeitch]

I am seeking an equation that describes this distribution (approximately).

The basics are:
1. An event occurs.
2. A number of days later a secondary event occurs.
I measure the difference in days between these two events. This specific case is invoices. An invoice is generated, and a specific number of days later it is settled (payment, credited off or bad debt).

The event does have a magnatude (sale value) - but at the moment I'm only interested in the duration.


When I plot the data for this year, I get a specific shaped distribution. When I plot the data for last year, the distribution matches. When I plot the data for all time (approx. 7 years) I get the same distribution. The problem is that the distribution doesn't look simple, and therefore distributions (I try to match) match poorly.

Perhaps the spikes are due to weekends - perhaps invoices are more likely to be issued and/or paid on specific days.

Just by looking at it, my guess is that this is a Poisson distribution with some type of harmonic occuring. But this is beyond my skills - perhaps some type of Fourier Transform is required???


Philip
___________________
Nobody can hear you scream in Euclidean space.

[TomGutman]

Your data definitely shows dependencies on various billing cycles. There is a peak every seven days (obvious if you modify the second graph to have x axis labels every seven days). There is another peak (the biggest one) at 30 days -- the nominal accounting month. An additional small peak occurs at 10 days.

I rather wonder whether your data reflects actual calendar issue and payment dates, or a more general classification of age (with, perhaps, many of the longer payment intervals being specified in weeks which are then converted to days for your table).

I don't think you are going to find any simple expression for this distribution (it certainly doesn't match any standard theoretical distribution). Besides the weekly peaks, and the two days before a week dip, the distribution seems to be different in different ranges. You have different behaviours for under a week, from one week to one month, from one to two months, and over two months.

If you have tyhe original data in terms of actual calendar dates, you might try recalculating the intervals as business days rather than calendar days, and see if that is any simpler.

If you need only very rough estimates, and if you assume that the seven day cycle reflects more the way the data are categorized rather than reality you could just ignore the weekly cycle. Then you might have a constant from one to four weeks and a roughly exponential decay above one month.

Or you might just not bother with an equation, and just use the data table as the definition for the distribution. Possibly, but not necessarily, with some smoothing. Much depends on why you want a distribution and what you want to do with the distribution.
__________________
� � � � Tom Gutman

[PhilipLeitch]

mmmm... some good points.

That makes me wonder if an invoice issued on Monday has a different distribution to one issued on a Tuesday, and so on for each day so that the combination of distributions is what causes this effect. Or a combination of this effect with that of calendar month (which most Australian business use as their financial periods), where businesses wait until the end of the calendar month to pay, regardless of the "due" date on the invoice.

Heck - I guess even the day that banks process payments and/or the day(s) the A/R staff process the payments could all make a difference.

I'll break apart the data and see what it turns up.


Philip
___________________
Nobody can hear you scream in Euclidean space.

[PhilipOakley]

It is likey to be a mixture distribution with a summation of a few different distributions / parameters.

You may be able to allocate customers to each of the ditributions, same with invoice value (e.g. small value, medium value high value => poisson, 1 week, 30 day)

The data highlights the distinction between Statistics (what the data says) and Probability (what the theory says) !

Philip Oakley

[TheodoreM.Bones]

Not any probability distribution will fit the data given. There is no use in trying to break the data up into segments.
Here several curve fit methods are
examined but the most reliable is a deProny solution of a Hankel matrix with upward sloping diagonals. This algorithm is not covered by any well known Internet sites, It gives exact agreement at the calibration points and excellent interpolation at in-between points when compared to a cspline run.

[PhilipLeitch]

Thanks. I think I'm going to have to spend some time reading through all of that.


Philip
___________________
Nobody can hear you scream in Euclidean space.

[RichardJ]

On 5/21/2009 2:20:12 AM, pleitch wrote:

Interesting data.

>That makes me wonder if an
>invoice issued on Monday has a
>different distribution to one
>issued on a Tuesday, and so on

Almost for sure, I think. At the company I used to work for they made all payments on Fridays (don't ask me why - accountants are a mystery to me).

Richard

[PhilipLeitch]

I agree - they are strange creatures. But they have cycles and processes in place to make the chaotic business conform into something that can be measured, managed and replicated.

I recently completed an MBA which had an honours component. I studied Accounts Receivable collection measures. Up until that point I always assumed that Accountants were good at mathematics. I quickly learnt that they are very good at concepts, moving money around and "accounting" (categorising and counting) for things, but aren't very good at mathematics.

They (both certified professionals and Drs) couldn�t grasp Net Present Value (NPV) when calculated based on a probability distribution. They didn�t have a grasp of calculus. I spent more time explaining the mathematics in my honours to my supervisor than the argument I was making.

In fact, of the practicing accountants I�ve chatted with, very few use any mathematics in their job. Most of the time they use the results of calculations, and are constantly interpreting figures, but they aren�t actually relying on anything more than junior school mathematics. I guess this is one reason Excel is so popular in businesses, it is powerful enough to do almost all business calculations and that's about it.

One Dr told me that Accountants who are good with mathematics are all in Corporate Finance... and we all know how well that is going at the moment!

Still - corporate finance does actually sound fun.

Philip
___________________
Nobody can hear you scream in Euclidean space.