Thread Options
#1494348 - 01/12/11 11:05 PM Formula for Kiting?
WonderWoman Offline
Diamond Poster
WonderWoman
Joined: Mar 2007
Posts: 2,108
gone fishin'
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.

Return to Top
BSA/AML/CIP/OFAC Forum
#1494372 - 01/13/11 03:46 AM Re: Formula for Kiting? WonderWoman
rlcarey Offline
10K Club
rlcarey
Joined: Jul 2001
Posts: 77,310
Galveston, TX
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?
_________________________
The opinions expressed here should not be construed to be those of my employer: PPDocs.com

Return to Top
#1494869 - 01/13/11 07:26 PM Re: Formula for Kiting? rlcarey
Pat Patriot Act Offline
Gold Star
Pat Patriot Act
Joined: Apr 2009
Posts: 450
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.
Last edited by hovis; 01/13/11 07:27 PM.
_________________________
CFE, CAMS

Return to Top
#1495045 - 01/13/11 09:41 PM Re: Formula for Kiting? Pat Patriot Act
kw004h Offline
100 Club
Joined: Nov 2009
Posts: 219
Chicagoland, IL
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!

Return to Top
#1495072 - 01/13/11 10:09 PM Re: Formula for Kiting? Pat Patriot Act
Elwood P. Dowd Offline
10K Club
Elwood P. Dowd
Joined: Aug 2001
Posts: 21,939
Next to Harvey
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.
_________________________
In this world you must be oh so smart or oh so pleasant. Well, for years I was smart. I recommend pleasant.

Return to Top
#1495632 - 01/14/11 06:17 PM Re: Formula for Kiting? Elwood P. Dowd
madukes Offline
Diamond Poster
madukes
Joined: Jul 2009
Posts: 1,448
Flyers Country
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) smile

Return to Top
#1495772 - 01/14/11 07:50 PM Re: Formula for Kiting? madukes
Pat Patriot Act Offline
Gold Star
Pat Patriot Act
Joined: Apr 2009
Posts: 450
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
Last edited by hovis; 01/14/11 09:05 PM.
_________________________
CFE, CAMS

Return to Top
#1495803 - 01/14/11 08:07 PM Re: Formula for Kiting? Pat Patriot Act
madukes Offline
Diamond Poster
madukes
Joined: Jul 2009
Posts: 1,448
Flyers Country
thank you!

Return to Top
#1496022 - 01/15/11 01:29 AM Re: Formula for Kiting? Elwood P. Dowd
WonderWoman Offline
Diamond Poster
WonderWoman
Joined: Mar 2007
Posts: 2,108
gone fishin'
Originally Posted By: Ken_Pegasus
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.


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.
_________________________
My opinions are my own, and not that of my employer.

Return to Top
#1496035 - 01/15/11 12:15 PM Re: Formula for Kiting? Pat Patriot Act
Elwood P. Dowd Offline
10K Club
Elwood P. Dowd
Joined: Aug 2001
Posts: 21,939
Next to Harvey
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.
_________________________
In this world you must be oh so smart or oh so pleasant. Well, for years I was smart. I recommend pleasant.

Return to Top

Moderator:  Andy_Z