This has been a recurring challenge and a potential land mine when it comes to the raw number crunching in demand planning. Zeroes and Nulls……….
Is the Null the same as a zero?
Although excel formatting can code a zero as a dash, can a zero be interpreted as a Null? If so when and when not?
Similarly, including some may also be bad for the health of your demand forecast.
Leading zeroes – Will you include them in developing a statistically modeled forecast.
How about nulls in the middle of the data? These either show up as nulls, .dots, or some times as zeroes.
Ok. Now that I have asked too many questions, I will also propose some answers for you.
Generally Nulls can never be treated as zeroes. They are different things. Nulls mean nothing, the absence of anything. Nulls mean no data or no observation. If you average a series with nulls, the nulls count in either the numerator or the denominator. Zeroes are different. If you average them, they will have no contribution to the numerator but will count as an observation in the denominator so you will have your average reduced with the presence of zeroes.
At least in demand forecasting, we can coin the following rules:
1. Leading Zeroes can be interpreted as Nulls.
At times a product may be slated to launch in a specific month and hence the system may start recording zeroes as data if the launch is delayed. Leaving them in may result in a poor statistical forecast.
2. Nulls in the middle can be interpreted as zeroes.
Some systems may record nulls if there is no demand activity. However if the nulls occur in the middle of a time series history, I would recommend they be treated as zeroes. Most intermittent demand data is characterized by zero sales volume frequently. If you leave them as nulls, this will inflate your average and generally result in a upwardly biased demand forecast.
Imagine this scenario:
90 Null Null 90 Null 90 Null Null 90 Null Null Null 90.
If you ignore the null, then your demand forecast will be 90 per month if you use the average as the model to forecast. If you interpret the null to be zero, then your average will be 30 units a month. If the customer has a three month requirement of 90 units and orders only once in every three months, then 30 per unit seems more likely as a forecast.
3. Trailing zeroes and Nulls
Exclude or include? What do you do with these?
They look harmless to me if you have a decent exponential smoothing engine. Do you agree?
We will discuss our approach in detail in our Demand Planning and Sales Forecasting Tutorial workshops scheduled for Feb 2013 in Dallas, TX. We go into the nuts and bolts of many practical challenges that demand planners face. This is why our workshop is considered to be the most practical and hands-on when it comes to Training for Demand Forecasters.
Ignoring these nulls and zeroes can be perilous and lead you down the wrong path. This may affect both your forecasting and inventory setting. More dangerous to ignore these if you are calculating safety stock parameters.
You can see more info on our workshop at http://demandplanning.net/demandplanning_tutorialCA.htm. I am told that we have just a handful of seats left for the early bird quota.
Have a great holiday!