Report on and Reconcile Inventory

Overview

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:

1. 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).
2. 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. 

Inventory Reports

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:

1. Navigate to Accounting Home and click the Run Reports tab. Then, under the Custom menu, click Management Reports.

2. At this point, there are several ways to access the reports.
1. From the side menu, select All Folders under the FOLDERS heading.
   
   
   

2. Next, select the Accounting Seed ERP folder. 
   
   
   

3. The Reports available are displayed and can be selected and run. 

4. From the All Reports view, perform a search to list the report(s) that need to be run. 
   
   
   
   
3. There are three (3) reports that have been set-up to support inventory reconciliations. 
   
   
   
   
1. 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. 
   
   
   

2. Products with Inventory Weighted-Average Cost report. This report provides for each inventoried Product, its current weighted-average unit cost and Inventory Type. This report can be used to verify the weighted-average cost and, with the Products with Inventory Quantity Histories report, create Trial Balance reconciliations. 
   
   
   

3. Products with Inventory Quantity Histories report. 
1. 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). 
2. This report is date specific, unlike the first two (2) reports discussed. To set the Date, once the report is opened, click Filter to open the Filter menu.
   
   
   

3. 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. 
   
   
   

4. 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. 
   
   
   

4. As noted above, two (2) reports - the Products with Inventory Quantity Histories report and the Product with Inventory Weighted-Average Cost report can be used together for Trial Balance reconciliations. This is accomplished by exporting the two (2) reports to Excel (or Sheets) and using them together to calculate the ending balance of the inventory. 
1. To export a report, while you are currently in the report itself, expand the top-right menu and click on Export. 
   
   
   

2. On the export menu, Click on Details Only, Set the Format to “Excel Format .XLS,” and click  Export. 
   
   
   

3. The report will be downloaded to your computer and can be opened in Excel. Note if you receive an Excel Alert when opening the file that the file format and extension do not match, you are fine and should click Yes to open the file. (It is a known issue.) 
4. Below is an example of an Excel worksheet where the Products with Inventory Quantity Histories report uses a simple vlookup to pull the weighted-average cost for each record from the Product with Inventory Weighted-Average Cost report to calculate the Inventory Account totals. This total can then be used to reconcile to the Trial Balance.