Bar Inventory Spreadsheet

Bar Inventory Spreadsheet– How to create inventory spreadsheets
When you run a small business and handle your own accounting issues, one of the most difficult tasks to manage is your inventory. The products are gone, the items are coming and, before you know it, you will have no idea what you keep in your inventory. At the end of the fiscal year you should know what you have in the inventory to put your books in order and complete your tax return. The best way to keep track of your stocks is to create inventory spreadsheets in Excel.

Bar Inventory Spreadsheet

Bar Inventory Spreadsheet

Open a new Microsoft Excel spreadsheet and create a new worksheet for each product in your inventory (right click on the tabs at the bottom to rename the worksheets and create new ones). For example, if you sell shirts, you must have a worksheet for each shirt style and color you sell. The general rule is, if you pay a different manufacturing cost for an item, you need to create a different worksheet for it.

Start by creating descriptive columns in each worksheet. Start with the transaction date, then the item number, the size (if applicable), a general description of the item and then a general description of the transaction.

Use the final columns of your spreadsheet for the numerical information. Label a column for the number of items that were ordered or sent in each transaction, label the following for the price at which the item was sold (or the cost of each if it was a new order), the total sale (the number of items multiplied by the price at which it was sold) and then a current list of the number of items you have in your inventory. You can create a simple formula in Excel to subtract the number of units sold from the previous total inventory listed in the previous line. For example, if you enter a new transaction on line 7 with column H for the remaining units in the inventory and D as the items sold in that transaction, the formula would be H7 = H6 – D7 (H6 is the remaining total inventory cell from the previous line).

Enter each new transaction as it takes place or collect all at the end of a work week. For example, a line in your inventory spreadsheet should read Date: 12/1/2008; Item #: 10385; Small size; Item description: Baby T-shirt; Transaction: sold to an Internet user; # Used: 10; Unit sale price: $ 10; Total sale: $ 100; Remaining units: 1,000.

Gallery of Bar Inventory Spreadsheet

In addition to the image above, you can see a gallery of Bar Inventory Spreadsheet below.

( Click Image to Enlarge )

Other Collections of Bar Inventory Spreadsheet