How to Create Inventory Spreadsheets
When you're running a small business and handling your own accounting matters, one of the most difficult duties to keep up with is your inventory. Product goes out, items come in, and, before you know it, you're clueless as to what you have in your inventory. At the end of your business year, you have to know what you have left in inventory, so you can settle your books and complete your taxes. The best way to keep track of your stock is to create inventory spreadsheets in Excel.
Things You'll Need
- Microsoft Excel
Open a new Microsoft Excel spreadsheet and create a new worksheet for each product in your inventory (right-click the tabs at the bottom of each sheet to rename each worksheet and to create a new one). For example, if you sell shirts, you'll want a worksheet for each style and color of shirt that 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 creating descriptive columns in each worksheet. Start with the date of transaction, then the item number, size (if applicable), a general description of the item and then a general description of the transaction.
Use the final columns of your spreadsheets for the numerical data. Label one column for the number of items that were ordered in or sent out for each transaction, then the next for the price that the item was sold for (or the cost of each item if this was a new order), the total sale (number of items multiplied by the price it was sold for) and then a running list of the number of items you have left in your inventory. You can set up a simple formula in Excel to subtract the number of units sold from the previous inventory total listed on the previous line. For example, if you are entering a new transaction on Line 7 with H as the column for the units leftover in inventory and D as items sold in that transaction, the formula would read H7 = H6 - D7 (H6 is the cell for the previous line's leftover inventory total).
Enter in each new transaction as it happens or summarize at the end of a working week. For example, one line on your inventory spreadsheet might read Date: 12/1/2008; Item #: 10385; Size: Small; Item Description: Baby Tee; Transaction: Sold to Internet customer; # Used: 10; Price Each Sold For: $10; Total Sale: $100; Units Left: 1,000.
Tips & Warnings
- When you get in a new order, add to the leftover inventory column instead of subtracting, so that you can maintain an accurate running inventory total.