forecasting with multiple variables/criteria

Hello all

attached file includes historical data (2018-2019) and my challenge is to forecast spend for each week of 2020 with some additional conditions/criteria

As the example lets say I need to forecast what will be the spend of CRACK DETECT process in each calendar week of 2020 with criteria as mentioned in the file:
- PO Status must <> "cancelled" (in cell M3)
- forecast is only for company "A" (in cell M4)
- forecast is only for "crack detect" process (in cell M5)

is there any formula that will allow me to calculate what is the forecast for each week taking into account criteria and and will return forecast only in comparison to the same week numbers in the past (what I mean is that formula will calculate only week 1 in 2016/2017/2018/2019 and for week 1 in 2020 return value, and the same for week 2 , etc)

i tried to use basic statistical approach (Yt=St x It X Tt) but it seems to be very long way to calculate

any suggestions please?

Raw data:
- dates in column C
- weeks in column D
- company in column E
- status in column F
- spend in column G
- process in column H

I would appreciate also for solution for Excel 2010 and Excel 2019 if possible

I tried FORECAST formula but it seems not working :/

thanks

I edit this, because now I realize this GROWTH function may be unavailable in your Excel version.
Anyway I'll leave it here just as an idea.

I created a pivot table with data segmentation in a new tab named Pivot and in this new tab I use this formula with GROWTH & OFFSET
functions combined:

Formula:

Please Login or Register  to view this content.

Please find file attached, run some test and let us know how it goes.

Good luck!