Shortages over time: trend versus noise

A manager sees shortage of 1.4 percent in the monthly report; last month it was 0.9. "What happened?!" he asks, but the next month shows 0.8 and everyone calms down. At another warehouse the sequence 1.0, 1.2, 1.5, 1.7 worries no one, because each month is looked at separately. Dynamics means looking at several months together, and it tells random wobble from real change.

A twelve-month series

For illustration, monthly shortage as a percentage of sales: January 0.8; February 1.1; March 0.7; April 0.9; May 1.4; June 0.8; July 1.0; August 1.2; September 1.5; October 1.7; November 1.6; December 1.9.

The first-half average is 0.95, with values ranging from 0.7 to 1.4. May jumps to 1.4, but June is back at 0.8: a one-off wobble at the edge of the usual range. From September, though, come 1.5, 1.7, 1.6 and 1.9: four months in a row above the highest figure of the first half (1.4). The second-half average is 1.48 against 0.95. This is no longer chance but a trend.

A practical rule: take the highest value of the earlier calm period (here 1.4) as the line. One crossing of the line is a reason to watch, three months in a row above it is a reason to investigate. In this series the rule would have fired as early as November.

Smooth it with a moving average

A three-month average softens the wobble. March 0.87; April 0.90; May 1.00; June 1.03; July 1.07; August 1.00; September 1.23; October 1.47; November 1.60; December 1.73. The May jump remains a small bump in the average, while from August the average rose four times in a row: 1.00 → 1.23 → 1.47 → 1.60 → 1.73. On a line chart keep the raw series and the average side by side: the first shows the event, the second the direction.

In Excel let the values sit in B2:B13. Put the line in cell F1: MAX(B2:B7). For the average, the formula AVERAGE(B2:B4) is dragged down. For the signal, enter in D10 and drag down: IF(AND(B8>$F$1, B9>$F$1, B10>$F$1), "check", ""). The separator depends on your Excel settings. You need at least 8-12 months to judge: do not infer a trend from two or three points.

Look at percentages, not so'm

A separate example: sales grew from 400 to 500 million so'm, shortage from 4 to 5 million. In so'm that is growth of 25 percent, but in percent both months give 1.0, so nothing has changed. When volume rises before the holidays, the shortage in so'm rises with it. So look at the dynamics as a percentage of sales (the calculation is covered in a separate article).

When a shortage becomes "visible"

An important point: a shortage becomes visible not when it happens but when a count is carried out. If the warehouse is counted once a quarter, three months of losses "appear" in a single month and the chart looks like a staircase. For instance, the March count showed a shortage of 4.5 million so'm: that is the total for three months, not one, an average of 1.5 million a month. If you need a monthly view, switch to cycle counting: count a part of the range every month. If the counting method changes (say from manual counting to scanners), shortage may suddenly jump: not because losses grew, but because what went unnoticed before is now visible. Mark that on the chart too.

Mark the events

Record on the chart the events that might have affected shortage: a new employee, a new supplier, a warehouse move, a change of layout, a change of counting method. Suppose the warehouse moved into a new building in August. Growth began in September; it may be connected with the move, but closeness in time is not yet a cause. Once the trend is confirmed, break the shortage down by zone, category and supplier: you will see where the growth began.

In the report, give the manager not one number but three: the current month, the three-month average and the same month last year. Then the question becomes not "why did it rise?" but "is it continuing?", and the decision comes faster.

Conclusion

One month says almost nothing about shortage, whereas four to six months show the direction. Through cycle counting and discrepancy analysis, CountLab provides a reliable month-by-month series and, in the management report, shows the trend together with the events.