This article will provide you with step by step instructions of how to import an inventory adjustment transaction into QuickBooks Pro, Premier or Enterprise. In addition we will provide you with some common pitfalls and some tip and tricks. If you do not already have a copy of our Transaction Pro Importer 5.o (“TPI5”) you can request a free trial and test this out here.
First here are some things that you need to pre-plan pre-import:
- Make sure all the items in your import file exist in QuickBooks pre-import. If the items do not exist you can first import them into your QuickBooks company file using the import type, Item List, within TPI5.
- If you are using sub-items in QuickBooks include both the item and sub-item in your import file separated by a colon (eg Item:sub-item). Also make sure there are no spaces on either side of the colon.
- If the entire import file is one big inventory adjustment you can provide a static value for the RefNumber on the TPI5 mapping screen. Read more about multi-lined transactions here.
- If you are adjusting both quantities and values you need to do two separate imports. You can use the same file but modify the mapping for each of the two imports.
- If you are using Advanced Inventory in QuickBooks Enterprise you need to have a separate inventory adjustment transaction for each site.
In my opinion inventory is where a lot of accounting and bookkeeping mistakes occur. I highly suggest you check out this video at School of Bookkeeping to help you alleviate any accounting errors. Also please note that I have been a paid subscriber to this service for over a year and highly recommend it for business owners, accountants and bookkeepers.
Below is a screen shot showing where all the fields on the TPI5 mapping field reside in QuickBooks. You should review this along with the data dictionary below the screen shot.
Inventory Adjustment Data Dictionary for Transaction Pro Importer 5.0
|Field||QuickBooks Form - Field Description||Required Field||Type/Length|
|RefNumber||Adjust Quantity/Value On Hand - Reference Number||Yes||Char 11|
|Account||Adjust Quantity/Value On Hand - Adjustment Account||Yes||Char 31|
|Transaction Date||Adjust Quantity/Value On Hand - Adjustment Dat||Date|
|Memo||Adjust Quantity/Value On Hand - Memo||Char 4095|
|Item||Adjust Quantity/Value On Hand - Item||Yes||Char 31|
|New Qty||Adjust Quantity/Value On Hand - New Quantity||Numeric|
|New Value||Adjust Quantity/Value On Hand - New Value||Numeric|
|Adj Qty||Adjust Quantity/Value On Hand - Quantity Different||Numeric|
|Adj Value||Adjust Quantity/Value On Hand - Total Value||Numberic|
|Customer||Adjust Quantity/Value On Hand - Customer:job||Numeric|
|Class||Adjust Quantity/Value On Hand - Class||Char 41|
|Inventory Site||Adjust Quantity/Value On Hand - Inventory Site||Char 31
If you have any questions please feel free to post them in the comments below.