Inventory template (stock list and stock count)
An inventory template is a stock list: every item you hold, where it's kept, how many you have, what it cost and when to order more. This free Excel template values each line, flags anything at or below its reorder level (taking stock already on order into account), and has a stock count sheet that shows where the shelf and the records disagree.
Running a company of 30 to 200 people? Documents like this are now worth money.
It suits a parts store, a small warehouse, a workshop or a service business's consumables: stock control in the form most small firms need.
What's in the template
The workbook has a read-me, the stock list, a stock count sheet and an example of each. The stock list also records each line's supplier, location, unit, reorder quantity, lead time and when it was last counted. Part of the example, from a fictional garden machinery dealer's parts store:
| SKU | Item | Quantity on hand | Quantity on order | Unit cost | Stock value | Reorder level | Reorder now? |
|---|---|---|---|---|---|---|---|
| SP-019 | Spark plug, small engines | 46 | 0 | 2.10 | 96.60 | 30 | No |
| BL-21 | Mower blade, 21 inch | 9 | 0 | 11.40 | 102.60 | 12 | Yes |
| AF-34 | Air filter, petrol mowers | 0 | 40 | 3.25 | 0.00 | 15 | Out of stock |
| DB-88 | Drive belt, ride-on mower | 6 | 0 | 24.80 | 148.80 | 4 | No |
| OIL-2S | Two-stroke oil, 1 litre | 30 | 24 | 5.60 | 168.00 | 36 | No |
| CH-16 | Chainsaw chain, 16 inch | 7 | 0 | 14.95 | 104.65 | 8 | Yes |
| Total | 620.65 |
Reorder now? says Out of stock when the shelf is empty, and Yes when stock on hand plus stock on order is at or below the reorder level. The air filters have run out but 40 are on order, and the oil is covered by an open order, so neither gets ordered twice.
How to set up the stock list
- Give every item one SKU and one location, such as BL-21 for a 21-inch blade in bin B2-01, so nobody records the same item two ways.
- Count each line in one unit (each, box, litre); a line counted in boxes one month and singles the next never reconciles.
- Enter unit costs from supplier invoices, before VAT if you're VAT-registered and reclaim it.
- Set a reorder level and quantity for each line (see below); leave them blank for items you only buy to order.
- Record every movement: goods in from delivery notes, goods out from job sheets or sales. Enter new orders under Quantity on order the day the purchase order goes out, and clear them when the delivery arrives.
- Filter on Reorder now? once a week (Data, then Filter) to build the next orders.
- Count a few lines every week (cycle counting) on the stock count sheet, and change the stock list only after a recount confirms a difference.
Setting reorder levels
Reorder when stock falls to the level that will last until the next delivery arrives:
Reorder level = average daily use × lead time in days + safety stock
The example dealer sells about 0.8 mower blades a day in spring and the manufacturer takes 10 days to deliver, so 8 blades cover the wait; 4 more as safety stock for a busy weekend make 12. The reorder quantity is how many to buy each time: enough to make an order worthwhile without tying up cash in stock that sits for months. Revisit both when the season or a lead time changes.
Stock counts and the rules
Regular counts catch errors while they're recent. The rules on stock records and valuation:
- UK: a company that deals in goods must keep, in its accounting records, statements of the stock held at the end of each financial year and the stocktaking records behind them (Companies Act 2006, section 386). Keep the signed count sheets.
- Valuing stock (UK): HMRC accepts the lower of cost and net realisable value as a basis, and net realisable value can fall below cost through deterioration, obsolescence or changes in demand. Damaged or slow-moving lines may be worth less in your accounts than their cost here.
- US: the IRS says that if you keep a perpetual or book inventory, you must take a physical inventory at reasonable intervals and adjust the book amount to agree with it (Publication 538).
This is a summary, not tax or accounting advice.
Example: a garden machinery dealer's parts store
The example sheets come from a fictional 45-person garden machinery dealer with a repair workshop. Its six example lines are worth £620.65 at cost. At the October count the spark plugs and chainsaw chains were each two short and the oil one over: three recounts, and a net difference of £28.50 below book. The recount confirmed the plug and oil figures; the two chains had been fitted on repair jobs without being booked out. The dealer's fix was a rule: parts leave the store only against a job number.
Common mistakes
- Ignoring stock on order, so the same item gets ordered twice in a week.
- Correcting the list straight after a count without a recount, so a miscount becomes the record.
- Reorder levels left unchanged when the busy season starts.
- Stock that leaves without a record: parts borrowed for a job, samples, damaged goods thrown away.
- Several people editing their own copies of the file. Keep one shared copy and decide who may change quantities.
Frequently asked questions
What's the difference between inventory and stock?
In everyday use they mean the same thing: goods you hold to sell, use up or build into products. "Stock" is the usual word in the UK and "inventory" in the US. Equipment you keep and use, such as tools and vehicles, isn't stock.
How often should you count stock?
Count fast-moving and valuable lines most often, monthly or even weekly, and slow, cheap lines a few times a year. UK companies that deal in goods also need a statement of stock at each year end, backed by the stocktaking records.