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:

1. use IC24 to adjust out Stock on Hand
2. 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

*Issue Corrections Query:

Module: IC

Table: IC Trans

Fields:

• Company
• Location
• Document
• Doc Type
• Item
• Trans-Date
• From To Company
• From To Loc
• Quantity
• Unit Cost
• Stock UOM
• Relations MMDist.Account
• Relations MMDist.AcctUnit
• Relations MMDist.DistCompany
• Relations MMDist.DistAmount

Maximum Records = 1000

OTM Values to Return = 2

Criteria:

• Company = Company
• Location = Inventory Location
• Doc Type = IS
• Trans Date >= Date of pricing error
• Entered Item = Item with Unit Cost error

**IC21_2-Reverse.uwf:

Map –

• Company = IAC-Company (f4)
• Location = IAC-Location (f6)
• Document Num = IAC-Document (f8)
• From To Cmpy = IAC-From-To-Cmpy (f9)
• From To Loc = Req-Loc (f10)
• FC = Line-FC1 (f17)
• Item = ICT-Item1 (f25)
• Quantity = ICT-Quantity1 (f28)
• Stock UOM = ICT-Tran-UOM1 (f29)
• Unit Cost = ICT-Unit-Cost (f31)
• MMDist.Acct Unit = ICT-Off-Acct-Unit1 (f38)
• MMDist.Account = ICT-Off-Account1 (f39)
• Comment = ICT-Line-Comment1 (f46)

***IC21_2-Correct.uwf:

Map –

• Company = IAC-Company (f4)
• Location = IAC-Location (f6)
• Document Num = IAC-Document (f8)
• From To Cmpy = IAC-From-To-Cmpy (f9)
• From To Loc = Req-Loc (f10)
• FC = Line-FC1 (f17)
• Item = ICT-Item1 (f25)
• New Quantity = ICT-Quantity1 (f28)
• Stock UOM = ICT-Tran-UOM1 (f29)
• MMDist.Acct Unit = ICT-Off-Acct-Unit1 (f38)
• MMDist.Account = ICT-Off-Account1 (f39)
• Comment = ICT-Line-Comment1 (f46)