MRO Inventory Management: AI-Set Minimums for Maintenance Spares

Export 12 months of work orders to a spreadsheet, total the parts used, rank each part by what a stockout would stop, and set minimum stock as lead-time usage plus a safety margin that rises with criticality. AI cleans the part names and drafts the formulas; your maintenance lead confirms the ranking.

No sensors or new software are needed for this. The only data you must have is a record of what was used, when, and on which machine, and nearly every factory already holds that in work orders, a maintenance log or the stores issue book. The difficulty is that it is messy: one bearing appears under four spellings, and the shelf holds parts nobody has touched since a machine left the building. That cleaning job is where AI saves hours.

Follow me on Instagram@sagnikteaches

Why a spares room needs different rules from a shop shelf

Stock for resale follows sales, and a forecast built on last year's sales is a decent guide. Maintenance spares behave differently. Demand is lumpy: a belt may sit untouched for nine months and then three fail in a week. Value is not proportional to cost, because a $6 bearing can stop a $400,000 line while a $1,800 drive module may only sit idle. And the penalty for a stockout is measured in downtime hours, not in lost sales of a single item.

Connect on LinkedInSagnik Bhattacharya

So three questions decide each part's level, in this order. How much does it hurt if we do not have it (criticality)? How fast do we use it, and how long does a replacement take to arrive (usage and lead time)? Should we hold it at all (obsolescence)? The rest of this tutorial answers each, with a spreadsheet as the working surface.

Subscribe on YouTube@sagnikteaches

Step 1: get one clean list of parts

Start with the stores room itself and the last year's work orders. If you run a computerised maintenance system, export its parts list and the work-order lines with part, quantity, date and asset. If you do not, type or photograph the last 12 months of the log. The tutorial on running a stocktake with an AI phone scanner helps for the physical count, and it is worth doing one count before you trust any levels, since a sheet that says 6 when the shelf holds 2 will mislead every decision after it.

Clean the part names with a prompt

The first pass is to give every physical part one name and one code. Paste the distinct part descriptions from your work orders and ask for grouping, never for deletion.

Below is a list of maintenance part descriptions taken from work orders, one per line, with the number of times each appeared.
Group lines that clearly refer to the same physical part. For each group give:
- Proposed standard name
- Standard part number if one appears in any line, otherwise "none"
- The original lines in the group
- Confidence: HIGH if the lines differ only in spelling or spacing, LOW if you are guessing
Do not merge parts that differ in size, voltage, rating or type. If two lines might be different parts, keep them separate and list them under "Check".

LINES:
[paste]

An illustrative result: "6205 bearing", "bearing 6205-2RS", "brg 6205 2rs" and "6205-2RS SEALED BEARING" form one group with standard part number 6205-2RS and high confidence. "6205 ZZ bearing" stays separate, because ZZ means a metal shield rather than a rubber seal, and the assistant lists it under Check. A maintenance technician should read the Check list. This is the stage where a wrong merge corrupts everything that follows.

Step 2: rank the parts by criticality

Criticality is about the consequence of not having the part. A long-established method, often called VED (vital, essential, desirable), asks the same kind of question, and a simple scoring version works well for a small factory. Score each part from 1 to 3 on four questions, add them, and set class A, B or C by total.

Totals of 10 to 12 make a part class A, 7 to 9 class B and 4 to 6 class C. Ask the assistant to score a first draft from a short description of each machine's role, but do not accept it as is. It does not know that your press is the only one that makes your main product. Sit with the maintenance lead and go through the A class aloud: for each part, "if this is missing on a Monday, what happens by Wednesday?" Expect to move a handful of parts either way. Keep the A class under about one part in six, or it stops meaning anything.

Step 3: read usage from the work orders

With part names fixed, count how many of each were used in the last 12 months. A pivot table does this directly: rows are parts, values are the sum of quantity. Also note how many separate jobs used each part, because a single big overhaul that took 12 belts is different from 12 belts used one at a time. Divide annual usage by the number of working days (say 250) to get daily usage.

Adjust for what will not repeat. A one-off machine upgrade that used 30 sensors is not normal demand, so strike it out or mark it. Equally, adjust for what is about to change: a new line adds machines that will use parts next year. If your work-order history is shorter than 12 months, use what you have and flag it on the sheet as low confidence. The tutorial on breakdown logs and maintenance plans shows how to turn the same history into patterns of failure, which is useful when a part fails in clusters.

Step 4: write the min and max rules

The minimum is the stock level at which you reorder. The maximum is what you order up to. A rule that works for small factories:

• Minimum = daily usage times (lead time in working days plus review period in days), rounded up, plus safety stock.
• Safety stock by class: A parts, 1 extra unit if the unit cost is under about $200, otherwise decide case by case; B parts, 0 to 1; C parts, 0.
• Insurance spares (critical, rarely used, expensive): hold 1 regardless of usage (minimum and maximum both 1), or agree with the supplier a repair or exchange service and write that on the sheet.
• Maximum = minimum plus an order quantity that covers about 60 days of usage, never less than one unit, and rounded to the supplier's pack size.

The review period is how often you check the sheet; with a weekly check it is 5 working days. Add it, because a part can drop below the minimum the day after you looked.

The main example: a 40-person plastics moulding factory

An illustrative factory pulls 12 months of work orders and gets the table below. Numbers are invented, with 250 working days a year, a 5-day review period and the rules above. The servo drive is treated as an insurance spare.

Take the heater band as a worked sum. Daily usage is 14 divided by 250, which is 0.056. Lead time 15 days plus the 5-day review gives 20 days, so 0.056 times 20 is 1.12, rounded up to 2. Add 1 safety unit because it is class A and costs under $200: minimum 3. Order quantity for 60 days is 0.056 times 60, about 3.4, rounded up to 4, so maximum is 3 plus 4, which is 7. At 3 units on the shelf the sheet says "order 4". For the servo drive, the maths gives a minimum of 1 and the rule says hold one. At $1,850 the factory writes "Insurance spare; ask supplier about exchange service" in the notes and does not stock a second.

Total value held at maximum for these eight parts is $2,827, of which $1,850 is the single servo drive. The factory sees at once where its money sits and that the risk lies in a few parts, not in the many cheap ones.

Step 5: let the sheet flag what to order

Add a column for on-hand count and a formula that shows the order quantity: if on hand is at or below the minimum, order max minus on hand, otherwise zero. In a spreadsheet that is =IF(H2<=F2, G2-H2, 0) with H as on hand, F as minimum and G as maximum. Sort or filter to show the nonzero rows each week, and send them to purchasing. The tutorial on building a purchase order chase sheet picks up where this leaves off, so ordered parts do not vanish.

Ask an assistant for a weekly summary only if you feed it the filtered rows. It can write the buyer's email, group by supplier and mention parts with long lead times, but the counts stay in the sheet. A prompt for the email:

Write a purchasing email to [your name]'s buyer listing the parts below that fell to or under minimum this week. Group by supplier. For each part show part number, name, on hand, minimum, quantity to order and lead time. Flag any class A part in bold. Do not change any numbers.

ROWS:
[paste]

Step 6: the obsolete-parts check

Spares rooms fill up with parts for machines that left years ago. Obsolete stock ties up cash and shelf space, and it hides the parts you need. Run this test on every item in the stores list.

1. No issue in 24 months (or a period that suits your equipment's life) in the work orders.
2. No machine on the asset list uses it. Cross-check the part's machine tag against the current asset register.
3. No class A flag. An insurance spare for a critical machine is exempt from rule 1.
4. No scheduled job needs it. Check the preventive maintenance plan.

Parts that meet all four conditions go on a candidate list. Provide the assistant with the stock list, the asset register and the last issue date, and ask it to produce the candidate list with a reason against each row. Then a person decides: return to supplier, sell, keep as a shared spare for a similar machine, or scrap. Stage it. Move candidates to a labelled shelf with a review date six months out, so that a mistake can be reversed before the part is gone. Our preventive maintenance schedule tutorial is worth reading first, since a part may look unused only because the service plan that needs it has not yet come round.

In the illustrative factory the check turned up 31 lines of 410 with no use in 24 months, worth about $2,600 at cost. After review, 22 went to the candidate shelf, 6 were kept because they fitted a machine used once a year for a special order, and 3 were returned to a supplier for credit. The part the team nearly scrapped was a seal kit for the oldest moulding press, kept on the strength of a technician who remembered it failing in 2019.

Reading the order list with a buyer's eye

A sheet that says "order 4 heater bands" is only the start of the buying decision. Before the order goes out, a short review stops the sheet from turning into an autopilot. Look at three things for each flagged row.

First, is the lead time still true? Supplier lead times drift, and the figure you typed in a year ago may be half what it is now. Record the actual days from order to receipt each time a part arrives, and compare the average to the sheet every quarter. If a part you planned on 15 days now takes 35, the minimum should rise: for the heater band, 35 plus 5 is 40 days, times 0.056 is 2.24, rounded up to 3, plus the safety unit makes a minimum of 4 rather than 3. A smaller drift, to 25 days, leaves it at 3 (30 days times 0.056 is 1.68, rounded up to 2, plus 1), which is why you redo the sum rather than assume.

Second, is there a better pack size or price break? The order quantity of 4 is the maths. If the supplier sells in packs of 5 at a discount, order 5, and note it. If a different part at twice the price lasts four times as long, the sheet has no way to know, and you will want to ask the maintenance lead. An assistant can draft a short comparison table for two quotes if you give it the figures, but it cannot know about a quality problem your technicians remember.

Third, is the demand real? A part that suddenly jumps in usage may signal a developing fault on a machine, not a new normal. Before you raise a minimum on the strength of one bad quarter, ask what changed. The breakdown notes on the work orders often hold the answer, and a quick prompt over those notes ("list the machines and failure reasons for every work order that used this part in the last 90 days") shows whether the demand has a single cause that a repair would remove.

A filled-in row and the notes that matter

Finally, give each part a short notes field. It is the place for the facts that numbers cannot carry, and it is what makes the sheet useful to a new technician. An illustrative entry for the servo drive reads: "Class A. Fits press 3 and press 4. Supplier lead 60 working days. Exchange service offered: 10-day turnaround, ask for quote after any fault. Last used 2025. Spare held on top shelf, bay C, tagged red." Another for the oil filter says: "Class C. Local supplier, next-day. Order 6 at a time. Check pack size before ordering." These lines take a minute each for the A class, and the assistant can turn rough technician notes into this tidy form if you paste them in and tell it not to add facts.

Examples from other workshops

A print shop with two presses

The shop's critical parts are rollers, blankets and a handful of sensors. With only 60 spare lines, it ranks all of them in one sitting with the press operator. Usage comes from a notebook of repairs, typed into a sheet by the office. The first sheet shows that three part types account for most spending, and the owner sets the levels with one aim: fewer emergency courier orders, which had been running at about one a month. The order count each quarter shows whether it worked.

A food packing plant starting with one line

Rather than the whole stores, the plant starts with the spares for its single bagging line: sealing jaw heaters, thermocouples, a conveyor belt, cutter blades and a few sensors, about 40 lines in all. Because the line stops the whole site when it stops, the first draft scores half of them class A. The team then asks which parts have a same-day local source and which fail without warning, which brings the A class down to six parts. Proving the sheet on one small set makes the second area quicker to add.

A CNC job shop with three machines

Spares are way-lube filters, coolant pump seals, toolholder retention knobs and a spindle drive belt. In this illustration the owner treats the spindle belt as class A, because a machine without it removes a third of the shop's capacity, and keeps one on the shelf because the machine builder quotes six weeks. Retention knobs are cheap and used steadily, so they follow the normal min and max rule.

A mistake that came from trusting the usage count

One pattern to watch: the sheet says a proximity sensor was used only twice in a year, so the minimum falls to zero. The reason for the low count is that technicians cleaned and reseated the sensors instead of replacing them, until a line change made that fix impossible, and then six failed in a month. The way to see it coming is to look at downtime by cause, not only at parts issued, and to ask maintenance staff which parts they expect to need more of. Class A parts keep a floor of one regardless of the usage count for exactly this reason. Check for the same pattern after any change of supplier, material or machine speed, which can change failure rates overnight.

How to check the sheet is working

• Stockouts: count the times a repair waited for a part in the last quarter. For class A parts the target is zero.
• Emergency purchases: count orders placed with express delivery or at a premium. This should fall month on month.
• Value held: total stock value at cost against last quarter. Expect it to fall in C parts and hold steady or rise slightly in A parts.
• Count accuracy: pick 10 parts and count them against the sheet each month. Aim for 9 correct or better.
• Review: redo usage and lead times every six months and after any supplier change.

A weekly 15-minute review is enough once it is running: filter the order column, check the three oldest open orders and look at any class A part under minimum. For a broader sweep, an operations exceptions report from exports can include this list. And if you are weighing sensors later, the tutorial on predictive maintenance for a small workshop says when they pay off.

If you would rather set up the criticality scale and the sheet with someone beside you, that is what my AI implementation consultation covers; you can start from the contact page.

Questions maintenance teams ask about spares levels

What does MRO stand for?

Maintenance, repair and operations: the parts and supplies that keep a plant running but do not go into the product. Bearings, belts, filters, sensors, fuses, lubricants and gloves are typical. They differ from raw materials because demand follows breakdowns and service plans, not customer orders.

Do I need a maintenance software package to do this?

No. A spreadsheet fed by your work-order records, even a paper log typed into rows, is enough to start. Software helps once you have several sites or thousands of parts, but the criticality ranking and min and max rules are the same in a sheet.

How many parts should I rank as critical?

Fewer than you think. As a rough guide, if much more than one part in six ends up in your top class, the scoring is too generous and every part looks equally urgent. Rank by what happens to production and safety if the part is missing today, then check the top class with your maintenance lead.

Is it safe to throw away parts the check calls obsolete?

Not automatically. Treat the list as a prompt for a decision by someone who knows the equipment. A part with no use for two years may fit a rarely failing machine that you cannot run without. Move candidates to a labelled shelf and set a review date before any are scrapped or sold.

Further reads

• Factory Energy Audit With AI: Find Peak Loads and Idle Waste — Another factory job that starts from exports you already hold.
• Check a Bill of Materials With AI: Template, Gaps and Revisions — Check the bill of materials side of stock with AI.
• AI Tools for Cataloguing Stock: From Photos to Item Records — Photograph the shelf and build item records quickly.
• Farm Equipment Maintenance Log: AI Builds Service Schedules From Manuals — A maintenance log method from another equipment-heavy business.
• Supplier Scorecard Template: Score Delivery and Quality With AI — Score the suppliers behind your longest lead times.
• AI Inventory Forecasting for Small Businesses: How It Works — How forecasting works for stock that follows sales.
• ISO 9001 Documentation: Hire a Consultant or Draft It With AI? — Which ISO 9001 jobs AI can draft for a small manufacturer, which ones need a consultant, and a costed worked example of the split.
• Is Cin7 ForesightAI Forecasting Worth It for a Small Maker? — A payback test for Cin7 ForesightAI: when it beats a spreadsheet forecast, a break-even price to bring to sales, and a backtest you can run first.
• AI for Industrial Manufacturing: Read Downtime Logs to Find Repeat Stops — A practical CSV walkthrough for finding repeated line stops, checking the arithmetic and choosing a maintenance action.
• Maintenance Request Form: Triage Factory Requests by Urgency — A factory-floor maintenance request form, four urgency levels, a safety override and an AI step that tags and routes each request.
• Safety Data Sheet Management: Keep SDS Files Current With AI Help — A register-first method for suppliers: AI reads each safety data sheet into a table, flags stale ones and drafts replies, while a person signs off.
• Shift Handover Template: Let AI Turn Operator Notes Into a Report — A fillable shift handover template and an AI prompt that turns rough operator notes into a report, with safety items protected.
• Stock Count Variance: Find Why Your Count and System Disagree — A value-first routine for small shops: rank every count variance in dollars, then rule out the three cheap causes before you suspect theft.
• ABC Analysis for a Distributor: Rank SKUs and Customers With AI — Rank a distributor's SKUs and customers into A, B and C classes in a sheet, then attach a service rule to each class.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: general inventory-control practice (min and max levels, ABC and VED classification); bearing distributors' listings for the 2RS (rubber seals) and ZZ (metal shields) suffixes, checked November 2026; all quantities, costs and lead times in the worked example are illustrative.