Categories: Infor CloudSuite & LawsonKnowledge Base

Correcting Inventory Unit Costs – Via Excel Add-ins

Occasionally a storeroom may face a situation where an item has been recognized and potentially valued at the wrong unit costs.

Following are the basic steps for correcting both the value of the item and any issues that may have been charged at the wrong unit costs – leveraging the Lawson Excel Add-ins.

Correcting the Value of Stock on Hand

Standard

To correct the Unit Cost of un-issued items:

use IC24 to adjust out Stock on Hand
use IC24 to adjust the Stock on Hand back in at the correct price

Special Scenario 1

If the item has Allocated Quantities, only the unallocated quantity can be removed. When possible reenter the unallocated quantity at a Unit Cost that results in the correct Average cost for the all the SOH.

To calculate the Unit Cost use the following formula:

((Correct Unit Cost * SOH) – (Incorrect Unit Cost * Allocated Qty)) / (SOH – Allocated Qty)

Note that if the Allocated Qty = 0 everything cancels out to “Correct Unit Cost”.

Special Scenario 2

If the item has no SOH, adjust a quantity of 1 in at the correct Unit Cost and then adjust the quantity back out. This is important because even though there is no Stock On Hand the last “Average Cost” of the item will default on several transactions including IC24 adjustments and potentially on the Purchase Order.

Correcting Issues

Step 1 – Identify and reverse incorrect issues

Login into Excel Add-ins Query Wizard
Open or create “Issue Corrections” Query*
Change Date and Item Criteria and run Query
Sort results by Account > Ascending
Delete lines where Account = (Inventory GL Account)
Review Unit Cost on remaining lines. Delete lines where Unit Cost is correct.
Insert Column C and type “Document Num” as header and fill all fields with the following formula: =”XX”&(LEFT(H#,2)&(I#)&”CYY”. Where XX = Correctors initials (i.e. KW), # equals the row number, H is the column for the To/From Company, I is the column for the To/From Location, and YY = the iteration of the set corrections this represents (i.e. 15 for the 15^th time you do it). Remember document numbers must be unique.
Highlight Column. Choose Copy and then Paste Special: Values.
Insert Column J and type “FC” as the header and “A” in all rows
Insert Column R and type “Comments” as the header and “Issues Reverse (+today’s date)” in all rows
Login into Excel Add-ins Upload Wizard
Open or create “IC21_2-Reverse.uwf”**
Choose Rows 2 through X where is X is the final row
Choose “Try to Add then try to Change” and select upload
Watch for Error Messages
Check Issues in IC21.1
Release Issues in IC25

Step 2 Correct Value of SOH (See Correcting the Value of Stock on Hand above for more details)

Step 3 Re-issue at corrected cost

Copy Paste Data to a new Sheet
In New Sheet change header of Column L “New Quantity” and rows to =-K# where # is the row number
Highlight Column. Choose Copy and then Paste Special: Values.
Highlight Column C (Document Num) – choose edit>replace and replace CYY for RYY where YY = the iteration of corrections this represents. Remember document numbers must be unique.
In Column R (Comment) Replace all fields with “Issues Reverse (+today’s date)” in all rows
Login into Excel Add-ins Upload Wizard
Open or create “IC21_2-Correct.uwf”***
Choose Rows 2 through X where is X is the final row
Choose “Try to Add then try to Change” and select upload
Watch for Error Messages
Review Issues in IC21.1
Release Issues in IC25