Need more info or you couldn't find what you were looking for, let us know by sending an email to: support@dancik.com.

Month End Reconciliation

Month End Reconciliation - Inventory to GL

Purpose

To show you the reports to use to reconcile the system's AR balance from month to month and understanding the entries created from cash receipts to GL.

To balance the open AR from one month to the next there are required reports, reports used to verify the amounts posted, and confirm the journal entries created to general ledger.

Requirements

Reconciling Open AR from month to month

Running the AR to GL Interface

Understanding the journal entry created from AR to GL Interface

Month End Reconciliation - AR to GL Things to consider

Potential obstacles

Requirements

End of month AR Reports for AR Reconciliation (see sample reports at the end of this document)

— AR Transaction Code Summary

— Invoice Number Span Used this Month

— AR Aging Summary by Account#

— AR Detailed Aging

— Invoice Register

— AR Miscellaneous Cash / Account #99999 Analysis

Understanding of accounting principles
Knowledge of AR to GL Interface
Knowledge of AR Transaction codes
Knowledge of sales and GL Reports
Knowledge of cash application

Reconciling Open AR from month to month

To reconcile your AR balances from month to month, you will need the following:

Start with the total of last month's end of month Aging Report by Account # (ARAGE1/AR) or the AR Detailed Aging Report (ARBORIS/AR). Refer to highlighted total on Example Report 1.

Note: The reports' name are labeled and on the upper right corner of the printout.

Locate the A/R Monthly Transaction Code Summary. This report is labeled REOM7/A/R and it prints automatically during the printing of sales reports for end of month processing. Use the total version of all branches that shows “ALL BRANCHES” on the upper right corner of the printout. Refer to highlighted totals on Example Report 2.

Note: Do not confuse this with the detailed By Branch version that prints right after that is called “Branch ALL”.

Add in all amounts in the Sales column.

Note: If you add all the computer-generated codes under the Sales Column, this should tie out to the Invoice Register and the Sales to GL Summary (journal entry to post Sales and AR to general ledger) for total sales posted to AR. Make sure you run these reports by invoice number span. If there are any manually generated codes under the Sales Column, make sure you know what these are and follow up with your AR personnel. Manually generated sales transaction codes are created by bypassing the normal invoicing program through the Manual Sales options on AR Menu.

Subtract the amount from the AR Credit Column in the field labeled Net AR (this is the amount of cash and discounts credited to AR without the Miscellaneous Transactions). The total cash and discounts should equal to your cash received reports such as AR Monthly Cash by Deposit by Bank, Check#. Refer to Example Report 2.
Add in the finance charges (finance charges on past due invoices) calculated for all customers, if necessary, from the AR Sales Edit / Interest Service Charge Invoices report call SALEE1/AR.
These results should equal to your end of month AR Aging Report. Refer to highlighted total on Example Report 3.

Running the AR to GL Interface

To create the journal entries to record your cash, AR and miscellaneous cash transactions, perform the following:

On the GL Reports Menu, run option #113 - AR to GL interface.
Run the report for the month using the bank, branch, or both sorting option(s).

Note: You only have until the current month end to run the previous month's journal entry. If you do not record your cash to specific branch, we recommend you select Y to assign branch ALL.

Request the report to run in Test Mode first to understand how the system creates the journal entries. Then run the actual posting.

Understanding the journal entry created from AR to GL Interface

The journal entry created from your AR to GL interface creates two entries (cash received and miscellaneous non-AR transactions).

The first entry is the recording of total cash received and discounts taken for the month to credit AR using the transaction codes (such as AD, PF, PP, etc.) used for the month. This entry includes all cash receipts except miscellaneous cash transactions. Refer to Example Report 4.

The second entry from the AR to GL Interface is the miscellaneous non-AR transactions that would debit/credit cash and debit/credit the general ledger account numbers assigned. Refer to Example Report 5.

Things to consider

There are other areas that you should be aware of and may need to consider changing your procedures and processes whether in cash application, AP, or in GL. Sometimes it is necessary to create new GL account numbers or change in financial reporting to accommodate these changes.

Topic	Recommendations
Refunds	Remove the credit invoice from the customer's account due to a refund check through AP. The AR department applies this amount to the GL number. Make sure AP department is aware of this same account and amount to post the refund check.
AR Invoice is offset by AP Invoice	Remove the AR invoices billed to your suppliers from the AR because they in turn have sent a credit via an AP invoice. The AR department applies this amount to the GL number. Make sure AP department is aware of this same account and amount to post the offsetting transaction.
Finance Charges on Past Due Invoices	You need to record this amount as a manual journal entry for each month. This is not an automatic entry.

Potential obstacles

If your AR is not balancing from month to month and to GL:

Check your reports are you using the end of month reports?
Check your AR to GL Interface

— Do the AR entries on the journal entries add up to the total sales as shown on the Invoice Register, and are they mapped correctly?

— Are the accounts to debit and credit correct?

Note: Make sure your mapping is considering the entry as a positive number. If the number is a negative, the system reverses the accounts shown on the interface. Ex: Entering the AR to GL interface for transaction code CF (credit fully used), you would think to debit AR and credit cash. Actually, you should debit cash and credit AR since CF transaction codes are normally negative the system will reverse the mapping.

Have you verified cash, discounts, and AR amounts are the same amounts as shown on other reports?
Are there manual entries that you have done on the GL that would distort the AR amount?
Have you reviewed your procedures on how reduction of AR due to AP would affect your AR balances?
Have you reviewed your Miscellaneous cash transactions?
Have you verified that you had proper cut-off and all sales and cash are in the correct month.

Month End Reconciliation - Inventory to GL

Purpose

To explain the flow of inventory to GL through the Inventory to GL Interface, the AP system, the cost of sales, and other processes that can affect your inventory on the system and the GL for monthly inventory reconciliation.

To reconcile the inventory from system to GL, use the Inventory to GL Interface, the manifest vs. AP Reconciliation, and the Invoicing to GL Interface. There are also other factors could effect your entries to inventory such as managing of inventory returns, direct shipments, etc. This outline gives you a review of the common errors found when system inventory does not match to GL.

Requirements

Understanding the JE from Inventory Interface to GL

Reconciling Inventory to GL_

Things to consider_

Potential obstacles

Requirements

• End of month AR Invoice Number Span

• End of month Invoice Register

• Understanding of accounting principles

• Knowledge and Mapping of Inventory to GL Interface

• Knowledge of AP processes for invoices from suppliers

• Knowledge of processes for unvouchered receipts

• Knowledge of inventory cost variances

• Knowledge of company's processes for return goods, funds, file backs/rebates, and cost drivers

• Knowledge of Invoicing to GL Interface

Recommended Processes of Inventory to GL

Use the interfaces (invoicing and inventory) in conjunction with AP to map your inventory GL account to GL. This allows you to follow the audit trails of what is posted to inventory on your GL.

Note: You will need to review your current procedures and change your reporting if you currently debit (increase) inventory through AP vouchering.

Inventory to GL Interface - maps your inventory receipts, transfers, and adjustments.

Receipts - in mapping your receipts, you'll be debiting the inventory and either a purchases (clearing account) or an accrual account.

Transfers - if you are recording inventory at the different warehouses, make sure you have a transfer in and a transfer out account.

• Adjustments - map your different reasons for adjusting inventory to their proper account.

Note: If the number is negative, the system reverses the accounts shown on the interface. Ex: When entering an adjustment code for damages into the interface, you would think to debit inventory adjustments and credit inventory. Actually, you should debit inventory and credit inventory adjustments. If the transactions have negative values, the system reverses the mapping.

AP vs. Manifest Reconciliation - when receiving invoices from suppliers, you will be debiting the purchases (clearing) or accrual account as it was credited in receiving of inventory.

Make sure that all receiving and AP vouchers for inventory have a manifest number.
Run the unvouchered receipts report immediately prior to AP end of month. Follow this path to run the report: Menu Option 217>Option 1>Option 3. To see a sample report, refer to Example Report 4.

Note: Set the flag for AP vs. Manifest Reconciliation to not exclude invoices vouchered in the future month from the unvouchered report in the current month.

You may need to create a GL account to keep track of cost variances from AP invoices vs. manifest received.

Invoicing to GL Interface - map your cost of sales to debit cost of sales and credit inventory.

Understanding the JE from Inventory Interface to GL

The journal entries to post inventory transactions for the month are requested on demand (GL menu option 112 - Inventory Summaries By G/L Acct). You can request this entry at anytime after month end. The system creates two reports:

The first report shows the inventory transactions by adjustments first, receipts, and then transfers. A subtotal is shown after each warehouse for every transaction. (See example 1).

The second report is the JE created to post to GL. It creates three JEs in the same order as your first report by adjustments first, receipts and then transfers.

Note: You can follow the JE creation by comparing each line item to the JE. For example, the first line on Example 1 shows a value of 117.83- for Cost Center ACC at the DUR warehouse. This corresponds to the first two entries (a credit and a debit) on the JE (Example 2). The JE is done at the summary level by transaction, by branch, and by cost center.

*** under cost center is a subtotal for a warehouse and is not shown on the JE.

Reconciling Inventory to GL

To reconcile your inventory value to GL, the following are recommended processes to help you determine where the discrepancies can occur

Run your inventory reconciliation report (Menu RIV option# 105). This report shows the beginning month inventory, receipts, transfers, adjustments, shipments, ending inventory value, actual inventory value, and variance.

Compare your journal entries from Inventory to GL Interface and confirm the receiving, transfers, and adjustments, equals to your reconciliation. If necessary, run the Receiving Adjustments Registers (menu RIV option #6) for the month in review.

Compare your journal entries from Invoicing to GL Interface and confirm your cost of sales amount is equal to your shipments for reduction of inventory value.
Compare your unvouchered receipts and confirm the receipts outstanding represent actual receipts and not inventory adjustments that were done through the receiving program. Make sure that manifest numbers entered in AP future month are included in the report.

Things to consider

The following table lists other areas that you should be aware of when performing this procedure. Furthermore, you may need to consider changing your procedures and processes of recording sales, funds, file backs, cost of sales, inventory, etc. Sometimes it is necessary to create new GL account numbers or change financial reporting to accommodate these changes.

Topic	Recommendations
Inventory Cut off Time	There is not an end of month specifically for inventory. When the last step of month end closes AR, it also closes inventory at that point. Any receiving, transfers, or adjustments done once you declare month end (menu EOM option #1) are considered in the next month's transactions.
Cost Variances	How are you managing cost variances from AP to receipts? If you correct the cost of the inventory to match the receipts, make sure you post the variance that appears on the Inventory Reconciliation report as some invoices could have gone through the system with the incorrect cost.
Invalid Receipts	Make sure users are not using Inventory Receipts to correct inventory adjustments such as cycle counts, damaged, etc. Since these are not real receiving, there will not be an AP invoice and these receipts appears on your unvouchered receipts.
Customer Credits	Review your processes for customer credits so that inventory is not affected twice if customer service returns to stock and adjustments to inventory were also done.
Freight	If you are including freight estimates in your inventory value. Verify your journal entries from receipts and in AP and confirm that freight is not double posted on your GL.

Potential obstacles

If your Inventory is not balancing from month to month to the GL:

Verify your reports - are you using the end of month reports?
Verify your Inventory to GL Interface - do the journal entries agree with the Inventory Reconciliation?
Verify your Sales to GL Summary - do the journal entries agree with the Inventory Reconciliation?
Are there manual entries that you've done on the GL that would distort the inventory differences from system to GL?
Verify your mapping and confirm - have you reviewed your procedures on how reduction of AR due to AP would affect your AR balances?
Do you have the proper cut off time for Inventory?

Month End Reconciliation - Sales to GL

Purpose:

To show the reports used to post the monthly sales to GL and explain the entries created from the sales to GL interface.

The system posts the sales to GL when you request the Sales to GL Summary report to run for the specific month. This document explains the reports used to verify the amounts posted, and confirm the journal entries created to general ledger.

Pre-Requisites

Running the Sales to GL Summaries

Understanding the journal entry created from Sales to GL Summary

Things to consider

Potential obstacles

Pre-Requisites:

End of month AR Invoice Number Span Report
End of Month Invoice Register
Understanding of accounting principles
Knowledge and Mapping of Invoicing to GL Interface
Knowledge of sales and GL Reports
Knowledge of branches and cost centers
Knowledge of company's processes for return goods, funds, file backs/rebates, and cost drivers

Running the Sales to GL Summaries

To create the journal entries to record your sales and AR to GL, you need to perform the following:

On the GL Reports Menu, run option #111 - Sales Summaries by GL Account.
Run the report by invoice number span. This report prints out automatically during AR end of month. It is important that you run the sales report to post to GL by invoice number span. This gives an accurate representation of sales amount billed for the month.
Sort the report based on the Invoicing to GL Mapping by Branch, Warehouse, and/or Cost Center.
Request the report to run a TRIAL RUN first to understand how the system creates the journal entries then run the actual posting.

Understanding the journal entry created from Sales to GL Summary

The Sales to GL Summary creates three reports when you select the option to post/edit to GL.

The first report is separated into four types of transactions:

— Intercompany Sales

— Direct Ship Sales

— Inventory Sales

— Special Orders Sales

Each part is shown in detail and is broken down in rows of different branches, cost centers, and/or warehouses. Their sales transactions are in columns consisting of Gross, Handling Charge, Funds, Freight, Tax, Cost, Cost Non-stock, and Cost Allowances. (These columns are the same columns used to map your Invoicing to GL Interface.

Note: You do not have to have any intercompany, direct ship, or special order sales transactions that post to GL. Make sure your mapping is considering the entry as a positive number. If the number is a negative, the system reverses the accounts shown on the interface. Ex: Entering the discounts for this interface, you would think to debit sales discounts and credit AR. Actually, you should debit AR and credit sales discount since the discount is normally a negative. The system automatically reverses the mapping for negative transactions.

The second report is the F6 Miscellaneous Charges by general ledger number. These are charges entered on F6 lines in order entry such as delivery charges, pallet charges, UPS charges, etc.

The last report is the actual journal entries. The journals are broken down in the same format as the first report by transaction types (intercompany, direct, stock, and special orders). The journal details each row and column amount and creates the entries based on your report. The actual journal entry report can be several pages. Examples of the first and last page are shown below.

The last transaction type report, usually Special Orders Sales, of the Sale to GL Sales Summary contains the Gross, Handling Charges, Discounts, Funds, Freight, and Tax totals for all the transaction types, (Intercompany, Direct Ship, Inventory, and Special Orders). If you add these totals (do not include any information past the Tax total) to your F6 Miscellaneous Sales this amount should equal the total sales on your invoice register from month end by invoice span and your AR Transaction Code Summary for ALL branches for computer generated sales transaction code. (This report is labeled AREOM7/A/R on the upper right corner and it prints automatically during the printing of sales reports for end of month processing.)

Things to consider

Topic	Recommendations
Funds/Overbills	Funds/overbills are extra charges incurred to the customer for a trip program. The system posts all funds into one GL account. You need to create a manual entry from this one account into the different fund accounts.
Sales Tax	The system posts all sales taxes into one GL account. You need to create a manual entry from this one account into the different sales tax accounts.
Cost Allowances	Cost Allowances refer to file backs expected from the manufacturer. Make sure file backs are entered in the system so your margins on the system and GL are reflected by these transactions.
Cost Non-Stock	Considered as credits given to customers for materials where you do not want the customer to return stock due to damage, claims, or other situations, but you have a cost on this line item. The recommendation for this interface entry is to debit cost of sales and credit inventory adjustments. If you have different GL account numbers for the different inventory adjustments such as claims, you may want to journalize the cost of the claims credits out of this adjustments account to the claims account.
Direct Ship Cost of Sales	You may want to create a direct ship “cost of goods account”. So when you invoice a direct ship, the credit is to the cost of goods account and AP offsets the same account upon receipt of supplier's invoice instead of putting it into inventory GL account.
Cost Drivers	Cost Drivers are costs that are associated with line items within the Dancik Distribution system. They identify costs or expenses that are not part of the product cost, but still affect the profit on the sale of the item. The “Cost Driver” is the mechanism that assigns the appropriate cost to the line items that were subject to free delivery for example. Another important use of Cost Drivers is to track rebates or expenses that are directly tied to line item sales, but are not necessarily reported or managed on a line-by-line basis. For example, if you want to keep track of the 3% rebate you need to give to the buying group each quarter and have this affect your overall margin as you view your statistics. Make sure that you are aware of the cost driver set up and which ones are mapped to affect GP.

Potential obstacles

If you are comparing the sales to GL to other reports, and the amounts are not matching:

Are you using invoice number span or date span?
Make sure when running your Sales to GL Summaries, you request it based on your mapping. Ex: if your mapping is by branch, you should request the Sales to GL Summary by branch not cost center.
Make sure your end of month AR Transaction Code Summary for ALL Branches for Computer Generated Sales transaction code ties to your total sales on the Sales to GL Summary. This report is labeled AREOM7/A/R on the upper right corner and it prints automatically during the printing of sales reports for end of month processing.
Make sure you review your journal entries for any error messages such as invalid cost centers, branches, etc.
Compare your Sales to GL Summary report to your journal and review that each transaction has a journal entry.
Review your mapping to make sure it is debiting and crediting the proper account.