How can I minimize forecasting error in 100 probably correlated time series data streams with incomplete data from each one?

Question

Suppose that I have 100 restaurants with identical menus and ingredients they need to procure to stay in business. Let’s assume that they have 100 menu items per restaurant and 500 ingredients in total per restaurant.

Now let’s assume that you want to - for whatever reason - centrally coordinate the procurement of these ingredients through quarterly reports from each restaurant to forecast coming quarters for the entire chain of restaurants.

Further assume that each restaurant has a distribution regarding usage patterns and that any two restaurants might be correlated via external factors (weather, geographical proximity, general human traits, and so on).

Lastly, we assume that, in reality, our hypothetical restaurants will be somewhat non-compliant in their reporting. So, for example, while one restaurant consistently might report data for every quarter of the year, another might send you the first two quarters lumped together, and a third might send you the second and third quarter data lumped together and so forth. The data is therefore not reported simultaneously and it regards different time periods for different restaurants. We do, however, have such non-uniform data dating several years back.

Assuming there is a connection between the distributions - albeit maybe a weak one - is there a theoretically optimal way to combine the lossy data streams from every restaurant to approximate the proper underlying distributions for each? Optimally, I would like to "add" every new observation that I manage to get in order to refine my predictions for all restaurants, if possible.

Answer 1

Note: revised based on OP’s comment, which helped me understand the question better.

As I now understand, you want to disaggregate the restaurants that report the sum of 2 or 3 quarters into 1 quarter buckets — and is there a way to use correlation between restaurants to do that in a clever way.

The problem, in effect, is after 2 or more numbers are added together, how can you reverse it and get the original numbers. The addition destroyed information and there is no way to get it back. So disaggregation methods typically use rules or estimating methods.

Simple Method: Fix the data that lumps quarters together. If two quarters aggregated, divide by 2. If 3 quarters, divide by 3.

Note we don’t use any correlations here. This is what makes it simple.

Complicated Method:

For this method, you’ll need 1 or more restaurants that report results every quarter. Call these restaurants Group 1.

Restaurants that report every 2 quarters will be Group 2. Every 3 quarters, Group 3.

Determine Correlated Restaurants: Take the Group 1 data series and aggregate it like Group 2. Then for Group 3. Compute the correlation of the time series Group 1’2 with Group 2. Group 1’3 with Group 3. Use your favorite method for computing correlation.

Hopefully out of this exercise you identified a Group 1 restaurants that correlate with Group 2 and Group 3 restaurants. So, for each Group 2/3 restaurant, you have a Group 1 restaurant as the reference.

Fill-in the Group 2/3 gaps:

For a Group 2 restaurant, you are missing values for Q1 and Q3 of each year. We want to assign values to those.

Fill-in method 1: if you can regress the Group 1 time series against some independent variables, create a Group 1 forecast model for the reference restaurant.

Use that group 1 model for the group 2 restaurant to predict Q1 and Q3 — using the values of the independent model for the group 2 restaurant of course. Subtract the Q1 value from Q2, Q3 from Q4. You now have Q1,2,3,4 for your Group 2 restaurant.

This would be very time consuming.

Fill-in method 2: For the Group 1 reference, compute the ratio of r1=Q1/Q2, r2=Q3/Q4.

Now for Group 2, multiply Q2 by r1 to get Q1. Subtract that Q1 from Q2. Similar for Q3 and Q4. You now have Q1,2,3,4 for your Group 2 restaurant.

This is easier.

Caveats

Why mention the simple method at all? Well, oftentimes more complicated methods don’t have much improvement over simple ones. You may do a lot of work and only get 1-3% improvement. So you should compare them.

For the complicated method to be better, the correlations need to be high — say > 0.50 . If some Group 2/3 restaurants don’t have a good reference Group 1 restaurant, go with the simple method.

Thank you for the opportunity to practice my spelling of restaurant.