Thread: Quarterly Forecast for Multiple Projects with Varying Project Start/ End dates

1. Registered User
Join Date
Jun 2015
Posts
2

Unanswered: Quarterly Forecast for Multiple Projects with Varying Project Start/ End dates

I have several projects that each have different start spending and end spending dates.
The customer has asked to see a quarterly spending plan for each project, summarizing how much money may be forecasted for expenditure each quarter.
Is there a function to say: yes or no, part of "Q1 2016" falls within the project spending start/ end dates?

For example:
Project 1: [Begin Spending] 1/14/2016, [End Spending] 6/12/2016
Project 2: [Begin Spending] 4/14/2016, [End Spending] 9/12/2016

The final report should look like this:

..................2015 Q4.......2016 Q1....2016 Q2......2016 Q3.....2016 Q4...2017 Q1 ......
Project 1.........No..............Yes.........Yes......... .....No.............No..........No
Project 2.........No..............No...........Yes........ .....Yes ..........No...........No

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
is there q function, no
is there way of doing this, most certainly

you need a way of translating dates into accounting periods
how you do that is upto you, but its quite common to have a table whicb defines an accounting caledar. something like
financial_year
period_ref
starts_on

..or ends on. be wary of having both a starts on and ends on date as its possible that users can screw up the data. say by defining one period as endign after another one has started, or missing some dates altogether.

..then join your financial data to that tabel to fidn the relevant year and period ID

3. Registered User
Join Date
Jun 2015
Posts
2
Thanks for the tip!

I found an argument in another thread that makes perfect sense to do a simple true/ false.
[StartDate1]<=[EndDate2] AND [StartDate2]<=[EndDate1]

Here I plugged in dates for a quarter

=IIf([Const - Begin Spend]<=#12/31/2016# And #9/30/2016#<=[Const - End Spend],([Const-Budget]/(DateDiff("q",[Const - Begin Spend],[Const - End Spend]))),"")

Join Date
Nov 2004
Location
out on a limb
Posts
13,692