How To: Reconciling Inventory to G/L (Month End)

Overview

At month end we recommend that you reconcile Inventory with the General Ledger. This process is completed by comparing the totals on the Inventory Valuation Report with the Inventory Totals on the Balance Sheet and analyzing and correcting any discrepancies that exist. This tutorial walks you step by step through this process.

Video

Tip: Use the controls on the video player to watch in Full Screen mode, adjust the quality, or watch on YouTube.

Step-by-Step

  1. The first step in the Inventory Reconciliation process is printing the Inventory Valuation Report. Make sure to enter “Y” for all items in the third block, e.g. ‘Stock Merchandise,’ ‘Special Order Items,’ ‘Negative Onhand Report,’ etc.

    After printing the report, turn to the second to last page (Inventory Valuation Totals) and find the ‘Inventory Value’ total. This is the amount you will compare to the total obtained from the Balance Sheet.

    NOTE: Before to running this report, make sure that:

    • All customer orders that have been physically shipped are updated in Order Processing (through Order Update (02-08-16))
    • All items that have been physically received on P/Os or transfers are updated in the system through Receiving Register/Update (04-12) and Update Processing (03-11-03)
    • Items adjusted through Receipts and Adjustments are updated (through Receipts & Adjustments Audit/Update (03-10-02))

  1. Print the last page of the Received, Not Invoiced (04-44) report, which shows the value of product received in Inventory, but not posted to the General Ledger.

    When you receive and update items on a P/O, the value of the merchandise is immediately reflected on the Inventory Valuation report, but not on the Balance Sheet. The value of the received items will not show up on the Balance Sheet until the P/Os are invoiced through P/O – A/P Entry and Update (04-20 and 04-21).

Click image to enlarge.

  1. Now you need to enter a reversal journal entry for any received, not invoiced merchandise in your system using Journal Entry (15-05). A Reversing Entry is a temporary change to the Balance Sheet that is cancelled at the beginning of the next month. These journal entries increase the total on the Balance Sheet and credit the A/P Control account.
    1. First, debit the Merchandise Inventory account for the amount from the Received, Not Invoiced report.
    2. Next, credit the Accounts Payable Control account for the same amount.

  1. Run Journal Entry Audit/Update (15-06) so that the entries show up in your reports.

  1. If your General Ledger is operating in the current month, you need to run A/P P/R P/O A/R (15-10). If your G/L is operating in a previous month and there are entries in the file that are dated for that month, you should also run this selection.

  1. Now that the amounts in the General Ledger have been adjusted and updated, it is time to print the Balance Sheet. Make sure to enter “C” at the ‘Current or Prior Month’ prompt.

  1. Compare the total on the last page of the Inventory Valuation report with the ‘Merchandise Inventory’ amount on the Balance Sheet.

    If they match, the Inventory Reconciliation is complete.

    If the two are not in agreement, continue through the remainder of this tutorial.

    NOTE: When the totals between the Inventory Valuation report and the Balance Sheet do not agree, consider, first, the percentage of that difference. Tyler uses weighted average inventory costing, which can cause a 1-2% discrepancy between the totals on these two reports. If the difference you are seeing exceeds 2%, continue through the rest of this tutorial for common causes of inaccurate data.

  1. Negative onhand quantities understate the value on the Inventory Valuation report. To fix this, take a look at the last page of the Inventory Valuation Report that you printed earlier. Add the ‘Total Inventory Value’ on this page (Negative Onhand) to the ‘Total Inventory Value’ on the second to last page of the report (Inventory Valuation Totals).

  1. Access the RDB Maint/Run a Report selection (32-01) and open and print the ‘$INV1’ report, which searches the Inventory Master file for any items with a blank Field 13 (Vendor Cost). When the Vendor Cost field is blank, the system recognizes the item’s cost as zero, resulting in the calculated value of the item being zero on the Inventory Valuation Report.

    For each item without a Vendor Cost, enter one in Field 13 of the Inventory Master File Inq/Maint (03-01).

    NOTE: Do not enter a Vendor Cost for Non-Inventory items. They are handled differently.

  1. Run the Inventory Valuation report (03-63) again.

The scenarios just described (Negative Quantity Onhand, missing Vendor Cost) are the two most common reasons for you inventory figures to be off. The following steps describe possible problems with the General Ledger. Since most of these issues deal with settings, once they are fixed they should not cause any more problems in the future.

  1. Open the G/L Account Number selection (02-18-06). For each branch, review the ‘Trade In Purchase’ field. The G/L account associated with trade ins should be the ‘Merchandise Inventory’ account.
    NOTE: These postings occur when Return to Supplier (RS) transactions are run in Point of Sale; they credit (reduce) inventory account designated here, which affects the Balance Sheet. Additionally, if the Trade In payment type is used to reduce the price of an item in POS Entry, that amount is debited to this account.

  1. Run Transaction Code Maintenance (03-13). Review the code ‘COG.’ The G/L number associated should not be the ‘Merchandise Inventory’ account.
    NOTE: All sold and delivered product, when updated, will credit (reduce) the ‘Merchandise Inventory’ account automatically to match the quantity reduced when the product was shipped. Associating the Merchandise Inventory account with the ‘COG’ transaction code will cause these credits to net to zero.

    Review the other codes in this selection. If any of these codes have the ‘Merchandise Inventory’ account associated with them, review the reason for this assignment. Make certain that the effects of this setting are known, understood and intentional. Speak with your accountant to make sure that they are aware of this and that their calculations are taking this in mind.

  1. Open the Auto Interface Posting Account Listing selection (15-15) and print the Auto Interface Posting Account report. Find the journal source code ‘I/P’ and make sure that it is assigned to the ‘Merchandise Inventory’ account. Correct this if it is not.

  1. Run P/O Preferences (04-90).

    2. Click image to enlarge.
    1. Tab to the second screen in this selection and check if any flags are set to ‘Yes’ on this page. Checking ‘Yes’ for any of these items will inflate or deflate the Net Cost on the P/O, depending on the item. Make certain that your accountant is aware of these settings and is making their calculations with this in mind.

    Click image to enlarge.
    1. Screen three covers how Vendor Prices are adjusted in the system in relation to Net Costs. Options 2 and 4 both automatically adjust vendor prices along with changes to net costs. Additionally, option 4 additionally maintains the ratio between Net Cost and Unit Cost. Option 3 is used if you want to make all of the changes to Vendor Cost manually. Whatever the selection is set to, make sure your accountant is aware of the setting and is acting accordingly.

      Example:
      Freight is flagged in this selection to be included in the Net Cost.
      Some product is $100.00 and the freight for that item is $10.00, totaling $110.00. This is the cost that is calculated using weighted average, written back to the vendor cost, and then appears on the Inventory Valuation report.

      In this scenario, the A/P department should be posting freight from the invoice to the merchandise account in the amount of $110.00. If the AP department posts only $100.00 to the merchandise account and the freight of $10.00 is posted to a different account, such as Freight on the P&L under cost of goods area, your merchandise account will not match the valuation of the product received.

    Click image to enlarge.
    1. The fourth screen of P/O Preferences is used to specify how payment discounts and accruals are calculated.

      If any of the items (Rebates, Co-op, Payment Discounts) are flagged to change Net Cost (Screen 2 of P/O Preferences), talk to the Accounts Payable department and verify that they are posting the invoices in the same manner that the cost is being posted. If the A/P department is posting differently, it will cause discrepancies in reconciliation.

      NOTE: If Rebates, Co-op, and/or Payment Discounts are not included in the Net Cost calculation, the settings on this screen are ignored.

    1. Open the G/L Inquiry selection (15-07). Access the Merchandise Inventory account and review it for any manual journal entries that increase or decrease the account total. Then determine the reasons that they were entered. Also look for unusual amounts or questionable entries.
Tags: ,