Forecasting in Excel (IF/Dates/And/OR???)

[maz86]

Hi All,

Little stuck with this and can't figure out how to get it to work - any help/advice will be greatly appreciated:

I have a worksheet with a list of suppliers, the frequency I pay them, payment value (amount) and date last paid. And then the start and end date of every week in the year.

Using the last paid date, I want the value to populate in the week when the next lots of payment will fall in for the whole year.

I've tried with several formulas mainly based around IF and DATE but can't get them to work.

Example of what the worksheet looks like:

Thank you in advance

 

[maz86]

Example:

Week Start 27/02/2016 05/03/2016 12/03/2016 19/03/2016 26/03/2016
Week End 04/03/2016 11/03/2016 18/03/2016 25/03/2016 01/04/2016

Supplier Frequency Amount Last_Pay
TESCO Quarterly 100.00 01/12/2015
SMITHS Monthly 50.00 11/02/2016
HSBC Weekly 200.00 25/02/2016
GH Annually 500.00 25/03/2015

TOTAL 0 0 0 0 0