Question: What must I do to reconcile subledgers with the general ledger for month-end processing?
Answer: Verify all transactions for the month are posted, and then perform the following for each module:
In Pick Demand Adjust Hold Audit Report, output the report with the default options, and verify there are no unposted invoices. If there are unposted invoices, post the invoices.
In Accounts Receivable Aged Trial Balance, select the Period radio button, the current period, and the accounts receivable general ledger account you want to reconcile.
Note Do not click the List button to select multiple accounts.
Leave all other default options and verify the following settings:
On the Select tab, all the filtering check boxes are selected (All Collection Teams, All Collection Groups, etc.).
On the Other Options tab, the Only Credit Balance Option check box is cleared, and the Print radio button is selected to the right of the check box.
On the Other Options tab, the Only Disputed Transactions check box is cleared.
On the Other Options tab, all the check boxes in the Include section are selected.
Output the report, and verify the grand total for the Open Amount column equals the amount in the accounts receivable general ledger account. You can ignore the report's aging columns for this analysis. Repeat this step for all accounts receivable accounts. You can also output the detail version of the report (by selecting the Detail radio button on the Display Options tab) to determine specific transactions that were not posted to the general ledger.
If accounts receivable does not reconcile, output the Accounts Receivable Aged Trial Balance Reconciliation Discrepancy Data Report. Perform the following to determine the cause of the discrepancy:
Review when the report last balanced. This allows you to determine if the problem is new or if accounts receivable was out of balance in previous periods.
Determine if the open accounts receivable amount is less than or greater than the general ledger amount.
Verify the report is from the moment the period was closed and not after additional invoices, credit memos, debit memos, chargebacks, or other accounts receivable transactions were made in a new period.
Verify that the following were posted:
invoices
accounts receivable
Verify there are no journal entries with origins of A/P or G/L affecting the account. If a user made a manual journal entry, this could affect the accounts receivable account. You would then need to add or subtract that value (depending on if there were debits or credits to the accounts receivable account).
Example The open accounts receivable amount on the report is $100,000. The accounts receivable account balance is $98,000. You review Account Detail Inquiry for the accounts receivable account, and find there is a $2,000 journal entry with an origin of G/L because a user made a manual journal entry. If this journal entry had not been created, there would not be a discrepancy. You must now research why this journal entry was created and reverse the journal entry if it should not have been made.
In Accounts Payable Aged Trial Balance, select the Reconciliation radio button, and select the current period and year. Specify the accounts payable account. Output the report and verify the grand total of the Open Amount column equals the amount in the accounts payable general ledger account. You can ignore the report's aging columns for this analysis.
If accounts payable does not reconcile, use the Accounts Payable Reconciliation Data Report and perform the following to determine the cause:
Review when the report last balanced. This allows you to determine if the problem is new or if accounts payable was out of balance in previous periods.
Determine if the total open accounts payable amount is less than or greater than the general ledger amount.
Verify the report is from the moment the period was closed and not after additional invoices, credit memos, or debit memos were made in a new period.
Verify that the following were posted:
vouchers
payments
Verify there are no journal entries with origins of A/R or G/L affecting the account. If a user made a manual journal entry, this could affect the accounts payable account. You would then need to add or subtract that value (depending on if there were debits or credits to the accounts payable account).
Example The grand total for the Open Amount column on the report is $100,000. The accounts payable account balance is $98,000. You review Account Detail Inquiry for the accounts payable account, and find there is a $2,000 journal entry with an origin of G/L because a user made a manual journal entry. If this journal entry had not been created, there would not be a discrepancy. You must now research why this journal entry was created and reverse the journal entry if it should not have been made.
In Accounts Payable Clearing Account Report, select the current period, and an accounts payable clearing account. Output the report, and verify the total accounts payable clearing balance equals the amount in the accounts payable clearing account. Repeat this step for all accounts payable clearing accounts. If there is a discrepancy, output the Accounts Payable Clearing Discrepancy Data Report to determine the cause of the discrepancy.
In Prepayment Account Reconciliation Report, select the current period and a prepayment account. Output the report, and verify the total balance equals the amount in the prepayment account. Repeat this step for all prepayment accounts. If there is a discrepancy, output the Prepayment Reconciliation Discrepancy Data Report to determine the cause of the discrepancy.
In Drop Ship In Transit Reconciliation Report, select the current period and a Drop Ship In Transit account. Output the report, and verify the total Drop Ship In Transit balance equals the amount in the Drop Ship In Transit account. Repeat this step for all Drop Ship In Transit accounts. If there is a discrepancy, output the Drop Ship In Transit Reconciliation Discrepancy Data Report to determine the cause of the discrepancy.
In Inventory Valuation Report, output the report without applying any filters. Verify the total value of inventory equals the amount in the inventory general ledger account.
Note You may see small differences between the two totals due to decimal rounding. General ledger postings are rounded to two decimal places, but the Inventory Valuation Report uses the current value of products, rounded to four decimals places. To display inventory rounding amounts greater than .01, select the Show Inventory Rounding Amounts check box before outputting the report.
If the inventory does not reconcile, output the reconciliation discrepancy information, and perform the following to determine the cause:
Review when the Inventory Valuation Report last balanced. This allows you to determine if the problem is new or if the inventory valuation was out of balance in previous periods.
Determine if the inventory valuation is less than or greater than the general ledger amount.
Verify the inventory valuation is from the moment the period was closed and not after additional receipts, cost adjustments, and/or stock adjustments were made in a new period.
Verify all inventory-adjusted transactions are invoiced and posted.
Verify that all the following were posted:
invoices
accounts receivable
accounts payable
general ledger
work orders
inventory receipts
inventory and cost adjustments
cycle counts
Verify there are no journal entries with origins of A/P, A/R, or G/L affecting the account. Only journal entries with origins of INV, PUR, WO, and IC should be posted. If a user made a manual journal entry, created an accounts payable voucher that affected the inventory account, or created a write-off/deposit/accounts receivable payment transaction for the inventory account, this could affect the inventory valuation. You would then need to add or subtract that value (depending on if there were debits or credits to the inventory account).
Example The inventory valuation is $100,000. The inventory account balance is $98,000. You review Account Detail Inquiry for the inventory account, and find there is a $2,000 journal entry with an origin of G/L because a user made a manual journal entry. If this journal entry had not been created, there would not be a discrepancy. You must now research why this journal entry was created and reverse the journal entry if it should not have been made.
Review the Pick Demand Adjust Hold Audit Report that was output in step 1, and determine if inventory was adjusted for any sales orders that were not invoiced. If this occurs, the general ledger amount will be higher or lower than the inventory valuation report amount by the value of the products on the pick tickets. The cost of the products for the pick tickets will appear on the report.
Example It is November 30th and a user adjusts inventory for multiple pick tickets at 5 PM. The related orders will be invoiced the next day (December 1st), and will post in the December period. The Pick Demand Adjust Hold Audit Report enables you to determine that there is inventory that was adjusted in period 11, for which invoices will be created in period 12. The inventory value will be lower on November 30th than the posting for period 11, because the invoices will be posted in period 12 (the opposite effect would occur for a credit memo).
Review purchase orders to see if temporary products are affecting the inventory account. If a temporary product is on a purchase order and it affects the inventory account, this will increase the posted balance of the inventory account. The inventory valuation and the inventory account will not balance because temporary products never appear on the inventory valuation report. Therefore, the general ledger inventory amount will be greater than the inventory valuation.
For service items, verify there is no quantity on hand. Also, verify the inventory general ledger account is not used for the service item (on the location, on the purchase order, etc). Service items do not appear on the inventory valuation report, so if the inventory account is used for a service item, it can cause the general ledger account amount to exceed the inventory valuation.
Verify that all products are set up with a unit of measure marked as the default display unit of measure. If this is not set up correctly for a product, the product does not appear on the inventory valuation report. In this situation, the general ledger inventory amount will be greater than the inventory valuation.
Verify that no extra amounts posted to the inventory account. Review the following:
order entry additional charges
purchase order additional charges
inventory adjustment codes
return order disposition codes
chargeback reason codes
write-off reason codes
Example If an inventory adjustment code posts to the inventory account for an inventory adjustment, it would balance with the change to the account so the amount is unchanged (zero impact), while the inventory valuation increases or decreases. The inventory valuation would then be less than or greater than the general ledger amount (depending on if the inventory was increased or decreased).
Verify that the break-to units of measure for all products are evenly divided numbers of larger units of measure.
EXAMPLE A product is set up so that a case of 50 units can be broken down into two cases of 25 units, which can be broken down into five cases of five units. The cases can be evenly divided into smaller units of measure, so this will not affect the inventory valuation. However, if a product is set up so that a case of 10 units can be broken down into cases of three units, which can be broken down into three single units, this will affect the inventory valuation. In this situation, if you only have a case of 10 units in inventory and an order requires six units, the pick ticket will indicate that the picker should break one case of 10 units into three cases of three units. The extra unit is lost and will no longer appear on the inventory valuation report. The inventory valuation would then be greater than the general ledger amount.
If Inventory Quantity Import was used to adjust inventory quantities, verify manual journal entries were posted to balance the general ledger inventory account(s) to the subledger.