Track physical stock, reservations and confirmed incoming orders separately; set a reorder threshold from measured usage and lead time, then verify the count before buying.
Use the resources in this guide
This is an original planning framework. Examples illustrate the method; they are not product ratings or measured business results.
If you have ever sold an item that was already reserved or reordered too late because nobody knew what was left, a product list is not enough. You need to separate what is on hand, what you have promised, and what is on the way. This guide suggests a lightweight structure for a small business with straightforward stock; it is not an accounting system or an automatic forecast.
Download the inventory workbook
The inventory XLSX has two tabs: Inputs, where you edit yellow cells, and Reorder, which calculates stock position, threshold and an illustrative order. The first three rows are fictional examples, with 20 prepared item rows. Save a copy before replacing them and record count ownership in your process.
Use a unique SKU and specify the unit. Quantities must be nonnegative numbers; zero is valid and a missing cell stays pending. Daily use and lead time must use the same day type. A target below the reorder point requires review and does not produce an order quantity.
| Check case | Position | Threshold | Illustrative order |
|---|---|---|---|
| 25 on hand, 4 reserved, no incoming; daily use 4, lead time 5, buffer 8, target 42 | 21 | 28 | 21 |
| The same case with 10 confirmed incoming units | 31 | 28 | 0 |
| All quantities and target set to zero | 0 | 0 | 0 |
We checked those cases, a missing input, duplicate SKUs, a target below the threshold and the first, middle and last prepared rows. Formulas recalculate after edits. A negative stock position can indicate commitments exceeding stock; review the inputs before approving an order. The workbook does not record simultaneous transactions, lots, expiration dates or supplier minimum orders.
A minimum spreadsheet structure that supports reordering
Start with one row per product and an identifier that does not depend on its name. Choose the unit and the person responsible for updating each field. This is an editorial template: adapt it to your products and process.
| Field | Example | Owner | Why it helps |
|---|---|---|---|
| SKU and item | CUP-350 — 350 ml cup | Purchasing | Distinguish variants with similar names |
| Location | Shelf B2 | Stock counter | Find and recount the units |
| Physical on-hand | 25 units | Person counting stock | Starting point for the calculation |
| Reserved | 4 units | Sales or service | Avoid promising the same unit twice |
| Confirmed order pending | 0 units | Purchasing | Avoid reordering items already on the way |
| Average daily use | 4 units/day | Inventory owner | Keep the usage and lead-time units aligned |
| Supplier lead time | 5 business days | Purchasing | Estimate what may be used before delivery |
| Safety buffer | 8 units | Process owner | Allow for variation; choose it for your operation |
| Reorder point | 28 units | Reviewed formula | Show when a purchase needs attention |
| Target stock | 42 units | Purchasing | Set the intended replenishment level |
| Last count and owner | Oct. 27 — Ana | Person counting stock | See whether the number reflects the shelf |
Microsoft’s Access inventory database template separates available, allocated, ordered and target quantities, and can show items that reach a reorder threshold. You do not need to copy its database to use the idea: keeping those amounts separate prevents a physical count from being confused with stock already committed.
For a shared spreadsheet, limit editing to people who record stock movements; give others view or comment access when that is enough. Google documents those Drive access roles; editors can change content and other items. Share with specific people and check permissions inherited from the containing folder.
A worked reorder point with checkable numbers
Use matching units and the same time period:
Stock position = physical on-hand − reserved units + confirmed incoming orders
Illustrative reorder point = average daily use × replenishment lead time in days + safety buffer
Hypothetical example, not a forecast: a shop counts 25 cups, has 4 reserved, uses about 4 per day, normally receives an order in 5 business days, and chooses a buffer of 8. Its current position is 25 − 4 + 0 = 21 units. The threshold is 4 × 5 + 8 = 28 units. Since 21 is below 28, it is time to review a purchase.
If the shop sets a target of 42 units, the illustrative order quantity is 42 − 21 = 21 cups, assuming the supplier sells that amount and no other constraint applies. The number does not include a minimum order quantity or replace a demand review. If sales change by season, recalculate usage from a representative period and check again later.
Google’s Sheets help explains how to enter formulas and functions. Even if a formula flags an order, verify its inputs: a correct calculation cannot detect a short delivery or a sale nobody recorded.
A review routine before placing an order
- Count items close to the threshold. Record the date and the person who counted them.
- Reconcile movements. Check sales, returns, damage and orders that are still open.
- Check the supplier’s actual lead time. If it has changed, update the assumption before calculating.
- Review exceptions. Do not combine variants, perishable items or stock in another location unless the sheet distinguishes them.
- Approve the purchase and save its record outside the stock cell; count the quantity as incoming until it arrives.
Add the check to a weekly operations review. Keep a copy of the file in your backup process and test a restore: syncing alone does not prove that you can recover a useful version.
When to move beyond a spreadsheet
A sheet may be enough for a short item list, one location and one person responsible for counts. Consider a transactional tool when the team needs simultaneous movement records, lot or expiration tracking, an adjustment history, serial numbers, multiple locations, or order and sales links without retyping. Pilot a small group of items first and check that opening balances, reservations and open orders move correctly.
The sheet does not decide how much to buy for you. It makes the balance, lead time and buffer you chose visible. Start with a few items, review results for several weeks and change systems when the effort of manual control outweighs its usefulness.
References & research notes
- Google: How to use Google SheetsOfficial source · Checked October 27, 2026
- Google: Add formulas & functionsOfficial source · Checked October 27, 2026
- Google Drive: Share and manage access to files and foldersOfficial source · Checked October 27, 2026
- Microsoft: Goods web database template for inventoryOfficial source · Checked October 27, 2026