Customer Sales Forecasting-Detailed Roll Up
I am working on a forecast for one department of a large consumer goods company. This department sells its produced goods to about 30 different customer (400-500 different products or sku's). These sku's come and go often (ie new products are added while others deleted).
What I am trying to depict is the overall departments result if x customer volume is up by Y.Y% YoY - and the subsequent margin made. The COGS would need to be broken out by raw material component make up in order to adjust for increase/decreases in raw material inputs.
My trouble with this is coming up with a way of showing a few years (by qtr) history, projecting it out 5 years BUT ALSO BEING ABLE TO EFFICIENTLY UPDATE IT WITH ACTUALS ON A QTR BASIS....
Any suggestions welcome.