A semi-advanced matching problem which may be solved with formulization or VBA

The Godfather

New Member
Joined
Jul 22, 2011
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear excel masters,

I have a task to match all vendor invoices with their related payments with FIFO logic. However I cannot build the model in excel and need assistance.

As you can see in data chunk (or screenshot); there are 3 clearing items in data (in my actual data more than 5.000) as seen in B column.

- In clearing 111111; we see 5.000 USD invoice as of 11.11.2022 is paid with 5.000 USD on 30.12.2022.
- However not every clearing consists up of only one invoice and one payment. Such a in clearing 3332222; we see 3 different invoices, where invoice no.s 17 and 18 (total 10 K USD) is paid with payment 32. And Invoice 19 is fully paid with payment 36.
- Sometimes invoice and payments are not directly matched in amount, and some portion of invoices might not have been paid yet. In clearing 111122, we see that,
+ payment 131 (30 K USD) fully pays invoice 112 (15 K USD), and then pays 15 K portion of invoice 114.
+ for invoice 114,
the remaining 5 K USD amount is paid by payment 134.
+ With FIFO method; when all invoices and payments are matched; we still see that invoice 131 have a 5 K USD portion which have not yet been paid.

What I am trying to do?
For each invoice; I am trying to obtain what part of amount is paid as of which date; and to see if there is any remaining invoice amount not yet been paid.
How can I construct a model which solves this?

Thanks in advance.

VendorClearing Reference NoDocument NoDocument TypeDocument DateAmount in USDPayment Matching AmountPayment Matching DateComments
SSSS GmbH11111115Invoice
11.11.2022​
5.000​
5.000​
30.12.2022​
fully paid by document 25
SSSS GmbH11111125Payment
30.12.2022​
-5.000​
XYZ Ltd.333222217Invoice
12.12.2022​
4.000​
4.000​
20.12.2022​
fully paid by document 32
XYZ Ltd.333222218Invoice
13.12.2022​
6.000​
6.000​
20.12.2022​
fully paid by document 32
XYZ Ltd.333222219Invoice
14.12.2022​
2.000​
2.000​
25.12.2022​
fully paid by document 36
XYZ Ltd.333222232Payment
20.12.2022​
-10.000​
XYZ Ltd.333222236Payment
25.12.2022​
-2.000​
ABC Company111122112Invoice
24.10.2022​
15.000​
15.000​
29.11.2022​
fully paid by document 131
ABC Company111122114Invoice
24.10.2022​
20.000​
15.000​
29.11.2022​
15.000 portion paid by document 131
5.000​
01.12.2022​
remaining 5.000 portion paid by document 134
ABC Company111122117Invoice
24.10.2022​
12.000​
12.000​
01.12.2022​
fully paid by document 134
ABC Company111122118Invoice
24.10.2022​
9.000​
9.000​
01.12.2022​
fully paid by document 134
ABC Company111122119Invoice
24.10.2022​
13.000​
13.000​
01.12.2022​
fully paid by document 134
ABC Company111122121Invoice
30.11.2022​
11.000​
6.000​
01.12.2022​
6.000 USD portion paid by document 134. Remaining 5.000 USD portion has not yet been paid.
ABC Company111122131Payment
29.11.2022​
-30.000​
ABC Company111122134Payment
01.12.2022​
-45.000​


EDIT:
I would like to renew my purpose;
I am just trying to automatically fill the two columns below;
- Payment Matching Amount
- Payment Matching Date

(Also as seen in invoice document 114; sometimes the information may be in more than one line since the invoice is paid with different payments).

I am not interested to find remaining invoice amount that have not yet been filled.
 

Attachments

  • image_2024-04-25_103006743.png
    image_2024-04-25_103006743.png
    52.5 KB · Views: 11
Last edited by a moderator:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Let me paraphrase the issue in a more clear way;

Invoices table
ClearingDocumentDocumentNoDocTypeDocDateAmount
1111Invoice24.10.202225.000
1112Invoice25.10.202225.000
1113Invoice26.10.202225.000
1114Invoice27.10.202225.000
22213Invoice27.10.20227.500

Payments table
ClearingDocumentDocumentNoDocTypeDocDateAmount
1117Payment01.01.202350.000
1118Payment01.02.202330.000
1119Payment01.03.202330.000
22215Payment02.03.20237.500


And the desired outcome is to obtain which portion of invoice is paid as of which date. Like this;

ClearingDocumentDocumentNoDocTypeDocDateAmountInvoiceAmountPaidPaymentDatePaymentDocNo
1111Invoice24.10.202225.00025.00001.01.20237
1112Invoice25.10.202225.00025.00001.01.20237
1113Invoice26.10.202225.00025.00001.02.20238
1114Invoice27.10.202225.0005.00001.02.20238
1114Invoice27.10.202225.00020.00001.03.20239
22213Invoice27.10.20227.5007.50002.03.202315

As you may see in the desired outcome,

1) For clearing no. 222; since there is only one invoice and there is only one payment which are matching in amount (7.500); the result is so smooth.
2) For clearing no. 111; it is a bit more complicated.
a) Invoice 1 and 2 (each 25 K) was paid with payment 7 (50 K). In the desired outcome we see that half of payment 7 (25 K) is attribute to invoice 1; and the other half is attributed to invoice 2.
b) As per FIFO, Payment 8 (30 K) first pays invoice 3 (25 K), and then pays the 5K portion of invoice 4. The remaining of invoice 4 is paid by payment 9.

So basicly; for under each clearing item and based on FIFO; I am trying to match invoices and payments according to the two rules I mentioned above.
It does not matter if the total amount of invoices and total amount of payments under the same clearing chunk is not the same (ie; does not result in 0). The code should run until either total invoice or total payment amount in that chunk runs to 0.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top