From spreadsheet guesswork to a purchasing process you trust.

Part I · Get the basics right · Chapter 01 · 5 min read

Why spreadsheets break, and when they don’t

Walk any distributor’s warehouse and you’ll find the same paradox: pallets of something nobody has ordered since last spring, two aisles from an empty shelf where the best-seller should be. Cash tied up in the wrong items while the right ones stock out. Most owners read this as two problems: buy less of that, more of this. It’s one problem. Nobody has decided, item by item, how much protection against uncertainty each product deserves. The slow item got too much by accident; the fast one too little.

The three ways the spreadsheet fails

The spreadsheet itself is rarely the villain. These three habits that grow up around it are.

• •

  Levels set once, by memory. A min and max typed in during a quiet afternoon in 2023 and never touched since. Demand moved; the levels didn’t. Every reorder quietly repeats a three-year-old guess.

• •

  Forecasting by multiplier. “Last year plus 30%” is a budget, not a forecast. It misses seasonality, trends and the customer who stopped buying in March, and it misses them equally for every single item.

• •

  Numbers that don’t agree. Stock in the ERP, open orders in email, commitments in someone’s head. When “available” means something different in each tab, buyers reorder stock that is already on the water, or trust stock that is already promised.

We watched a buyer at a building-products distributor work a screen where items bounced between 15 and 45 days of supply, week after week. Nothing was wrong with his judgment. The trigger level and the order quantity were the same number, so the math itself guaranteed the sawtooth. Chapter 6 takes that story apart.

When a spreadsheet is the right answer

Honesty first: if you carry a few hundred active SKUs, ship from one location, and demand is reasonably steady, a weekly export and the formulas in this playbook will outperform most software that is set up badly. The expensive failure we see is not “still on Excel.” It is buying a system before defining the process, then running the old guesses on new screens.

The sequence, and the item we’ll carry through it

A purchasing process is six decisions in order: classify your items so attention goes where the money is (chapter 3), forecast demand (chapter 4), size the buffer each class deserves (chapter 5), set the trigger that says when to buy (chapter 6), decide how much (chapter 7), and run it all on a routine instead of a mood (chapter 9).

To keep the math concrete, every worked example in this playbook uses the same item: FS-112, a 2-inch coated deck screw sold by the bag at Harbor Supply, a fictional two-location fastener distributor stitched together from real ones we’ve worked with. FS-112 sells about 10 bags a day and takes 14 days to arrive. By chapter 6 you will know exactly when to reorder it, and how many bags.

Part I · Get the basics right · Chapter 02 · 5 min read

The numbers you need before any formula

Chapter 1 promised a process in six decisions. Before the first of them, an unglamorous stop: the four numbers every later formula consumes. None of this is hard, which is exactly why it gets skipped — and why the expensive failures downstream almost always trace back here, to two systems quietly meaning different things by the same word.

Four numbers, four definitions

Chapter 6 will combine them into the inventory position — available plus on order — the number your reorder point is compared against. Get the four right and that chapter is arithmetic. Get them wrong and no reorder point can save you.

Lead time: measure it, don’t quote it

Lead time is every day between cutting the PO and the stock being sellable on your shelf — supplier handling, transit, receiving, put-away, all of it. The supplier’s “two weeks” is marketing; your own last six receipts are data. Pull the gap between PO dates and receipt dates per supplier and use that, because an optimistic lead time silently shrinks the buffer chapter 5 computes, exactly where it hurt you last time. And lead times are not all alike: a domestic 2–3 day supplier forgives nearly any mistake, while a six-month overseas lead turns the expected-arrival date on every PO into one of the most important fields you maintain.

Units: one item, three names

At one distributor we worked with, the same screw was sold by the bag, received by the box, and bought from the factory by the thousand — so a pallet of 360 bags appeared on the purchase order as “90.” Everyone fluent in the codes managed fine, and every new hire ordered something wrong in month one. Record, for every item, the selling unit, the receiving pack, and the conversion between them, in the system rather than in folklore. The rule from chapter 6 follows from it: triggers live in selling units, orders get rounded to packs.

Demand: the number your stockouts corrupted

Demand is what customers wanted, not what you shipped. The weeks you were out of stock recorded zero or starved sales — precisely the weeks demand outran you — and averaging them in teaches every formula that the item is slower than it is, which shrinks the buffer, which causes the next stockout. The fix is bookkeeping, not modeling: flag stockout weeks in the history, and either exclude them from averages or replace them with a sensible estimate. Lost quotes and walk-aways are demand too, if you have any way to capture them.

The one-afternoon audit

• •

  Pick five items — your top seller, a slow mover, one you buy in packs, one import, one recently stocked out.

• •

  For each, write what your system says: on hand, committed, available, on order. Then walk the floor and count.

• •

  Check every open PO on them: is it real, and is the arrival date one you would bet on?

• •

  Recompute each supplier’s lead time from its last six receipts and compare with what the system assumes.

• •

  Mark the weeks each item was out of stock in the last year. That flag is chapter 4’s first input.

Part I · Get the basics right · Chapter 03 · 5 min read

ABC/XYZ: spend your attention where the money is

You cannot give 3,000 items the same attention, and you shouldn’t try. The whole trick of inventory classification is admitting that out loud and writing it down, so the 600 items that pay the bills get managed carefully and the long tail gets managed cheaply. ABC/XYZ is the standard way to do that, and you can run the first pass in an afternoon.

ABC: rank by annual value

For every item, multiply the units sold in the last 12 months by the unit cost. Sort descending. Walk down the list adding up the cumulative share of total value: the items that make up the first 80% are your A class, the next 15% are B, and the last 5% — usually half your catalog — are C.

XYZ: rate the predictability

Value says how much an item matters. It says nothing about how hard the item is to plan. For that, measure how much demand jumps around: take each item’s monthly sales for the last year and divide the standard deviation by the average. Statisticians call this the coefficient of variation; you can call it the wobble.

• •

  X — wobble below 0.5. Steady demand. Forecasts work; buffers can be lean and scientific.

• •

  Y — wobble between 0.5 and 1. A trend or season is in there. Forecastable with care.

• •

  Z — wobble above 1. Lumpy, feast-or-famine demand. Forecasts will embarrass you; plan with buffers and honesty instead.

The thresholds are starting points, not physics. What matters is the discipline of knowing, before you set a buffer in chapter 5, whether this item’s history is something a formula can lean on.

Nine boxes, nine strategies

At Harbor Supply, FS-112 sells about 10 bags a day with a wobble around 0.4 and sits comfortably in the A band: an AX item. That is why the next chapters can be precise about it — AX is exactly where the formulas earn their keep.

Part II · Decide when and how much · Chapter 04 · 5 min read

Forecasting without a data scientist

You do not need a data scientist to forecast 3,000 SKUs. You need three ideas — average, season, trend — applied honestly, and the discipline to measure how wrong you were. The flat multiplier fails not because it is simple but because it applies one guess to every item; the methods here are nearly as simple and they listen to each item’s own history.

Start with the moving average

Average the last three months of (stockout-corrected) demand and call that next month’s forecast. FS-112 sold 290, 310 and 300 bags: forecast 300. This one line already beats the multiplier, because it follows each item up and down instead of inflating everything by decree. Its weakness is lag — it notices a season or a trend three months late — which is what the next two ideas repair.

Correct for the season

For each calendar month, divide its average demand across your history by the overall monthly average: that ratio is the month’s seasonal index. If June historically runs 1.3× and January 0.7×, then a deseasonalized average of 300 forecasts 390 for June and 210 for January. Two or three years of history make the indices trustworthy; with less, borrow the index from the item’s category rather than inventing one per SKU.

Respect the trend, after you doubt it

If deseasonalized demand has climbed steadily for six months, carry the slope forward — but make trend earn its keep, because chasing noise as “trend” whipsaws your buying. A practical test: a trend you cannot explain in one sentence (new customer, new channel, competitor gone) is probably noise. When in doubt, forecast flat and let safety stock absorb the surprise; that is what it is for.

Score it or it is astrology

Every month, for every item that matters, record two numbers. Accuracy: the average size of your miss as a percent of actual — a steady A item should land within 10–20%, and lumpy items will be worse, which is why chapter 3 told you not to lean on their forecasts. Bias: whether the misses lean one way — persistently forecasting 10% high quietly builds a warehouse of excess even while “accuracy” looks tolerable. Accuracy sets your safety stock through chapter 5’s σ; bias tells you a method is broken. And when two methods compete, backtest: run both over last year as if live, and the one that would have missed least earns next month. That contest, rerun periodically, is most of what the forecasting software you are quoted actually does.

What not to forecast

Z-class items — the lumpy, feast-or-famine tail from chapter 3 — will embarrass any formula. Their average is fiction and their σ is worse. Plan the expensive ones by hand, order by order, and give the cheap ones a simple min/max with accepted risk. Knowing where forecasting stops is as valuable as the forecasting.

Part II · Decide when and how much · Chapter 05 · 5 min read

Safety stock and service level: the dial between cash and stockouts

Chapter 6 will tell you to reorder when stock falls to the demand you expect during the resupply wait, plus a buffer. This chapter is about that buffer. Size it by feel and you get chapter 1’s warehouse: too much cushion under the slow items, too little under the fast ones. Size it with the formula below and the cushion goes exactly where the uncertainty is.

First, pick the risk you’ll accept

Service level is the probability that a replenishment cycle ends without a stockout. At 95%, you are accepting that roughly one cycle in twenty comes up short. 100% does not exist — it would require infinite stock — so the real question is which items deserve 98% and which can live with 90%. That is a cash allocation question, which is why chapter 3 told you to set it per class, in a meeting, about once a year.

The formula

Safety stock = z × σ × √(lead time in days)

• •

  z — how far into the demand distribution you want protection. It comes straight from your service level target: 1.28 for 90%, 1.65 for 95%, 2.05 for 98%, 2.33 for 99%.

• •

  σ — the standard deviation of daily demand: how much a normal day strays from the average. (Better still, the standard deviation of your forecast error — chapter 4.)

• •

  √lead time — uncertainty compounds over the days you wait, but with the square root, not linearly. Four times the lead time needs twice the buffer, not four times.

Harbor Supply’s FS-112 sells 10 bags a day on average, with a σ of about 4.9 bags. Lead time is 14 days, and as an A item it gets 95% service. Safety stock = 1.65 × 4.9 × √14 ≈ 30 bags: three days of average demand held purely against surprise.

When the formula lies to you

• •

  Lumpy demand. The formula assumes demand wobbles around an average. A Z-class item that sells nothing for a month and then 40 units does not — the σ it produces is fiction. Plan those by hand.

• •

  Stockout-censored history. Weeks you were out of stock record zero sales, which shrinks σ and the average exactly where they failed you. Flag those weeks; don’t average them in.

• •

  Variable lead times. If the supplier’s 14 days is sometimes 10 and sometimes 25, the lead-time uncertainty needs its own term. As a first defense, use the lead time you’d bet on, not the one on the quote.

The stockouts you should keep

Protection is a purchase, and sometimes the price is wrong. An item that sells 18 bags a year once took a single order for 36. Buffering against a repeat would have meant holding roughly two years of supply, forever, against a customer who might never do it again. The right answer was to accept the risk: the item restocked in two or three days from a domestic supplier, so the occasional miss cost an apology, not a season. Had that same item shipped from overseas on a six-month lead, the same math would point the other way. Risk is cheap when recovery is fast.

Part II · Decide when and how much · Chapter 06 · 5 min read

Reorder points and min/max that don’t re-trigger

Everything so far was preparation. This is the chapter where a number appears in a cell and tells you to act. Two numbers, in fact — and most of the trouble we see in the field comes from collapsing them into one.

The trigger: when to order

Reorder point = average daily demand × lead time in days + safety stock

The first term is the demand you expect while the resupply is in transit. The second is chapter 5’s buffer against that expectation being wrong. For FS-112: 10 bags a day × 14 days + 30 bags of safety stock = 170. When the item’s position touches 170, order. The formula is standard; Oracle NetSuite’s documentation states the same relationship if you want a second source.

Oracle NetSuite: reorder point calculation

Position, not shelf stock

The number you compare against 170 is the inventory position: on hand, minus what’s committed to customers, plus what’s already on order. Miss the committed term and stock that is promised looks available. Miss the on-order term and every review re-orders what is already on a truck — the most common duplicate-buying bug in spreadsheet purchasing. And check arrival dates: a PO landing three weeks after the stockout defends nothing.

FS-112 today: 150 on hand − 10 committed + 0 on order = position 140. That is under 170, so it’s time to buy.

The target: order up to max

How much? Set a second level, the max, and order the difference between it and the position. Make the gap between min and max roughly one order cycle of demand — how often you want to be cutting POs for this item. Harbor Supply likes a monthly cycle for FS-112: max = 170 + (10 × 30) = 470. Today’s order: 470 − 140 = 330 bags, rounded up to the supplier’s box of 12 → 336 bags, 28 boxes.

Run your own item through the same math here — it is the playbook’s only rule that fits in three boxes.

Lead time rules the math

The formula doesn’t care whether lead time is 3 days or 180 — but you should, because it changes everything downstream. One distributor we worked with buys fasteners domestically on a 2–3 day lead: reorder points sit at days of supply, misses recover in a week, and risk is cheap. The same company imports coil on a six-month lead: reorder points sit near seven months of usage, the expected-arrival date on every PO becomes a critical field, and a missed order is a lost season. Same formula, different world. Chapter 8 covers the scheduled-ordering policies that long lead times usually deserve.

Keep the inputs honest

• •

  Units: if you sell bags but buy boxes, round the order, not the trigger. 336 bags is 28 boxes; 170 stays 170.

• •

  Lead time: measure to sellable-on-the-shelf, not to “supplier shipped.” Receiving and put-away days are real days.

• •

  Recompute: demand and lead times drift. Recalculate levels on a schedule — weekly if automated, quarterly by hand — and whenever a supplier or a big customer changes.

• •

  Demand: flag stockout weeks before averaging; they record zeros exactly where demand was highest.

Part II · Decide when and how much · Chapter 07 · 5 min read

How much to order: EOQ, minimums and free freight

Chapter 6 told you when to order and left “order up to max” holding the how-much question. The max was set by your order cycle — and this chapter is about choosing that cycle deliberately, because it is a trade: order often and you drown in POs, receiving and freight charges; order rarely and your cash sleeps on shelves. There is a formula for the balance point, and — more usefully — a reason you are allowed to be casual about it.

The formula, and our item one more time

EOQ = √( 2 × annual demand × cost per order ÷ holding cost per unit per year )

Cost per order is what one more PO costs you end to end — the admin, the receiving labor, the freight that doesn’t scale with size; $30–60 is a common honest range. Holding cost is what one unit sitting a full year costs in tied-up cash, space and risk; a common estimate is 15–30% of unit cost. FS-112: 3,650 bags a year, $45 per order, $2 per bag per year to hold → EOQ = √(2 × 3,650 × 45 ÷ 2) ≈ 405 bags, an order roughly every 40 days. Harbor Supply had been running a monthly cycle (max − min = 300 bags, chapter 6); the formula says that was already close.

Now bend it around reality

• •

  Case packs and pallets: 405 bags is 33.75 boxes of 12 — order 34 boxes, and if 36 boxes make clean pallet layers, order 36. The curve forgives you.

• •

  Supplier minimums: when the MOQ sits above your EOQ, you are being paid in price break and charged in holding cost — do the math per item, and remember chapter 3: a big MOQ can make a sleepy item impersonate an A.

• •

  Free-freight thresholds: if $3,000 ships free, topping an order up with items you will certainly sell is usually cheaper than paying freight — top up with your fastest movers, not with shelf-warmers.

• •

  Shared trucks: at one distributor we worked with, fastener orders were timed to ride along with the weekly heavy-goods load, trading a little extra buffer for a lot of freight. If orders wait for a consolidated shipment, chapter 5’s buffers must cover the wait.

• •

  Shelf life and fashion: perishables and seasonal goods cap the order no matter what the formula prefers. An expired bargain is just a loss with better paperwork.

Long lead times change the arithmetic

On a 2–3 day domestic item, order-quantity mistakes self-correct within the week. On a six-month import, the order quantity IS the planning horizon: a container is both your EOQ and your season, booked against a forecast (chapter 4) rather than a reorder trigger. That is chapter 8’s territory — scheduled buys — but the cost logic here still decides how full the container should be.

Part III · Run it every week · Chapter 09 · 5 min read

The weekly purchasing routine

Everything so far produced numbers: classes, forecasts, buffers, triggers, quantities. None of it survives contact with a busy quarter unless someone runs it on a rhythm. The good news: run from exceptions, the whole discipline costs a few hours a week — less than most buyers currently spend re-deriving yesterday’s panic from a 3,000-row report.

Daily: the fifteen-minute sweep

• •

  Items projected to run out before their next arrival — position, demand rate and PO dates, chapter 6’s math run as a flag.

• •

  New orders that blew past a forecast (a spike worth a phone call, or an error worth a fix).

• •

  POs whose expected date slipped past the projected stockout date — critical on long-lead items.

• •

  Feeds that didn’t sync: a channel or accounting connection down for a day quietly poisons every number downstream.

Everything else waits. The sweep exists to catch fires, not to shop. One buyer we worked with reviewed his items each morning filtered to just his warehouses and just the rule-breakers — the screen stopped crying wolf (chapter 6’s sawtooth had been flagging half his catalog daily) and the sweep fit inside a coffee.

Weekly: the buy-list review

Once a week — or on each vendor’s order day — walk the suggested buys grouped by supplier. For each line you can see the reasoning: forecast, position, buffer, pack rounding. Approve, adjust, or strike; consolidate lines toward freight thresholds and shared trucks (chapter 7); cut the POs. If parameters recompute automatically, skim what changed and why. If they are maintained by hand, this is also the moment to recompute the handful chapter 3 ranks highest — never “when someone gets to it.”

Monthly and yearly: the slow loops

Earning trust in the numbers

When this routine replaces a manual process — or software arrives to run parts of it — run old and new in parallel for a cycle or two. The buyer keeps buying their way; the system suggests alongside; the weekly review compares. Trust built this way is specific (“it caught the spike I missed; it was slow on the new customer”) instead of ideological, and the handover happens when the comparison, not the calendar, says so. Keep the exceptions list short enough to finish: a routine that takes four hours gets skipped in busy weeks, and busy weeks are the ones that needed it.

Part III · Run it every week · Chapter 11 · 5 min read

How to avoid stockouts and backorders you created yourself

By this point in the playbook your buffers are sized and your triggers are set, and you will still disappoint customers — because the stockouts that hurt most are often ones demand had nothing to do with. You promised the same units twice: two channels, two warehouses, or a spreadsheet and an ERP that each thought the stock was theirs to sell. The result lands on customers as a backorder email and on your team as a scramble, and no amount of safety stock fixes it, because the stock was never there.

Ten units, two channels, twelve sold

The −2 means you promised two units you do not have, not that the shelf holds negative stock. Decide now — as policy, not mid-crisis — what happens to that second order: reject it, reduce it, backorder it with the customer’s explicit agreement, or fill it from stock that is genuinely free somewhere else. A small per-channel buffer lowers the odds but cannot rule it out.

Agree what each stock number means

Write down on hand, committed, on hold and incoming as four separate numbers, and check what each connected system means by each. Shopify, for example, splits on-hand stock into available, committed and unavailable. Copy Shopify’s “available” into another system’s on-hand field and subtract open orders again, and you have counted the same promises twice — now you’re rejecting orders you could have filled, which is the same disease in the other direction.

Shopify’s definitions of inventory states

Incoming purchase orders deserve special suspicion: they belong in your reorder math (chapter 6) but not in what channels sell, unless you deliberately take preorders with a promised date you have verified against the PO’s actual arrival.

A second warehouse is not spare stock

Units in another building may be reserved there, on hold for inspection, or too far away to meet the delivery date you just promised. Count transfer time and shipping eligibility before treating all locations as one sellable pool — and make sure stock in transit between warehouses never shows as available at both ends. Moving inventory is usually cheaper than buying it, but only stock above a location’s own max is really free to move.

The test that settles it

• •

  Name the one system that creates and releases reservations. If the answer has an “and” in it, you found the bug.

• •

  Map each channel’s location to physical stock, so the same units never appear as two pools.

• •

  Place two orders at the same moment and time the gap between order, reservation and updated stock on each channel.

• •

  Walk the ugly paths: quantity changes, cancellations, partial shipments, returns, damaged stock.

• •

  Make sure a repeated or late update cannot overwrite a newer one.

• •

  Decide what a channel shows when the connection is down: stop publishing stock, or publish a conservative figure.

Part IV · Level up · Chapter 13 · 5 min read

Using AI on your inventory data

Every chapter so far scales badly in one direction: you. The formulas are simple, but applying them across 3,000 items, every week, is exactly the work that gets skipped the week a container is late. This is the work AI assistants are genuinely good at — not deciding your strategy, but running your stated rules across the whole catalog and surfacing what breaks them.

Getting your data in front of it

Three routes, in increasing order of convenience. Paste or upload an export — a CSV of items, sales history, on hand, on order and lead times is enough for everything below. Point it at a spreadsheet it can read. Or, if your inventory system supports MCP (the open standard Claude, ChatGPT and Gemini all use to connect to business systems), connect it directly and the assistant queries live numbers instead of last Tuesday’s export. Whichever route: start read-only.

Five prompts that earn their keep

Here are my items with 24 months of monthly sales, unit cost and current on hand. Classify each item ABC by annual sales value (A = first 80% of cumulative value, B = next 15%, C = last 5%) and XYZ by the coefficient of variation of monthly demand (X < 0.5, Y 0.5–1, Z > 1). Return a table with class, annual value and wobble, then list the ten AX items with the fewest days of cover. Show how you computed one item end to end.

Using on hand minus committed plus on order as the position, daily demand from the last 90 days, and each item’s lead time: which items will run out before their next delivery arrives? Rank by annual value, show the math for the top ten, and note where a recorded stockout makes the demand history suspect.

Item FS-112 has a reorder point of 170, set as 10/day × 14 days lead time + 30 safety stock at 95% service. Sales over the last 8 weeks are attached. Has average demand or its variability moved enough to change the reorder point or the safety stock? Recalculate both, show each step, and tell me what you’d set and why.

List items where current stock exceeds 180 days of supply at the recent demand rate and the trend over the last two quarters is flat or falling. Rank by dollars tied up at unit cost, and separate items with an open PO — those are about to get worse.

Compare this week’s export to last week’s. Flag only exceptions: items that crossed below their reorder point, stockouts, items whose 4-week demand is more than 50% above or below their 26-week average, and open POs now expected to arrive after their item’s projected stockout date. One line each, with the number that triggered the flag.

Make it show its work

Treat every answer like a junior analyst’s first draft: plausible, fast, and unverified. Ask for the calculation per item, not just the conclusion, and recompute three rows yourself before acting on any list. Give definitions explicitly — tell it “available means on hand minus committed” rather than hoping it guesses your ERP’s vocabulary. And remember what it cannot see: the trade show next month, the customer your rep just lost, the supplier who always ships a week late. Context lives with you; say it in the prompt.