Marketing Forecast, Quarterly Revenues

[Laura]

I am working with a Business Development database. We need to generate
a new report showing forecasted revenues by quarter. The data I am
working with includes:
Project, Revenue, POP Start, POP Finish
What I need to produce is a report that displays the current quarter
(according to today's date), plus the next four quarters (i.e., 2Q05,
3Q05, 4Q05, 1Q06, 2Q06). I need to show the revenue for each project
for each quarter according to the period of performance (POP).

For example:
Project A has a total projected revenue of $300,000 and a projected POP
from 9/1/05 to 2/28/06. For the purposes of this report, we are
assuming the revenue will be evenly distributed over each month of the
project.
The result would be:
2Q05 3Q05 4Q05 1Q06 2Q06
Project A $0 $50,000 $150,000 $100,000 $0

I apologize if this is something that has already been covered on here,
but I have done quite a few searches with no real success. I have
poured through Access books, and I believe the solution will be some
type of crosstab query. However, I am inexperienced with more
complicated queries and am stumped. Am I overthinking this? Any help is
greatly appreciated. Thanks!

[shumaker]

When you have a big problem, tear it into little pieces, to paraphrase
a White Stripes song.

Maybe I'm stating the obvious. But basically we want to determine how
many months the POP covers, 9/1/05-2/28/06 would be 6 months. Divide
the Revenue by that number of months to get the revenue per month(this
assumes even distribution), then for each quarter, multiply the $/month
by the number of months that the quarter covers that are also in the
POP to get the $ for that month.

If performance isn't an requirement, it may be easier to do this in
code if you are not confident with complex queries.

[Laura]

Thanks for the response. Actually, I've done that much so far. My
problem is displaying this information by quarter, in the appropriate
quarters. I cannot figure how to make the query determine how many
months are applicable for each project for each quarter. I think I may
be one step closer, though. I've started individual queries for each
quarter. I know I'm missing something simple here, but what criteria do
I set to only pull projects whose period of performance falls within
the specified quarter? I started with
DatePart("q",Date()) & "Q" &
Right(Str(DatePart("yyyy",Date())),2)=DatePart("q" ,[POP Start]) & "Q" &
Right(Str(DatePart("yyyy",[POP Start])),2)
I know that seems kind of long and drawn out, but I need the quarter
displayed as 2Q05, and this works to pull the proper quarter for the
proper year. If I just put in DatePart("q",Date())=DatePart("q",[POP
Start]), it pulls all projects that start in the 2nd quarter of any
year.
This works for pulling any projects that start in the current quarter,
but of course does not include projects that may have already started
and are ongoing. I think I've been looking at this thing too long...

[jimfortune]

Laura wrote:

I apologize if this is something that has already been covered on here,
but I have done quite a few searches with no real success. I have
poured through Access books, and I believe the solution will be some
type of crosstab query. However, I am inexperienced with more
complicated queries and am stumped. Am I overthinking this? Any help is
greatly appreciated. Thanks!

I answered something like this before. Hold on.... Googling. Ah, yes,
it was you who asked a similar question:

http://groups-beta.google.com/group/...c0622af9?hl=en

I can modify the solution if it wasn't to your satisfaction. Of
course, if you'd like to keep looking through Access books or want to
try a crosstab query instead go ahead :-).

James A. Fortune

[Laura]

James,
Thanks for the help. I apologize for not seeing that before.
Unfortunately, I work for people who don't know exactly what that want
with this database and cannot make up their minds. I thought I had
posted something similar, but I never went back to it because they told
me to scrap it. Now they want they same type of thing, but in
quarters...
In any case, I'll give your suggestion a try and see what I come up
with.
Thanks.
Laura

[jimfortune]

Laura wrote:

James,
Thanks for the help. I apologize for not seeing that before.
Unfortunately, I work for people who don't know exactly what that want
with this database and cannot make up their minds. I thought I had
posted something similar, but I never went back to it because they told
me to scrap it. Now they want they same type of thing, but in
quarters...
In any case, I'll give your suggestion a try and see what I come up
with.
Thanks.
Laura

O.K. Give quarters a try and post back if you need any help.

James A. Fortune

Who was it that died?
I could barely answer.
Who was like the planet Venus' brightness compared to the stars?
Who will well-fed birds and deer mourn?
Who hoped only for health and happiness for her children?
Who was loved by everyone who knew her?
Who was my greatest ally?
Who was my mom?

[Laura]

Okay, I tested that out, and it seems to work fine. However, my
requirements have changed a little bit, in that I'm no longer looking
for total forecasted revenue per month, but forecasted revenue for each
project broken down by month or quarter. Also, the table with the start
month is a little cumbersome. That means that every time we run this
report, someone will have to go in and manually change the start month.
I tried to make it default to the current date, but that returned no
results in the queries. Did I misunderstand something there?
Again, any additional help is greatly appreciated. Thanks!

[jimfortune]

Laura wrote:

Okay, I tested that out, and it seems to work fine. However, my
requirements have changed a little bit, in that I'm no longer looking
for total forecasted revenue per month, but forecasted revenue for each
project broken down by month or quarter.
See if you can figure out the change from month to quarter first. If
you run into trouble with it I'll be happy to help.
Also, the table with the start
month is a little cumbersome. That means that every time we run this
report, someone will have to go in and manually change the start month.
I tried to make it default to the current date, but that returned no
results in the queries. Did I misunderstand something there?
Again, any additional help is greatly appreciated. Thanks!

I'll look into that problem this weekend. I'm surprised the query
didn't error out when you tried to use the current date. Perhaps when
I examine the start date problem I can deal with this error condition
also.

James A. Fortune

[jimfortune]

ji********@compumarc.com wrote:

Laura wrote:

Okay, I tested that out, and it seems to work fine. However, my
requirements have changed a little bit, in that I'm no longer looking
for total forecasted revenue per month, but forecasted revenue for each
project broken down by month or quarter.

See if you can figure out the change from month to quarter first. If
you run into trouble with it I'll be happy to help.

Also, the table with the start
month is a little cumbersome. That means that every time we run this
report, someone will have to go in and manually change the start month.
I tried to make it default to the current date, but that returned no
results in the queries. Did I misunderstand something there?
Again, any additional help is greatly appreciated. Thanks!

I'll look into that problem this weekend. I'm surprised the query
didn't error out when you tried to use the current date. Perhaps when
I examine the start date problem I can deal with this error condition
also.

James A. Fortune

Here goes...

Put the following barely tested function in a module:

Public Function QuarterIntersection(dt1 As Date, dt2 As Date, dt3 As
Date, dt4 As Date) As Integer
'Return the number of months overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
QuarterIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then QuarterIntersection = 0
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then QuarterIntersection = 0
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
QuarterIntersection = DateDiff("m", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
QuarterIntersection = DateDiff("m", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
QuarterIntersection = DateDiff("m", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
QuarterIntersection = DateDiff("m", dt1, dt4) + 1
End If
End Function

Then create a table containing all the quarters you ever want to
consider:

tblQuarters
QuarterID Auto
Quarter

1 1Q04
2 2Q04
3 3Q04
4 4Q04
5 1Q05
6 2Q05
7 3Q05
8 4Q05
9 1Q06
10 2Q06
11 3Q06
12 4Q06

tblProjects
ProjectID proj_name proj_value proj_start proj_end
1 Project A $200,000.00 11/1/04 4/30/05
2 Project B $600,000.00 1/1/05 12/31/05
3 Project C $1,000,000.00 12/1/04 5/31/06

qryQuarterlyBudget:
SELECT tblQuarters.QuarterID, tblQuarters.Quarter,
tblProjects.proj_name, tblProjects.proj_value, tblProjects.proj_start,
tblProjects.proj_end,
QuarterIntersection([proj_start],[proj_end],DateSerial(Right([Quarter],2)+2000,Left([Quarter],1)*3-2,1),DateSerial(Right([Quarter],2)+2000,Left([Quarter],1)*3+1,0))
AS MonthsIntersecting,
DateDiff("m",tblProjects.proj_start,DateAdd("d",tb lProjects.proj_end,1))
AS DurationInMonths,
CCur(tblProjects.proj_value*[MonthsIntersecting]/CDbl([DurationInMonths]))
AS ContributionToQuarterlyBudget
FROM tblProjects, tblQuarters
WHERE
(((DateIntersection([proj_start],[proj_end],DateSerial(Right([Quarter],2)+2000,Left([Quarter],1)*3-2,1),DateSerial(Right([Quarter],2)+2000,Left([Quarter],1)*3+1,0)))>0));

! qryQuarterlyBudget:
QuarterID Quarter proj_name proj_value proj_start proj_end
MonthsIntersection DurationInMonths ContributionToQuarterlyBudget
4 4Q04 Project A $200,000.00 11/1/04 4/30/05 2 6 $66,666.67
4 4Q04 Project C $1,000,000.00 12/1/04 5/31/06 1 18 $55,555.56
5 1Q05 Project A $200,000.00 11/1/04 4/30/05 3 6 $100,000.00
5 1Q05 Project B $600,000.00 1/1/05 12/31/05 3 12 $150,000.00
5 1Q05 Project C $1,000,000.00 12/1/04 5/31/06 3 18 $166,666.67
6 2Q05 Project A $200,000.00 11/1/04 4/30/05 1 6 $33,333.33
6 2Q05 Project B $600,000.00 1/1/05 12/31/05 3 12 $150,000.00
6 2Q05 Project C $1,000,000.00 12/1/04 5/31/06 3 18 $166,666.67
7 3Q05 Project B $600,000.00 1/1/05 12/31/05 3 12 $150,000.00
7 3Q05 Project C $1,000,000.00 12/1/04 5/31/06 3 18 $166,666.67
8 4Q05 Project B $600,000.00 1/1/05 12/31/05 3 12 $150,000.00
8 4Q05 Project C $1,000,000.00 12/1/04 5/31/06 3 18 $166,666.67
9 1Q06 Project C $1,000,000.00 12/1/04 5/31/06 3 18 $166,666.67
10 2Q06 Project C $1,000,000.00 12/1/04 5/31/06 2 18 $111,111.11

qryQuarterlyBudget_Crosstab:
TRANSFORM Sum(qryQuarterlyBudget.ContributionToQuarterlyBudg et) AS [The
Value] SELECT qryQuarterlyBudget.proj_name,
Sum(qryQuarterlyBudget.ContributionToQuarterlyBudg et) AS
TotalContribution FROM qryQuarterlyBudget GROUP BY
qryQuarterlyBudget.proj_name PIVOT qryQuarterlyBudget.Quarter;

! qryQuarterlyBudget_Crosstab:

proj_name TotalContribution 1Q05 1Q06 2Q05 2Q06 3Q05 4Q04 4Q05
Project A $200,000.00 $100,000.00 X $33,333.33 X X $66,666.67 X
Project B $600,000.00 $150,000.00 X $150,000.00 X $150,000.00 X
$150,000.00
Project C $1,000,000.00 $166,666.67 $166,666.67 $166,666.67
$111,111.11 $166,666.67 $55,555.56 $166,666.67

Then another query can sum up the quarters:
SELECT Sum(qryQuarterlyBudget_Crosstab.[1Q05]) AS SumOf1Q05,
Sum(qryQuarterlyBudget_Crosstab.[1Q06]) AS SumOf1Q06,
Sum(qryQuarterlyBudget_Crosstab.[2Q05]) AS SumOf2Q05,
Sum(qryQuarterlyBudget_Crosstab.[2Q06]) AS SumOf2Q06,
Sum(qryQuarterlyBudget_Crosstab.[3Q05]) AS SumOf3Q05,
Sum(qryQuarterlyBudget_Crosstab.[4Q04]) AS SumOf4Q04,
Sum(qryQuarterlyBudget_Crosstab.[4Q05]) AS SumOf4Q05 FROM
qryQuarterlyBudget_Crosstab;

I included QuarterID in qryQuarterlyBudget in case you want to use it
to try to put the quarters in ascending order. Trying to use all SQL
instead of using the QuarterIntersection function got a little messy.
Perhaps others can give suggestions about how to make this easier.
Also note that some of the fields in some of the queries aren't
necessary. They were just there to help see what was going on.

James A. Fortune