Inventory & Purchase Order Tracker
This inventory and purchase order tracker counts what is on the shelf together with what is already on order, so the reorder flag never tells you to buy stock that is arriving on Tuesday.
Eight tumblers left on the shelf, and you sell twelve a week — so you order a hundred and sixty more. Two hundred and forty were already on a lorry, arriving Tuesday. Every inventory sheet knows what is on the shelf; this one also knows what is already on its way.
What is in it
- Eleven linked tabs and over 7,300 formulas: Setup, Products, Suppliers, Purchase Orders, PO Lines, Stock Movements, Stock on Hand, Reorder List, Valuation Report, Dashboard and Start Here.
- Products — one row per item with cost, price, reorder point, reorder-up-to level, supplier, location and opening quantity.
- Stock on Hand — on hand, on order, coverage, average cost and valuation, all calculated for you. Covered means on hand plus on order, and that is the figure the reorder flag actually reads.
- Reorder List — ORDER means stock has fallen to its reorder point and you should buy it; LOW means stock is on its way but not fast enough to cover you.
- Purchase Orders and PO Lines — 80 orders and 300 lines with part-receipts, days late and the value still to arrive, sitting next to a Suppliers tab that holds payment terms, promised lead time, the lead time each supplier actually manages and their on-time rate.
- Valuation Report — printable by category at cost and at retail, valued at weighted average cost from the unit costs you actually paid on receipt rather than FIFO or LIFO. Capacity: 100 products, 25 suppliers, 80 purchase orders, 300 order lines, 800 movements, 12 months.
Who it is for
Small shops, makers and anyone reordering from more than one supplier. It is one business per file, and it is deliberately not a till: there is no barcode scanning, no batch or serial tracking, and no accounts in it.
The worked example
The filled workbook is a worked half-year for a homeware shop: 40 products across 8 suppliers, with 43 purchase orders raised over the period and the receipts, sales, returns, adjustments, damage and loss behind them all written into Stock Movements. Every valuation figure in the file is built from those actual receipt costs, not from a list price.
The point it makes is the one the opening scenario sets up. Eight tumblers on the shelf against sales of twelve a week reads as an emergency until the coverage column adds the 240 already in transit — and the 160 you were about to order stays unordered. The blank workbook ships with the same 7,300+ formulas already built, so it does the same arithmetic from your first entry.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
Questions people actually ask
How does it stop me reordering stock that is already coming?
The coverage figure is on hand plus on order, and that is the number the reorder flag reads. A product with eight on the shelf and 240 in transit is not flagged as ORDER; it shows LOW only if what is coming still will not cover your sales rate. Open purchase orders feed that figure automatically as you receive against them.
Is it right for a shop with a few hundred lines?
It is built for up to 100 products, 25 suppliers, 80 purchase orders, 300 order lines and 800 stock movements over 12 months. Small shops, makers and anyone buying from more than one supplier are the intended users. Above that capacity you would be better off with a stock system than a spreadsheet.
I do not know my current stock figures. Can I still start?
Yes — start with an opening quantity per product on the day you count, and let the movements build from there. The Products tab has an opening quantity column for exactly that. The filled workbook already contains a homeware shop's half-year — 40 products, 8 suppliers, 43 purchase orders — so you can see what each column expects before counting anything.
Does it work in Google Sheets, or do I need Excel?
It works in both — the file is .xlsx and every formula runs in Google Sheets once you upload it and open it with Sheets. Excel 2016 or newer works on Windows and Mac, and LibreOffice opens it too. There are no macros or add-ons, so nothing breaks in the move.
Does it scan barcodes or connect to my till?
No — there is no barcode scanning, no till connection and no batch or serial tracking in it. It never connects to a shop system, a bank or a supplier account; you type receipts, sales and adjustments in yourself. It is a stock and ordering file, not a POS and not accounting software.
Need it built around your business?
This file assumes one business, one stock location per product and suppliers you order from and receive against. If you run several sites, assemble products from components, or hold consignment stock that is not yours until it sells, I can rebuild the same coverage and valuation arithmetic around how your stock actually moves.
All other templates are listed on the Excel & Google Sheets Templates page.