I have a some historical sales data for various product SKUs, including category information ("department" "category", "subcategory"). I want to use this to generate sales curve (a baseline forecast for future demand), no doubt using an appropriate exponential smoothing algorithm.

The trouble is that the data for a particular SKU will often be too sparse to be able to infer a sales curve. I'm thinking that when that is the case, the sales data for the SKU could be aggregated with other SKUs for the same product and I could then use the sales curve for the product rather than the SKU (which would presumably be more reliable). If the product doesn't have enough sales history then I'd use the sales curve for the subcategory, and so on and so forth… potentially even having to resort to a sales curve for the top level category (a department in this case) if insufficient sales data is available to generate reliable sales curves for lower level categories.

What I'm wondering is, how do I determine when I have enough data to be able to trust a sales curve? How many sales "data points" do I need for a SKU or a subcategory in order to be able to trust its sales curve?

Perhaps I'm approaching this the wrong way though. An alternative that I'd considered was to use data from all levels of the hierarchy in the forecast with appropriate weightings for each. So a product's future sales would be predicted by W1 * Forecast(Product) + W2 * Forecast(Subcategory) + W3 * Forecast(Category) + W4 * Forecast(Department).... Working out the weightings might be a bit complex but not impossible (and might be done using a regression analysis).

I'm sure this kind of problem has been solved before but I'm a bit of a stats newbie. Are there "off the shelf" algorithms/techniques that could use for this problem?

  • $\begingroup$ You can never absolutely trust a prediction - you need confidence intervals. Have you tried plotting a projection with confidence intervals? Your second prediction algorithm doesn't make much sense to me. Isn't a given product always going to be in the same subcategory, category, and department? If so, isn't the forcast for the product already going to include all the information that the categories could add anyway? What would you be regressing against? $\endgroup$ – naught101 May 17 '13 at 2:02
  • $\begingroup$ ah... enter keys. Imagine I have three products SKU1, SKU2 and SKU3: SKU1 Stock Levels: 0 0 0 10 9 8 SKU1 Sales Levels: 0 0 0 1 1 2 SKU2 Stock Levels: 10 8 4 10 9 8 SKU2 Sales Levels: 2 4 0 1 1 2 SKU3 Stock Levels: 10 8 7 0 0 0 SKU3 Sales Levels: 2 1 7 0 0 0 SKU1 doesn't have "sales" data for t=1, t=2 and t=3. SKU3 lacks data for t=4..6. However if all of the SKUs are part of the same subcategory, we could perhaps "guess" at what SKU1 and SKU3 sales would have in those periods by borrowing the seasonal and trend components for those SKUs in something like a Holt-Winters algorithm). $\endgroup$ – James Crosswell May 17 '13 at 11:01
  • $\begingroup$ More generally, the historical sales for an individual SKU might not be the best predictor for future sales of that SKU. Perhaps seasonal, cyclical and trend data for the subcategory are better predictors. I'm thinking of an algorithm that includes the trend data for all levels in the hierarchy (subcategory, category etc.), with different weights to each. The best values for the weightings is going to be tricky to guess at though... so this is what I was thinking of using a regression for. $\endgroup$ – James Crosswell May 17 '13 at 11:02
  • $\begingroup$ Ah, I see the regression idea was stupid now... not very appropriate for one-step-foreward prediction algorithms 8-/ $\endgroup$ – James Crosswell May 17 '13 at 13:31

There is a way of optimally combining forecasts at all levels of a hierarchy. See Hyndman et al. (CSDA, 2011). The method is implemented in the hts package for R.

The basic idea does use a regression approach as you suggest.


OK so I think I've tracked down a fairly well accepted technique to do this on the SAS website: Forecasting Intermittent Demand Data.

Actually implementing all of that in a software algorithm is going to be challenging but then, "This isn't mission difficult, Mr Hunt!"


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.