Guide

Managing stock in Excel: a practical guide and free template

For a shop, a café or a small workshop, stock is money sitting on the shelves. Always knowing what you have, what it’s worth and what to reorder prevents both empty shelves and goods that never sell. You don’t need expensive software to start: a well-built Excel sheet and the habit of keeping it up to date are enough.

The three numbers to watch

  • Stock on hand = opening stock + goods in − goods out ± adjustments. It is what should be on the shelf.
  • Stock value = stock on hand × purchase cost, excluding VAT. It is how much money you have “parked” in goods.
  • Minimum stock: the quantity below which it is time to reorder.

How to set up your stock in Excel

  1. Make a list of items: code, name, category, supplier, unit, purchase cost, selling price and minimum stock.
  2. Start from a real count. Opening stock must be counted physically: start from wrong numbers and everything else will be wrong.
  3. Record every movement on its own line, with date, item and quantity: goods in (deliveries) and goods out (sales, own use, returns, breakages).
  4. Use adjustments when the count doesn’t match, instead of overwriting stock by hand: that way you can see how much you lose to breakage, expiry or shrinkage.
  5. Keep a “to reorder” list that automatically shows the items below minimum stock.

When to reorder: the reorder point

Minimum stock shouldn’t be a guess: it depends on how much you sell and how long your supplier takes to deliver.

Reorder point = average daily sales × delivery days + safety stock

Example: you sell 4 units a day on average, your supplier delivers in 5 days and you want 6 units of safety stock for busy days. Reorder when you get down to 4 × 5 + 6 = 26 units. Slow-moving items can have a much lower minimum.

Margin and markup: don’t mix them up

An item you buy for €6 and sell for €12.20 including 22% VAT is worth €10.00 without VAT.

FormulaResult
Margin(price without VAT − cost) ÷ price without VAT(10 − 6) ÷ 10 = 40%
Markup(price without VAT − cost) ÷ cost(10 − 6) ÷ 6 = 67%

They are two ways of looking at the same profit: when you talk prices with suppliers or partners, make clear which one you mean.

Stocktaking: actually counting

  • Count on rotation: one category at a time, for example every month, instead of everything in one day.
  • Count everything at least once a year. For many businesses the year-end stocktake is also needed for the accounts and taxes: ask your accountant what you need.
  • Compare the count with the stock figure and record the difference as an adjustment. If the differences are always on the same items, there is a problem to look into.

Excel or stock software?

Excel works well while you have a few hundred items and one or two people updating it. When you need barcode scanners, a link to your till or online shop, several locations or many people working on the same data, it’s worth moving to dedicated software. A good Excel sheet is still the simplest way to start and understand your numbers.

A ready-to-use sheet

For a list with quantities, value and what to reorder, the free template at the top of this guide is enough. Our full Stock & Inventory sheet does everything in this guide: stock calculated from goods in, goods out and adjustments, value and margin per item, an automatic reorder list with a suggested quantity, and a summary by category.

Run a shop? The Shop Kit adds gift vouchers, social media templates and income and expense tracking. And if you want to be found online, see our websites for shops and local businesses.

Tell us about your business.

We’ll reply by email with a clear proposal and a written quote — no commitment.