Hi,
Need some help on what formula to use for my forecasting report.
So I have a spreadsheet with the total amount of claim in column C. Part of this claim (based on percentage B3:B9) should be received on the date it falls due (based on claim due dates E14:K14).
For example, on 28-Mar-2017 the Pre Construction claim falls due and thus 1% of $536,580 should be received (i.e $5365.80).
Now, the question is where should the amount of $5365.80 reflect. As you can see, from columns L14:BK14 there are predefined dates. These dates corresponds to every Monday of the week for the whole year and is a week ending date for the purpose of my reporting.
What I want to happen is for the $5365.80 to be shown under 03-Apr-2017 column because technically this date covers all claims received from 28-Mar-2017 to 03-Apr-2017.
Then the next type of claim that would fall due would be the Council claim, and this is 4% of the total claim. With 4-April-2017 as due date, the $21,463.2 ($536580*4%) should then reflect under 10-Apr-2017 column since again this column corresponds to all amounts received from 04-Apr-2017 to 10-Apr-2017.
And this goes on and on and so forth.
Lastly, the week ending dates with no amounts to be received should reflect zero amount.
So basically, the formula should be able to detect the percentage and date range for which the type of claim falls due.
I hope I have explained it clearly.
Attached is the file with the data and expected results (in red font color).
Thank you so much!
Bookmarks