When you start a manufacturing or retail business, one of your duties is inventory management and tracking. If you do not track your inventory properly, you can find yourself in a situation where a customer wants to buy your product urgently but it is unavailable. Do not risk losing an important sale—find a consistent, reliable and easy way of tracking your inventory.
Create a new spreadsheet file using a spreadsheet program (like Microsoft Excel, Quattro Pro or OpenOffice Calc). Enter the name and SKU or item number for the product you want to track in the top row of the spreadsheet.
Enter column headings on the next available row of the spreadsheet (should be row 2 in the document). Type in "Date" into cell A2 (intersection of column A and row 2) of the spreadsheet, "Inventory In" (B2), "Inventory Out" (C2), "Description" (D2) and "Total" (E2) as the column headings.
Type in the first inventory transaction on the next line of the sheet (row 3). This is the amount of product you are adding to inventory to start the tracking process. Fill out each column of that row per your column headings. For instance, your first entry might read 11/2/2010, 50 (inventory in), 0 (inventory out), New Order Received (the description), and 50 (total in inventory right now).
Go down to the next row of the sheet (row 4) and tab over to the “Total” column. Enter a formula in this cell to automatically calculate the new inventory amount for the next transaction. The formula in this example should read “=E3+(B4-C4)” (no quotation marks). This simple formula calculates the new inventory for the next transaction you enter on row 4.
Insert that same formula into each subsequent row under the “Total” column. In some spreadsheet programs you can click and hold the bottom right corner of the cell containing the formula and drag your mouse down to copy the formula over automatically.
Proceed to enter new transactions into your new inventory tracking spreadsheet. You can then create a new worksheet for each product in your inventory.