4 min read

How to Set Inventory Targets by SKU (Not One Number for Everything)

One inventory target for every product guarantees you hold too much of some and run out of others. Here is how to set targets by SKU in Excel.
Planner reviewing SKU-level inventory target ranges instead of one blanket stock target

Ask most businesses what their inventory target is and you will get one number. "Four weeks of cover." "Two weeks for everything." It sounds disciplined. It is usually the reason they are out of stock and overstocked at the same time.

A single target treats a fast, steady seller you make every week the same as a slow, erratic one you make twice a year. The first ends up with far more stock than it needs. The second runs out. Setting targets by SKU fixes that, and it doesn't need software. It needs three numbers per item and an afternoon in Excel.

What an inventory target is actually made of

Every sensible inventory target has two parts:

  • Cycle stock: the stock you hold because you make or buy in batches. If you produce two weeks of demand at a time, your stock rises by two weeks after each run and falls to nothing before the next. On average you hold about half a batch.
  • Safety stock: the buffer for being wrong. Demand comes in higher than forecast, a supplier is late, a run gets cut short. Safety stock covers that uncertainty during the time it takes to respond.

Some products need a third part, pre-build, for a seasonal peak or a promotion that exceeds capacity. Plan it separately so it doesn't quietly become the permanent target.

So the target is not one number. It is a range: the floor is your safety stock, the ceiling is safety stock plus one full batch, and the average sits about halfway between.

Step 1: Segment your SKUs

Before calculating anything, split the range into groups that deserve different treatment. The most practical method is an ABC/XYZ matrix.

ABC by volume or value. Rank SKUs by annual sales and work down the cumulative total. The items making up the first 80% are A, the next 15% are B, the last 5% are C. You will usually find A is a small share of your SKU count.

XYZ by variability. For each SKU, calculate the coefficient of variation: the standard deviation of weekly demand divided by the average weekly demand. Under 0.5 is X (stable), 0.5 to 1.0 is Y (variable), over 1.0 is Z (erratic).

That gives nine groups. An AX product, high volume and steady, can run lean because it is easy to predict. A CZ product, low volume and erratic, is hard to forecast and probably not worth forecasting carefully, so it gets a simple rule and a generous buffer, or a conversation about whether it should be in the range at all.

Step 2: Choose a service level for each group

Service level is the share of order cycles you want to get through without running out. Higher service needs disproportionately more safety stock, so don't set 99% everywhere.

A common starting point:

  • A items: 98%. These are the products your customers notice.
  • B items: 95%.
  • C items: 90%, unless a C item is critical to a key customer.

Each service level converts to a safety factor, often written as z: roughly 1.28 for 90%, 1.65 for 95% and 2.05 for 98%.

Step 3: Calculate safety stock and cycle stock

For each SKU you need four inputs: average weekly demand, the standard deviation of weekly demand, lead time in weeks (how long it takes to respond with new stock), and the batch size or production frequency.

Safety stock = z × standard deviation of weekly demand × √lead time

Average cycle stock = batch size ÷ 2

A worked example

Take a yoghurt SKU that sells an average of 1,000 cases a week, with a standard deviation of 300 cases. It is made every two weeks in batches of 2,000 cases, and it takes two weeks to respond with a new run. It is an A item, so we want 98% service: z = 2.05.

  • Safety stock = 2.05 × 300 × √2 ≈ 870 cases, or about 0.9 weeks of cover
  • Average cycle stock = 2,000 ÷ 2 = 1,000 cases, or 1 week of cover
  • Target range: 0.9 weeks (floor) to 2.9 weeks (ceiling), averaging about 1.9 weeks

Now compare that with a blanket "four weeks of cover" rule. This SKU would be carrying more than double the stock it needs, every week, for no improvement in service. Multiply that across your A items and you have found real cash.

Express the final targets in weeks of cover so they stay meaningful as demand changes.

Step 4: Build it in Excel

You can set this up in one sheet:

  1. Paste 52 weeks of sales history, one row per SKU.
  2. Add columns for average (AVERAGE), standard deviation (STDEV.S) and coefficient of variation (standard deviation ÷ average).
  3. Add the ABC class using a cumulative percentage of annual volume, and the XYZ class using IFS on the coefficient of variation.
  4. Look up the z value for each SKU's class with XLOOKUP from a small service level table.
  5. Add lead time and batch size, then calculate safety stock, cycle stock, and the floor, ceiling and average target in weeks.

If you don't have clean data to hand, the Nerd Foods dataset has 104 weeks of demand you can build this on.

Step 5: Review it, don't set and forget

Targets drift out of date as demand, lead times and batch sizes change. Recalculate them quarterly, and immediately for any SKU with a big change: a new customer, a lost listing, a new supplier. Then check them against reality. If an item keeps breaching its floor, the safety stock or the forecast is wrong. If it never gets near its floor, you are holding too much.

Common mistakes

  • Using raw demand when you have forecast error. If you can, base the variability on forecast error rather than raw demand. If you track forecast accuracy, you already have it.
  • Forgetting supply variability. If your supplier's lead time swings, demand variability alone will understate the buffer you need.
  • Setting targets nobody plans to. A target only matters if the supply plan aims at it. Build the range into your planning sheet so every week outside it is flagged.
  • Treating the result as precise. The formula gives a sensible starting point, not a guarantee. Adjust with judgement, and then measure.

Done properly, SKU-level targets are one of the few planning changes that improve service and release cash at the same time.

Want a second set of eyes on your inventory targets? A Planning Health Check from Forward Cover looks at your forecast, stock and service data and gives you a short list of what to fix first. Get in touch and we can talk through what you are seeing.