Invoice Payments and Receivables

[John_356]

Good day to all,

Currently working on an Excel based tool for my company that will automatically check if invoices are paid in due time, without any manual data entries - weekly data will be extracted from our software on XLSX format and will be copied in the said tool which will use macros to power it.

Data source: two extraction files (invoices and payments) with following columns:

Invoices: Customer ID, invoice number, date, amount
Payments: Customer ID, date, amount

I'd like to find a way that checks without any manual data entry whether an invoice has been paid, in full or partially, in due time or not.

Thank you very much in advance for your potential help.

Cheers to all

John

 

[GraH]

How come your payments don't have the invoice number reference? Would make it easier.
Where is the invoice due date?

Else why not simply combine all data in a single table and use a pivot grouping per customer ID? Invoice amount should be negative and payment amounts positive. First analysis "due amount" is basically done.
Furthermore, using a data model, you can easily have a (DAX) measure in place that looks for payment dates between invoice and invoice due date for each customer number.
Since you 365, the full analysis might even be possible with Power Query.

 

[John_356]

Hi GraH,

First of all let me thank you for your swift reply and my apologies for having provided limited information; being new to the forum I understand users are expected to break down their requests, though I might have overdid it. So I'll be providing below more context, which I'll be doing so while answering your questions.

Re on no invoice number: The company I manage is based in the Middle East and they have a different business culture and processes compared to us in the West - when paying for an invoice, a customer wires money to its supplier and it's up to the latter to figure things out and properly keep its books.

Re on due date: That is fixed to 90 days for all customers, there is a term of payment column with 90 all around.

Re on putting everything on a single tab and using PivotTable: Here's a little more context - above mentioned data sets are extracted directly from the (rather unsophisticated) accounting software of the company. I want to build an Excel based tool that the on site manager will be able to use easily, as his IT skills are yet to be honed.

So the handling of data should be simple enough to minimize human error; I'm thinking of having a spreadsheet with three tabs (as there are three extracted files: payments, invoices, balance) and having the manager copy/paste the extracted data on each corresponding tab, click on the macro button and have the tool provide the results such sales performance by product, calculate sales people's commissions, show customer performance of on-time payment, etc... The whole point of this tool being to avoid having manual entries.

In order to make things easier, I've decided to start extractions of the invoice and payment data from December 1st 2020. For customers with an ongoing balance at that date, I'm going to exceptionally copy paste that data on the invoices tab and fill in dummy cells to get the program running.

Full summary of data sheets extracted on Dec 1st 2020 :

Invoices: customer ID, invoice number, invoice date, product name, amount, unit, price, term of payment
Payment: customer ID, payment date, amount, currency
Balance: customer ID, unpaid amount till date of extraction, date of last payment made

Hope I'm not forgetting something here, if any questions please don't hesitate to write me.

Thanks again for your help, much appreciated.

Cheers,

John

 

[John_356]

PS: The spreadsheet must be able to show which invoice has been paid which hasn't for any given customer. Obviously once a payment is made the sheet should deduct the amount from the oldest unpaid invoice.

 

[GraH]

Hi @John_356 ,

First of all, thank you for all the extra information and clarifications. That's really helpful.
I'm convinced a simple and automated solution is possible. (I'm still thinking Power Query/Power Pivot), but that's because I'm wired that way. (I do not do macro/vba).

Currently I have some urgent work to finish and besides the weekends I'm hardly active on the forum since many weeks. So I hope you can have some more patience.

 

[John_356]

Hi @GraH ,

Of course. Also I'm curious about how you'll tackle this as you're seeing it in a different light already.

Many thanks for your kind support !

Cheers

 

[John_356]

Hey @GraH ,

Figured it out by creating a unique ID in payments tab by using the customer ID concatenated with a ranking of my creation based on payment date, and created extra columns on the invoice tab for each payment made and creating a small balance there. From that point on it was all downhill.

Cheers for your input