Invoice Payments and Receivables

John_356

New Member
Joined
Jan 17, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 17, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

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
Top