Tracking a customer's credits & debits to determine if kiting.

(I have a strong feeling they are, but management wants proof).

I have everything in excel - is there a formula I can use? Tried and true methods you have used in the past that have helped? Our uncollected funds numbers on our core system are ridiculously unhelpful.

Are there enough collected funds in the account the minute they are writing the checks to cover them and all others outstanding at the time?

Having been in your shoes for dealing with inaccurate core data, I might be able to help. What I've done in the past is build a spreadsheet for the date range of the suspected activity, with the following columns from left to right: date, time, weekday, account #, transaction type (e.g. check, cash, wire), to/from (payees, makers, other accounts, etc.), credit amount (column g), and debit amount (column h). The tough part comes when you re-create the collected balance. I've found it helpful to add three columns: ledger balance, float balance, and collected balance.

Before I do the ledger balance, I double-check to make sure every transaction is present on the spreadsheet, because my figures will be off otherwise. Then, I sort the spreadsheet by date and time ascending (oldest at the top, newest at the bottom) and input the ledger balance from core into the first row. In the row below, I input the formula (=if(g3<>"",i2+g3,i2-h3) then drag it all the way down to the last transaction. This should give you an intra-day ledger balance figure.

Next, I'll manually input float figures by into every row for any given day, into the column labeled "Float" (column j). For instance, if 01/12/2011's transactions consisted of one $4,000 check deposit, three ATM withdrawals, and an outgoing $4,000 check; I would place the float amount - $4,000 - next to all five transactions that occurred.

Lastly, I'll do a simple formula in the uncollected column (column k), "=J2-I2", then drag it all the way down. If they're kiting, this column will have a good amount of negative figures.

Hope that helps.

That is fabulous, hovis.

I'm going to try it on some of my own past kiting spreadsheets and see if it paints a more accurate picture.

Thanks!

I would be inclined to accept rlcarey's standard as evidence that a customer is consistently drawing on uncollected funds. Whether they are kiting or just regularly drawing on uncollected funds, I want them gone. If I determine after the fact it was kiting, then I'll worry about the SAR.

Interesting, when I review for possible kiting I don't look at collected/available balances. I look at the actual items and the timing of the transactions. If customer deposits a check from bank A into his account at our bank (bank b) and the same or next day a check drawn on his account here is deposited back at bank A. Of course, this would have to be a repetitive issue and not just once or twice (there are valid transfers between accounts after all)

Don't get me wrong, I don't get into that much detail unless the transactions pass the sniff test. If they do, then I run the numbers. That way, when SAR time comes along, I can demonstrate the float manipulation. I've had law enforcement tell me they like to see things that way, so I do the best I can to show it.

Here's the type of spreadsheet the FBI uses for their Check Kite Analysis System:

http://findarticles.com/p/articles/mi_m2194/is_n11_v62/ai_15139850/pg_4/?tag=content;col1

thank you!

I agree with you Ken & Rl ... but I'm dealing with management & they don't think like we do.

Also, I believe this customer is using Credit Cards and personal loans to increase the time between float - so it's not as easy as Bank A & Bank B. The cards are going to fall - I'm just trying to prove it in time.

Thank you Hovis - this should help me tremendously since I already have all the data in spreadsheets.

Great, simple resource. Thanks.

If the new SAR includes the Excel compatible spreadsheet as planned, this could be readily implemented. It would have to be supplemented with information regarding the items to prove kiting rather than just drawing on uncollected funds.