Export 12 months of parts from your job records, merge duplicate names with AI, rank the 40 parts used on the most jobs, and set a minimum (the restock trigger) and a maximum (the top-up level) for each from weekly use and how often the van is restocked. Check the list against what your supplier holds before printing it.
The ranking is the step most van lists get wrong. Parts should be ranked by how many jobs used them, not by units, or one big refit decides what every van carries. In the illustrative two-van firm used throughout, with 1,420 jobs in the year, a single bathroom refit put 38 push-fit elbows through the books, enough to make a part that turns up on one job every two and a half weeks look like a weekly essential.
Export 12 months of parts and strip the customer details first
Twelve months matters because it captures the winter bursts and summer outdoor work that a three-month sample misses. Pull the parts and materials lines from completed jobs in your job software or invoicing system. In a spreadsheet, keep the job ID, date, job type, the part description exactly as typed, and the quantity. Delete every column that holds a customer name, address, phone number or email, since the job ID is all you need to link back.
Two gaps are worth closing early. If your invoices lump small items into "sundries" or "materials", the export will miss tape, sealant and fixings, so add them from supplier statements as a separate list. And if some quantities are packs (a line for "clips x10"), add a units-per-pack column now, because the mistake it prevents appears in the section on failures below. The illustrative export for the two-van firm had 3,980 part lines across 1,420 jobs, with 612 different descriptions.
Merge duplicate part names with a mapping file, not a retype
Over 600 descriptions for about 240 real parts is what happens when five people type invoices their own way. "15mm push fit coupler", "PF coupler 15" and "15 mm straight connector (push fit)" are one part. Do not paste 3,980 lines into an AI. Make a unique list of the 612 descriptions with a count beside each (a pivot table does it in a minute), then paste only that list. It is short enough for any chat tool, and the output is a mapping file you reuse next year.
Below is a list of part descriptions exactly as typed on our plumbing invoices over 12 months, with the number of invoice lines for each in column B. Customer details are not included.
1. Give each description a canonical name in the form: size (if any), type, fitting style, for example "15 mm push-fit straight coupler".
2. Never merge different sizes. If a description has no size where a size matters, start the canonical name with "SIZE UNKNOWN:" and do not merge it with any sized part.
3. Do not merge different brands if the description names one.
4. Put labour, call-out fees and discounts in a group called NOT A PART.
5. Return a table with three columns: original description, canonical name, and a CHECK column (yes or no, with a short reason if yes).The output is a table you paste into a Mapping tab. Here are rows from the illustrative run, with what a good result does:
| Original description | Canonical name | Check |
|---|---|---|
| 15mm push fit coupler | 15 mm push-fit straight coupler | No |
| PF coupler 15 | 15 mm push-fit straight coupler | No |
| ptfe | PTFE tape roll | No |
| Flexi 15x15x300 | Flexible tap connector, 15 mm | No |
| isolating valve | SIZE UNKNOWN: isolation valve | Yes: no size given |
| coupler push fit | SIZE UNKNOWN: push-fit straight coupler | Yes: no size given |
| Call out fee | NOT A PART | No |
Review the CHECK rows by hand, and the 20 canonical names with the most lines. This is where AI cleaning earns trust or loses it. In a first attempt without rule 2, the AI merged "22 mm push-fit coupler" into the 15 mm one because the words were identical apart from the size, and the 22 mm line vanished from the usage numbers. The tutorial on SKU clean-up with AI for a hardware store covers the same job on a larger catalogue, and the guide to cleaning messy data covers the spreadsheet side.
Add a Canonical column to the full export with a lookup (=IFERROR(VLOOKUP(D2, Mapping!A:B, 2, FALSE), "UNMAPPED")), filter for UNMAPPED, and add any new descriptions to the mapping. Delete NOT A PART lines. Now every row names a real part.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowRank the top 40 by jobs used on, not by units
Count the lines per canonical part as a stand-in for jobs, since a job normally lists a part once (=COUNTIFS(Canonical, A2)). Add units too (=SUMIFS(Qty, Canonical, A2)) and sort by jobs. A part used on 214 jobs belongs on every van even if the unit total is small, while a part with a big unit total from a single job should not.
The 15 mm push-fit elbow shows the difference. It used 64 units across 21 jobs, which gives 1.28 units a week. But one bathroom refit used 38 of them. Take that job out and the part used 26 units across 20 jobs, or 0.52 a week for the firm and 0.26 for each van, which means a minimum of one and a maximum of two. A van that carried the inflated figure would hold a drawer of elbows for no reason. Flag any job where a part's quantity is more than about five times that part's usual quantity, check whether it is a refit or a typing slip, and exclude it from the weekly use figure while keeping the job in the count.
In the illustration, the top 40 parts covered 82% of all part lines (3,264 of 3,980). The rest is a long tail of items bought for one job and not worth carrying. That figure is for this firm; yours may be higher or lower, and the point is to find your own cut-off by looking at where the cumulative share flattens.
To find the cut-off in your own data, sort the parts by jobs used on and add a running share column (=SUM($C$2:C2)/SUM($C$2:$C$239) on the ranking tab, where column C holds the lines per part and row 239 is the last part, so adjust it to your list). Look for the row where the share stops climbing quickly. Keep everything above it, and treat the first ten parts below it as candidates for the critical-spares check described next.
Two further groups go on the list whatever their rank. Critical spares are rarely used and expensive to miss: a repair coupler or clamp for a burst pipe, a spare isolation valve and a universal fill valve each turn a visit into a callback if the van does not have one. Set their minimum at two, whatever the usage says. Safety and compliance items such as test labels or warning tags, if your trade uses them, belong on the list for the same reason.
Set a minimum and a maximum for each part
The minimum is the restock trigger: when the van holds fewer than this at the weekly count, it gets topped up. The maximum is the top-up level. Both come from how fast the van uses the part and how long it goes between restocks. Put the settings in cells so one change recalculates the whole sheet. In the example the settings are:
B1 Vans = 2
B2 Weeks of data = 50
B3 Restock interval = 1 week (vans topped up from the workshop shelf)
B4 Lead time = 0 weeks (parts held on the shelf)
B5 Safety factor = 1.5
B6 Critical minimum = 2
Column C = units a year, column D = "Y" for critical spares (otherwise blank)
Use per van a week (E2): =C2/$B$2/$B$1
Minimum (F2): =MAX(IF(D2="Y",$B$6,0), ROUNDUP(E2*($B$3+$B$4)*$B$5, 0))
Maximum (G2): =F2 + ROUNDUP(E2*$B$3, 0)The safety factor of 1.5 means the van should hold one and a half times the expected use between restocks, so a busy week does not empty a drawer. Raise it for parts that cost you a trip when missing and lower it for cheap items you can substitute. The first 12 lines of the illustrative list look like this, rounded up to whole units:
| Part | Jobs used on | Units a year | Use per van a week | Min | Max |
|---|---|---|---|---|---|
| PTFE tape roll | 410 | 420 | 4.20 | 7 | 12 |
| 15 mm push-fit straight coupler | 214 | 260 | 2.60 | 4 | 7 |
| 15 mm isolation valve | 188 | 205 | 2.05 | 4 | 7 |
| Flexible tap connector, 15 mm | 176 | 340 | 3.40 | 6 | 10 |
| Silicone sealant cartridge | 150 | 150 | 1.50 | 3 | 5 |
| 22 mm push-fit straight coupler | 96 | 110 | 1.10 | 2 | 4 |
| Universal toilet fill valve | 92 | 94 | 0.94 | 2 | 3 |
| 40 mm waste trap | 83 | 83 | 0.83 | 2 | 3 |
| Washing machine hose | 74 | 74 | 0.74 | 2 | 3 |
| 15 mm compression repair coupler (critical) | 66 | 71 | 0.71 | 2 | 3 |
| Toilet flush valve and seal | 61 | 61 | 0.61 | 1 | 2 |
| Pipe clips, pack of 10 | 58 | 58 | 0.58 | 1 | 2 |
Check one line by hand. The 15 mm coupler is used 260 times a year across two vans over 50 weeks, which is 2.6 a van a week. Multiply by one week and the safety factor of 1.5 to get 3.9, round up to a minimum of 4, then add a week's use (3) to get a maximum of 7. At cost, the maximum for these 12 lines is about $237 per van using placeholder prices (put unit costs in column H and use =SUMPRODUCT(G2:G13, H2:H13)), and that is a number worth tracking as the list grows to 40. If the full-van total is more than you want tied up, lower the safety factor on cheap, slow items first, not on the fast movers.
If your vans do different work, calculate each van on its own. Add a van column to the export (most job software records who attended), then use SUMIFS by van instead of dividing the totals by two. In the illustrative firm the second van took most of the bathroom installs, so its elbow and waste-fitting levels came out at about double the service van's, while the service van carried more fill valves, hoses and repair couplers. Dividing by two would have starved one van and over-stocked the other.
Check the list against what your supplier really holds
A level you cannot refill is a fiction, so test the list against the supplier before you print it. Get the stock list or catalogue export from your trade merchant (ask your account contact for one) or collect the product names and pack sizes you buy from supplier invoices. Then ask the AI to compare the two lists and report, never to guess.
Here are two lists. List 1 is our van stock list with a canonical part name, minimum and maximum. List 2 is what our supplier holds, with pack size and availability.
For every part in List 1, tell me: is it in List 2 (exact match, close match or none); the pack size; whether it is held for same-day collection or special order, and the lead time if given; and whether another listed product could substitute. Flag any part where the pack size is larger than our maximum for two vans.
Do not guess at availability. If List 2 does not say, write "not stated".Typical results from the illustrative run, with the action taken:
| Part | Supplier position | Action |
|---|---|---|
| 15 mm push-fit straight coupler | Held for same-day collection, packs of 10 | Order in tens; workshop shelf holds 20 |
| Universal toilet fill valve | Special order, three days | Lead time 0.6 weeks, so the minimum rises from 2 to 3 |
| 15 mm compression repair coupler | Held, but sold in packs of 25 | Buy one pack for both vans; keep the spare on the shelf |
| Washing machine hose | Two similar products, different lengths | Pick one length for both vans so spares interchange |
The fill valve line shows why lead time belongs in the formula. With a three-day wait, or 0.6 of a week, the minimum becomes 0.94 x (1 + 0.6) x 1.5 = 2.26, which rounds up to 3. A part that takes days to arrive needs more cover than one on the shelf. Where the AI finds a substitute, treat it as a suggestion to check, not an instruction. Ask your supplier about price breaks and the order cut-off while you are there: a part sold in packs of 25 may cost less per unit than five singles, and a noon cut-off for same-day collection changes what you can promise a customer in the morning. The tutorials on turning supplier price lists into a parts markup sheet and on comparing supplier quotes cover the pricing side of the same supplier files.
Turn it into a van sheet and a weekly restock routine
Print the list as one page per van, sorted by where each part lives in the van (drawer or bin) and not alphabetically. Columns: part, location, min, max and a blank box for the count. At the end of each week the technician counts against the sheet. Anything below its minimum gets topped up to its maximum. Everything else is left alone, which is the point: the weekly job takes minutes, and the rule needs no judgement.
The sheet replaces vague instructions. "Couplers, tape, valves: restock when low" cannot be checked by anyone, while "15 mm coupler, drawer B3, min 4, max 7" can be checked in thirty seconds by a new technician or whoever covers when the usual stock person is away. That check-in-thirty-seconds property is what keeps the routine alive after the first enthusiastic month.
The AI can turn a typed or dictated count into an order list. Give it the sheet and the rule, and let the spreadsheet do the arithmetic; the tutorial on AI reorder points covers the wider method. In the illustrative week a technician dictated these counts, and the order list came back:
Counts: 15 mm couplers 2, flexi connectors 5, silicone 1, PTFE 3, fill valves 0.
Order list (rule: if count is below min, order up to max):
15 mm push-fit straight coupler count 2, min 4, max 7 order 5
Flexible tap connector, 15 mm count 5, min 6, max 10 order 5
Silicone sealant cartridge count 1, min 3, max 5 order 4
PTFE tape roll count 3, min 7, max 12 order 9
Universal toilet fill valve count 0, min 3, max 4 order 4
Not counted this week: all other lines. Please confirm.Notice the last line. A good prompt tells the AI to list lines that were not counted instead of assuming they are fine. The fill valve line already carries the higher minimum and maximum from the supplier check, which is why it orders four. Add an out-of-stock log as well: a column on the job sheet, or a text to the office, whenever a technician needs a part the van does not have. The log tells you which minimums are wrong, because the same part will keep appearing.
Four ways an AI-built van list goes wrong, and how each shows up
Merged sizes. The 22 mm coupler disappeared into the 15 mm line. It showed up as a technician reporting that the van never had the larger size, while the usage table said the part barely existed. Fix it with rule 2 in the mapping prompt and a manual check of every canonical name that contains a size.
Packs treated as units. A line reading "PTFE x10" meant a bag of ten rolls, but it went into the units column as 10 units for one roll each. If every PTFE line were mistaken that way, a year of 420 rolls would become 4,200, weekly use per van would read 42, and the minimum would come out at 63 rolls. The sanity check is whether the maximum fits in the bin. Anything that needs a crate for a roll of tape is a unit error.
The outlier job. The bathroom refit that used 38 elbows would have given every van a drawer of fittings. It showed up as a part with a high unit total and a low job count. Rank by jobs, and exclude the outlier from weekly use.
Returns and credits. If the export includes credit notes or returned parts, they can appear as negative quantities or be missing, which makes a part look more or less used than it is. Filter out negative lines and check the list against purchases for the biggest ten parts before trusting the totals.
Seasonal swings and the cost of a mid-job supplier run
A year of data averages the cold snap into the other 44 weeks. Say 31 of the 71 compression repair couplers went in six cold weeks. Over that spell the firm used 5.2 a week, or 2.6 for each van, giving a minimum of 4 and a maximum of 7. For the rest of the year the firm used 0.9 a week, giving a minimum of 2 and a maximum of 3. A single flat level would be too low in winter and too high in summer, so give the parts that swing a winter and a summer level. Spot them by sorting each part's use by month and flagging any month above twice the monthly average.
The reason to care is the supplier run. A trip to the counter in the middle of a job takes about 45 minutes of a technician's day. If that happens three times a week across the two vans, over 48 weeks, it is 6,480 minutes, or 108 hours. At $55 an hour of technician cost that is $5,940 of paid time, and at a $130 billing rate it is $14,040 of work that could have been sold. Those figures are illustrative, and the habit is to count your own runs for a month before deciding how much stock to carry. A van list built from job history will not remove every run, but each minimum that was right is one run less.
To see whether the list is working, compare three numbers for the eight weeks after the sheet goes in with the eight weeks before: supplier runs logged, out-of-stock entries, and the value of stock on each van at the weekly count. Runs and misses should fall while van value settles a little under the total of the maximums at cost. If runs fall but van value keeps climbing, someone is topping up past the maximum. If the misses cluster on two or three parts, raise only those minimums and leave the rest of the sheet alone.
Van stock questions owners ask
How many items should a plumbing van carry?
There is no standard number. Start with the 40 parts used on the most jobs, because they cover most lines in a typical export, then add critical spares that are rarely used but turn a job into a return trip. Grow the list only when your out-of-stock log shows the same miss more than once.
Can I do this without inventory software?
Yes. A spreadsheet with a mapping tab, a usage tab and a levels tab does the whole job, and the van sheet is a printed page. Software helps once several vans share a store, but the levels still have to come from your own job history, so build them this way first.
How often should I recalculate the levels?
Every quarter, and after any change in the work you take on, such as adding water heaters or bathrooms. Recalculate immediately for a part with repeated misses in the out-of-stock log, or when a supplier changes a pack size or drops a line.
Further reads
- Van Stock Management for HVAC and Electrical Vans: Reorder Levels With AI — The same method for HVAC and electrical vans
- Tyre Stock Excel Sheet: Track Sizes and Reorder Points With AI — Another min and max build in a plain spreadsheet
- Plumbing Flat-Rate Pricing: A Worked 30-Task Price List Built With AI — Price the repairs your van list lets you finish first time
- Can AI Analyse My Sales Spreadsheet? What to Upload and Check — What to upload to an AI and what to check afterwards
- How to Use AI in a Plumbing Business: A 30-Day Setup Plan — Where stock fits in a 30-day plan for a plumbing firm
- Tool Tracking for Contractors: Paid App or a Sheet Built With AI? — A sheet with a QR check-out form costs nothing; paid apps start free or around $100 a month. See what each must record and the sum that decides it.
- QuickBooks for Plumbers: Set Up Job Costing and Let AI Sort the Bills — Turn on Projects, code supplier bills to jobs and see each plumbing job's real margin, with a worked bathroom replumb from quote to profit.
- Water Heater Installation Checklist: Photo Proof and Handover Pack With AI — The installer's photo-proof checklist for a water heater job, the tests you record yourself, and how AI drafts a plain-English handover pack.
- Can AI Tools Help With Plumbing Design? What Needs a Qualified Person — What AI can draft in a plumbing layout, what a licensed designer must verify, and a sign-off checklist for the contractor.
- Can AI Update Your Plumbing Price Book From Supplier Invoices? — Update a plumbing price book from a wholesaler invoice with AI: line extraction, cost comparison, markup rules and the checks that catch mistakes.
- Can AI Write Plumbing Proposals for Repipes and Water Heaters? — A worked water heater proposal with three options, plus repipe and bathroom notes and the checks to run before you send.
- Can an AI Dispatcher Replace Your Plumbing Office Dispatcher? — What an AI dispatcher can take over in a small plumbing company, and the list of calls a person must still make.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: the illustrative job export, part counts and costs in this tutorial are assumptions for a two-van firm; the formulas use standard spreadsheet functions (ROUNDUP, VLOOKUP, SUMIFS).