Physical Inventory Import
The Physical Inventory Import feature is used to adjust the Qty Real Time On Hand for Inventory items to reflect the actual counted quantity.
Normally this adjustment is made as the result of a routine physical count of items “on the shelf.” A spreadsheet (tab-delimited text) template is provided for this import.
During the import, AcctVantage will compare your physical count quantities entered on your import template to the system’s Qty Real Time On Hand value. The system will create a Ready-to-Post (RTP) Inventory Maintenance transaction to adjust (add or draw) the system count to your actual count as of the date you specify in the import template. The Inventory Maintenance Transactions may be reviewed, deleted, or modified prior to Posting.
It is highly recommended to backup your data prior to starting the import process.
1. Make a Backup of your Database!
- Before importing your physical counts, you must select a GL Expense Account that will offset the entry to the Inventory Asset Account. You may use either:
- The default account designated in System Setup. (Step 1.1)
- The Expense Account associated with the Product Class/Warehouse.
2.1. Select a GL Expense Account for Inventory Adjustments.
If you would rather use the Expense Account associated with the Product Class/Warehouse instead of the System Default:
- Open System Configuration ➤ Inventory
- Double-click into the line for Physical Inventory Import Offset Account and proceed to the next screenshot.
2.3. Select a Costing Source for Inventory Adjustments.
The default Cost Source setting is the Default PO Cost on the Product record. If you would rather use an average Cost double-click the line for Physical Inventory Import Cost Source and proceed to the next screenshot.
3. Physical Inventory Import Process
Prior to exporting the Template you should Post all Ready-to-Post (RTP) inventory related transactions currently on your system.
These transactions include Invoices, Purchase Events, Warehouse Transfers and Inventory Maintenance Transactions.
If a transaction is not already marked RTP, it does not need to be posted before doing the import.
4. Export the Template
- Create a selection of Products in the Inventory ➤ Products window.
- Select Export Physical Inventory Import template from the Action Menu. You will be prompted to select a Warehouse.
- Save this file to your hard drive. The file will be a tab-delimited text file named PhysicalInvenImport.txt
5. Prepare the Template in Excel®
The file may be opened in your Microsoft Excel® and updated with current inventory quantities. The only columns to be saved for import are:
- Part Number - This value must match EXACTLY to an active Part Number in your Product list.
- Warehouse - This is the Warehouse ID not the Warehouse Name.
- Quantity - This is your ACTUAL COUNT of the items currently on hand. Do not put in the variance/delta/difference/change quantity.
The rest of the columns (everything from Column D and to the right) are for informational purposes only and should be deleted before importing.
The completed import template...
The Import Template should look similar to this one when completed.
- Do not change Cell A1.
- Enter the Post To date in Cell A2.
- Part Numbers in Column A should be listed exactly as they are on the Product record.
- Warehouse ID (not the Warehouse Name) is listed in Column B.
- Enter your ACTUAL COUNTED QUANTITIES in Column C under Qty.
- Delete all columns to the right of Column C.
- The last row below the Part Numbers in Column A must have EOF in that cell.
6. Save the Template as a tab-delimited text file
7. Import the Template
- Open the General Ledger ➤ Imports and External Transactions window
- Navigate to the List Menu (top of the screen) and select Import.
- When prompted, select your import template.
The system will now create the transactions behind the scenes. This may take several minutes. You can check the progress by double-clicking the Imports and External Transactions record to see the results. However, do not leave this record open as doing so will prevent the import from progressing.
8. Review the imported Inventory Maintenance Transactions
- Highlight the Imports & External Transactions record you’ve just created and cross-reference to the Inventory Maintenance window.
- Review (and modify if necessary) the newly created Inventory Maintenance transactions as necessary.
- Post the Inventory Maintenance transactions when you're finished.
- Be sure to make a single entry on the spreadsheet for each Part Number/Warehouse combination.
- It’s okay to have more than one Warehouse on an Import Template. However, when creating the templates you will only be able to export one template per Warehouse. You can merge these templates later.
- The date you specify in Cell A2 of the Import Template is the Post To date for the transactions that will be created.
- If you use Excel®, format each column as TEXT. After saving your text file, open it in a word-processing program and use the Find/Replace function to strip out any quotation marks. Excel® will wrap cells in a text file in quotation marks if it encounters a comma or other potential field delimiter.
- If your physical count matches the system count no Inventory Maintenance record will be created.
- It’s always a good idea to make a backup prior to doing an import.
- If the import results in a draw (reduction) in Inventory AcctVantage will draw on a FIFO (oldest first) basis.
- This process cannot be used for Serialized items.