Inventory with Excel

Inventory Management using Quickbooks and Excel Part 3 of 3

You can read Part 2 here and Part 1 here.

With Excel we can create simple customized graphs and run projections. The only drawback I have with Excel is that it can’t be used as a database (I have heard that Access can but I have never used it. I did find a great tutorial for it available here.)

Since Quickbooks was designed to be used with Excel it is really easy to export info to an existing Excel workbook so all you have to do is update the file and all of your preset formats are already in place. The “Auto Update” feature in excel can’t be used when the Quicbooks file is opened by multiple users but I have found that it is easy enough to export reports manually.

Excel can be used to:

 

  • 1. Create a bar graph or pie chart of product performance for comparison by category, customer type, day, week, month or year.
  • 2. Create a bar graph to help project future demand. Using the multiple Workbook Sheets (one for each product) it is easy to separate all the info yet have them easily accessible for review.
  • 3. Create a worksheet to analyze Inventory stock levels, cost and profit over time.
  • 4. Create a worksheet to analyze Sales volume and profits over time.

 

It is common for small businesses to use Quickbooks and Excel to track inventory because Quickbooks is being used for the accounting and Excel is already included on most computers. Generally when we check into upgrading for better inventory management we find that the next step up is a rather large step and one that is not easily affordable or justified.

However it is good to know that a lot of the features we need in these more expensive and sophisticated inventory management systems are available with our current setup it just requires a couple of extra steps besides what we are already doing.

So we let the Quickbooks program do the database functions in our inventory management and use Excel for our inventory reporting and analysis.

With a little creativity many of the hangups in this setup can be worked around and your efficient and effective inventory management system can be a reality.

Written by Carlos Schrock

I am the Buyer and Bulk Sales Rep at our Company, I am also the office manager and inventory control person. I love to analyze data and test my projections. I love to buy and sell.