In general tools such as exponential smoothing can help but in periods were demand is zero or is very variable the forecasting error can be enormous. And as a consequence excessive inventory will be held . MAPE [mean absolute percentage error] also runs into serious issues especially when calculating the error when the sales are actually zero. You can either ignore zeros in the forecast , thus making MAPE unusable or you can eliminate the zeros by padding out the zero periods with the average demand over a forecast horizon. At least this will give some semblance of proportionality to the forecast – but will still lead to excessive errors.

You could also measure the demand by periods to construct a distribution curve from the actual demand. You do this by picking a period of time let’s say two years [ 24 periods = N] and noting the demand for each month[ including zeros]. A distribution curve can then be constructed to get the overall demand average over the 24 periods X and the standard deviation. We can estimate the standard deviation from the relationship MAD [mean absolute deviation] ≅ 0.7σ so σ is approximately 1.25MAD. Using this we can calculate the 95% confidence interval for the demand over the 24 periods as follows:

Average demand forecast is equal to =X ∓ 1.96 × σ/√N

I realise this is not an easy task to do especially when you have many lumpy components or sub assemblies to consider but at least this may give you some handle and proportionality with the actual forecast. This is all doable in a very simple excel spread sheet that has all the functions you need.

It may be best for you to try several different methods as an experiment over the last two years and see which one of them gives you the least error over that time. When I have more time I will write a full post on how to handle lumpy demand with some forecasting examples for both standard and lumpy profiles. I have seen lots of systems and procedures to handle this but quite frankly only of interest in an academic sense as they do not achieve much more in accuracy than the simple methods.

Continue reading...