Posts Tagged ‘Manage Inventory’

Importing your inventory into QuickBooks

August 5, 2009 Leave a comment

Watch the web cast here |

When you import an inventory item for the first time you can import the quantity, however you cannot import changes in quantity to an existing inventory item. For this there is the inventory adjustments module.

Figure 1 – this is how you adjust existing inventory item quantities

Importing your inventory listing into QuickBooks can be done easily when you understand how to set up the mapping from QuickBooks to Excel. Your inventory listing in excel has to be clean – no formatting, just columns with column headers and data. You cannot have sub-categories set up unless you already have all the parent categories set up in QuickBooks. So if you have an item that looks like this “Parts:754A” the item “Parts” must already be in the item list in QuickBooks before you do the import.

You also have to include the following columns at a minimum in your excel spreadsheet in order for the import to be effective:

Type | Item | Description | Income Account | COGS Account | Inventory Asset Account | Cost, Price | Quantity on hand.

You can have more than this, but these are required at a minimum in order for the import to work properly. There is a likelihood that you will get errors on the first try. QuickBooks will prompt you to save the error log. Usually it is something simple, so just look at the log and scroll to the errors column. Then all you have to do is fix the issues in your excel file and/or QuickBooks as you will see in this web cast. Once this is done, you just go back to QuickBooks and re-do the import. You do not have to re-do the mapping unless there was an additional column that needed to be included in the mapping. Assuming this is all set then simply repeat the steps and go right to the import. If you have successfully fixed the errors then your items will be imported. Then go to your item list and fix any hierarchy issues or make any changes needed directly there:

Figure 2 – go to your item list

Figure 3 – This is what the item list looks like


You can go in and edit any of these items by selecting the item, then click ‘Edit’ and choose ‘Edit Item’. If you want to import additional items or change to existing items you can do so by setting up a spreadsheet. It is recommended that you export your current listing as we did in the web cast to be sure that there is consistency with respect to existing items. Otherwise you may wind up with duplicate items. Also remember that QuickBooks will not import changes to the quantities of existing items.

Watch the web cast here |


QuickBooks Inventory – Managing & Tracking your inventory

July 26, 2009 Leave a comment


April 15, 2009

Managing and tracking your inventory

Please enjoy the web cast! 

View The Web Cast |

When we sell products it is important to understand the dynamics surrounding inventory and particularly how we use QuickBooks for inventory management purposes. QuickBooks average costs inventory and sometimes that is not the most accurate way to value the inventory we have on hand. This web cast goes over a template I designed in MS Excel that lets you take an inventory report from QuickBooks and export it to my template. 

There are just a few steps involved in managing and updating your inventory using this template:

  1. Run the item listing report in QuickBooks.
  2. Customize the report to get only the columns you need and re-order them to match the template.
  3. Export the report to Excel and copy and paste the exported report into the template.
  4. Update your Inventory.
  5. Use your ALT + TAB and Copy & Paste to post your Inventory adjustment in QuickBooks.
  6. Analyze your inventory statistics in the template.

Inventory is one of the more complex areas when it comes to running a business and it is also the part of your business that carries the greatest potential weakness in terms of internal controls. That is to say that people can walk out of your warehouse with inventory, inventory can sit on the shelves and become obsolete, and it can be forgotten as new products are developed and brought out. The point is that it becomes very important to put a great deal of emphasis on this area.

Keeping on top of your inventory will help you maximize your profits by minimizing waste and theft. By tracking this on a regular basis you will be clued in to the situations where something is wrong. For example if you have one particular product that is really popular and you keep taking inventory resulting in an adjustment to that product, this might indicate that someone is walking out with the inventory. Just knowing that you are tracking this closely will be a deterrent to employees to take things so make sure you involve them in the inventory taking process.

We also suggest using a special “Cost of Goods Sold Account called “Inventory Adjustments” (very original I know). This is so that you can run your profit & loss regularly and see what is happening with this account. Does it keep increasing? Again an indication there may be a problem worth looking into.

Analyzing the products in this template that we use will help you gain insight into which products are the most profitable in terms of Gross profit, and also which ones are actually generating the most profit based on volume. Check back for a follow up web cast in which we will show you how to run a report that lets you perform this analysis on sales volume which you will find invaluable. Coming on Wednesday July 29, 2009.

Please enjoy the web cast

View The Web Cast |