An Accounting Seed recommended best practice for month-end close is to reconcile all sub-ledgers to the general ledger. (see Best Practices for Month-End for more detail). When utilizing inventory accounts, this includes a periodic reconciliation between the inventory sub-ledger and the Trial Balance. An inventory sub-ledger is essentially the detail of your inventory that isn’t captured in your general ledger. A key inventory sub-ledger is Inventory Quantities which are captured in two different objects:
- Inventory Quantity Available - Captures the inventory quantities available and inventory movements as of the current point in time in the system. (See Checking Inventory Quantity Available for more detail).
- Inventory Quantity Available Histories - Captures the inventory quantities available and inventory movements as of a specific past point in time in the system. Typically, you will generate these history records at month-end but it can be for any date. (See Create Inventory Quantity Available Histories for more detail).
The Inventory sub-ledger is used to create reports specifically to support performing inventory reconciliations.
Several inventory reports have been set-up that use the Inventory sub-ledger to create reports specifically to support performing inventory reconciliations.
To run these reports:
- Navigate to the Reports tab.
- From Accounting Home, click on Management Reports.
- If in the AS: Order Management application, click on Reports found on the tab menu.
- At this point, there are several ways to access the reports.
- From the side menu, select All Folders under the FOLDERS heading.
- Next, select the Accounting Seed ERP folder.
- The Reports available are displayed and can be selected and run.
- From the All Reports view, perform a search to list the report(s) that need to be run.
- There are three (3) reports that have been set-up to support inventory reconciliations.
- Month End Inventory Detail report. This report provides for each inventoried Product, by Accounting Period, every Transaction, the GL Account impacted and the Amount, plus the Quantity and type of Inventory Movement it was. This report can be used for many reconciliations including Cost of Goods Sold, Builds, verifying weighted-average cost, and many others.
- Products with Inventory Quantity Histories report.
- A prerequisite to running this report is that Inventory Quantity Available Histories must have been created for all inventoried Products included in the reconciliation for the period being reconciled. (See Create Inventory Quantity Available Histories for more detail).
- This report is date specific, unlike the first two (2) reports discussed. To set the Date, once the report is opened, click on the Filter button to open the Filter menu.
- Click on the Date filter, modify the date to the end date of the period being reported on. Note that this date must be the same date that was used to create the Inventory Quantity Available Histories. When correct, click Save.
- This report provides for each inventoried Product, Warehouse and Location combination, the Inventory Quantity by the Inventory Movements as of a specific past point in time (set by the Date used to create the Inventory Quantity Available Histories.) This report with the Products with Inventory Weighted-Average Cost report can be used for Trial Balance reconciliations.
- As noted above, the Products with Inventory Quantity Histories report can be modified to calculate the valuation of Inventory at Standard Unit Cost in comparison to what is reflected on the General Ledger.
- Edit the report and add the Unit Cost field to the report.
- Create a custom formula field.
- Column Name = Total Value
- Formula = AcctSeedERP__Inventory_Balance__c.AcctSeedERP__Available_Quantity__c:SUM * Product2.AcctSeed__Unit_Cost__c:SUM
- Compare the calculated value against what is recorded on your Trial Balance. For any differences, record a journal entry adjustment.
- The Inventory Balance object has been renamed to Inventory Quantity Available.