Reconcile Inventory Workshop

By Andrew Needham

Tuesday, July 8, 2013

Closing Inventory Control Period

What is an Inventory Control Period?

An Inventory Control Period is the length of time which inventory transactions can occur without interruption.

What are Analysis Reports?

Analysis reports are various inventory calculations that you can print and review at the close of an Inventory Control period. The Inventory Control application uses the end period dates to calculate analysis reports.

Why do I need to Release Unreleased Documents?

Before you close the Inventory Control period, you need to release all the unreleased documents. The reason for this is so that the Inventory Control period can accurately capture period end balances and calculate the average stock-on-hand quantity for each item location. If any unreleased documents remain, the Inventory Control application will not allow you to close the period.

Note: In transit documents can remain open during period end processing.

Closing the Inventory Control Period

Closing the Inventory Control period is a process that enables the Inventory Control application to share transaction and account information with the General Ledger application. This process is necessary to keep accounts across all Lawson applications in balance. The following procedure explains the process of closing the Inventory Control period. After you close the Inventory Control period, close the General Ledger period.

Steps to print a list of unreleased documents:

  1. Access Unreleased Documents (IC222).
  2. Add a job name and description.
  3. Define the parameters that determine how you want the job to run. Consider the following fields.

Document Type / Select a value to limit the report to only one type of inventory document.
Document types include adjustments, bin transfers, intransitreceivings, issues, transfer issues, and receipts.
Document / Type a document code to limit this report to transactions that were created exclusively for the document you specified.
  1. Submit the job to create the list of unreleased documents.

Steps to release unreleased documents

  1. Access Document Release (IC25.1).
  2. Select company.
  3. Select the location.
  4. Select the document type.
  5. Select the unreleased documents you want to release.
  6. Choose the Change form action.

Steps to update General Ledger transactions

  1. Access General Ledger Interface (IC130).
  2. Add a job name and description.
  3. Select the parameters that determine how to run the report.

IMPORTANT SelectNo in the Update field to run the report in Report Only mode the first time you run it. This enables you to review the General Ledger transactions before you update them.
  1. Submit the job.
  2. (Optional) Standard cost companies can run Standard Cost Calculation (PO139) to create reversing entries for the difference between purchase order cost and standard cost for matched not received and received not invoiced accounts.

Steps to close the Inventory Control period

  1. Access Closing (IC190).
  2. Add a job name and description.
  3. Select the company for which you are closing the Inventory Control period.
  4. If you are closing the last period of the year, define the period end dates for the new year.
  5. Add the job.
  6. Submit the job to close the period.
  7. (Optional) Print analysis reports to review period end balances.

To / Print
List items by gross margin return on investment (GMROI) percentages. This report suggests movement classes based on these percentages / Movement Analysis – GMROI (IC152)
List GMROI percentage for items in descending order / Gross Margin Return on Investment (IC224)
Print inventory turnover based on item sales if you use Order Entry / Inventory Turnover (IC134)
Print inventory turnover based on item issues if you do not use Order Entry / Inventory Turnover by Issue Report (IC135)
Print a report of inventory valuation / Inventory Valuation (IC233)
Valuate inventory as of a specific date / Period End Valuation (IC234)
List inventory item material cost, overhead cost, and average unit add-on cost / Lower of Cost or Market Valuation (IC235)
Audit posted transactions by period for a specific accounting unit and account / Departmental Procurement Expense Inquiry Selection (MM80)
List general ledger postings broken down by accounting unit and account / Departmental Procurement Expense Report (MM280)

Closing an Inventory Control Period under System Control

If your company uses system control with General Ledger, you need to follow a special procedure that alerts the application when you have ended the closing cycle for the Inventory Control application. After you have closed the Inventory Control period using Closing (IC190), use the following procedure to end the closing cycle.

  1. Access Subsystem Close (IC199) to end the closing cycle.
  2. Add a job name and description.
  3. Select the parameters needed to complete subsystem close procedures.
  4. Choose the change form action.
  5. Submit the job.

Resolution:
This document was created to help you reconcile your inventory report and the general ledger account. Balancing inventory to the general ledger has frequently been an area of concern for clients, although a common goal for clients is achieving a clean cut-off at the end of each period. If all applications can be closed on the last day of the period, this reconciliation process is relatively easy. However, most Lawson clients cannot close all of their applications on the last day of the period because they still have a large volume of transactions for the period that still need to be processed. This "catch up" processing can adversely affect your end-of-the-month balancing.
In order to insure an accurate inventory, Lawson recommends conducting frequent physical inventories (i.e. cycle counts, month-end counts, and year-end counts). These physical inventories enable you to capture data on inventory shrinkage, damage, inaccurate receivings, missing items, returns, theft, etc..
SUGGESTION 1:
When you first begin using Lawson's IC system, we recommend balancing on a daily or weekly basis. This will help you see the transactions as they occur and understand the posting that takes place. (It is always easier to find errors/discrepancies in a day’s or a week’s worth of transactions than it is to find discrepancies in a month's worth of transactions.)
You can run the following programs on a nightly basis to send transactions to the General Ledger interface file (MMDIST).
IC130 (General Ledger Interface) / Posts inventory transactions that occurred in the IC and Purchase Order (PO) systems.
AP175 (Invoice Distribution Closing) / Posts any variances in unit cost that occurred in PO.
BL123 (Sub-System Update) / Posts inventory shipments from the Order Entry (OE) system that were invoiced to Billing (BL).
GL190 (Journal Posting) / Posts the GLITRANS to the GL system.
After you have run these programs in Update mode, you should run IC233 (Inventory Valuation). IC233 shows you the inventory balance that will appear in GL90 (Transaction Analysis) for your inventory account. IC233 lists the inventory valuation as of the date and time the IC233 program was run.
At this point, there should be no "released" transactions that have not been posted to GL; if there are "released" transactions that haven't been posted to GL, then GL90 will not match. Transactions are included in the inventory valuation at the time they are released, "delivered" in Requisitions (RQ), or "shipped" in OE. Transactions are included in the GL valuation based on the GL posting date and whether or not the GL create/posting programs have been run.
Example :
My beginning inventory balance in GL90.1 for Period 1 was $50,000. After a week of transactions, I ran IC130, AP175, BL123, and GL190. IC130 posted $20,000 to inventory from IC transactions and $50,000 to inventory from PO transactions. AP175 posted $2,000 to inventory, and BL123 posted $60,000 to inventory. My ending inventory balance in GL90.1 for Period 1 is $62,000.00. If I run IC233 right after posting, my inventory report will show $62,000.
Balancing your inventory on a daily or weekly basis will help you understand the postings that are created, and will help you smooth out the End of Month balancing. Once you are familiar with the posting process, you can extend out and balance on a monthly basis.
Keep in mind that if your beginning inventory valuation matches the GL valuation for the inventory account, you are reconciling are the transactions for the month. If at the end of a time-frame the two balances are out of sync, GL190 will display the system and date of the transaction posted. This allows you to trace a transaction back to IC130 for a date.
SUGGESTION 2:
On the last day of the period, you need to "clean up" all your monthly transactions and run the following programs:
BL123
IC130
AP175
GL190
After you have run these programs in Update mode, you should run IC233. When GL closes, you should be able to tie your inventory report to your inventory account. No transactions for this period will be entered after you have run BL123, IC130, AP175, and GL190--any transactions that have not been run will fall into the next period. (In other words, do not post or future date the transaction (for PO) or GL date (for IC) or ship and deliver date (OE/RQ) or invoice (OE) date.
SUGGESTION 3:
If you aren't able to finish all your transactions for the current period (as mentioned in Suggestion 2) because of "catch up" transactions, you will need to follow these steps:
  1. On the last day of the period, run BL123, IC130, and AP175 in Update mode.
  2. Run IC233.
Once you completed these steps, you will have an "as of" inventory valuation. You may then process your "catch up" transactions. After you have cleaned up the transactions for the period, you will need to run BL123, IC130, and AP175 again and save the reports.
Next, take the IC233 report you ran and add in the transactions that were posted by BL123, IC130, and AP175. (By doing this, you are effectively adding in the amounts that were posted to GL.) By saving the reports and IC233, you will have an audit of how you balanced your inventory to your GL inventory account.
Summary :
This scenario allows you to take your time processing transactions that need to go into the period you are closing. This also gives you an accurate account of what will post to GL, and keeps you from having transactions entered in the Lawson inventory that may be posted to a different period. (Please see Suggestion 4 for more information on how Lawson handles inventory transaction dates and GL posting dates.)
SUGGESTION 4:
Lawson has a period-end inventory valuation report called IC234 (Period End Valuation). IC234 captures your inventory with an "as of" date, which can be run by Update date or Transaction date. IC234 is ideal if you will always have a transaction date, update date, and GL posting date in the month you're currently closing.
Transaction date = the date that is entered on the transaction. (This date will default from the system date, but it can be overridden.) For RQ, this is the delivery date. For OE, this is the ship date or the invoice override date.
Update date = the date the transaction is released (IC/PO), delivered (RQ), or shipped (OE).
General Ledger posting date = the date that the transaction is put into GL. (This date will default from the system date, but it can be overridden.) IC transaction screens have a GL date that can be entered. In PO, this is the transaction date. In RQ, this is the delivery date. In OE, this is the ship date (unless it is overridden with an invoice date.) We highly recommend leaving these dates blank and letting the system default the dates in. You will encounter problems if these dates fluctuate. Here are a couple of examples:
  1. You may enter and release an inventory transaction on September 1, but put a GL posting date of August 31 on the transaction. Let's say that this transaction is for $100,000. The result: your GL inventory account for August 31 will have $100,000 more in inventory than is shown on your inventory report.
  2. You may enter and release a transaction on August 31 with a GL posting date of September 30. This will cause your inventory report to be higher than GL inventory account.
  3. You may enter a transaction on August 31 with a GL posting date of August 31, but you may not release the transaction until September 1. If you run IC234 by UPDATE date for August 31, this report will not show the transaction mentioned, but the transaction will show up in your GL totals. However, if you run IC234 by TRANSACTION date, the transaction mentioned above will show on both your inventory report and your GL account.
GL90 shows General Ledger transactions that have posted and a beginning/ending valuation for the inventory account.
IC233 shows inventory valuation as of the date and time the program was run. It uses the stock-on-hand multiplied by the unit cost for the quantity (which is based on your company’s costing method). Any released transaction will show up here as affecting valuation.
IC234, when run by update date , tries to find the first transaction after the cutoff date. If it cannot find the transaction, the system uses the current stock on hand and its unit cost. If we find a transaction, however, we use the stock-on-hand and the unit cost valuation (which is captured at transaction release time and is the stock-on-hand before the transaction took place). (In other words, we basically grab the stock-on-hand at the end of the day for the cut-off date.)
IC234, when run by transaction date, starts with the current stock-on-hand and the unit cost for that quantity (based on your costing method). Then we back out the necessary transactions by transaction date using the transaction quantity and unit cost to the first transaction after the cut-off date.
Additional Information:
Keywords:
IC130 IC234 IC233 GL190 AP175