Allocating usage across aging buckets

[Adrian1962]

Hello,

Attempting to allocate usage across aging buckets. Below are three invoices
for billed consumption with unique period of coverage dates. I am attempting
to allocate the volume across the aging columns that represent the period of
consumption. My control is that the sum of the aging buckets needs to equal
the invoiced volume for each invoice. For example invoice 300 spans three
aging columns and the daily usage represents 90 days for a total of 200,000.
My colleagues are attempting to avoid the manual decision making, and attempt
to automate this within excel. Our working excel version is 2000. Below I
pasted two arrays into two sections: the invoice details and aging break-out
below the solid line. Thanks for your help.


Invoice Start End Term Usage Daily use
100 3/1/2008 3/31/2008 30 200,000 6,667
200 2/1/2008 2/29/2008 28 200,000 7,143
300 12/1/2007 2/29/2008 90 200,000 2,222
600,000
________________________________________________________________

Current Bucket Bucket Bucket Bucket Bucket Bucket
1 2 3 4 5 6
29 31 31 30 31 30
Invoice Mar-08 Feb-08 Jan-08 Dec-07 Nov-07 Oct-07 Sep-07
100 200,000
200 200,000
300 62,222 68,889 68,889
Total 200,000 262,222 68,889 68,889 0 0 0