Can Excel even do this? Help!

[halpwr]

Hey guys. I have sample data from an order management system below. INV means an invoice, so the balance goes up. PY means a payment, so the balance goes down. And CM means Credit Memo, which also means the balance goes down. However, for our purposes, we need to look at the group of clearing numbers separately, which is causing issues.

Type Clearing Number Amount document_date
PY 2151885 -30 5/10/2010
INV 2151886 100 5/4/2010
INV 2151886 100 5/5/2010
PY 2151886 -50 5/6/2010
CM 2151886 -50 5/6/2010
PY 2151886 -100 5/10/2010
PY 2151887 -673.64 5/10/2010
PY 2151888 -7060 5/10/2010

The way the system works, within each CLEARING GROUP (as identified by the clearing numbers), each payment (PY) will be "applied" to the earliest open invoice, and close it out.

For example let's focus only on clearing #..886, first comes an invoice of 100, then comes another invoice of 100. The rules are that each time we have a Payment or Credit Memo, we apply (subtract) it to the EARLIEST OPEN invoice. Therefore, in this example, the first payment (PY) should close out 50 of the first invoice, leaving a remaining balance of 50. Then, the Credit Memo, (CM) should close the remainder of that first invoice. Finally, the last PY within the group should close the SECOND invoice.

The goal is to get the time between the credit memo, and the invoice it was applied to. I know this can be done in SQL via a loop function, but I am not so familiar with the language.

I have created many columns with IF statements, including a net running balance, but have not yet figured out how I would correctly calculate the time between each credit memo and early open invoice, within the clearing group.

I figure a vlookup may work, with conditions set that the invoice needs to be open. However, I would then need to run another column which would effectively close the next invoice per group. Keep in mind that there are many rows to the spreadsheet, and the groups may have as many as 10 credit memos in them.

Any help at all, even "it's not possible" is much appreciated.

 

[Special-K99]

Well I've tried but have just about given up.
My idea was to first locate the CM, then sum all the PYs before the CM
then see if using the sums we can work out which the first INV thats still outstanding.
Unfortunately Im not so sure this can be done easily or quickly with formulas.
I reckon this would be a VBA job but Im no expert at that.

Hope this helps and you manage to solve it, good luck.

 

[halpwr]

Dear Special-K99,

Thanks for trying!

I have reached a point where I have logic that says: IF the item is a Credit Memo, THEN perform a VLOOKUP to find the latest open invoice. (What's open or closed depends on the running balance) This actually works for only the first Credit Memo.

However, it's tough to make it work for the second and third Credit Memo. What I need to is have some way (with more columns, probably) to close that Invoice which was once open, and have the next vlookup SKIP that invoice.

If anyone knows how to create a VLOOKUP with multiple criteria, that is probably the answer. I looked online and most blogs say to use a combination of INDEX and MATCH functions, but these don't seem to be working.

Any help at all is appreciated!

 

[jc.021286]

Hey there,

I was thinking of using 1 of 2 approaches.
Either you generate a table using an if statement with index(small()) this will allow you to pick the first instance of "INV" and then the second, and then the third, etc. your if statement would drive whether the summation of the indexed amounts would give you what you require, this would give you a list of invoices and would continue to list PY and CM until the balance was covered.

Alternatively, I tried subbing this into a pivot table for the following results.


do either of these two options seem feasible?
jc

 

[jc.021286]

the other approach using the indexes will do exactly as you said, however errors will show up if there are no more invoices to allocate amounts to. Also, I believe it must start at a 0 balance with a leading invoice to start plugging amounts against.









The 2nd amount column is the same as the previous column, just change the index column from 2 to a 3.
The last column that identifies PY or CM is simply the last code moved over and change the 3 for the index column to 1.

Uploaded with ImageShack.us

How does that look?
jc

 

[jc.021286]

woops,

for the 2nd set of columns, change the count from the "G" to a "J" and it will work until you run out of invoices to match against.

jc

 

[mancubus]

sounds like FIFO in accounting.
you may search for a FIFO UDF.

deleted code. stg wrong with it, i think. sorry..

 

[halpwr]

Guys, thanks for the responses. After much thought, I found the best way was to add many columns.

If anyone cares, the columns I added were "First Open INV" (meaning just give me the first open invoice within each group) - this was done using a combination of two other columns, one that would tally up all invoices per group, and another that would give me the first.

The second set of columns gave me the first negative in a group, mostly the same way. And the third set of columns was a combination of two vlookups to find the dates.

The problem is this gives me only one "round" of closing out invoices, this needs to be done many more times, causing my spreadsheet to need as many as 50 columns.

If anyone wants to look at the file PM me.

Thanks for everyone's help!