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.
Last edited by hovis; 01/13/11 07:27 PM.
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.