The Demand Planning Dilemma – Setting a Service Level

It’s tough to make predictions, especially about the future

The future ain’t what it used to be

Yogi Berra

The dilemma of demand planning: to use a forecast or history.  Analysts tell us sternly “Past performance is not an indication of the future”.  We cannot expect history to repeat itself, at least not when demand planning is concerned.  Meanwhile, every day we are reminded of  inaccuracy and bias in the forecast.

Neither forecast nor history are dependable sources of demand information.  However, they are usually all we have, so here is a way that to get some clues from the order history and use it to determine a service level.

Where To Use a Service Level?

The most obvious use of a service level is to size inventory.  In setting stock sizing policy, it is common to talk in terms of days.  We might be using Min-Max levels or setting a re-order point for a group of items.  Consignment and vendor-managed inventory agreements will fix the “days-of-supply”.  Merchandisers live or die by their days of cover.

You hear many people talking about “days of stock/inventory/cover/supply”.  Fewer talk about exactly what a day means in terms of volume.  After all, you can’t write a purchase order for a “week” of parts.  That week needs to be converted into units.

The most common answer is the forecast.  When we agree to a minimum stock level of two weeks, we are covering two weeks of the forecast.  Hence the terms “Forward Days of Cover” or “Days of Supply”.  Trouble with using the forecast is it is either too nervous or too aggregated.

If the forecast is weekly, then the target stock level is moving.  Hard enough to hit a target in the distance, a moving target is all the more challenging.  If the forecast is monthly, then it doesn’t say much about the rate of consumption across a smaller period inside that month.  Anyone who wants to average it out will be bitten by an end-of-month hockey stick or other demand spikes inside that month.

And let’s not forget that  inaccuracy and bias.

Variation From the History, Volume From the Forecast

Forecasts are much better at telling us about volume than variation.  We hope to get some deterministic pattern from the forecast.  Market trends, product cycles, promotion effects, pricing changes.

Demand variation has a random component.  We  can smooth out the lumpiness that comes with discrete orders by using a rolling average.  There will be a level of variation that is random and this will typically lie on top of the deterministic trends that we might detect in the forecast.  And here is the crucial point:  The demand variation is much more predictable from the history than the demand volume.

Variation can be a simple standard deviation, STDEV in Excel.  Divide it by the MEAN and it provides a useful co-efficient of variability.

Charting variability against volume with a scatter plot provides a great overview of all items and how they might be segmented into different classes and given a stocking or order policy.

STDEV can be calculated right alongside the order history table.  Here is a method to perform these Excel calculations in seconds with 65K+ records.

Rolling Averages and Service Levels

A rolling average across an order history can smooth out some of the lumpiness that is caused by discrete orders.  Given the transaction cost of placing and filling an order, it makes sense for both buyer and supplier to operate with a minimum order quantity.  This means that demand and supply comes in chunks and the rolling average filters out this effect.

The rolling average is calculated against an actual order history with each record representing an order line.  A aggregated total of demand over weeks or months will not give us the level of detail we seek.  For each order record, we want to know the average demand over the last n days.  This can be a single period across all parts, or be equivalent to the lead-time for each item.  This article shows how to calculate rolling averages and demand analytics quickly in Excel.

All of these rolling average values can be ranked to get a service level across all order days.  And then the average demand for the 95th percentile is equivalent to offering a 95% service level across the order history for that item.  Once you link the rolling average period to the lead-time for that item, you can size stock at to give you a 95% service level from inventory.

A Kanban sizing will also require a daily rate of demand, and the rolling average value at the target service level can be used to size Kanban.

Download a copy of this Demand Analytics workbook.  See this article for connecting with text files and check this technique to perform this calculation over thousands of Excel  rows.

This example uses a sort function to calculate quickly and avoid slow Excel number-crunching.  The Sort, dynamic range and paste down functions are all automated in our free Fast Excel Development Template. You can download it here and learn how to use it in our tutorials.