I have a revolving credit system. A customer purchases and then has 6 months to pay off the purchase. i.e. Purchase for 60 in Jan and then need to pay 10 a month in Feb, Mar, Apr, May, Jun, Jul.
The aging of account is kept as current, 30 days, 60 days, 90 days, 120 days and 150 days +.
At the beginning of every month, my software takes all the amounts in each period and moves it one period older. e.g. 30 -> 60, 60 -> 90, etc It then takes the pre-calculated amount owing for that month, for each account, and puts it into the current balance of the account.
I obviously have customers re-buying all the time. Some after their original amount has been paid off and some after only a certain amount of the original purchase has been paid off.
What is the best way for me to calculate what the bad debt has been over the past year? (Keeping in mind some accounts are in 90 days and will still get paid and some accounts in current are from new customers that will never get paid.)
I don't think that there is a way to insert a table, so I've pasted the data in CSV format.Here are the sales and payments as well as the totals of the buckets for Jan - Dec 2017:
Month, total,current,30 ,60 ,90 ,120 ,150 ,sales,pay
Jan ,140422628 ,7281546 ,5705615 ,4684113 ,4615482 ,4360849 ,94074615 ,7543717 ,-8568162
Feb ,143324665 ,7362944 ,5574404 ,4526285 ,4042478 ,4216614 ,97382041 ,9993857 ,-8762434
Mar ,145702145 ,7520503 ,5578599 ,4376838 ,3828715 ,3532749 ,99492168 ,11007918 ,-9242764
Apr ,150914995 ,7114145 ,5528774 ,4311449 ,3786774 ,3471839 ,101951718 ,13570121 ,-9948512
May ,153498743 ,7162139 ,5391142 ,4327766 ,3716374 ,3327988 ,103380754 ,11564184 ,-9575771
Jun ,155889045 ,7759287 ,5394046 ,4155481 ,3803228 ,3423377 ,105815682 ,10027996 ,-9265012
Jul ,158453744 ,8371916 ,5709239 ,4029464 ,3512949 ,3337981 ,107431457 ,11896567 ,-10131923
Aug ,161301534 ,8614500 ,6227544 ,4410486 ,3517605 ,3232267 ,109948251 ,10710184 ,-9551682
Sep ,165307654 ,8625254 ,6338409 ,4738558 ,3820820 ,3215644 ,112276387 ,12918026 ,-10564499
Oct ,173744849 ,8966799 ,6374958 ,4789648 ,3982816 ,3338196 ,113804865 ,19517180 ,-11876328
Nov ,180674495 ,9724925 ,7109244 ,4855723 ,4098669 ,3563787 ,115933379 ,16659417 ,-11042206
Dec ,180928310 ,9954910 ,7536676 ,5328046 ,4240225 ,3749598 ,118665330 ,9359356 ,-10942224
The data won't tie up exactly as there are other transactions not included here (such as interest, account charges, etc.)
Please let me know if you need any further data and if so, what data you need.