Bank Deposits in GP

Bank Transactions in GP

Setting up a Checkbook in GP

Bank Reconciliation in Dynamics GP - Getting Started

The Bank Reconciliation module in GP is very simple. Like most things, the basics are straight forward but the devil, as they say, is in the details.

To get started you need to setup a checkbook. Checkbook is GP’s term for a bank account. The Checkbook is linked to a unique GL account and should be used anywhere that bank account and therefor the GL account needs to be used. I will get into more details about setting up the Checkbook in a video post later.

If you have to create a Checkbook for a new bank account just as you open the bank account, it would be simple. You can setup all the required information and start off with a zero balance. Your first deposit would be your first transaction and you simply recreate every activity that happens against your bank account against the checkbook going forward. In this scenario, the first slightly complicated step would be when you do your first bank reconciliation, typically at the end of the first period.

But in most real life scenario’s, you are probably looking to setup a checkbook for an existing bank account. And that is where things get a little complicated. But if we breakdown the individual components we need, then you will realize that it is not as complicated as it might first appear.

There are only two things you need to set up a checkbook for an existing bank account. We are assuming here that you are not concerned with the historical activity on the bank account.

1.       Bank statement ending balance. You should always start with the ending balance from a bank statement. Obviously, this means that you don’t want to start reconciliation in the middle of a period.

2.       List of outstanding transactions. These are the transactions that have posted to your financial books but have not yet cleared the bank.

If you add/subtract all the outstanding transactions to the statement balance, you should have the account balance of the GL account that is associated to the Checkbook.

We will explore and demonstrate the various aspects of the Bank Reconciliation module in GP with video posts on the following sub topics.

·         Setting up a Checkbook

·         Different types of Bank Transactions & Transfers

·         Bank Deposits

·         Bank Reconciliation process

Using IM to Import AP Transactions (Advanced ODBC)

Today, in part two of our AP integrations through IM, I will show you how just using one feature of IM (Advanced ODBC), we can avoid a lot of manual work and make the process  a lot simpler and faster.

SQL Code Used in Header:
SELECT BatchID, VendorID, DocumentDate, Description, sum(distributionamount) as amount, left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8) as DocNumfrom AP2.csv

group by BatchID, VendorID, DocumentDate, Description,left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)
order by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)

SQL Code Used in Detail:
select DocNum, Amount, ExpenseAccount,DistributionType, DistributionReference
from
(select left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8) as DocNum, distributionamount as amount, expenseaccount, '6' as distributiontype, distributionreference
from ap2.csv
union all
select left(expenseaccount,3)+'-'+BatchID+'-'+left(VendorID,8), -sum(distributionamount), left(expenseaccount, 3)+'-2000-00', '2', ''
from ap2.csv
group by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8),left(expenseaccount, 3)+'-2000-00') GAPD
order by DocNum by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)

Using IM to import AP Transactions (Simple Method)

A lot of times, we get expense reports and credit card statement files that need to be entered in GP as payables transactions. Integration Manager is a great tool to do this. There are several ways that we can accomplish this. I would like to share a couple of them.

Since writing out the instructions for this would take several pages and one of the rules I have setup for myself is not to have posts that are too long, I am going to venture into my first video blog post today.

This video explains in detail a simple way to use IM to import AP transactions. All the information needed is passed from the files. No additional features of IM are used. This method is labor intensive but requires less technical expertise.

Things to know before installing a new GP client

Every once in a while I have to install a GP client in a new workstation.  This is the kind of customer who has a very simple setup and I don’t hear from for a while. That means I don’t know much about their GP environment, including their version, the products that are being used and if they have any customized forms or reports. In such a situation, this is what I usually do.

Before starting the new client install, I will log onto another workstation where GP is installed and working properly with all the modules and reports. Once logged on, here are the things I will make note of.

GP Version. GP makes it very easy to find out the version information. Just launch GP and on the very first screen where we put in our username and password, we can see the version of GP. Once I find out the version number, I know if I need to apply any service packs. The next step here is to locate the service pack executable somewhere on a network drive or log onto PartnerSource to download the service pack.

Other GP Products. To find out the other GP modules that have been installed, I go to Control Panel >> Add Remove Programs (or Programs and Features) and click on Microsoft Dynamics GP (9.0 or 10.0 or 2010) and then click on the Change button.

On the window that opens, I would click on Add/Remove Features.

On the next window, I would scroll through the window on the left and make note of every product that DOES NOT have a red X mark against it. These are the GP products that have been installed on this workstation.

Modified Reports & Forms.  To see if there are any modified forms or dictionaries, I would log into GP and go to Tools >> Customize >> Customization Maintenance.

If I see any forms or reports listed, I make a note of all the products (last column) and whether reports or forms have been modified (2^nd column).  I will then need to know the location of the reports and forms dictionaries. To find out this location, I would go to Tools >> Setup >> System >> Edit Launch File. Once here I make a note of the location of the necessary dictionaries from each product I made note of on the Customization Maintenance window.

Obviously, we have not addressed any 3^rd party products or customizations. You can start to get an idea about them by looking into the Dynamics.SET file. But that is beyond the scope of this post J.

You can follow any installation documentation to do the GP client install but you will notice that you now have enough information to answer any questions you will have to complete the installation instructions.

Once you have completed the install and logged into GP, you should go back to the Edit Launch File window (last screenshot) and set the paths same as the workstation that you took notes from.