Allocate payments
 

I am breaking an unwritten rule by re-asking a question but the first time
was late on Friday and has become lost and ignored over the weekend. At
least that's what I hope as I'm sure there is a solution albeit a long
question.

I have a list of unpaid invoices in two columns.
In A is the original value. In B is the outstanding amount.
Normally payments would be allocated to the oldest debt at the top and
invoices would be zeroed in B as the payments progressed. Zero invoices drop
off.
As often happens, some payments have been allocated to the wrong invoices
and although the overall debt position is the same I cannot use the data in
column B to supply an accurate statement of what is due.
Whilst it is easy to identify where the payments are and how much has been
paid in a separate col I would like to be able to use a formula to show the
position as it should be. As col A still has the original debt and each
invoice appears in it's own row it is not difficult to track down the
payments and the total value. A1 -B1 = payment made or nil.
The hard bit is to have Excel deduct the total of the wrongly allocated
amounts from the old debts until that total is used up.
Simple example
A1 has original 5000.00 and B1 5000.00 bal
B2 has original 5000.00 and B2 2000.00 bal
A3 has original 5000.00 and B3 5000.00 bal
A4 has original 5000.00 and B4 3000.00 bal
etc
The list can be up to 50 invoices.
Here 5000.00 has been wrongly allocated and B1 is still showing as due.
Any ideas?
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Allocate payments
 

Add two columns. One to determine the payment made, the other for the
correct balance. In this example I put some headers in row 1

Original Bal Pmt Correct Bal
5000 5000 0 0
5000 2000 3000 0
5000 1000 4000 3000
5000 1000 4000 5000
5000 4000 1000 5000

Ciolumn C (Pmt) is of course easy =A1-B1 and copy down

Column D is a little trickier

D2 has a unique formula =A2-(MIN(A2,SUM($C$2:$C$6)))
This one is fairly simple being the first item subtract the smaller of
either the total or the sum of all Pmts.

D3 gets tricky =A3-MIN(A3,SUM($C$2:$C$6)-MAX(0,SUM($A$2:A2)-SUM($D$2:D2)))
This get copied down.

Again subtracting the smaller of the total or remaining Pmt. The trick here
is the absolute and relative references
Sum($C$2:$C$6) is simply the total of all payments made
Max(0,Sum($a$2:a2)-sum($d$2:D2)) calculates the unapplied portion remaining
of the total payments. The max(0, is needed because eventually this formula
turns negative.

Sum($A$2:A2) totals all of the original invoice values above the row in
question. Sum($D$2:D2) subtracts any correct reamining balance from this.

Subtracting this from the Sum($c$2:c2) yields the unaaplied portion of the
payments.
--
If this helps, please remember to click yes.


"Russell Dawson" wrote:

I am breaking an unwritten rule by re-asking a question but the first time
was late on Friday and has become lost and ignored over the weekend. At
least that's what I hope as I'm sure there is a solution albeit a long
question.

I have a list of unpaid invoices in two columns.
In A is the original value. In B is the outstanding amount.
Normally payments would be allocated to the oldest debt at the top and
invoices would be zeroed in B as the payments progressed. Zero invoices drop
off.
As often happens, some payments have been allocated to the wrong invoices
and although the overall debt position is the same I cannot use the data in
column B to supply an accurate statement of what is due.
Whilst it is easy to identify where the payments are and how much has been
paid in a separate col I would like to be able to use a formula to show the
position as it should be. As col A still has the original debt and each
invoice appears in it's own row it is not difficult to track down the
payments and the total value. A1 -B1 = payment made or nil.
The hard bit is to have Excel deduct the total of the wrongly allocated
amounts from the old debts until that total is used up.
Simple example
A1 has original 5000.00 and B1 5000.00 bal
B2 has original 5000.00 and B2 2000.00 bal
A3 has original 5000.00 and B3 5000.00 bal
A4 has original 5000.00 and B4 3000.00 bal
etc
The list can be up to 50 invoices.
Here 5000.00 has been wrongly allocated and B1 is still showing as due.
Any ideas?
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

Allocate payments
 

Paul

Thanks for that. It's not working for me. I'd done the calc manually and
doesn't match. I did make a mistake in my original posting - I put B2
instead of A2 in my example but you would have seen that.
It's hard to describe. You have an extra row in the basic example with
different figures.
Is there anywhere that I can post the original data- with manual result.
I've used Google docs previously but some prefer not to.

Regards

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Paul C" wrote:

Add two columns. One to determine the payment made, the other for the
correct balance. In this example I put some headers in row 1

Original Bal Pmt Correct Bal
5000 5000 0 0
5000 2000 3000 0
5000 1000 4000 3000
5000 1000 4000 5000
5000 4000 1000 5000

Ciolumn C (Pmt) is of course easy =A1-B1 and copy down

Column D is a little trickier

D2 has a unique formula =A2-(MIN(A2,SUM($C$2:$C$6)))
This one is fairly simple being the first item subtract the smaller of
either the total or the sum of all Pmts.

D3 gets tricky =A3-MIN(A3,SUM($C$2:$C$6)-MAX(0,SUM($A$2:A2)-SUM($D$2:D2)))
This get copied down.

Again subtracting the smaller of the total or remaining Pmt. The trick here
is the absolute and relative references
Sum($C$2:$C$6) is simply the total of all payments made
Max(0,Sum($a$2:a2)-sum($d$2:D2)) calculates the unapplied portion remaining
of the total payments. The max(0, is needed because eventually this formula
turns negative.

Sum($A$2:A2) totals all of the original invoice values above the row in
question. Sum($D$2:D2) subtracts any correct reamining balance from this.

Subtracting this from the Sum($c$2:c2) yields the unaaplied portion of the
payments.
--
If this helps, please remember to click yes.


"Russell Dawson" wrote:

I am breaking an unwritten rule by re-asking a question but the first time
was late on Friday and has become lost and ignored over the weekend. At
least that's what I hope as I'm sure there is a solution albeit a long
question.

I have a list of unpaid invoices in two columns.
In A is the original value. In B is the outstanding amount.
Normally payments would be allocated to the oldest debt at the top and
invoices would be zeroed in B as the payments progressed. Zero invoices drop
off.
As often happens, some payments have been allocated to the wrong invoices
and although the overall debt position is the same I cannot use the data in
column B to supply an accurate statement of what is due.
Whilst it is easy to identify where the payments are and how much has been
paid in a separate col I would like to be able to use a formula to show the
position as it should be. As col A still has the original debt and each
invoice appears in it's own row it is not difficult to track down the
payments and the total value. A1 -B1 = payment made or nil.
The hard bit is to have Excel deduct the total of the wrongly allocated
amounts from the old debts until that total is used up.
Simple example
A1 has original 5000.00 and B1 5000.00 bal
B2 has original 5000.00 and B2 2000.00 bal
A3 has original 5000.00 and B3 5000.00 bal
A4 has original 5000.00 and B4 3000.00 bal
etc
The list can be up to 50 invoices.
Here 5000.00 has been wrongly allocated and B1 is still showing as due.
Any ideas?
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.