Forum Discussion
Costumer payments velocity in one day in percntage
- Dec 17, 2023
If I had a clear description of the calculation required, illustrated with step-by-step hand calculations, I could probably reproduce the results using MS 365. As things stand, I do not understand the meaning of the term 'payment velocity'; I am not sure why the customer started out by paying so much more than was invoiced; I don't know whether the fact that at the end transactions do not appear in date order.
The sort of things I can do are to calculate the balance and identify points at which it falls to zero; calculate the average balance for each reconciliation period, similarly average the selling amount. For all that, I do not know what you want returned by way of calculations.
If I had a clear description of the calculation required, illustrated with step-by-step hand calculations, I could probably reproduce the results using MS 365. As things stand, I do not understand the meaning of the term 'payment velocity'; I am not sure why the customer started out by paying so much more than was invoiced; I don't know whether the fact that at the end transactions do not appear in date order.
The sort of things I can do are to calculate the balance and identify points at which it falls to zero; calculate the average balance for each reconciliation period, similarly average the selling amount. For all that, I do not know what you want returned by way of calculations.
i want to find an index for my costumer to know which costumers doing their payments in shortest time to classify the costumers
- PeterBartholomew1Dec 28, 2023Silver Contributor
The attached works out the average delay between invoice and payment.
It is a 365 solution that is presented as a Lambda function.
= LET( cumSales, SCAN(0, amount, ADDλ), cumPmnts, SCAN(0, payment, ADDλ), dayCount, date - MIN(date), mmtTransact, SCAN(0, dayCount*(payment - amount), ADDλ), balance, cumSales - cumPmnts, reconciled?, ABS(balance)<1, accumulations, FILTER(HSTACK(mmtTransact, cumSales), reconciled?), blockedAcc, accumulations - DROP(VSTACK({0,0},accumulations), -1), keyDates, FILTER(date, reconciled?), avPmntPeriod, TAKE(blockedAcc,,1) / TAKE(blockedAcc,,-1), HSTACK(keyDates, avPmntPeriod) )