Having accurate inventory is VERY important for the success of any ERP system. An accurate inventory will improve the efficiency of Customer Service, Purchasing, Manufacturing, and Shipping. Improving efficiency means reducing cost and waste in almost all of your processes.
We suggest that you measure inventory accuracy as part of your monthly “Dashboard”. This can be done by setting “accuracy standards” for your items. Not every item needs to be exactly correct in order to be “accurate”. For low cost, easily obtained items +/- 10% might be OK. For high cost, long lead time items, +/- 0% might be appropriate. The typical process for obtaining these numbers is through cycle count records, so if you are not already doing that, we highly recommend that you start.
First of all, there are two levels of inventory “accuracy”:
1. The first level is having the Inventory System reports match the General Ledger inventory account balances. This first level must be consistently achieved first before the second level can be addressed.
2. The second level is having the Inventory system reports match “reality”- the quantities and values in the inventory reports match the physical counts and documented values within an acceptable amount of error.
Second, there are multiple types of inventory in an ERP system:
1. The perpetual or “shelf” inventory of items that are sitting in item locations.
2. Job/Project WIP inventory represented by the dollar value of material, labor and overhead issued into jobs that has not yet been relieved.
The reports that are recommended to represent the “shelf” inventory are the “Total Inventory Value by Account”, the “Item Cost by Product Code”, and/or the “Inventory by Location” reports. The reports that are recommended to represent the Job WIP inventory are the “Total WIP Value by Account” and “WIP Valuation” reports. The reports that are recommended to represent the GL system are the “Trial Balance” and/or the “General Ledger by Account” reports. You may also need to run the “Journal Transaction” report sorted by Account for any un-posted journals.
Level 1- Inventory reports should match the General Ledger Account Balances
Under “normal” conditions, these two measures of inventory value should always move in lockstep with each other. There are two types of errors that can cause these to diverge:
A. Setup errors
B. Procedural errors
The connection between the perpetual inventory system in SyteLine and the General Ledger are the Product Codes and Distribution Accounts. Every item must be assigned to a Product Code. Each Product Code must have at least one Distribution Account record, and may have multiples- one for each Warehouse.
Common Setup Errors, Shelf Inventory-
1. Product Code Variance and Adjustment accounts set to Inventory accounts rather than expense accounts.
The Product Codes hold the Inventory Adjustment and Variance accounts. These accounts need to be set to the proper expense accounts rather than inventory accounts. Otherwise, quantity adjustments done in the system could debit and credit the inventory account numbers, causing the perpetual inventory to diverge from the GL balances.
2. Distribution Account Inventory accounts set to Expense accounts or the wrong Inventory accounts.
The Distribution Account records connected to the Product Codes contain the Inventory accounts for the items. The Inventory accounts in the Distribution Accounts record are the accounts that get populated into the Item Locations for an item when it is created. It is these accounts in the Item Locations that actually determine the accounts for GL transactions. If the accounts in the Distribution Accounts records are set incorrectly, the Inventory system reports will diverge from the GL balances.
3. Item location accounts not set to the correct accounts.
Even though the accounts may be set correctly at the Distribution Accounts, it is possible that the Item Location accounts are incorrect. When an item is created, SyteLine copies the Distribution Account Inventory accounts to the Item’s first Stock Location. It is these accounts that drive the GL transactions.
When an item is changed from one Product Code to another, a message appears that warns the user that the accounts in the locations must be changed manually. Since this does not occur often, many users do not understand the importance of this message and fail to update the accounts.
Common Procedural Errors, Shelf Inventory-
1. PO Lines for Non-inventory items set to Inventory accounts
A buyer creates a purchase order for something that is not in the Items table, but sets the account on the PO line to one of the Inventory accounts. When the item gets received, it hits that account, but never enters the Inventory system.
2. AP transactions entered directly against inventory accounts
When an invoice comes in from a vendor and there is no purchase order to voucher it against, the AP person creates an AP Voucher and distributes it to an inventory account rather than an expense account. No AP voucher should ever be created against an Inventory account.
3. Not posting all journals or accounting for all un-posted journals during reconciliation
When doing a reconciliation, the user must make sure that either all journals are posted, or they have taken into account the un-posted amounts in every journal that pertain to the inventory accounts. Also, the reconciliation must be done during a time when absolutely no transactions are entering the system- job transactions, PO receipts, shipments, etc.
4. Transactions with dates out in the future
The GL is date driven, but the inventory reports are not. If someone enters a PO receipt with a date out into the future, the inventory reports will show that balance immediately, but the change will not be in the account balances until the date of the transaction. So, one of the checks is to run the account balance report a couple of years out into the future and see if the balance remains the same as it is today.
Common Setup Errors, WIP Inventory:–
1. Product Code Variance and Adjustment accounts set to WIP Inventory accounts rather than expense accounts.
The Product Codes hold the Inventory Adjustment and Variance accounts. These accounts need to be set to the proper expense accounts rather than inventory accounts. Otherwise, quantity adjustments done in the system could debit and credit the inventory account numbers, causing the WIP inventory to diverge from the GL balances.
2. Product Code WIP accounts set to Expense accounts or the wrong Inventory Accounts
The Product Codes contain the WIP account numbers and these are referenced every time a job is created for an item with that Product Code. If those are set to the wrong accounts, the GL and the Job WIP reports will diverge.
Common Procedural Errors, WIP Inventory
1. General Journal and AP Voucher transactions that hit the WIP accounts
The WIP accounts should only be hit by normal job transactions and job material transactions. They should never be directly hit by an AP voucher. The only time that they should be hit with a General Journal entry is to correct a reconciliation issue.
I strongly suggest that every company reconcile their Shelf and WIP inventory to the G/L account balances every month. A simple spreadsheet can be used to do this. Match the total of the Account balances plus any unposted journal transactions to the Inventory reports of Total Inventory Value by Account and Total WIP Value by Account. Be sure that no activity is going on while you print these reports.
If you find any problems, make the appropriate corrections and reconcile on a weekly basis until you are satisfied that everything is again flowing as it should.
Level 2- Matching Inventory Reports to “Reality”
This is almost always a matter of procedural rather than setup issues. Here are some of the more common problems:
Quantities, common procedural errors-
1. Inaccurate reporting of PO Receipts
2. Inaccurate reporting of Job Material Issues
3. Inaccurate reporting of Job Production
4. Inaccurate reporting of Cycle Counts and Physical Inventory Counts.
5. Inaccurate reporting of CO Shipments
6. Unauthorized Misc. Issues and Receipts
Assuring Accurate Quantities-
In order to keep accurate quantities users need to:
· Provided with proper process training and documentation
· Provided with the proper technology that makes the data entry easy and accurate
· Held accountable for following processes and for their inventory accuracy
The most common way that quantity errors are fixed is through the Physical Inventory process. However, if that is only done once a year, it allows for a lot of potential error. Plus, no activity can be going on when this is done, so you lose production time.
A more aggressive method is to use Cycle Counting. This can be done in conjunction with Inventory ABC analysis, which marks all items as being either an A, B, or C depending on current on-hand value or quantity. For example, a company may count its “A” items every month, its “B” items every three months and it “C” items once a year. A good cycle counting process can eliminate the need for a full physical inventory, which adds more production days to your calendar.
Assuring Accurate Costs
Every item in SyteLine has two costs- Current Cost and Unit Cost. Current Cost does NOT affect inventory value- it is usually used to represent the most “up to date” cost of the item. Unit Cost is either the Standard Cost or the Actual Cost of the next item to be issued out of inventory (under Actual FIFO, LIFO or Average). Under FIFO/LIFO, the inventory is valued at the total of all the individual FIFO/LIFO stacks.
For Purchased items, Current cost is whatever you enter in the Item Cost screen under Current Purchased Unit Cost. This can be set to update automatically from each Purchase Order receipt in the Purchasing Parameters screen, but does not have to. Note: The Unit/Standard cost is the cost that will drop in to Purchase Order lines as the default cos, not the Current Unit Cost.
For Manufactured items, Current cost is the rolled up Current Costs of all the items in their bills of material, plus the labor and overhead costs from their Current Operations. This is the most up to date “theoretical” cost to make the item, but it does NOT affect inventory value.
Standard Costs for all items can only be updated by running the Roll Current Cost to Standard Cost utility. They cannot be updated manually. This utility can be run for individual items.
Costs, common procedural errors-
1. Not performing Current BOM cost roll ups on a regular basis
2. Setting items as Manufactured, but with no routing/BOM- this results in $0 Current Cost the next time that Current BOM Cost roll up is run.
3. Failing to Roll Current to Standard Cost on new items, resulting in $0 value of these items.
4. Failing to keep Standard Costs up to date when significant, permanent changes are made to their purchased costs or routing/BOM.
5. Failing to review Unit costs regularly for reasonableness, especially for manufactured items. The best time to do this is at the conclusion of a job.
6. Failing to review Standard Cost Variances on a regular basis- weekly or monthly.
In summary, the accuracy of your inventory is a good indicator of the health of your ERP system. Good processes and responsible users will result in accurate inventory and this will improve the overall efficiency of your ERP system and your organization as a whole. It is well worth making an effort to set up a plan to measure your accuracy monthly and to provide your people with the proper training and technology to keep it as accurate as possible.
For more ERP information, videos, how-to guides and helpful information – visit www.Godlan.com