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.

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)
Start Discussion

Total Avg Compensation

June 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (35) $364
  • Associates (205) $232
  • 2nd Year Analyst (115) $151
  • Intern/Summer Associate (97) $145
  • 3rd+ Year Analyst (27) $145
  • 1st Year Analyst (422) $131
  • Intern/Summer Analyst (340) $82