One of our GP clients needed a breakdown of a bank transaction file. They started their company using Quicken a few years ago. Now they have grown to doing more than a million credit card sales transactions per year. Quicken is exploding. Their one bookkeeper is working 70-80 hours per week. They can't accurately analyze the 47 different CC merchants, the 5 companies, and the 50 bank accounts. They are moving to Dynamics GP.
Although GP has it's own Electronic Banking module, I felt it wasn't flexible enough to handle the specialized requirements of this fast-growing company. The COO stressed analysis as his major requirement. Enter Scribe.
All banks are able to export a file in Quicken or QuickBooks format. I used a cheap file converter to take the same file they import to Quicken and convert it to a comma separated value, text, file. Then I created a Scribe Data Translation Spec file, by mapping the fields in the bank import text file to matching fields in GP 2013. Scribe has written adapters for Microsoft Dynamics GP, Dynamics CRM, Sales Force, SAP, Epicor, Sage, InsideView, Marketo, etc...
The great thing about the Scribe adapters is that they follow the API's of the target systems. They don't break datatype rules, and they do provide much of the housekeeping that would otherwise have to be done manually. For instance, Scribe generates the next unique number for the transactions; creates the batches; validates the Journal Entries, and so on.
I mapped a bank transaction for just the detail record and header. Scribe created both sides of the transaction (the cash hit and the distribution: sales, refunds, COGs...). It also created the journal entry. Yes, you need to write some VBA-like phrases to do some lookups or formats, but reading the manual and common sense gets you the result you need. Here are the steps I followed and the resulting GL distributions.
Here is the raw, bank import, file:
Here is the Scribe Workbench, mapping, screen:
We started with a checkbook balance of zero before the import.
After the import the bank deposit is ready for posting.
Before posting the GL journal entry, I printed the GL Edit List to see if it was correct.
Here is a check register for this account - after import.
Here is what the GL Detail looks like after posting.
Finally, here is the Summary Trial Balance, showing the distribution of the cash from the bank file just imported.
This was just a demo, using a small dataset. But it shows how you can automate a cash distribution based on a complex, logical, pattern.