RKL eSolutions Blog Trends and Insights

Sage 500 ERP Year-end Closing Procedures

Sage 500 ERP yr end

The following tips and tricks will help you get ready to close out the old year (2017) in Sage 500 and ring in the New Year (2018)!

This article will 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:

Sage 500 Post GL Batches

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.

Sage 500 Post SO Transactions

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.

Sage 500 Setup SO Options

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.

Sage 500 ERP Inventory Cost Report

On the Options tab, select the “Valuation” option and the “Stock UOM”

Sage 500 ERP Inventory Cost Valuation

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:

Sage 500 ERP PO Options

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.

Sage 500 ERP Drop Ship

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:

Sage 500 AR Accounts

On the Main tab, Subtotal by “Accounts Rec Account” and be sure not to enter any filters in the Select grid.

Sage 500 AR Trial Balance

On the Options tab, select the “GL Account Recap” option.

Sage 500 ERP GL Account Recap

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:

Sage 500 ERP AP Maintain Vendor Classes

On the Main tab, Subtotal by “Accounts Payable Account” and be sure not to enter any filters in the Select grid.

Sage 500 AP Trial Balance

On the Options tab, select the “GL Account Recap” option.

Sage 500 ERP GL Account Recap Options

 

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.

Download Sage 500 ERP Year-end Procedures

 

Watch the Webinar Replay and Download the Slides

Watch the webinar recording on YouTube and download the slides as a reference tool for your year-end processing questions.

Note:  this article was 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 2017

Doug Hart

Written by Doug Hart

Doug is a Senior Solution Architect on our Sage X3 team.