Calculating weighted average collection days for each invoice on FIFO basis

[Arslan Shahid]

Dear Excel/VBA Experts,

I have downloaded a very useful VBA Macro to assign payment received to Invoices using FIFO method. However, i aould be really really grateful if someone can modify the vba code to also calculate weighted average payment days for each invoice ? e.g. if an invoice issued on 1st jan is fully cleared on 31st jan (this is already modeled in vba in the attached file) so total days will be 31, but if customer makes partial payments e.g. 50% payment on 15th jan and 50% on 31 jan, than weighted average days will be 23 days approx.

I would be really grateful if someone can modify this code to include this aspect also. Hoping for a positive and quick response, fingers crossed

Best regards

 

[Hui]

Can you please provide a worked example using some data from the workbook?
Are all the invoices meant to be numbered 1?

 

[Arslan Shahid]

Can you please provide a worked example using some data from the workbook?
Are all the invoices meant to be numbered 1?

Dear Hui,

Invoice numbering is not important. The example is follows:

Invoice
1st Jan Invoice 100,000

Payment
15th Jan 50,000
31st Jan 50,000

Given these, the macro in the attached file will given invoice clearance date as 31st Jan. Actual invoice clearance days will be 31 days. Now i want weighted average days to be calculated which should be as follows:

50% payment related to the invoice received on 15th Jan- 15 days x 50%
50% payment related to the invoice received on 31st Jan- 30 days x 50%
100% payment weighted average collection days will be = 22.5 Days

I hope this helps and looking forward for a potential solution as really stuck up. Really appreciate you taking time out for the same.

Best Regards

 

[AliGW]

Cross posted here: https://www.excelforum.com/excel-pr...tion-days-for-each-invoice-on-fifo-basis.html