Spare parts inventory template: stock, reorder point and critical spares in Excel

Why maintenance spares need their own inventory

Retail stock is managed to sell. Maintenance stock is managed to avoid downtime. A bearing that sits on a shelf for two years is not a failure if it saves a production line the day the old one seizes. The opposite mistake is just as real: shelves full of parts for machines that left the plant long ago, while the one seal everybody needs is missing.

A spare parts inventory answers three questions at any moment: what do we have, where is it, and what must we buy before it runs out. Without a record, the answers come from memory and from a walk to the storeroom. The usual symptoms are easy to recognise.

• Stockouts on critical parts that turn a one-hour repair into a wait for the supplier.
• Duplicate references: the same part bought under two names, stored in two places and counted twice.
• Parts taken without a record, so the stock on paper drifts away from the stock on the shelf.
• Money tied up in slow-moving parts that nobody dares to throw away, because nobody knows which machine they fit.

How the template is built

The file has two sheets. The Parts sheet is the catalogue: one row per part, with a summary block at the top. The Movements sheet is the logbook: one row per receipt or issue. You never type the current stock by hand; it is computed from the movements, which is what keeps it honest.

On the Parts sheet you type the part number, description, manufacturer and reference, the equipment the part is used on, its storage location, whether it is a critical part (Yes or No), its unit, the opening stock, the minimum stock, the average daily use, the lead time in days, the maximum stock, the unit cost and the supplier. On the Movements sheet you type the date, the part number, the type (In or Out, from a drop-down list), the quantity, a reference such as a work order or a delivery note, who made the movement and a comment. The description fills itself in from the part number and shows Unknown part number when the number is not on the Parts sheet.

The grey columns compute the rest. They are formulas, so keep them intact when you copy or sort the file.

• In and Out = the sum of the movements of that part number, by type
• Current stock = opening stock + in − out
• Suggested reorder point = average daily use × lead time, rounded up, + minimum stock (the minimum alone when use or lead time is blank)
• Quantity to order = maximum stock − current stock, once the stock is at or below the reorder point
• Stock value = current stock × unit cost
• Status = Out of stock at zero or below, Reorder at or below the reorder point, OK otherwise

Reorder point, minimum and maximum: the logic

The reorder point is the stock level at which you place an order. It has to cover what you will use while you wait for the delivery, plus a margin for the unexpected: a usage peak, a late supplier, a breakdown that eats three parts at once.

In the template, that margin is the minimum stock. It is the safety stock, the quantity you never want to dip below. The consumption during the lead time is the average daily use multiplied by the lead time in days, rounded up to a whole unit. Add the two and you get the suggested reorder point.

The maximum stock caps the order. When a part reaches its reorder point, the file proposes the quantity that brings it back to the maximum. Set the maximum from storage space, shelf life, pack size and the money you accept to tie up, not from habit.

An illustrative example: a seal is used at an average of 0.5 per day, the supplier needs 14 days and you never want fewer than 3 on the shelf. Reorder point = 0.5 × 14 = 7, plus 3, so 10. With a maximum of 20 and a current stock of 9, the status reads Reorder and the quantity to order is 20 − 9 = 11.

• Average daily use: take it from your own movements over a representative period, the issues of that period divided by its number of days. Seasonal parts deserve a second look.
• Lead time: from the order to the part on the shelf, including internal approval and transport, not only the figure in the supplier’s catalogue.
• Minimum stock: higher for critical parts and unreliable suppliers, zero or blank for parts you accept to buy only when needed.
• Parts with no regular use, such as a spare motor or a gearbox, do not fit the formula well. Set their minimum directly, often to one, and leave the daily use blank.

Critical spares and ABC thinking

A critical spare is a part whose absence stops a machine, creates a safety or environmental risk, or cannot be bought quickly. Think of the motor of a conveyor with no bypass, a specific controller card, or the seal kit of the only boiler feed pump. These parts are kept even if they rarely move, because the cost of not having them is a production stop, not a purchase.

Mark them Yes in the Critical part column and review them first. A critical part with no minimum stock should be a decision made on purpose, not an omission.

ABC classification is a general method to decide where to spend counting and review time. Sort the parts by yearly consumption value: the few A parts that carry most of the value get frequent counts and tight reorder settings, the many C parts get simple rules. Combine it with criticality, because a cheap C part can still stop a line. To decide whether a part is critical, ask these questions.

• Does a failure of the equipment stop production or create a safety risk?
• Is there redundancy, such as a standby pump or a second line?
• How long does the supplier take, and is the part a standard item or made to order?
• Can the part be repaired, or replaced by an equivalent taken from another machine?

When the spreadsheet shows its limits

The template works well for one storeroom and one person who keeps it up to date. It is a solid way to clean up a catalogue, set reorder points and see what to buy this week.

It strains when several technicians take parts while the file is open on one computer, when the stock sits in two or three storerooms, when you need to know which work order used which part on which machine, when nobody opens the file until a part is missing, or when the stock has to be checked from a phone in the plant.

FreeMaint keeps the same logic in a maintenance system. Parts, stock, minimum quantity, reorder point, low-stock alerts and parts used on work orders are in the free Core tier. Part kits come with Starter, $29 per company per month, and transfers between storerooms with Business, $99 per company per month, both flat, never per user. The Excel file stays useful for analysis and for a first clean-up of the catalogue.