No business is too small for an inventory control system. Despite this, options such as an integrated electronic system are neither necessary nor cost-effective. On the other hand, striking a balance between having too much and too little inventory can be difficult with a manual system. If this describes your business, using an Excel spreadsheet to track inventory can be a cost-effective alternative. Although Excel can’t automate everything, formulas and conditional formatting rules can help you calculate on-hand stock levels and assist with accurate and timely inventory ordering.
Create a Basic Inventory Spreadsheet
Create a spreadsheet from scratch or download a free inventory-tracking template from the Microsoft Office website at office.microsoft.com. For a simple system, an 11-column spreadsheet will work for inventory-tracking tasks. Create column labels for inventory ID, product name, description, unit price, quantity in stock, items sold, current quantity, reorder level and a column label to identify discontinued items. You will need to fill in the spreadsheet with current information, so this may also be a good time to conduct a physical inventory count.
How It Works
Enter current inventory data in all but the items sold, current quantity and discontinued columns. The discontinued items column serves as an alternative to deleting information from the spreadsheet as items become unavailable. As you update the spreadsheet by entering items-sold data, a formula will calculate the quantity currently on hand. If the current quantity levels are equal to or less than the reorder level, the background color in the current quantity column will alert you to reorder by changing color.
Create a Current Quantity Formula
Enter a quantity in stock minus items sold formula in the current quantity column. To create it, click in the first cell under the current quantity column label and enter an equal sign. Next, click in the first cell under the quantity in stock label, enter a minus sign, click in the first cell under the items sold label and press "enter." For example, the formula might appear as “=E3-F3.” To extend the formula, click the cell, position the mouse pointer over the bottom-right corner of the cell so that the pointer resembles a cross. Hold the left mouse button down and drag to the bottom of the spreadsheet.
Enter a Reorder Rule
Change the background color of the current quantity cell to red to alert you when it’s time to reorder. To do this, click in the first cell under the current quantity column label, select “conditional formatting” from the Excel ribbon, choose "highlight cell options" and select “between.” In the dialog box that opens, type the number 5 in the first text box, type zero in the second text box and choose “light red fill” from the drop-down box. Extend the rule by dragging through all of the cells in the column just as you did with the formula.