Calculating Payment Frequency as a Predictor of Bankruptcy

[Tarver]

I've got an interesting challenge that I'm not sure how best to tackle.

I'm looking for a possible predictor to a client being unable to pay their invoices. It's not uncommon (unfortunately) for a client to be in financial straits, to order a lot of new goods to sell, then to declare bankruptcy. I'd like to be able to predict when that's going to happen by changes in their payment history.

I've got the following tables to work from:
Payment dates and Invoices

AR_model_data (002).xlsx
	A	B	C	D
1	Account No	Invoice No	Document type	Posting Date
2	1003541	A3254	Payment	3/2/2021
3	1018685	A15607	Payment	3/2/2021
4	1013827	A10562	Payment	3/2/2021
5	1014488	A15669	Payment	3/2/2021
6	1005150	A18078	Payment	3/2/2021
7	1013566	A17385	Payment	3/2/2021
8	1005324	A16178	Payment	3/2/2021
9	1008316	A16602	Payment	3/2/2021
10	1013407	A13859	Payment	3/2/2021
11	1014782	A15107	Payment	3/2/2021
12	1003313	A18269	Payment	3/2/2021
13	1006219	A19335	Payment	3/2/2021
Payment dates

Sales Orders and Amounts

AR_model_data (002).xlsx
	A	B	C	D	E	F	G
1	Account no	Payment Terms Code	Responsibility Center	Posting date	Document Type	Invoice No	Amount
2	1005694	CC3	FIELD	3/12/2021	SI	A11433	75
3	1000734	NET30	INTERNET	3/1/2021	SI	A17584	38.64
4	1012441	CC3	FIELD	3/1/2021	SI	A17814	2043
5	1000734	NET30	INTERNET	3/1/2021	SI	A17245	38.64
6	1000734	NET30	INTERNET	3/1/2021	SI	A19422	77.28
7	1000734	NET30	INTERNET	3/1/2021	SI	A19890	77.28
8	1000734	NET30	INTERNET	3/1/2021	SI	A17727	38.64
9	1000734	NET30	INTERNET	3/1/2021	SI	A15354	38.64
10	1000734	NET30	INTERNET	3/1/2021	SI	A18907	38.64
Sales transactions

What strategy would you use to match invoices and payments to then determine a payment frequency?

Initially I thought I'd run a simple SUMIFS formula to calculate the invoices and the outstanding balance, but that really won't give me the frequency of payment. I may get a regular payment (in some cases) as often as weekly, but usually it's a monthly payment. When payments start coming in at intervals that aren't in line with historical payments, that's what I want to flag and investigate. I'm looking to do this analysis for about 15,000 customers.

Any thoughts on how best to accomplish this?

 

[JamesCanale]

I'm not sure about your sample data - so I made up some simple data - just a list of when you get paid from different accounts.

In D1, the number of days prior to TODAY that separates "historic" for "newer payments". So column D is a list of all the accounts, and column E shows the average days between payments for recent ones, and F is average days for older payments.

I can't say how this would perform for 15,000 customers and hundreds of thousands of records.

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G
1	Account	Payment Date		120
2	1	12/7/2021			Recent Payments (average days between payments)	Older Payments (average days between payments)	Change
3	2	12/18/2021		1	18.0	36.0	18.0
4	3	12/24/2021		2	14.5	34.5	20.0
5	2	1/7/2022		3	18.4	54.5	36.1
6	1	1/14/2022		4	118.0	22.8	-95.2
7	1	1/23/2022
8	4	1/28/2022
9	1	2/12/2022
10	2	2/25/2022
11	4	3/12/2022
12	3	3/26/2022
13	4	4/1/2022
14	3	4/12/2022
15	4	4/17/2022
16	1	4/30/2022
17	4	5/10/2022
18	4	5/22/2022
19	4	6/1/2022
20	3	6/10/2022
21	3	6/17/2022
22	3	7/1/2022
23	3	7/16/2022
24	3	7/25/2022
25	2	8/7/2022
26	2	8/18/2022
27	1	8/29/2022
28	2	9/5/2022
29	3	9/10/2022
30	1	9/16/2022
31	4	9/27/2022
Sheet10

Cell Formulas
Range	Formula
A2:A31	A2	=RANDARRAY(30,1,1,4,TRUE)
D3:D6	D3	=SORT(UNIQUE(A2:A31))
E3:E6	E3	=LET(a,FILTER($B$2:$B$31,($A$2:$A$31=D3)*($B$2:$B$31>TODAY()-$D$1)),mn,MIN(a),mx,MAX(a),c,ROWS(a),(mx-mn)/(c-1))
F3:F6	F3	=LET(a,FILTER($B$2:$B$31,($A$2:$A$31=D3)*($B$2:$B$31<=TODAY()-$D$1)),mn,MIN(a),mx,MAX(a),c,ROWS(a),(mx-mn)/(c-1))
G3:G6	G3	=F3-E3
B2:B30	B2	=B3-RANDBETWEEN(5,15)
Dynamic array formulas.