Forum Discussion
Jalal_1988
Dec 17, 2023Copper Contributor
Costumer payments velocity in one day in percntage
I have an invoice for a customer to whom we sell on credit. In one column, the amount paid by the customer is specified, and in another column, the sales amount is specified. In one column, there is...
- 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.
PeterBartholomew1
Dec 17, 2023Silver Contributor
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.
Jalal_1988
Dec 23, 2023Copper Contributor
No problem for the payment in first time more than its purchase,
i want to find an index for my costumer to know which costumers doing their payments in shortest time to classify the costumers
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) )