RPI Consultants Knowledge 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:

  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 15th 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)

suchdescription

Want More Content?

Sign up and get access to all our new Knowledge Base content, including new and upcoming Webinars, Virtual User Groups, Product Demos, White Papers, & Case Studies.

[gravityform id=”4″ title=”false” description=”false” ajax=”false” /]

Follow us online for faster access to announcements, knowledge base updates, and upcoming events!

Entire Knowledge Base

All Products, Solutions, & Professional Services

Contact Us to Get Started

Don’t Just Take Our Word for it!

See What Our Clients Have to Say

Denver Health

“RPI brought in senior people that our folks related to and were able to work with easily. Their folks have been approachable, they listen to us, and they have been responsive to our questions – and when we see things we want to do a little differently, they have listened and figured out how to make it happen. “

Keith Thompson
Director of ERP Applications

Atlanta Public Schools

“Prior to RPI, we were really struggling with our HR technology. They brought in expertise to provide solutions to business problems, thought leadership for our long term strategic planning, and they help us make sure we are implementing new initiatives in an order that doesn’t create problems in the future. RPI has been a God-send. “

Skye Duckett
Chief Human Resources Officer

Nuvance Health

“We knew our Accounts Payable processes were unsustainable for our planned growth and RPI Consultants offered a blueprint for automating our most time-intensive workflow – invoice processing.”

Miles McIvor
Accounting Systems Manager

San Diego State University

“Our favorite outcome of the solution is the automation, which enables us to provide better service to our customers. Also, our consultant, Michael Madsen, was knowledgeable, easy to work with, patient, dependable and flexible with his schedule.”

Catherine Love
Associate Human Resources Director

Bon Secours Health System

“RPI has more than just knowledge, their consultants are personable leaders who will drive more efficient solutions. They challenged us to think outside the box and to believe that we could design a best-practice solution with minimal ongoing costs.”

Joel Stafford
Director of Accounts Payable

Lippert Components

“We understood we required a robust, customized solution. RPI not only had the product expertise, they listened to our needs to make sure the project was a success.”

Chris Tozier
Director of Information Technology

Bassett Medical Center

“Overall the project went really well, I’m very pleased with the outcome. I don’t think having any other consulting team on the project would have been able to provide us as much knowledge as RPI has been able to. “

Sue Pokorny
Manager of HRIS & Compensation

MD National Capital Park & Planning Commission

“Working with Anne Bwogi [RPI Project Manager] is fun. She keeps us grounded and makes sure we are thoroughly engaged. We have a name for her – the Annetrack. The Annetrack is on schedule so you better get on board.”

Derek Morgan
ERP Business Analyst

Aspirus

“Our relationship with RPI is great, they are like an extension of the Aspirus team. When we have a question, we reach out to them and get answers right away. If we have a big project, we bounce it off them immediately to get their ideas and ask for their expertise.”

Jen Underwood
Director of Supply Chain Informatics and Systems

Our People are the Difference

And Our Culture is Our Greatest Asset

A lot of people say it, we really mean it. We recruit good people. People who are great at what they do and fun to work with. We look for diverse strengths and abilities, a passion for excellent client service, and an entrepreneurial drive to get the job done.

We also practice what we preach and use the industry’s leading software to help manage our projects, engage with our client project teams, and enable our team to stay connected and collaborate. This open, team-based approach gives each customer and project the cumulative value of our entire team’s knowledge and experience.

The RPI Consultants Blog

News, Announcements, Celebrations, & Upcoming Events

News & Announcements

High Fives & Go Lives

Upcoming Events

Go to Top