The Perils and Payoffs of Forecasting at Increasing Granularity

What Is a Granular Forecast?

Looking at the visual above, lets give a hypothetical example of what we could be forecasting. Let’s say we want to forecast how many units or dollars of different parts we are going to sell. The hierarchical break down might look like this:

Level 0: Total parts sold worldwide.

Level 1: Parts sold in different countries.

Level 2: Parts sold in different districts within those countries.

Level 3: Parts sold to different verticals within those districts.

The object being forecasted, and the particulars of the hierarchy are interchangeable. But the concept of granularity is the important take away. There are some interesting, and for a decision maker, problematic, phenomena that happens as you get more granular.

Why Bother With Granular Forecasts if They Are Hard?

I’ll give the most common issues with granular forecasts right now, and then build on why they are issues and how to solve them further below.

1. The accuracy of the forecast tends to get worse with more granularity
2. The time to make the forecasts (potentially thousands of individual forecasts) can be too long

The chance of messing up your back-test increases

• The chance of having missing data increases

Accuracy

As you go deeper in your hierarchy of forecasts, data tends to become more volatile. You may have heard this phenomenon described different ways including:

1. Lower signal and more noise
2. “Lumpy” data
3. “Sparse” data

To give an example, you may have an upward trend at a national level for a special sweater, but as you get to lower levels, individual stores may have no clean trend that corresponds to national trend. Hypothetical store #1 looks like it has increasing sales for a few months, but then has a slow-down, do we forecast a return to growth or not for it moving forward? It can be hard to say, but the model has to forecast something, so it makes the best statistical forecast it can and the result is that it may be a little less accurate than the national level forecast. I’ll briefly detail one approach I’ve developed to help improve accuracy on granular forecasts.

Improving Accuracy on Granular Forecasts

How it works:

1. Make forecasts at the most granular level
2. Make forecasts at the next level up (because it will have a better signal to noise ratio)
3. Use the higher level forecasts to adjust the underlying granular forecasts

Example:

Forecast for L1 = 10. Forecasts for N1, N2, and N3 are 2,3, and 3 respectively.

2+3+3 = 8

10/8 = 1.25

The forecast at the higher level L1 is 1.25x bigger than what the underlying granular forecasts add up to. However, we know the signal is better at the higher level and is more accurate, so we adjust the underlying forecasts by multiplying each of them by 1.25. Now the underlying forecasts add up to the more accurate L1 forecast, and this tends to make the more granular forecasts more accurate as well.

This trick increases accuracy in forecasting volatile granular objects in my experience, but the trend is still true that the deeper you go the less accurate forecasts will be. The important question is if the forecast at granular levels is still “GOOD ENOUGH” to help you make decisions. Does the model forecast just have to be more accurate than a human looking at the chart or do you have a magic number (like 95% accuracy) it has to exceed?

Time

When you desire more granular forecasts, that means there are more unique forecasts to produce. Despite the breakneck pace of computing innovation, these forecasts still can take quite a while to produce. If forecasts are desired for 100 products, each of which has 4 sizes, and is further split out by 10 geographies, that equates to 4,000 forecasts. If it takes 10 seconds per model (a baseline approach of a simple exponential smoothing model) that’s now 40,000 seconds or about 11 hours. Assuming you have the code to run these forecasts, would you really want to spend 11 hours a month, or worse, every week, to get this information?

The baseline approach is faster, and likely more accurate, than manually looking and creating all 4,000 forecasts, but there has to be a better way! That better way is to turn a univariate (one time series) forecasting problem into a multivariate regression problem. This approach is called a global model, it builds one model that forecasts all time series, and it turns out it is often more accurate than baseline approaches.

This can immensely speed up the process and opens up the option to use more sophisticated machine learning algorithms such as XGBoost, Random Forest, and others, which have won several forecasting competitions. In addition to changing the model approach, other solutions including renting a more powerful computer by running the program on the cloud.

Missing Data Problems

Let’s say you’re making a monthly forecast. Assuming you sold anything every month, you have a complete record at the total level (no missing data). However, at a deeper level, like products, what if you didn’t sell widget A last month, but did sell it in months further in the past. Using some models, if you don’t explicitly put a “0” in for widget A last month, no forecast will be made for the coming month. That could be really disappointing, especially if you were expecting a forecast and last month was just a fluke. This issue intensifies as you get more granular and data becomes more “lumpy”. You sell a lot of something one month and then none the next, but it could still be important to you to forecast it each month.

My solution for this is the fill_gap() function in R, which I have set up to automatically find missing data and fill it in with a 0. As a note of caution, it is important to have confidence that all missing numbers are truly 0 when using this approach.

Conclusion

While this article has come to an end, the battle for better granular forecasts rages on. We’ve covered some of the challenges of granular forecasts and how I handle them. Looking forward, exciting opportunities to have more accurate forecasts present themselves through blending models, fine-tuning back-tests, and integrating “external regressors” such as economic data, marketing spend, or even the weather. New dangers also lurk, such as over-fitting the back-test and data-leakage. While this may seem overwhelming to consider, the rewards of capturing market-share opportunity and minimizing cost from waste make it well worth the fight.