How to Do an Inventory Spread Sheet

Update your inventory spreadsheet when products are sold.

If you have access to a spreadsheet program, you can use that program to enter your product inventory every time a new hand count is conducted. You can also use that spreadsheet to calculate the value of the products you have on hand, and to determine how many of each item you have. By keeping the inventory spreadsheet up to date, you can easily determine when to order more products and eliminate shortfalls and other problems.

Instructions

1. Log on to your computer and open the spreadsheet program. Open a new spreadsheet and save it to your hard drive or network share. Give the spreadsheet a descriptive name like "Inventory Spreadsheet" or "Product Inventory."

2. Create columns across the top of the spreadsheet. The exact column names will vary depending on your needs, but some standard columns include Product Name, Description, Product Number, Quantity and Price.

3. Use the inventory information from your most recent hand count to fill in the spreadsheet. Enter each product on a separate row, and list the number of units on hand, the price of each item, the product number and a description of that product.

4. Create a calculated field to compute the total value of each product, based on the number of units on hand and the price of each unit. To create that calculation, simply enter a formula that multiplies the number of units on hand by the price per unit. In Excel, you would enter the formula "=D3*E3" assuming that the number of units was contained in cell D3 and the price per unit in cell E3. To enter the formula, simply click on the field where you want it to appear and begin typing. Press "Enter" when you are done.

5. Remove products from the inventory spreadsheet as they are sold or used in the manufacturing process. Keep your inventory spreadsheet up to date to avoid conflicts between what the spreadsheet shows and what is actually on hand.

6. Update the inventory spreadsheet each time a new count is undertaken. Save the updated spreadsheet and refer to it whenever you need inventory numbers.