Forensic Accounting Formula Help Desperately Needed

I have a forensic spreadsheet which I need to use in my investigation work with elderly persons. I did not create it and thus am totally perplexed at the formulas and unfortunately, now that I have screwed up my original, I cannot get it fixed. Can someone please, please help me by looking at this and figure out what the problem (s) are?

I highlighted 3 examples on the Transactions worksheet where it should be carrying that amount over to the amount on the Accounting spreadsheet, but is not. I'm wondering if it is the conditional formatting that I screwed up or simply that I have the date inputted wrong at the top of the Accounting worksheet or now it won't work in the formula. This bank does not have months that cleanly run form the 1st to the 31st, but rather they are mid-month to mid-month. What is weird is that some of the data is carrying over, but not others.

I uploaded the speadsheet and added two screenshots of what I am referring to. This is so critical in my work, so I would be incredibly grateful to anyone taking the time to look through this and give me their ideas. Thank you again and take care. -Jessie

one thing I notices is that your column names don't match up. In Accounting tab it says "Victim ACH Vain Life Insurance" and in the transactions tab it says "Victim Vain Life Insurance".

The same thing is happening with your blue highlighted problem.

If you change the accounting tab so that the names match, number will pop up. hope that helps

Hi Hal,

Okay, I've worked out where your problems are...

1) 12/13/11 1365 The Category values (used in matching) don't agree between Accounting and Transactions (grouping problem)
2) Vain Life Insurance (Repaired in updated sheet attached) In Accounting you had mis-typed the Category -> mismatch problem now resolved
3) 06/08/12 1434 The date in Transactions fell outside the date-range in Accounting (see note below)

NOTES on Dates:

The Date fields in the Accounting sheet have been changed to reflect your comment on mid-month to mid-month and the formula logic is wrong.
The Start Date of the Range = the value as entered in Row 6 (somewhere around the 10th)
The End Date of the Range = EOMONTH(R6 value) which will be 28/29/30/31 depending on month & year
Any date after EOMONTH and before next Start Date will vanish into a black hole

At a guess you had to make the changes to implement the mid-month to mid-month cycle as the original author had probably used logic where the entered values were always the 1st day of the month (i.e. Start Date) and the End Date was calculated due to its being variable.

Your changes to the entered dates shifted the Start Date out but the End Dates remained unchanged, thereby leaving a gap - you're unlucky that few entries fell into the gap otherwise you'd probably have picked it up sooner.

What I would suggest as a fix for the dates is enter into each of the Start Dates, the actual Start dates from Dec to Dec (i.e. 13 months) and then use the following logic for the two dates:

1) Start Date = Column Heading Value (same column) eg: G6
2) End Date = Column Heading Value of Next Column minus 1 eg H6-1

I hope that helps...

Dave

Updated Workbook:

Financial Accounting-Checking 2012 Problems - DdeV.xlsx

Hi Jessie aka Hal,

Another tip to prevent category mismatches:

The workbook contains a table of pre-defined standard values for Category (on third worksheet) named "Categories" (check with Name Manager)

For the Accounting worksheet, you need use Data Validation on all Category entries:

Select each Category array, Click on Data tab, click on Data Validation, select "List", in Value column type the following:

=Categories

Click on OK.

When completing Accounting worksheet, when you click on the cell where you need a Categroy, you will be presented with a Drop-Down selector. Click on the selector and scroll until you find the value you need, click on it and the value will be populated into the cell.

The Transactions worksheet already uses this approach and using the same source for two matching arrays will eliminate any possibility of typos giving rise to match failures.

Have fun...

Dave

Hi Jessie,

Some background as to why the bank's month cycles are offset from the calendar month...

The process in banking that has the highest volume of work is statement rendition as it requires the printing of a statement for each and every client and then all of the checks issued by all the clients need to be sorted into account number/serial number sequence so that the checks for each account can be inserted along with the appropriate statement into an envelope for posting or collection.

If all customers received their statements at month-end, then the peak for one day would be enormous - demanding massive capacity in terms of printing statements and for the sorting of the cheques. This massive capacity would then stand idle for the balance of the month, giving rise to serious asset under-utilization problems.

To get around this problem, banks spread the statement load across a month by allocating each client a different statement cycle number (equivalent to the Last Date in your workbook). The next Start Date therefore becomes cycle number plus 1. If the bank's cycle number allocation is effective, then each day would see approximately only 1/30th of the total volume, and the capacity requirements for printing and sorting would be reduced accordingly.

So, your example and the dates used would be customer-specific and could vary from one customer to another and from one calendar year to another as, in some instances, banks adjust their cycle dates to avoid any cycle ending on a Sunday or a Public Holiday (banks are averse to overtime).

Be prepared to maintain those date fields from one case to the next...

I hope that helps.

Dave