What is a spare parts inventory?
A spare parts inventory is the list of every part the workshop keeps on the shelf, with how many are in stock, what they are worth, where they are kept and when to order more. Kept with every receipt and every issue, it shows what each vehicle consumed and which parts tie up money without moving.
This spare parts inventory template in Excel holds a parts master, a transactions log of receipts, issues to work orders, returns and count adjustments, current stock calculated with SUMIFS, reorder and slow-moving alerts, a stock summary by category and parts cost per vehicle.
What should a parts master include?
| Field | Why it matters |
|---|---|
| Part number and description | One code per part, so receipts and issues add up to one stock figure |
| Category and compatible vehicles | Finds the right part for a vehicle and groups stock value by system |
| Unit and unit cost | Sets how you count the part and values the stock |
| Location or bin | Lets anyone find the part and count the shelf |
| Min stock and reorder quantity | Decide when to order and how much |
| Supplier and lead time | Who to order from and how many days the part takes to arrive |
| Opening stock | The starting point from your last physical count |
How to use the spare parts inventory template
- Set up Settings once. Choose the currency from the list, then enter the date of your opening stock count, the slow-moving threshold and the usage window, then your vehicles, categories, units and suppliers.
- Build the parts master. Give each part one row with its number, description, compatible vehicles, unit cost, bin, min stock, reorder quantity, supplier, lead time and opening stock.
- Log every receipt. Add a Receipt row with the quantity, the unit cost on the invoice and the goods received note number.
- Log every issue to a work order. Add an Issue row with the part, quantity, work order number and vehicle. Leave Unit cost empty to use the master cost.
- Act on the status. Filter Status on Parts master: order the Reorder and Out of stock parts using the suggested quantity, and review Slow-moving parts with the workshop.
- Review cost by vehicle. Set the date range on Parts cost by vehicle and bring the vehicles with the highest parts cost to the monthly maintenance review.
How is current stock calculated with SUMIFS?
In Excel, the issued quantity for the part in row 5 of Parts master is:
The template uses the same pattern for receipts, returns and adjustments. The 180-day threshold and the 90-day usage window are on Settings, and a part that has never been issued counts its days from the stock start date. Stock value uses the unit cost on Parts master, so update it when supplier prices change.
Worked example: brake pads at the reorder point
The store opened the year with 6 sets of front brake pads (P-1002, 180 each, min stock 4, reorder qty 6, lead time 7 days). On 5 October 2026 the workshop issued 2 sets to work order WO-2026-0142 for truck TRK-07.
| Item | Calculation | Result |
|---|---|---|
| Current stock | 6 + 0 − 2 + 0 | 4 sets |
| Stock value | 4 × 180 | 720.00 |
| Days of cover | 4 ÷ (2 ÷ 90) | 180 days |
| Status | 4 ≤ min stock 4 | Reorder |
| Suggested order | larger of 6 and (4 − 4) | 6 sets |
The days of cover are longer than the 7-day lead time, so the min stock rule triggers the alert. As of 12 October 2026, air filters (P-1004) were last issued on 10 March 2026, 216 days earlier, so 5 filters worth 475 show as Slow-moving. For January to December 2026, Parts cost by vehicle shows TRK-07 at 455 (brake pads 360 plus an air filter 95), VAN-02 at 248 and TRK-11 at 76: 779 in total.
Common spare parts inventory mistakes
- Issues without a work order or vehicleParts cost per vehicle is incomplete and nobody can check the job.
- Parts taken without a log entryThe sheet shows stock that is not on the shelf.
- Two numbers for the same partStock splits across rows and one of them always looks empty.
- Min stock set once and forgottenUsage changes with the fleet. Review min stock against days of cover each quarter.
- No physical countCount at least once a year and post each difference as an adjustment with a note.
- Returns not recordedUnused parts go back on the shelf but stay charged to the vehicle.
How does the parts inventory link to work orders?
Every part fitted to a vehicle should leave the store against a work order. Write the work order number on each Issue row, and use the same parts and prices on the job in the maintenance work order template, so stock, job cost and cost per vehicle agree. To plan demand for filters, oil and other service parts, check what is due in the preventive maintenance schedule and keep enough stock to cover the next month's services.
Limits of a spreadsheet parts inventory
The stock figure is only as good as the movements typed in. The sheet does not scan barcodes, reserve parts for open jobs or raise purchase orders, it values stock at one unit cost per part rather than by batch, and several people editing one file at once can overwrite each other. As the number of parts or stores grows, an inventory or maintenance system is easier to keep accurate.