Stock Control Excel - Free Template

1. Open the Stock Movements sheet and enter each transaction on a separate line. Use the transaction date, SKU, product name, movement type and location so the record stays traceable.
2. Add the quantity in or out, together with unit cost, selling price and VAT rate. This lets you compare purchase value, sales value and margin on the same row.
3. Set up the Product Master sheet with your core item list. Keep SKU, category, supplier, reorder level and reorder quantity consistent so the stock file stays clean.
4. Check the stock on hand and stock value fields after each batch of entries. If an item drops to or below its reorder level, place the next order before you run short.
5. Review the Dashboard regularly for the items that need attention. Use it before a weekly order run, month-end close or stocktake.
6. Follow the Instructions sheet when you hand the file to someone else. A short written process stops people using different codes for the same item.

This template suits the people who have to know, at any point in the week, how many units are left and what they are worth. A sole trader with 40 product lines, a small warehouse with 300 SKUs, or a club shop running weekend sales all need the same basic thing: a reliable movement record.

The Stock Movements sheet is the working sheet. Image 1 shows columns for Transaction Date, SKU, Product Name, Category, Location, Movement Type, Supplier / Customer, Qty In, Qty Out, Unit Cost, Unit Selling Price, VAT Rate, Gross Value, Net Value, Reorder Level, Reorder Qty, Stock On Hand, Stock Value, Margin % and Status.

What the sheet is for

If you buy 120 printer cartridges at £22.00 and sell 18 at £38.50, the file gives you both the quantity movement and the trading value in one place. That is far more useful than a simple count column, because you can see whether the item is earning its keep or just sitting on the shelf.

Where the workbook fits into the month

Most users pick this up at receiving, dispatch or stocktake time. A builder with 4 employees may update it after a van run; an online shop with 300 orders a month may update it daily; a bookkeeper may use it before month-end to reconcile stock against the profit and loss.

For HMRC purposes, stock records sit inside your wider bookkeeping trail. If you are self-employed, keep the records for 5 years after the 31 January Self Assessment deadline for that tax year; companies keep records for 6 years.

The key point is that you need to be able to explain opening stock, purchases, sales and closing stock. If you claim stock purchases as expenses without a proper movement record, you make it harder to support your profit figure and your year-end accounts.

Tax and accounting treatment

Stock is not the same as an expense at the point you buy it. In proper double-entry bookkeeping, unsold stock sits on the balance sheet and only moves into the profit and loss account when it is sold or written down. That matters when you are preparing year-end accounts for a sole trader or a Ltd company.

Why the figures matter

A shop with £18,000 of stock on hand at the year-end should not leave that number to guesswork. If the closing figure is wrong by even 5%, that is £900 either missing from assets or overstated in costs, which can distort the gross margin and the tax computation.

The most common failure is not the counting itself, but the record split. If receipts are entered, sales are not, and adjustments are left until month-end, your stock on hand can drift by dozens of units without anyone noticing.

That drift turns into real money quickly. A consumables line with 50 items at £12.00 each is £600 of stock; lose 8 units through unrecorded issues and you have £96 of missing value before you have even checked for shrinkage.

Where the numbers go wrong

Another frequent error is using the wrong unit cost after a price increase. If a cartridge cost £22.00 last month and now costs £24.50, reusing the old figure understates stock value and margin on every sale line.

Operational pain points

Bad SKU discipline creates duplicate items, split counts and wrong reorder calls. One customer order can trigger two entries under different descriptions, and that can make you think you hold 36 units when the real usable stock is 18.

If you run a quarterly stocktake and your file is out by 10% on a £25,000 stock base, you are looking at £2,500 of error. That is enough to distort ordering, cash planning and the year-end accounts in one go.

Use the workbook on a fixed cycle, not when you remember. The simplest approach is to update it at goods-in, after sales close, and again before your weekly order run or month-end review.

Build the habit

• Keep the file open on one screen while you book in deliveries.
• Use the same SKU and location code every time, so the data stays clean.
• Copy the previous week’s entries pattern if you process similar movements each day.
• Review the Dashboard before you place any new order, so you buy to the actual reorder level rather than gut feel.

Know when a spreadsheet is no longer enough

If you are handling 1,000+ order lines a month, multiple users or barcode scanning, the spreadsheet will start to creak. At that point a proper stock system is safer, but for a small business with controlled item counts this workbook is a solid working tool.