OperleafTechnology ES

A Simple Inventory Spreadsheet for a Small Business

Track on-hand stock, reservations and incoming orders in one sheet, set a reorder point and recognize when you need a different system.

This framework reflects the publication date shown above. Adapt its examples to your current situation before using it.

The practical takeaway

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

  1. Count items close to the threshold. Record the date and the person who counted them.
  2. Reconcile movements. Check sales, returns, damage and orders that are still open.
  3. Check the supplier’s actual lead time. If it has changed, update the assumption before calculating.
  4. Review exceptions. Do not combine variants, perishable items or stock in another location unless the sheet distinguishes them.
  5. 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.

Roberto Niño

Research sources and limitations are disclosed in this article. Read our methodology.

Found an error? Email [email protected] with the article URL and a source that supports the correction.

References & research notes

  1. Google: How to use Google SheetsOfficial source · Checked October 27, 2026
  2. Google: Add formulas & functionsOfficial source · Checked October 27, 2026
  3. Google Drive: Share and manage access to files and foldersOfficial source · Checked October 27, 2026
  4. Microsoft: Goods web database template for inventoryOfficial source · Checked October 27, 2026
Read our editorial and update policy