Quarterly Guest Demand Forecasting

I’m trying to automate full-year forecasting for guest sourcing data in Excel. I have:

- 37 DMAs (Designated Marketing Areas) (ex: California)
- 234 sub trades (ex: Vancouver)
- Data available per sub trade per DMA for:

1. YTD CY (current year cumulative bookings to last data pull)
   
2. YTD LY (same point last year)
   
3. L4W CY (# of guests gained or lost in the past month)
   
4. L4W LY
   
5. CY Target (# of guests expected by year end)
   
6. LY Target
   
7. CY To Go = CY Target – YTD CY
   
8. LY To Go
   
9. % Achieved CY (how close we are to Target)
   
10. % Achieved LY

The only fixed number is the subtrade’s total target. DMA-level targets for each year are what I need to forecast for each sub trade.

I want a formula-based method in Excel to “auto generate” forecast values for each DMA. I am trying to improve upon our current model with something less manual.

What’s the best approach given these variables?