How do I calculate a reorder point for a spare part?

Reorder point equals average daily usage times lead time in days, plus safety stock. If you use 2 units per week (0.29 per day) and the supplier lead time is 21 days, that is 6 units, then add safety stock. For a critical part with erratic demand, set safety stock at roughly one to two weeks of usage so a late shipment does not cause a stockout.

What carrying cost rate should I use for spare parts?

Most manufacturers use 20 to 30 percent of average inventory value annually. That rate bundles capital cost, storage, insurance, obsolescence, and shrinkage. For a crib holding $200,000 in average inventory at 25 percent, annual carrying cost is $50,000. Slow-moving or perishable items (gaskets, batteries) justify the higher end near 30 percent because obsolescence risk dominates.

How much safety stock should I keep for critical spares?

Size safety stock to lead time variability and the cost of a stockout, not a flat percentage. For a part with a 30-day lead time whose failure stops a line, holding one or two units of safety stock is cheap versus lost production. A common method is safety stock equals service factor (1.65 for 95 percent) times the standard deviation of demand over lead time.

How do I decide which spares to stock versus order on demand?

Weigh criticality against lead time and cost. Stock parts where a failure causes downtime and the lead time exceeds the tolerable outage. A $15 sensor with a 6-week lead time on a bottleneck machine belongs on the shelf; a $3,000 motor available next-day from a local distributor may not. Run a criticality ranking and stock the high-consequence, long-lead items first.

What is a good inventory turns ratio for a spare parts storeroom?

Spare parts turn far slower than production inventory. Insurance spares may turn 0.5 to 1 time per year, while consumables like filters and belts turn 3 to 6 times. Compute turns as annual usage value divided by average inventory value. Turns below 0.25 flag dead stock worth reviewing for write-off; you rarely chase high turns on critical insurance spares.

How often should I do a physical count of the storeroom?

Use cycle counting instead of one annual blitz. Count high-value or high-movement A-items monthly, B-items quarterly, and C-items once or twice a year. Counting roughly 5 to 10 percent of SKUs each week keeps records accurate without shutting the crib. Investigate any variance over 2 to 3 percent to find the root cause, whether that is unlogged issues or misplaced parts.

Maintenance and Reliability

Spare Parts Inventory Spreadsheet Template

Manage spare parts stock levels by tracking quantity on hand, reorder points, lead times, and annual carrying cost.

Overview

This template helps maintenance planners, storeroom managers, and reliability engineers control spare parts stock without a CMMS in place. It tracks quantity on hand, reorder points, lead times, and annual carrying cost for every part in the crib. Guessing reorder levels leads to stockouts on a $40 bearing that shuts a line for a shift, or shelves full of dead stock. A spreadsheet forces the actual math so you order the right quantity at the right time.

Each row holds a part number, description, supplier, unit cost, and quantity on hand. You enter annual usage and lead time, which drive the reorder point using average daily usage times lead time plus safety stock. Reorder quantity and unit cost feed the carrying cost calculation, where a 20 to 30 percent holding rate applied to average inventory value gives annual cost. A flag column compares on-hand quantity against reorder point and marks items due for replenishment.

In practice, load your crib into the sheet during a physical count, then set reorder points from usage history or PM frequency. Run a weekly filter on the reorder alert column to build a purchase list. At budget time, sum the carrying cost column to justify staffing or a min/max program. Pair it with the Spare Parts Carrying Cost Calculator to model how changing the holding rate or lead time shifts your annual cost before you commit numbers.

What this template includes

Part number, description, and supplier
Unit cost and quantity on hand
Reorder point and reorder quantity
Lead time and safety stock
Annual usage estimate
Carrying cost calculation
Reorder alert flag column

Suggested use case

Use this to document a storeroom, set reorder points before implementing a CMMS, or calculate annual inventory carrying cost for budget planning.

How to use it

Enter each spare part with cost and current quantity.
Set reorder point based on lead time and average usage.
Enter carrying cost rate to calculate annual holding cost.
Review reorder alert column to identify items that need replenishment.

Frequently Asked Questions

How do I calculate a reorder point for a spare part?: Reorder point equals average daily usage times lead time in days, plus safety stock. If you use 2 units per week (0.29 per day) and the supplier lead time is 21 days, that is 6 units, then add safety stock. For a critical part with erratic demand, set safety stock at roughly one to two weeks of usage so a late shipment does not cause a stockout.
What carrying cost rate should I use for spare parts?: Most manufacturers use 20 to 30 percent of average inventory value annually. That rate bundles capital cost, storage, insurance, obsolescence, and shrinkage. For a crib holding $200,000 in average inventory at 25 percent, annual carrying cost is $50,000. Slow-moving or perishable items (gaskets, batteries) justify the higher end near 30 percent because obsolescence risk dominates.
How much safety stock should I keep for critical spares?: Size safety stock to lead time variability and the cost of a stockout, not a flat percentage. For a part with a 30-day lead time whose failure stops a line, holding one or two units of safety stock is cheap versus lost production. A common method is safety stock equals service factor (1.65 for 95 percent) times the standard deviation of demand over lead time.
How do I decide which spares to stock versus order on demand?: Weigh criticality against lead time and cost. Stock parts where a failure causes downtime and the lead time exceeds the tolerable outage. A $15 sensor with a 6-week lead time on a bottleneck machine belongs on the shelf; a $3,000 motor available next-day from a local distributor may not. Run a criticality ranking and stock the high-consequence, long-lead items first.
What is a good inventory turns ratio for a spare parts storeroom?: Spare parts turn far slower than production inventory. Insurance spares may turn 0.5 to 1 time per year, while consumables like filters and belts turn 3 to 6 times. Compute turns as annual usage value divided by average inventory value. Turns below 0.25 flag dead stock worth reviewing for write-off; you rarely chase high turns on critical insurance spares.
How often should I do a physical count of the storeroom?: Use cycle counting instead of one annual blitz. Count high-value or high-movement A-items monthly, B-items quarterly, and C-items once or twice a year. Counting roughly 5 to 10 percent of SKUs each week keeps records accurate without shutting the crib. Investigate any variance over 2 to 3 percent to find the root cause, whether that is unlogged issues or misplaced parts.