16 Nov Transfer of Inventory Beginning Balances to a New File
Transfer of Inventory Beginning Balances to a New File
Q – We are restructuring and need to create a new company. All accounts will change, however inventory will remain the same. What is the easiest way to create a new company file that will achieve this?
A – Just let me say before I provide the solution, that I picked this question for two reasons. It was submitted in a slightly different format two times by the QuickBooks user, and it sparked my interest. In addition, quite frankly, I thought I had a couple of ideas on how to solve it. After three hours of frustration, I was determined I was going to win. What follows is my thought process on what “should work” with a final explanation of what “did work.”
I started with the thought that I could import the lists for the majority of the item information. I could then use the inventory valuation summary report because it includes the average cost and extended value not included on the list import. I took that report and added the item type and as of date in Excel. I then proceeded to try to use the Excel import new with version 2004. However, because the account columns were missing from that report, it would not let me import, even though the inventory item was already there. I tried going at it several different ways, and short of merging the two reports together (which if I was more of an Excel wiz) I could not achieve an automated solution for a large inventory list with inventory balances to be transferred.
My next attempt was using an Excel Add-In from FLEXquarter Solutions. It is called the QTable grabber, and I must admit I have known about this tool for some time, but had not found a situation where I understood its power, until now. Basically when this tool is used for the ItemInventory table all the information needed to import into the new QuickBooks file is there, including the average cost.
The next tab has the fields. To move them all onto the resulting report, press the arrow until everything is on the left and nothing is left on the right. I ended up with extra columns I did not use, but better too much than not enough in my opinion at that point. There is another filter tab, but in this case I had already filtered for the inventory only based on the table chosen so all I did was press insert data. The columns and related information appeared in the Excel spreadsheet.
Although there is a total value column, in my example, it was not filled in, but I was quickly able to add the formula, add the column for item type (i.e. inventory part) and “as of date” for the value. Once it was saved as an Excel file, I used the new Excel import available with version 2004. Keep in mind the Excel import does not support Group or Inventory Assembly type items. In this case everything was an inventory type item. It worked like a charm.
TRICK: For all dollar amounts, make sure the entry is only 2 digits (i.e. dollars and cents). If the value is extended further than that an error will be noted during the import.
THANK YOU!! Thanks to K. Barrett for the challenge. And thanks to Chuck at FLEXquarter Solutions for helping me find an answer. For a more automated solution, read about Karl Irvin”s Beginning Balance Transfer Utility.
List Limits Expanded
For most QuickBooks users, the list limit for the QuickBooks Pro and Premier products of 14,500 is sufficient. For some, however, that is not the case. We have been seeing increased list size for a variety of reasons. One of the most common is the increased number of customers as the result of web site sales. For version 6 and prior, the list limit was doubled for the Enterprise Solutions Product. New with version 7, the Enterprise Solutions limit has been removed.