Stock Register (Excel)

Preview

Real view of the file with its sample data (yellow cells are where you type).

What's inside

• Items sheet: item, unit, opening stock, reorder level, purchase rate and sale rate
• Movements sheet: date, item (drop-down), quantity in, quantity out and a note, 1,000 ready rows
• Stock sheet: opening, total in, total out, closing stock, stock value and a red "Reorder" flag
• Total stock value at purchase price and at sale price

How to use it

1. On the Items sheet, replace the sample items with your own and type the opening stock (what you have today).
2. Set a reorder level for each item: the stock at which you should order again.
3. Every time goods come in or go out, add a row on the Movements sheet: date, item, quantity in or out.
4. Open the Stock sheet to see closing stock and value. Items at or below the reorder level turn red.
5. At the month end, print the Stock sheet or save it as PDF for your records.

Tips

• Use the same unit for an item every time (pieces, kg, boxes) or the totals will be wrong.
• For returns from customers, enter the quantity in the In column with a note "return".
• Do a physical count once a month and add an adjustment row for any difference, with a note.

Questions and answers