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 code | Site, zone, aisle, rack, level and bin, such as WH1-A-01-R1-L1-B02, or a service van's plate, so anyone can 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, location code from Locations, 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.
- Count the shelves. At least once a year, fill Physical count with one line per part per location, print Blind count for the counters, recount the lines over tolerance and post each approved difference on Transactions as Adjustment + or Adjustment −.
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 with the Physical count and Blind count sheets, and post each approved 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.
How do you run a physical count in the spare parts template?
Give every bin a location code on Locations and pick it on Parts master. For a count, freeze movements at the cut-off, copy the current stock into System qty on Physical count with one line per part per location, and print Blind count, which leaves the system quantities out so counters write what they see. A line is over tolerance when its variance is above both the tolerance % and the value threshold on Settings (5% and 100 in the example); recount those lines, then approve and post each difference on Transactions with the type and quantity the sheet shows.
The example count on 12 October 2026 has 6 lines and 4 match exactly: a line accuracy of 66.7%. The engine oil on the oil store floor (WH1-O-F1) is one pail short, −16.7% and −210, so it was recounted and waits for approval with Adjustment − 1 to post. Two oil filters turned up in van VAN-02 (WH1-V-VAN-02), away from their bin, and are flagged as found in the wrong location. An absolute variance of 286 on a system value of 5,363 gives a value accuracy of 94.7%. For a full count across the workshop store, yard and vans with variances sorted by value and accuracy by zone, category and counter, use the Fleet Workshop Physical Inventory Count Sheet, which uses the same location codes.
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.