V521 and Prior Versions

Setup Required

General Ledger Settings

Before you set up the processes for daily balancing, there is a little set up you need to do in the General Ledger and G/L Integration Points so the Daily Balancing process recognizes the subsidiary G/L accounts to balance.

Go to: General Ledger > Reports > G/L Subsidiary Balancing Report

•    Setup the Subsidiary Balancing Report
•    Save for All Users

Go to: General Ledger > Chart of Accounts > Categories Definition

•    Setup new GL categories in Agility "Sales" & "COGS"

Go to: General Ledger > Chart of Accounts > Account Maintenance

• Change the Category on the Sales account and COGS account.

Go to: System Administration > System Controls > Default System G/L Accounts

•    Assign default GL accounts to the new categories

These settings are needed for the custom Cyberquery that you will run daily to indicate what the balance is in your General Ledger for the G/L Subsidiary Accounts. 

Custom Cyberquery

1. Create a new folder named “GL_Balance_Rpt” under the cq_reports folder.
2. There are 3 Cyberquery files that need to be placed into your Agility Cyberquery folders
   1. Copy and paste these 3 lines (highlighted in red) into a new Cyberquery Macro (in CQ:  File > New Macro) report named: GL_balancing_report.mf

                                             run ../GL_Balance_Rpt/gl_balance_rpt1.eq
                                             run ../GL_Balance_Rpt/gl_balance_rpt2.eq
                                             rreplace gl_balance

1. The file “GL_balancing_report.mf” created in step "a" above needs to be saved (File > Save Macro As) in the folder "cq_reports"
2. Attached to this Article are two Cyberquery report files named “gl_balance_rpt1.eq” and “gl_balance_rpt2.eq”.  These 2 files need to be brought into CQ (save the file locally on your desktop, then in CQ use File > Import from “your computer”). Then save them in the new sub-folder “GL_Balance_Rpt” created in step “4” above (File > Save Enquiry As)

Daily Processes

At the end of each day you will want to do the following for Daily Balancing:

1. Ensure the Data Warehouse Update is run.  This is typically an Automated Process that is run each day after hours. 
2. Set up the G/L Subsidiary Balancing Report to run as an Automated Process daily, after hours, following the Data Warehouse Update automated process. This report can be emailed in the appropriate format.  Typically this is .xlsx or .pdf. 
3. Set up the “GL_balancing_report.mf” to run daily through Automated process, after hours. It can be included in the same scheduling that is used for running the GL Subsidiary Balancing Report in order to eliminate any timing issues between the two reports.  This report can be emailed in the appropriate format.  Typically this is .xlsx or .pdf.  
4.  Compare the G/L Subsidiary Report Balances to the 'GL_balancing_report' cyberquery.  Often the output from these two reports is entered into a daily balancing spreadsheet where you can track any variances that occur. 

Researching accounts that are out of balance

The following help links will assist you with the steps needed for you to research accounts that are out of balance
For Balancing Reports:
http://documentation.dmsi.com/agility/prod/General_Ledger/g.lbalancingreports.htm
For balancing inventory:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancinginventory.htm
For balancing AP:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancingaccountspayabletrade.htm
For balancing AP Recon:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancinga.preconaccount.htm
For balancing AR:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancingaccountsreceivable.htm
For balancing COGS:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancingcostofgoodssold.htm
For balancing sales:
http://documentation.dmsi.com/agility/prod/General_Ledger/balancingsales.htm
 
The following are some of the most common errors that cause out-of-balance entries:
1.       AP and/or AR parameters set to manual post, but transactions have not been posted to the GL
2.       Reports were not printed at the correct time
3.       Backdating transaction such as invoices and receivers
4.       Posting both debit and credit side of payables and/or receivables to the same account
5.       Dropping A/P Recon records and using the A/P Clearing account as the offset
6.       Inventory adjustment reason codes set to post to the Inventory account which will leave a net impact on the GL of zero, while increasing/decreasing the on hand value report.
7.       Sales account parameters set incorrectly