Creating a 5 year projected revenue report

[fergler]

I've been asked to creat a report that will project grant revenues for a 5 year period. Our fiscal year runs 9/1-8/31, but grants have start dates that might begin during the middle of a fiscal year. If a grant has a start date of 1/1/07, then 8 months of the revenus fall in fisal year 2007 (1/1/07-8/31/07) and 4 months fall within fiscal year 2008. I was able to develop a query to generate revenues for 1 year, based upon actual fiscal year dates and what portion of the grant period fell within the fiscal date, but I can't seem to make it work to cover more than a 1 year period. I don't have a lot of experience developing my own code, but it seems this may be the way I will have to go. Does anyone out there have any helpful suggestions?

Thanks.

 

[SkipVought]

Hi,

What have you tried?

What did it do or not do that is a problem?

What about the data you are working with? We need to know something about the data.

Skip,
[sub]
[red][/red]
[/sub]

 

[fergler]

Skip:

Thank you for responding.

The basic query I created uses the following information/calculations:

Account Budget Date: 1/1/07-12/31/07
Fiscal Year: 9/1/06-8/31/07
Account Budget: 100,000
Number of months for the account budget: 12

Since the Account budget date has a start date > than the fiscal year start date, I calculate the datediff between the Account budget start date (1/1/07) and the Fiscal Year end date (8/31/07). This calculates that 8 months of the Account Budget fall within the Fiscal year.

The query then calculates the following:
Account budget(100,000)/Number of Months(12)*datediff between Account Budget Start Date and Fiscal Year End Date(8) giving me a total revenue of $66,666.66 for the Fiscal Year.

I’m trying to find a way to move from a “fixed” fiscal year date so that I can project the revenues for a more extended period of time.

I hope this helps.

Here is the SQL for my query:

SELECT [tblActive Grants].[CUFS#], [tblGrant Funding Details].DirCost, [tblGrant Funding Details].IndirCost, IIf([start date]<#9/1/2006#,DateDiff("m",#9/1/2006#,[end date])+1,IIf([start date]>=#9/1/2006#,DateDiff("m",[start date],#8/31/2007#)+1,Null)) AS Months, Sum([dircost]/(DateDiff("m",[start date],[end date])+1)*[months]) AS DirectCosts, Sum([indircost]/(DateDiff("m",[start date],[end date])+1)*[months]) AS IndirectCosts
FROM ([tblActive Grants] INNER JOIN Investigators ON [tblActive Grants].FacultyID = Investigators.FacultyID) INNER JOIN [tblGrant Funding Details] ON [tblActive Grants].GrantID = [tblGrant Funding Details].[Grant ID]
WHERE ((([tblActive Grants].[Revised End Date])>#9/1/2006#) AND (([tblGrant Funding Details].[Start Date]) Between #9/1/2006# And #8/31/2007#)) OR ((([tblActive Grants].[Revised End Date])>#9/1/2006#) AND (([tblGrant Funding Details].[End Date]) Between #9/1/2006# And #8/31/2007#))
GROUP BY [tblActive Grants].[CUFS#], [tblGrant Funding Details].DirCost, [tblGrant Funding Details].IndirCost, IIf([start date]<#9/1/2006#,DateDiff("m",#9/1/2006#,[end date])+1,IIf([start date]>=#9/1/2006#,DateDiff("m",[start date],#8/31/2007#)+1,Null)), DateDiff("m",[start date],[end date])+1;