Inventory Quantity Reconciliation

Usually at year end a physical inventory is performed and then reconciled with SME. Any adjustments required should be understood, however SME provides an inventory reconcile tool.

• NOTE* If you are tracking inventory quantities in QuickBooks, any adjustments made to SME inventory using reconciliation will also need to be made in QuickBooks inventory. Only invoices from SME automatically deplete inventory in QuickBooks. Only POs or Bills from SME automatically increase inventory in QuickBooks.

New Inventory Reconcile in SME 8.1 and Beyond

Features and Benefits of New Inventory Reconcile

• Easier operation for reconciling inventory
• Full tracking of adjustments into "Virtual Physical Count" warehouse.
  • This special warehouse shows all your scrapped or adjusted inventory
  • This warehouse should NOT be used for inventory reporting
  • This warehouse SHOULD be used for reporting of adjustments or scrap of inventory
  • Over time your business may adjust parts both positive and negative. This warehouse will get added stock counts when you decrease regular stock warehouse and decrease if the parts are found again.
  • If parts are found again, inventory reconciliation can be used to move back into a warehouse.
  • You do not have to worry about inventory stock "disappearing" from inventory tracking and reconciliation since all inventory is tracked in and out.
  • This warehouse is forced as inactive so that it's not used in standard inventory reports
  • Note: If you have a custom inventory report it will need to be updated to NOT count this virtual warehouse.
• Batching
  • Batches will allow inventory to be snapshoted for adjustments over multiple days. You can create a batch, make adjustments and then come back later and continue to make adjustments for that batch.
    • For example, you may want to run a quarterly inventory reconcile with the following process:
    • Create a new Batch
    • adjust physical counts on day 1 and click Reconcile Batch but select not to close the batch
    • Do additional physical count adjustments on the next day and then close the batch.

Inventory Reconciliation version 8.1 Operation

1. Select Utilities>Utilities>Inventory>Inventory Reconciliation and click Run It
2. 
3. Batch: Select New for a new batch or if you already created a batch you want to continue then select that batch.
   1. Select New>All Inclusive to adjust all warehouses. Note: If you have lots of data, this is not recommended.
   2. Select New>For Location Only to adjust one warehouse.
4. Filter on Location: Inventory adjustments need to be done per warehouse, so select the warehouse you are adjusting. Note that you will need separate batches for each warehouse.
5. Make adjustments in the "Phys. Count" column by editing the count value.
   1. If you want a 0, Zero count then keep the Phys Count as zero and check "Accept" checkbox.
   2. Serialized parts will show individually since they are each unique. Be sure to adjust the specific serialized parts.
6. Once you have all the counts, select Reconcile Batch
   1. If you are done with your adjustments for this warehouse or period, click Yes to "Close batch when completed". If you plan to make additional adjustments select No.
7. Use the top bar for filtering or sorting just like search views
8. Print Reconciliation Report will print the
   1. Note, and inventory template report bust be defined in Setup>Company>Printing>Inventory Template
9. Export to Excel to get an excel report of all the adjustments and inventory

Advanced Functionality with Physical Count Data Uploads New instructions:

1. Export Location table to get the UID's of your warehouse
2. Export Inventory items mainly the following fields:
   1. UID
   2. Name (optional, this is exported for reference but not used)
   3. Number (optional, this is exported for reference but not used)
   4. Export as a csv file. For this example export as InventoryRecon.csv
3. Open InventoryRecon.csv in excel and add do the following:
   1. Add a column called LocationUID. Copy the Location UID from step one for all items you want to count
   2. Add a column called iCount and enter all the physical counts for each item. Note that this should not be negative.
4. Import InventoryRecon into the InvenRecon table as follows:
   1. Go to Utilities>Import>Import Data and select Run It
   2. Select the button next to Source Filename and Load the InventoryRecon.csv files from above. Note in the bottom right corner change teh file type from Excel to csv (or All) to find this file. Select Open
   3. For Destination Table select InvenRecon and check include UID fields
   4. 
   5. Set the field mappings as follows:
      1. ItemUID
      2. Name and Number should be set not to import
      3. iCount
      4. LocationUID is the warehouse location
      5. 
      6. 
   6. HIGHLY recommended to save this template. Select Save Template and give name like InventoryReconcile
   7. Select Start to import this file
5. Now that the inventory reconcile data is loaded it's time to reconcile the warehouse
6. Go to Utilities>Inventory>Inventory Reconcile and select Run it
   1. Select the warehouse you want to reconcile under Filter on Location
   2. Create a new batch for the warehouse location you want to reconcile by selecting New>For Location Only. Note that this takes a snapshot of inventory and can take a while.
   3. Optional: To see the data you imported into InvenRecon select View Physical Counts
   4. Select Update Physical counts. This will take the data from InvenRecon imported above and setup the physical counts. Note that this may take a while with lots of parts.
   5. Go through and accept for each part that you want to update for the new physical count.
   6. When completed accepting, select Reconcile Batch

Old instructions

• Note: This functionality is requires data to be entered through an external program. At this time, no external program has been created so this functionality is not available without addition work.
• View Physical Counts will load the physical count data table to be used for reconcile.
• Update Physical Counts will update the data table.
• Export to Excel can be used for an excel report or to use for importing a physical count file as described below. Steps to export to excel for a physical count file:
  • Create new batch or select existing batch. For new batch you can select for all locations (recommended in your case I think) or a specific location/warehouse.
  • Select Export to Excel
  • Use this file for manipulation to upload in the next step
• Physical Count Table Structure. Note this is technical since it would most likely be populated by software developer from external system or uploaded from excel.
  • Table name: InvenRecon
  • Serialized items: Load process for Serialized items is to just load the serial numbers and location. SME logic will find the item information based on matching serial number. So you can just upload a bunch of serial numbers and location that you have in stock
    • SerialNumber
    • LocationUID: The location of where the serialized part is currently located. If SME reconcile finds this part in another location, it will MOVE it to this location during reconcile.
  • Non-serialized items: Import or load the following fields:
    • LocationUID: UID of the warehouse/location
    • ItemUID: UID of the inventory item
    • iCount: Physical inventory count
  • Field that should not be imported or loaded:
    • BatchID: This should NOT be filled in or imported. SME will look for all records with no batchid to assign to a batch.
    • Reconciled: Would usually be false or 0 when data uploaded, then SME will set to true once reconciled.
    • Notes: This will be filled in by the reconcile process, so should not be imported or loaded.

Inventory Reconcilation BEFORE SME 8.1

Following Section Applies to before SME 8.1

Adjusting a single inventory item

If you are adjusting a single inventory item, you can open the Inventory Reconciliation window from the inventory item.

1. Go to the Inventory item in SME.
2. Click Actions > Inventory Reconciliation
3. Inventory Reconciliation window opens for that item only.

4. In Inventory Reconciliation window, you will need to expand the item (first arrow below)
5. Expand the warehouse the stock is in (second arrow below)
6. Type the accurate amount of stock in the warehouse in Physical Count column (circled below) and then hit tab on your keyboard. After you hit tab, the next column shows you the adjusted amount and the Accept column is checked.
7. Click Post Changes at the bottom.

Alternative to manage stock adjustments

Another option for adjusting stock quantities for inventory items is to setup a separate warehouse in SME to move the excess stock to. This way, if the adjustment was done in error, it is very simply to move the stock back to your working warehouse location. When you do your usual physical count, set the ‘Stock Adjustment’ warehouse counts to zero using the inventory reconciliation utility.

Set up a new warehouse and name it Stock Adjustment or something similar:
1. Go to the Setup Module and click Company
2. Select Lists.
3. Locate Warehouses in the Lookup Lists.
4. Click New in the lower right corner.
5. Enter the new warehouse name such as Stock Adjustment.
6. Click OK

To move stock out of working warehouses into Stock Adjustment warehouse:
1. Locate the inventory item with wrong stock quantities.
2. Select the Material Details tab.
3. Click Move Stock.
4. Enter the quantity to move and select the new warehouse from the Destination drop down.
5. Click OK.

You will now have a detail line showing the stock adjustment. You will need to inform users to not use the stock in the ‘Stock Adjustments’ warehouse.

Adjusting multiple inventory items

1. Go to Inventory module and select the item you need to adjust.
2. Copy the Item Number.

NOTE – If your Item Names are unique, you can use that and substitute Name for Number for the remainder of the procedure.

3. Go to Utilities > Utilities (1)
4. Select Inventory. (2)
5. Select Inventory Reconciliation (3) and click Run It (4).

The Inventory Reconciliation window will open listing all of your inventory items.

6. Click in the Number column header and select (Custom..).

7. In the Custom Filter window, enter the Item Number in the field circled in the image below.

8. Click OK.

Notice the inventory list filtered down to the item you need to adjust.
9. Click on the inventory item row (1 in image below). The editing window will expand.
10. Click to expand the warehouse locations. (2)
11. Enter the accurate inventory count in the Physical Count'Bold text' column (3).
12. Check the box in the Accept column.
13. Click Post Changes (4).

14. Click Yes to message in Information window.
15. When the screen clears, click Close. The quantities will be adjusted.