extrapolating existing data depending on where in month and terms

Hi

We have a number of contractors, who submit timesheets. Their payment terms differ in terms of when they fall due for payment. Most are on 28 day payment term but there are some on sub-28 days. I would like to forecast for a month for all payment terms but I'm stuck on how to extrapolate existing values for those short payment terms and rolling them for rest of the month. Extrapolations depends on when the forecast is run. If it is run beginning of the month, I'd expect to extrapolate the 7-day payment term timesheets across the following three weeks and for 14 days two weeks. Whereas if I run it mid-month, I wouldn't need to extrapolate for 14-day timesheets as they will have submitted timesheets by this point in time, in order to be due by the end of the month, but I'd need to extrapolate 7-day payment term timesheets at least once. Not got a clue how I would do this, without manual moving around of cells.

Have attached a dummy workbook. Tab 1 as values based on submitted timesheets, with separate rows for 7-day payment terms and 14 day payment terms so I can use those rows to extrapolate based on the data in Tab 2. Tab 2 is a breakdown of those values in payment terms but also split by weekday (row 2) as well as countrycurrencypaymentterm in column A

Thanks!

Hope the design makes sense with the comments box.

1) "Data" tab is where the "forecast" pulls in the SAP forecast from
2) "Growth" tab is for adjusting the extrapolation based on existing month's forecast plus future month's growth
3) In terms of extrapolating the SAP forecast tab, the first extrapolation needs to extrapolate based on the beginning of each payment terms' cycle, and so on.

I'm not sure if all this makes sense tbh but see how we go

P.s. the SAP forecast data is similar to the data I play around with in this thread but that was for a different purposes, if another solution is to play around with the SAP forecast data?:

https://www.excelforum.com/excel-for...ay-as-2-a.html