Balancing the Inventory Subledger

Warehouse Management (WM) > Warehouse Management Procedures > Auditing Daily Inventory, WIP, and Pending Activity

Balancing the Inventory Subledger

This procedure is used to prove that the difference between the beginning and ending inventory valuation for a period of time (usually a day) agrees with the total of the transactions that were recorded for the items. This step is performed prior to balancing the Inventory sub-ledgers to the general ledger control accounts.

1. Ensure that all Inventory, WIP and Pending activity for the current date has been completed and that no other transactions affecting these subledgers will be posted until after the reconciliation procedure has been completed and/or the date has been changed.

2. Record the last inventory transaction number used in the Inventory Activity file (File 91). This number can be used to verify the validity of the cuttoff on the Inventory report which is used to reconcile the days activity. Note: The current version of the Inventory, WIP, and Pending subledger reports automatically record the last transaction number used when the reports are run and print an error message if the number changes during processing.

3. Produce a valid ending Inventory Subledger report. This can be accomplished by running the Inventory Valuation report for all inventory locations in the division being reconcilied. Include all items with quantities (including items with negative quantities). Non stock items will not be included in the report total (these items are not included in the Inventory, WIP or Pending subledger totals). Note: The ending Inventory subledger report is a point in time report (a “snapshot” of the item quantities and costs at a particular point in time). The ending subledger report must include all activity through the end of the day being balanced and no activity after that date. A valid report can only be produced if it is run at the proper time. This is normally accomplished by running the report after hours as part of the nightime process. The report can be processed automatically by the system at night and the system date can be changed immediately before or after running the report to ensure that the next days transactions will be posted using the proper date. This produces a report with a valid cutoff.

4. Verify that the subledger was in balance at the end of the prior day. The only way to prove that the beginning balance being used during this procedure is valid is to have already balanced the subledger for the prior days activity. This proves that the subledger beginning balance has a valid cutoff (that it includes all activity thru the end of the prior days processing and no activity from the current days processing). If the prior days activity is not in balance, then you must either balance it first (discover and resolve the cause), or balance the activity for the entire period since the last time that the subledger was proven to be in balance.

5. Run the Inventory Activity by Transaction Type report (FV091R05) for the date range being reconciled. The Inventory Activity report will total the inventory transactions for the selected date range by source. The net total of this report should match the change to the Inventory Valuation report for the same date range.

6. If you are running a version of the system prior to Cove File System Revision 275, you should also run the Inventory Cost Adjustments Report (FV091R13) for the date range being reconciled. This report provides you with the total change made to the inventory subledger as a result of average cost adjustment and price protection transactions. The report also lists the total adjustment that should be made to the cost of sales accounts in the general ledger as a result of Price Protection processing. As of the 275 revision, the Average Cost Adjustments are included in the Inventory Activity by Transaction Type report and the Cost Adjustments report only needs to be run if you are processing Price Protection Transactions.

7. Prepare a reconciliation worksheet using the information listed on the above reports. A description of each of the transaction types which affect the Inventory, WIP and Pending subledgers are listed in the Reference Information section of this document.

Inventory Subledger - Daily Reconciliation Worksheet

Inventory Subledger Balance - beginning (1) 125000.00

Plus

Inventory Activity by Transaction Type (2) -25000.00

Less

Inventory Cost Adjustments (see note 3) -0-

Calculated Subledger Balance (4) 100000.00

Inventory Subledger Balance - ending 100000.00

Variance -0-

(1) This report should be a verified beginning balance and should be printed in detail and preferably to file so that the file can be searched if any problems are discovered during the balancing procedure.

(2) Add the net total from the Inventory Activity by Transaction Type to the beginning subledger balance and compare the result to the ending subledger balance.

(3) The Cost Adjustments report should only be included if you are running a version of the system prior to File System 275 or if you are processing Price Protection transactions.

(4) The calculated subtotal should match the Ending Inventor y Subledger balance.

8. Check if the calculated total matches the ending report. If the totals are in agreement, file the reports (or archive the files and store the daily reconciliation worksheet for future use). If the totals do not match, the cause of the variance should be resolved. If the subledger cannot be reconciled, the reasons for the variance may include the following.

• Reports used to balance subledger are invalid or not produced properly. If the inventory valuation report is produced at the wrong time (ie does not include all activity for period or includes activity from next period), the report cannot be used to balance the subledger. The validity of the report can be checked by using the inventory activity file to determine if activity from last transaction for period is reflected in report totals and to confirm that activity from next period is not reflected in report total.

• Note: EDI (Electronic Data Interchange) invoice processing can cause situations in which invoices will be created by the system with dates different than the dates of the Inventory Activity records for the invoices. For example if invoices are created on the evening of 10/1 using 10/1 invoice dates and they are processed after the system date has been changed to 10/2, then the invoices will show 10/1 invoice dates and the inventory activity records will be dated 10/2. This can cause reconciliation problems if the ending inventory valuation for 10/1 is printed after the EDI invoices have been processed. In this example the result would be a 10/1 ending inventory valuation which includes 10/2 activity. This type of timing difference needs to be handled properly and or accounted for during the reconciliation process in order to balance the subledger.

• Items have been changed from Stocking to Non stocking during the period (ie Trk flag in file 70 is changed from Y to N) or vice versa. Non stock items are not included on the inventory valuation and changing the value in this field can cause undocumented (unrecorded) changes to the inventory valuation. To confirm that this value has been changed you may compare the beginning and ending valuation reports and the reports listing inventory activity to determine if there is a change to the valuation not backed up by activity detail, or you can use the inventory activity detail records for each item to see if the value in the field has changed (the most current versions of programs affecting inventory now write the value in the Trk field to the inventory activity records.

• Transactions have been improperly processed. Use of obsolete programs (ie Materials Conversion), improper versions of some programs, and disregard for system design and procedures may cause undocumented or incorrect updates to the inventory subledger. The effects of each of these types of errors differ and they must be resolved on a case by case basis.

• Off line editors have been used to change item quantities and or costs in the Inventory Master file. Off line editors do not write to the inventory activity file and improper use of the programs can result in undocmented changes to the inventory subledger.

• Data loss. Loss of inventory master or inventory activity records can cause the inventory subledger to go out of balance. This occurrence should be extremely rare on a stable system. Recovering from data loss is a complicated procedure which is handled on a case by case basis and is not discussed in this document.

Balancing the Pending Subledger