(703) 860-6945

How to Set Up Electronic Bank Reconcile in Dynamics GP

How to Set Up Electronic Bank Reconcile in Dynamics GP

Microsoft Dynamics GP Starter or ExtendedSetting up Electronic Bank Reconcile in Microsoft Dynamics GP is incredibly useful to using the software, but clients often have questions on the setup of the function.  Today, we’d like to share with you a step by step guide on how to set up Electronic Bank Reconcile in Dynamics GP.

1. Confirm setup of Checkbook record (Financial>Cards>Checkbook)

  • Confirm that the Bank Account field is populated with the account number (this needs to match the Account Number in the upload file)

Electronic Bank Reconcile Setup 1

 2. Set up the file upload configuration in Configurator (Financial>Routines>Electronic Reconcile>Configurator)

  • To import the Configurator template and replicate the same configuration used for other checkbooks, click on the Import button:

Electronic Bank Reconcile Setup 2

  • Browse to the local C drive of the Accounting RDP Server C:Accounting and select the file “electronicbankrecon_config” and then click Open:

Electronic Bank Reconcile Setup 3

  • Enter the Checkbook ID as the Bank Format ID:

Electronic Bank Reconcile Setup 4

  • Update the Description and the Record Type Code to match the Checkbook ID:

Electronic Bank Reconcile Setup 5

  • Click Save to save the new format:

Electronic Bank Reconcile Setup 6

 3. Prepare the file to upload with the list of cleared transactions from the bank

  • Download the file from the bank to Excel with the following columns:
    • Column A – Date
    • Column B – Type
    • Column C – Reference Number
    • Column D – Description
    • Column E – Debits
    • Column F – Credits
  • Add the following columns to the Excel file:
    • Column G – Transaction Amount (add Columns E and F)
    • Column H – Transaction Code by using the following values based on the transaction type
      • CHK (check)
      • DEP (deposit)
      • DAJ (decrease adjustment)
      • IAJ (increase adjustment)
      • XFR (transfer)
      • Note: DAJ, IAJ, XFR may not be marked as cleared if the reference numbers do not match the transaction numbers in Dynamics GP
    • Column I – Record Type Code – Checkbook ID (must be the same value that is set up in Configurator under Record Type Code)
    • Column J – Account Number (must be the same as the Bank Account field in the Checkbook Maintenance Card)

EBR_7

  • Save the Excel file as a tab delimited text file and save to the local C drive of the Accounting RDS Server: CAccounting with the filename “Company_CheckbookID_Date”, e.g., APF_TCB2734_Jan2015”:

Electronic Bank Reconcile Setup 8

 4. Set up the Download Maintenance window to point to the file (Financial>Routines>Electronic Reconcile>Download Maintenance)

  • Set up the Download Maintenance record for this Checkbook ID with the following details:
    • Bank Download ID – enter the Checkbook ID
    • Bank Format – select the Bank Format ID created in Section II
    • Bank ID –select the Checkbook ID
    • Click on “Add Checkbook” and add the Checkbook ID associated with this setup
    • Download Filename and Path – Browse to the C:Accounting for the file created in Section III
    • Click Save to save changes

Electronic Bank Reconcile Setup 9

 5. Download the transactions to the Bank Reconciliation window (Financial>Routines>Electronic Reconcile>Transactions Download

  • Select The Bank Download ID created in Section IV
  • Click on the Download button to import files from the file designated in the Download Maintenance window

Electronic Bank Reconcile Setup 10

  • The Confirmation window will pop up, and enter a confirmation number and click the Successful button

Electronic Bank Reconcile Setup 11

  • The window will be populated with transactions from the upload file and a report is generated to provide details on transactions with issues or error messages

Electronic Bank Reconcile Setup 12

  • Click the Reconcile button to push the transactions into the Reconcile Bank Statement window

Electronic Bank Reconcile Setup 13

  • The Reconcile Bank Statement window will automatically open
  • Enter the Bank Statement Ending Balance, Bank Statement Ending Date, and Cutoff Date

Electronic Bank Reconcile Setup 14

  • Click on the Transactions button to open the Select Bank Transactions window
  • Note: if message window appears “Are you sure you want to match deposits by amount within the eight day grace period?”, click Yes

Electronic Bank Reconcile Setup 15

  • Transactions that match the transactions listed in the file will be marked as Cleared in the Select Bank Transactions window
  • Complete the reconciliation until the Difference is $0
  • Click Reconcile to reconcile the Checkbook ID

1 Comment

  1. ian 5 months ago

    Hi there – the step;

    •Browse to the local C drive of the Accounting RDP Server C:Accounting and select the file “electronicbankrecon_config” and then click Open:

    does this file exist out of box, or is this a file that has been previously exported?

    i.e. an initial implementation of electronic bank rec has to manually create the file?

    thanks!

Leave a reply

Your email address will not be published. Required fields are marked *

*

GP User? Learn the Secrets to Improved Travel and Expense Management

Is your organization wasting away in manual processes for travel and expense management, spending even more time sending the data back to GP? Learn more about ExpenseConnect for Concur and GP, and just how much time it can save you.

Learn More