optimizing weight of historic forecasts to apply to future forecast

[deanwitmer]

First of all, I'm an Excel novice and much of the information on here is over my head. With that being said, I'm not expecting to be spoon-fed the solution but would love someone to at least point me in the direction of additional research I need to pursue.

I'm looking for a solution to a forecasting optimization challenge. Let's say that over a given time period, I collected forecasts from 10 individual analysts on 300-400 previously released products. Each of the 10 analysts gave a forecast on each of the products. Because they were in the past I have the actual sales number.

Evaluating all of the historical data on the 3-400 products for all of the analysts , I want to come up with an optimal "weight" of each analyst forecast that rolls up with the other analysts (and their optimal weight) and gets me closest to the actual sales number.

I'm hoping to find a single multiplier (+/-) that can then be applied to each of the analysts future forecast to increase overall forecasting accuracy.

My apologies if I'm not clear. Please ask for any other information and I'll try to make it a little clearer. Thanks in advance.