The following tips and tricks will help you get ready to close out the old year (2019) in Sage 500 and ring in the New Year (2020)!
First a word of caution about what this article is NOT: This is NOT intended to be a step-by-step guide for what order to close the modules in, how to create a new Fiscal or Inventory Calendar Year, or how to process your 1099’s. There are several excellent resources available for those already. For example:
How to Process Year End Closing in Sage 500 ERP
Sage 500 ERP: Year-end Resources
Sage 500 ERP 1099s for Tax Year 2017
What are 1099 requirements for the year 2018
So what is the point of this article? To give you pointers about common reconciliation issues, tips and tricks, and a little information on what may be different for you this year vs. previous years.
“What’s the quickest way to ensure we have posted every batch in a module?” Good question! Almost every module has a “Post xx Batches” task on the module’s Activities menu, where xx represents the 2-letter abbreviation for the module (IM = Inventory Management, PO = Purchase Order, etc.). That task gives you a list of all pending batches for the selected company and module. The task will display whether the register has been printed, posted, the process status, the batch ID, the post date, the batch status, the user who created the batch, and a checkbox for whether the batch is flagged as “private” for the user who created it or those with Supervisory access to the task, like this screen-shot:
Here’s a quick list of the different Batch ID types you’re likely to see:
Inventory
IMIT – Inventory Transaction
IMPC – Physical Count
IMCU – Cost Tier Adjustment
IMTA – Adjust Transfer Discrepancies
Purchase Order
PORG – Receipt of Goods
POVO – Receipt of Invoice
PORT – Return of Goods
Accounts Payable
APMC – Manual Checks
APPA – Payment Applications
APSC – System Checks (Process Invoices for Payment)
APST – AP-AR Invoice Settlement
APVO - Vouchers
Sales Order
Note: Instead of Post SO batches, look for the “Post SO Transactions” task. Make sure all shipments, transfers, and returns are posted. When they are, the Unposted column will contain a zero for each row and the GL Posting > Post Date field will be blank.
Accounts Receivable
ARIN – Invoices
ARCR – Cash Receipts
ARPA – Apply Payments and Memos
Cash Management
CMCM – Bank Transactions
CMRE – Bank Reconciliations
General Ledger
GLGJ – General Journals
GLAJ – Allocation Journals
GLIC – Intercompany Journals
Reconciliations
What fun is there in closing your books if the data contained within them is unreliable?
NOTE: The first rule of thumb is to enable the “Post in Detail” settings in each module’s “Set Up XX Options” task whenever practical. Disk space is relatively cheap, and this detail setting will help ensure you have as much information available to you as possible in order to track down variances that may arise.
So when might it not be practical to use post in detail? Chances are the detail option may prove overwhelming for most Control Accounts – Accounts Payable, Accounts Receivable, Inventory, and if you are using the Cash Management module to reconcile your Bank Statements, add the Cash account(s) to this list. You may also consider posting in summary for the Sales Clearing account.
Inventory Management
Use the Inventory Cost Report as the basis for reconciling your Inventory to your General Ledger. On the Main tab, Sort and Subtotal by “Inventory Account” and be sure not to enter any filters in the Select grid.
On the Options tab, select the “Valuation” option and the “Stock UOM”
Remember that for Inventory Items (Raw Materials, Finished Goods, BTO Kits, and Assembled Kits) the Inventory GL Accounts are defined in Maintain Inventory. Item Classes and/or Warehouses can be set up with default GL accounts in Inventory Management > Maintenance > Maintain Item Classes or in Inventory Management > Maintenance > IM Setup > Set Up Warehouses.
For non-inventory items (Miscellaneous, Expense, and Services) the accounts are defined in Common Information > Maintenance > Maintain Non-Inventory Items, and the defaults for these accounts come from Maintain Non-Inventory Item Classes.
Purchase Order
Use the PURCHASES CLEARING REPORT found in the PO > Insights > Reports folder to try to reconcile all Purchase Order lines that have been received but not yet invoiced. In a perfect world, the total of this report will agree to the balance in your Purchases Clearing GL account, which is defined in Set Up PO Options on the General Ledger tab:
The challenge with the native Sage Purchases Clearing report is that it always reflects data as of the current moment in time and there are known issues with some vendor returns not properly being reflected. If you find the native report does not agree to your General Ledger balance, you may want to contact our Support team or your Sage 500 Consultant to obtain a custom Purchases Clearing Report. You may also have better luck with the Purchases Clearing report template available for Sage Intelligence. Use the Sage Intelligence Report Utility to download a copy of this report template from Sage.
There is also a Drop Ship account defined in Set Up PO Options and in Set Up SO Options. (Changing the value in either screen updates the field in both tasks.) This account is used instead of an inventory account for Drop Ship transactions. The account is credited when the Customer’s Invoice has been posted subsequent to running Drop Shipments to AR Invoicing. When the related Vendor Invoice is posted in PO Receipt of Invoice, the same account is debited. Therefore if you have a credit balance in this account, you should review Explore Purchase Order Lines, filtering on “Drop Ship … Is Equal To … Yes” and “Status … Is Equal To … Open” to ensure you have processed the relevant vendor invoices for Drop Ship transactions.
Sales Order
The “Sales Clearing” account is defined in Set Up SO Options. This account is debited with the cost of goods sold when a shipment is posted and credited when the related customer invoice is posted. Conversely, it is credited when a Customer Return is posted and then debited when the related customer credit memo is posted. The value in this GL account should, therefore, represent the net cost of sales which have not yet been invoiced.
Accounts Receivable
Use the AR Trial Balance Report as the basis for reconciling your Accounts Receivable to your General Ledger.
The Aged Receivables Report can also be used but make certain you choose on Options “InclusionDate – Posted On or Before”. Note that if you use The Aged Receivables Report it does not have the GL Account Recap shown below.
The AR control accounts are defined in Maintain Customer Classes:
On the Main tab, Subtotal by “Accounts Rec Account” and be sure not to enter any filters in the Select grid.
On the Options tab, select the “GL Account Recap” option.
Accounts Payable
Use the AP Trial Balance Report as the basis for reconciling your Accounts Payable to your General Ledger.
The Aged Payables Report can be used but make certain you choose on Options “InclusionDate – Posted On or Before”. If using the Aged Payables Report note that it does not have the GL Account Recap shown below.
The AP control accounts are defined in Maintain Vendor Classes:
On the Main tab, Subtotal by “Accounts Payable Account” and be sure not to enter any filters in the Select grid.
On the Options tab, select the “GL Account Recap” option.
Download Sage 500 ERP Year-end Processing Instructions
Download a printable pdf of the above instructions. The document includes additional references and helpful Sage links.
