Procurement Playbook: Ready-to-use templates, worked examples and controls for SKU rationalization, vendor scorecards, reorder rules and month-end credit reconciliation

Most electrical shops don't have a procurement problem. They have a procurement visibility problem. The buying happens — parts show up, jobs get done — but nobody can tell you which 40 SKUs eat 80% of your spend, which vendor shorts you on credits every month, or why the same breaker lives in three different bins under three slightly different part numbers.

This is the hands-on companion to the strategy side of buying. If you've already read the supplier & procurement playbook on turning buying into a margin lever, this is the "okay, now actually build it in a spreadsheet" piece. Everything below is low-tech. No ERP required. If you can open Google Sheets or Excel, you can run all of it.

The exact templates are below, with the math worked out using real-ish numbers, plus the governance controls that keep the whole thing from falling apart after week three — because that's the part everyone skips.

Start with the ugly truth: your SKU list is a mess

Before you build scorecards or reorder rules, clean the catalog. Almost every shop under 20 techs is carrying 2–4x more active SKUs than they actually need, and the duplicates quietly wreck your reorder logic.

Here's how it happens. A tech runs out of 12-2 Romex, the counter guy types it in as "12-2 NM-B 250ft," next month someone enters "Romex 12/2 w/ground," and the supplier's invoice calls it "SIMpull 12-2." Now you've got three line items for one product, each with its own partial purchase history. Usage data is split, so no single line ever trips a reorder point correctly.

The SKU rationalization template

The decision rule:

1. 1
   Keep anything in the top 80% of annual spend OR ordered 6+ times in 12 months.
2. 2
   Merge anything that's a clear duplicate of a kept SKU (consolidate the history into one internal SKU).
3. 3
   Drop anything with fewer than 3 orders AND under ~$150 annual spend AND no job-critical reason to stock it.

Run this once and you'll usually cut the active list by 30–50%. A shop with around 650 "active" SKUs on paper — pretty typical — can collapse to roughly 310 real ones after a couple afternoons of cleanup. The point isn't fewer products for its own sake. Clean usage data is the foundation for every other template in this playbook. Reorder points built on fragmented SKUs are garbage in, garbage out.

One mistake worth flagging: don't drop a low-volume SKU that's low-volume because it's a specialty part you can't get fast in an emergency. Add a "job-critical" flag column and protect those regardless of order frequency.

Reorder-point rules that actually match how you buy

Once your SKUs are clean, reorder points are just arithmetic. Most shops either eyeball it — and run dry mid-job — or over-order and tie up cash in a stockroom full of dead inventory. You don't need fancy forecasting. You need two numbers per SKU.

The worked example

Reorder Point = (Average daily usage × Lead time in days) + Safety stock

1. 1
   Average daily usage

   96 used over ~250 working days = 0.38/day

2. 2
   Supplier lead time

   3 days (local counter, reliable)

3. 3
   Safety stock

   cover one bad week → 0.38 × 5 = ~2 units

Reorder Point = (0.38 × 3) + 2 = 1.14 + 2 = ~3 units

When GFCI stock hits 3, you reorder. How many? Use an order quantity equal to roughly 3–4 weeks of usage to avoid ordering constantly: Order Qty = 0.38 × 20 working days = ~8 units

Now compare that to a slow-moving but job-critical part — say a specific 200A meter-main combo you install maybe 10 times a year but with a 9-day lead time:

1. 1
   Daily usage

   10 / 250 = 0.04/day

2. 2
   Lead time

   9 days

3. 3
   Safety stock

   1 (a stockout here stalls a whole install)

Reorder Point = (0.04 × 9) + 1 = ~1.4 → round up to 2

Reorder rules template columns

SKU | Avg Daily Usage | Lead Time (days) | Safety Stock | Reorder Point | Order Qty | Primary Vendor | Backup Vendor | Job-Critical? (Y/N)

The thing most shops miss: lead time variability matters more than average usage for parts that stall jobs. A part you use every day with a reliable 1-day lead time barely needs safety stock. A part you rarely use but that holds up a $9k install deserves a buffer even when the math says you "shouldn't" carry it. Weight your safety stock toward consequence of stockout, not just volume.

Van-par management without the clipboard chaos

Van stock is where theory dies. You set a par list, techs pull parts without logging them, and three weeks later half the trucks are missing the same five items. The fix is a par sheet per van plus a dead-simple replenishment trigger.

Van-par sheet (per truck)

The replenishment logic: anything below half-par at Friday check gets topped up before Monday. That's the whole rule. You don't need to track every individual pull in real time — you reconcile to par on a fixed cadence.

1. 1
   Each tech photographs their van-par sheet every Friday afternoon and sends it to the shop (or drops it in a shared folder).
2. 2
   Whoever runs the counter aggregates shortages into Monday's pull list.
3. 3
   Parts are staged by van, not in one pile — labeled bins or bags per truck.
4. 4
   Tech confirms restock when loading and initials the sheet.

Van stock shrinkage is almost never theft. It's unlogged consumption plus no reconciliation cadence. Shops that go from "whenever someone notices" to a fixed Friday check typically see mystery shortages drop within a month or two, without anyone changing behavior other than counting on schedule.

Vendor scorecards: stop grading suppliers on vibes

Everyone has a "good" vendor and a "pain in the neck" vendor based on feel. Feel is expensive. A vendor who's 4% cheaper but short-ships 1 in 8 orders and takes two weeks to credit returns is costing you more than the sticker price difference.

Math on Vendor A: (9×.25)+(6×.25)+(7×.20)+(4×.15)+(8×.15) = 2.25+1.50+1.40+0.60+1.20 = 6.85

Vendor A looks cheaper and has a friendly rep, but the slow credit turnaround and middling delivery drag the real score below Vendor B. That 6.85 is what you bring to your quarterly vendor conversation — not "hey, you guys have been slow lately," but "your credit turnaround scored a 4 three months running, here's the data."

How to score each metric objectively:

1. 1
   On-time delivery

   % of orders delivered by promised date. 95%+ = 9–10, 85–94% = 6–8, below 85% = under 6.

2. 2
   Order accuracy

   % of orders with no shorts, wrong items, or damage.

3. 3
   Credit turnaround

   average days from return pickup to credit posted. Under 5 days = 9–10, 6–14 = 5–8, 15+ = under 5.

Keep a running tab per vendor and update it from the same data you're already pulling during month-end reconciliation — which comes next, and is the piece that pays for itself fastest.

Month-end credit reconciliation: the money hiding in plain sight

This is where most shops will see the biggest dollar return. Returns, cores, warranty swaps, and short-shipped items generate credits that are supposed to come back to you. A surprising amount never does — not because vendors are crooks, but because nobody's tracking expected credits against posted credits.

The reconciliation workflow

The failure pattern is predictable. A tech returns $420 of unused material from a cancelled job. The counter issues an RMA. Three weeks later a credit might appear on a statement, applied against some other invoice, and nobody checks whether the amount matches. Multiply that across a year and you're looking at real money quietly evaporating.

Below is a simple workflow diagram of the month-end reconciliation process.

Build an Expected Credits Register:

At month-end, every row that isn't "OK" gets action. The –$32 on RMA-1182 is either a restocking fee they didn't disclose or a pricing discrepancy — that's a dispute. The unposted $14.40 is a chase.

The worked payoff:

The worked payoff: a shop doing somewhere around $30k–$40k monthly in material spend typically generates $800–$1,500/month in returns and short-ship credits. If even 10–15% of those slip through uncaptured — a conservative estimate for shops with no register — that's roughly $1,200–$2,500 a year walking out the door. The register takes about 20 minutes a month once it's running.

Monthly close process (do it in this order)

1. 1
   Pull all vendor statements for the month.
2. 2
   Match every invoice line to a PO or job (flag anything unmatched).
3. 3
   Open the Expected Credits Register — mark each row Posted / Not Posted.
4. 4
   Calculate variance on posted credits (expected minus actual).
5. 5
   Anything with variance or no posting → move to the dispute/chase queue.
6. 6
   Update each vendor's scorecard numbers from this same data.
7. 7
   Carry unresolved items forward with an aging count (days outstanding).

Reconciliation and vendor scoring are the same dataset viewed two ways. Run the close once and both the credit recovery and the scorecard fall out of it. Shops that treat them as separate chores end up doing neither consistently.

Dispute scripts that get credits posted without burning the relationship

When you find a variance, the chase is where people lose nerve or lose politeness — both of which cost you. Have scripts ready so it's a two-minute, unemotional task.

Email script — missing credit:

> Subject: Credit not posted — RMA-1201, Invoice #[xxxx] > > Hi [Rep name], following up on RMA-1201 (defective 15A GFCI, returned 09/14). I don't see the $14.40 credit on the September statement. Can you confirm it's processing and give me an expected post date? Happy to resend the RMA paperwork if helpful. Thanks.

Phone script — short-paid credit (the –$32 case):

> "Hey [Rep], quick one on RMA-1182. We returned $420 of material but the credit posted at $388. There's a $32 gap — is that a restocking fee? If so I didn't see it disclosed on the return. Can you either reverse it or send me the fee policy so I can account for it going forward?"

Two rules that make scripts work: name the reference number first, state the dollar gap second, ask for one specific action third. Vague complaints ("your credits are always slow") get vague answers. Specific references with specific amounts get resolved because the rep can actually look them up.

When to escalate: if an item ages past 30 days with two documented chases, it goes to the vendor's branch manager with the scorecard attached. At that point you're not complaining — you're presenting a pattern.

KPI tracking: the five numbers that tell you if any of this is working

Don't build a 30-metric dashboard. Track five procurement KPIs monthly. If these move the right direction, the system works.

1. 1
   SKU count (active)

   should drop then stabilize after rationalization.

2. 2
   Stockout events / month

   jobs delayed or extra trips made because a part wasn't on the shelf or van. Target: trending toward zero on job-critical parts.

3. 3
   Credit capture rate

   posted credits ÷ expected credits. Target 95%+.

4. 4
   Credit days outstanding

   average age of unresolved credits. Target under 15.

5. 5
   Material cost as % of revenue

   your north-star margin signal. Watch the trend, not any single month.

A realistic starting picture for a shop that's never tracked any of this: credit capture around 80%, credit days outstanding north of 30, and a general sense that "parts cost too much." After a quarter of running the register and scorecards, credit capture in the mid-90s and days outstanding under 15 is a normal, achievable outcome — not a miracle, just the effect of actually counting.

The 30/60/90 rollout

Don't try to build everything at once. That's how these systems die in week three.

Days 1–30 — Clean the foundation:

1. 1
   Run SKU rationalization (keep/merge/drop).
2. 2
   Set up the Expected Credits Register and start logging every return/short-ship immediately.
3. 3
   Pick your dispute scripts and put them somewhere the counter person can grab them.

Days 31–60 — Build the controls:

1. 1
   Calculate reorder points for your top ~50 SKUs by spend.
2. 2
   Set van-par sheets per truck and start the Friday-check cadence.
3. 3
   Run your first real month-end close using the numbered process.

Days 61–90 — Measure and tighten:

1. 1
   Build vendor scorecards from two months of reconciliation data.
2. 2
   Stand up the five-KPI tracker.
3. 3
   Hold your first data-backed vendor review with your worst-scoring supplier.

Governance: who owns what (so it doesn't rot)

The biggest reason these systems fail isn't the templates — it's fuzzy ownership. Assign it explicitly:

1. 1
   One person owns the credit register and month-end close. Not "the office." A name.
2. 2
   Reorder points get reviewed quarterly, because usage drifts and lead times change.
3. 3
   Van-par checks happen every Friday, same time, no exceptions — the cadence is the control.
4. 4
   SKU list changes require a second sign-off so duplicates don't creep back in.

The biggest reason these systems fail isn't the templates — it's fuzzy ownership. Assign it explicitly:

Who should NOT bother with the full system

If you're a one-truck shop buying straight off the counter with almost no returns, the full reconciliation register is overkill. Run the SKU cleanup and a simple reorder list and move on. The credit register starts earning its keep once you're doing enough volume that returns and short-ships are a weekly reality — usually somewhere around 4+ trucks or roughly $25k+/month in material. Below that, the time cost outweighs the recovery.

And if your real problem is that nobody logs anything — not SKUs, not van pulls, not returns — fix the logging habit first. The fanciest template in the world does nothing without the discipline of writing things down on a fixed schedule.

Everything here runs in a spreadsheet, and honestly it should start there — you learn where your actual leaks are before you'd ever justify software. The templates aren't the hard part. The cadence is.

A credit register checked once a month, a van-par sheet counted every Friday, a scorecard updated from data you're already pulling — that rhythm is what turns procurement from a cost you can't see into a lever you can actually pull.