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 current 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 you specify in the import template, with a Post To Date matching the As Of date.

Best Practice is to complete all transactions for the counted Products, then cease operations/data entry for those Products while you count. Backdating an import will not change/edit  or in any way touch existing transactions that have already been entered into AcctVantage.

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!

Navigate to Administration ➤ Backup to execute a backup of your AcctVantage database.

Make a Backup of your Database!

2. Setup: Establish GL Accounting for inventory counts

See THIS ARTICLE for instructions on setting up the GL accounting for inventory counts. You will need to choose the GL expense account and the cost source to use for inventory adjustments.

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

  1. Create a selection of Products in the Inventory ➤ Products window.
  2. Select Export Physical Inventory Import template from the Action Menu. You will be prompted to select a Warehouse.
  3. Save this file to your hard drive. The file will be a tab-delimited text file named PhysicalInvenImport.txt
Export the Template

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.

Prepare the Template in Excel®

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.
The completed import template...

6. Save the Template as a tab-delimited text file

The Import Template must be saved as a Tab Delimited Text file!

Save the Template as a tab-delimited text file

If you are using MS Excel® to prepare your template and you see this message click Continue.

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.

Import the Template

Processing...

Processing...

8. Review the imported Inventory Maintenance Transactions

  1. Highlight the Imports & External Transactions record you’ve just created and cross-reference to the Inventory Maintenance window.
  2. Review (and modify if necessary) the newly created Inventory Maintenance transactions as necessary.
  3. Post the Inventory Maintenance transactions when you're finished.

Notes

  • 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.