18-Month Revenue Forecast

[Laura Stout]

Please help! I've been spinning my wheels on this one! I am working on
a database that tracks future project opportunities. For each
opportunity, the data entered includes total project value and period
of performance, given in start date and end date. I need to generate
an 18-month forecast report that shows potential revenues per month,
with the total revenues for each project averaged over the period of
performance. It seems like maybe there's something simple I'm just
missing all together, but I'm stumped. Any help would be appreciated.

[Darryl Kerkeslager]

I'm 100% certain that someone here knows the answer, but the question is
very vague. Are you just trying to find out the formula to use, or need
some code help?

If I understand correctly (big if), then what you have is:

proj_total_value
proj_start_date
proj_end_date

monthsOfProject = proj_start_date - proj_end_date
' The exact sytax could vary because ...
' how do you define a month? - only complete months? calendar months?
' 30 days from the start date? What do you do with the remainder?

There just doesn't seem to be enough information here to supply what you are
asking about. Is project_value actually project cost? Or is it that you are
just trying to do this:

monthlyRevenue = (proj_total_revenue / days (proj_start_date -
proj_end_date))* 30
eighteenMonthProjection = monthlyRevenue*18
Darryl Kerkeslager

"Laura Stout" <ls****@motacorp.com> wrote in message
news:10*************************@posting.google.co m...

Please help! I've been spinning my wheels on this one! I am working on
a database that tracks future project opportunities. For each
opportunity, the data entered includes total project value and period
of performance, given in start date and end date. I need to generate
an 18-month forecast report that shows potential revenues per month,
with the total revenues for each project averaged over the period of
performance. It seems like maybe there's something simple I'm just
missing all together, but I'm stumped. Any help would be appreciated.

[Laura Stout]

Darryl,
I apologize if the question seems vague - it seems vague to me, too! You
are exactly right with the fields -
proj_total_value
proj_start_date
proj_end_date

Hopefully this example will help:

proj_name proj_value proj_start proj_end
Project 1 $200,000 11/1/04 4/30/05
Project 2 $600,000 1/1/05 12/31/05
Project 3 $1,000,000 12/1/04 5/31/06

In this example,
Project 1:
period of performance - 6 months
monthly revenue - $33,333.33
Project 2:
period of performance - 12 months
monthly revenue - $50,000.00
Project 3:
period of performance - 18 months
monthly revenue - $55,555.55

The report I need to generate will show monthly revenues for the next 18
months:
Nov '04 - $33,333.33 (Proj. 1)
Dec '04 - $88,888.88 (Proj. 1 + 3)
Jan '05 - $138,888.88 (Proj. 1 + 3 + 2)
Feb '05 - $138,888.88
Mar '05 - $138,888.88
Apr '05 - $138,888.88
May '05 - $105,555.55 (Proj. 3 + 2 - Proj. 1 completed)
and so on, with Proj. 2 dropping off after another 6 months, etc.

I hope this makes more sense. Again, any help is greatly appreciated!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Laura Stout]

Clarification on my previous post.

Hopefully this example will help:

proj_name proj_value proj_start proj_end
Project 1 $200,000 11/1/04 4/30/05
Project 2 $600,000 1/1/05 12/31/05
Project 3 $1,000,000 12/1/04 5/31/06

In this example,
Project 1:
period of performance - 6 months
monthly revenue - $33,333.33
Project 2:
period of performance - 12 months
monthly revenue - $50,000.00
Project 3:
period of performance - 18 months
monthly revenue - $55,555.55

The report I need to generate will show monthly revenues for the next 18
months:
Nov '04 - $33,333.33 (Proj. 1)
Dec '04 - $88,888.88 (Proj. 1 + 3)
Jan '05 - $138,888.88 (Proj. 1 + 3 + 2)
Feb '05 - $138,888.88
Mar '05 - $138,888.88
Apr '05 - $138,888.88
May '05 - $105,555.55 (Proj. 3 + 2 - Proj. 1 completed)
and so on, with Proj. 2 dropping off after another 6 months, etc.

I hope this makes more sense. Again, any help is greatly appreciated!

[Darryl Kerkeslager]

Ah, it appears that I was on the wrong track, or at least was watching the
wrong race.

What you want, I believe, is a report like so:

+----------------+-------------+--------------+-------------+--------------+
| Month Year | Project 1 | Project 2 | Project 3 | Total
|
+----------------+-------------+--------------+-------------+--------------+
| Nov 04 | $1,500.00 | 0 | 0 |
$1,500.00 |
+----------------+-------------+--------------+-------------+--------------+
| Dec 04 | $1,500.00 | $1,200.00 | 0 |
$2,700.00 |
+----------------+-------------+--------------+-------------+--------------+
| Jan 05 | $1,500.00 | $1,200.00 | 0 |
$2,700.00 |
+----------------+-------------+--------------+-------------+--------------+
| Feb 05 | $1,500.00 | $1,200.00 | $2,500.00 | $5,200.00
|
..
..
..
..
| Apr 06 | 0 | 0 | $2,500.00
| $2,500.00 |
+==========+=========+=========+========+========= +
| Total | $15,000.00 | $9,600.00 |$27,500.00 | $52,100.00
|
+----------------+-------------+--------------+-------------+--------------+

Have you considered Excel for this? In Access, this would need to be done
with a crosstab query - which I have no useful experience with.

I hate to do this, but please re-post your request with the added
clarification, and I know that you will get a correct response.

Sorry, I just didn't see that this was where it was going ....
Darryl Kerkeslager

"Laura Stout" <ls****@motacorp.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Darryl,
I apologize if the question seems vague - it seems vague to me, too! You
are exactly right with the fields -
proj_total_value
proj_start_date
proj_end_date

Hopefully this example will help:

proj_name proj_value proj_start proj_end
Project 1 $200,000 11/1/04 4/30/05
Project 2 $600,000 1/1/05 12/31/05
Project 3 $1,000,000 12/1/04 5/31/06

In this example,
Project 1:
period of performance - 6 months
monthly revenue - $33,333.33
Project 2:
period of performance - 12 months
monthly revenue - $50,000.00
Project 3:
period of performance - 18 months
monthly revenue - $55,555.55

The report I need to generate will show monthly revenues for the next 18
months:
Nov '04 - $33,333.33 (Proj. 1)
Dec '04 - $88,888.88 (Proj. 1 + 3)
Jan '05 - $138,888.88 (Proj. 1 + 3 + 2)
Feb '05 - $138,888.88
Mar '05 - $138,888.88
Apr '05 - $138,888.88
May '05 - $105,555.55 (Proj. 3 + 2 - Proj. 1 completed)
and so on, with Proj. 2 dropping off after another 6 months, etc.

I hope this makes more sense. Again, any help is greatly appreciated!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

ls****@motacorp.com (Laura Stout) wrote in message news:<10**************************@posting.google. com>...

Clarification on my previous post.

Hopefully this example will help:

proj_name proj_value proj_start proj_end
Project 1 $200,000 11/1/04 4/30/05
Project 2 $600,000 1/1/05 12/31/05
Project 3 $1,000,000 12/1/04 5/31/06

In this example,
Project 1:
period of performance - 6 months
monthly revenue - $33,333.33
Project 2:
period of performance - 12 months
monthly revenue - $50,000.00
Project 3:
period of performance - 18 months
monthly revenue - $55,555.55

The report I need to generate will show monthly revenues for the next 18
months:
Nov '04 - $33,333.33 (Proj. 1)
Dec '04 - $88,888.88 (Proj. 1 + 3)
Jan '05 - $138,888.88 (Proj. 1 + 3 + 2)
Feb '05 - $138,888.88
Mar '05 - $138,888.88
Apr '05 - $138,888.88
May '05 - $105,555.55 (Proj. 3 + 2 - Proj. 1 completed)
and so on, with Proj. 2 dropping off after another 6 months, etc.

I hope this makes more sense. Again, any help is greatly appreciated!

tblMonthNumbers
--------------------------
MonthNumber Long
1
2
....
18

tblStartMonth
-------------------
StartMonth Date m/yy
11/04

tblProjects
---------------
proj_ID AutoNumber
proj_name Text
proj_value Currency
proj_start Date m/d/yy
proj_end Date m/d/yy

1 Project 1 $200,000.00 11/1/04 4/30/05
2 Project 2 $600,000.00 1/1/05 12/31/05
3 Project 3 $1,000,000.00 12/1/04 5/31/06

qryDisplayMonths
--------------------------
SELECT Format(DateAdd("m",CInt([MonthNumber])-1,[tblStartMonth].[StartMonth]),"mmm""
'""yy") AS DisplayMonth,
DateAdd("m",CInt([MonthNumber])-1,[tblStartMonth].[StartMonth]) AS
theMonth
FROM tblMonthNumbers, tblStartMonth;

Then:

qryDisplayMonthlyBudget
--------------------------------------
SELECT A.DisplayMonth, (SELECT CCur(Sum(proj_value / (DateDiff('m',
proj_start, proj_end) + 1))) FROM tblProjects WHERE A.theMonth BETWEEN
proj_start AND proj_end) AS Budget FROM qryDisplayMonths AS A;

Produces:

DisplayMonth Budget
Nov '04 $33,333.33
Dec '04 $88,888.89
Jan '05 $138,888.89
....
Apr '05 $138,888.89
May '05 $105,555.56
....
Dec '05 $105,555.56
Jan '06 $55,555.56
....
Apr '06 $55,555.56

Note that choosing a StartMonth before there are any projects started
produces an error since the subquery doesn't return any records.
Adding or subtracting records in tblMonthNumbers varies the length of
the forecast. I hope this helps get you started.

James A. Fortune

[John Winterbottom]

"Laura Stout" <ls****@motacorp.com> wrote in message
news:10**************************@posting.google.c om...

Clarification on my previous post.

Hopefully this example will help:

proj_name proj_value proj_start proj_end
Project 1 $200,000 11/1/04 4/30/05
Project 2 $600,000 1/1/05 12/31/05
Project 3 $1,000,000 12/1/04 5/31/06

In this example,
Project 1:
period of performance - 6 months
monthly revenue - $33,333.33
Project 2:
period of performance - 12 months
monthly revenue - $50,000.00
Project 3:
period of performance - 18 months
monthly revenue - $55,555.55

The report I need to generate will show monthly revenues for the next 18
months:
Nov '04 - $33,333.33 (Proj. 1)
Dec '04 - $88,888.88 (Proj. 1 + 3)
Jan '05 - $138,888.88 (Proj. 1 + 3 + 2)
Feb '05 - $138,888.88
Mar '05 - $138,888.88
Apr '05 - $138,888.88
May '05 - $105,555.55 (Proj. 3 + 2 - Proj. 1 completed)
and so on, with Proj. 2 dropping off after another 6 months, etc.
These types of temporal problems are best solved by using a table of
discrete date periods. Without this you are forced to group on a calculated
column which can be bad for performance.

So create a table of months ...

create table months
(
monthStart datetime not null,
monthEnd datetime not null,
primary key (monthStart)
)
now add your month periods ...

insert into months(monthStart, monthEnd) values (#10/1/2004#,#10/30/2004#)
insert into months(monthStart, monthEnd) values (#11/1/2004#,#11/30/2004#)
insert into months(monthStart, monthEnd) values (#12/1/2004#,#12/31/2004#)
insert into months(monthStart, monthEnd) values (#1/1/2005#,#1/31/2005#)
insert into months(monthStart, monthEnd) values (#2/1/2005#,#2/28/2005#)
insert into months(monthStart, monthEnd) values (#3/1/2005#,#3/30/2005#)
insert into months(monthStart, monthEnd) values (#4/1/2005#,#4/30/2005#)
insert into months(monthStart, monthEnd) values (#5/1/2005#,#5/31/2005#)

then do something like this ...

select Format(m.monthStart, "mmm-yy") AS month,
(
select sum(p.proj_value / (dateDiff("m", p.proj_start,
p.proj_end)+1))
from projects as p
where p.proj_start <= m.monthStart
) as monthlyRevenue
from months as m




I hope this makes more sense. Again, any help is greatly appreciated!