E•ENGINEERING
DESK TOOLS
All guides
Maintenance7 min readUpdated 5 October 2026

Spare parts register: stock, reorder alerts and criticality

Use the Excel register to track quantities, reorder and maximum levels, part value, suppliers and critical spares, with order suggestions and slow-moving stock flags.

Give each stocked part a stable identity

The spare parts register is a blank Excel workbook with a Records sheet, a Dashboard and a Lists sheet. Each row is one stock item. The entry columns are Part no., Description, Category, Used on (asset), Store / bin, Supplier, Unit, Qty on hand, Reorder level (min), Maximum level, Unit cost, Lead time (days), Critical spare?, Last issued and Last stock count. Category and Critical spare? are dropdowns; the category list starts with Bearings, Seals, Filters, Belts, Electrical, Instrumentation, Lubricants, Motors and Consumables and can be edited on the Lists sheet.

Rows are counted only when a part number is present, so the part number is the key. Use the same code on receipts, issues and equipment documents. Put interchangeable or repaired parts under a rule you have agreed beforehand, and do not enter two rows for the same physical stock unless they are in genuinely separate locations.

Quantity, levels, cost and lead time accept only numbers of zero or more, and dates must be real dates. All costs must be in one currency, because the dashboard adds values together.

How the stock status is decided

The Stock status column applies these rules in order:

ConditionStatus
Quantity blankNo quantity
Both reorder level and maximum blankNo levels set
Quantity of zero or lessOut of stock
Quantity ≤ reorder level (and not at or above a maximum)Reorder
Quantity above the maximumOverstock
OtherwiseOK

Stock value = quantity on hand × unit cost. A blank quantity or cost is treated as zero in the value.

The status uses physical quantity on hand only. It does not subtract reserved stock or add open purchase orders, so a part with an order already placed still shows Reorder until goods are received and the quantity is updated.

The special case is a part held at a single fixed quantity. If the minimum and the maximum are equal and the quantity equals both, the workbook treats it as OK, not Reorder. A single standby motor with minimum 1, maximum 1 and quantity 1 is therefore OK; at quantity 0 it becomes Out of stock.

Worked example: status and suggested order

The suggested order quantity applies to Reorder and Out of stock rows only. Suggested order quantity = maximum level − quantity on hand. If no maximum is entered it uses 2 × reorder level − quantity on hand. The order value is that quantity × unit cost. It tops up to the maximum; it is not an economic order quantity and ignores pack sizes.

Worked example 1. A mechanical seal has quantity 1, minimum 2, maximum 3 and unit cost 320. Since 1 ≤ 2 it is Reorder. Suggested quantity = 3 − 1 = 2 units, order value = 2 × 320 = 640. An air filter element has quantity 0, minimum 2, maximum 4 and cost 64: Out of stock, suggested quantity 4 − 0 = 4, value 4 × 64 = 256. A contactor has quantity 2, minimum 2, maximum 5 and cost 45: because 2 ≤ 2 it is Reorder (the trigger is at or below), quantity 5 − 2 = 3, value 3 × 45 = 135.

Worked example 2 (no maximum). A part has quantity 1, minimum 3 and no maximum entered. It is Reorder, and the suggestion is 2 × 3 − 1 = 5 units. Enter a real maximum so the order does not depend on this default.

Worked example: stock value and dashboard totals

The Dashboard has two rows of tiles: Stock items, Stock value, At or below reorder, Out of stock, Critical spares short, Suggested order value, Slow-moving and Overstock. It also shows the status breakdown, stock value by category, the ten highest-value items on the order list and the ten highest stock-value items.

Worked example 3. Using the nine fictional example rows supplied in the file, the row values are: bearing 4 × 38.50 = 154; seal 1 × 320 = 320; air filter 0 × 64 = 0; V-belt 10 × 9.20 = 92; fuse 6 × 18 = 108; contactor 2 × 45 = 90; proximity sensor 14 × 22 = 308; hydraulic oil 3 × 85 = 255; standby motor 1 × 620 = 620. The Stock value tile is the sum, 1,947. The Suggested order value tile adds the seal (640), the filter (256) and the contactor (135): 1,031. At or below reorder shows 3 items, and 1 of them is out of stock.

The V-belt (10 against a maximum of 6) and the proximity sensor (14 against a maximum of 8) are Overstock, which gives an Overstock count of 2. Together they hold 400 of the 1,947 total value, so they are the first items to challenge if capital must be released.

Criticality and movement

Criticality is a Yes or No dropdown meaning that the plant stops or safety is at risk without the part. The Critical spares short tile counts rows marked Yes whose status is Reorder or Out of stock. In the example above, only the seal is both critical and short, so the tile shows 1. The filter is out of stock but marked No.

The Movement column is separate. For a part with stock on hand it reads Never issued if no last-issue date exists, Slow-moving if today minus the last-issue date is greater than the slow-moving window on the Lists sheet (default 365 days), and Active otherwise. Rows with no stock are left blank.

Worked example 4. Take today as 5-Oct-2026. The proximity sensor was last issued on 18-Jun-2025, which is 474 days earlier, so it is Slow-moving. The standby motor was last issued on 4-Nov-2024, 700 days earlier, so it is also Slow-moving even though it is critical and in stock at exactly its level. The fuse, issued 11-Feb-2026, is 236 days earlier and stays Active. A slow-moving flag is a prompt to review, not a reason to scrap an insurance spare.

Common mistakes

  • Treating the reorder level as a guess. It should come from usage during lead time plus a safety margin; see the guide on reorder levels.
  • Setting the maximum at or below the reorder level. A part at its reorder level may then show OK instead of Reorder.
  • Not updating Qty on hand after each issue and receipt. All statuses and values are only as current as that column.
  • Mixing currencies in Unit cost. The value totals will be meaningless.
  • Expecting open purchase orders to clear a Reorder status.
  • Typing over formula columns such as Stock status, Suggested order qty, Order value or Movement.
  • Using the slow-moving flag to dispose of critical standby spares.

Checks before you trust the result

  • Count a sample of bins and compare with Qty on hand; record the date in Last stock count.
  • Filter Stock status for No levels set and No quantity, then complete those rows.
  • Confirm every maximum is higher than its reorder level.
  • Check that unit costs are the latest purchase prices in one currency.
  • Before ordering, check compatibility against the asset documentation and consider repair or an approved substitute.
  • Check minimum order quantities and pack sizes, because the order suggestion ignores them.

Frequently asked questions

Why is a part with an order placed still on the order list?

The register uses physical stock only. Update the quantity when goods arrive, or reduce the suggested order by hand and note the purchase order in your purchasing system.

What if I do not set a maximum?

The status can still show Reorder or Out of stock. Overstock cannot be flagged, and the order suggestion becomes twice the minimum minus stock.

Can I change the slow-moving window?

Yes, on the Lists sheet. Choose a period that matches how often parts normally turn over.

Does the workbook keep a transaction history?

No. It is a snapshot register. Keep receipts and issues in the authoritative stock system.

Recording the data and using the template

Start from the blank spares register template. Clear the fictional example rows, set the slow-moving window and category list, then enter one row per part with its levels and cost. Update quantities after every receipt and issue, and record the date of each count. Use the order list on the Dashboard to prepare purchase requests, and the highest stock-value list to find capital that can be reviewed. If a part is critical, record the reason in your inventory procedure; the workbook stores only the Yes or No flag. To estimate what a missing critical part could cost, the production downtime cost calculator gives an event figure you can keep with your justification.

Try it with the calculators

Free Excel templateSpare parts register: blank records, dropdowns and a live dashboard.
Download .xlsx

Browse all Excel templates and dashboard previews