Distribution of values over multiple quarters

[Guest]

For financial and budgeting purposes, we want to allocate a total project
value across the 4 quarters of the year based on the start and end date. I'm
looking at calculating either a weekly or daily average based on the date
range but I'm stumped as to how to best distribute the amount evenly over the
affected quarters. We have about 500 projects for FY07. Amounts and dates for
a specific project often change over time as modifications are made to
contracts.

For example, for a project spanning 3/1/07 to 7/20/07 with a value of
$20,000, we would expect to see a distribution of $4,000 for QTR1, $13,000
for QTR2, and $3,000 for QTR3. Distribution to an exact dollar or day is not
necessary as actual activity will not be evenly distributed.

Thanks in advance for your help.

 

[Guest]

Sounds like you need project planning software like Primavera Project Planner
or MP*M by Micro-Frame Technologies, Inc.

 

[Guest]

Thank you for the suggestion. Our actual project planning needs are simple
and working well within Access. Looking for a calculation, function, or
approach in a query, programming, etc. that would make it relatively
straightforward to calculate the distribution of $$ over time.

Thanks for your time.

 

[Michael Gramelspacher]

@microsoft.com>, (e-mail address removed)
says...

Thank you for the suggestion. Our actual project planning needs are simple
and working well within Access. Looking for a calculation, function, or
approach in a query, programming, etc. that would make it relatively
straightforward to calculate the distribution of $$ over time.

Thanks for your time.

See if this gets you anywhere. You can also look at this to
possibly get a view:
http://www.psci.net/gramelsp/temp/SampleDB2.Zip
(lots of line wrapping)

If it actually works, it is mostly just due to luck.

CREATE TABLE FiscalQuarters
(fiscal_year INTEGER NOT NULL,
fiscal_quarter INTEGER NOT NULL,
quarter_start_date DATETIME NOT NULL,
quarter_end_date DATETIME NOT NULL,
PRIMARY KEY (fiscal_year, fiscal_quarter));

CREATE TABLE Projects
(project_id TEXT (30) NOT NULL PRIMARY KEY,
project_name TEXT (50) NOT NULL,
project_value DECIMAL (12,4) NOT NULL,
project_start_date DATETIME NOT NULL,
project_end_date DATETIME NOT NULL);

Query: Contract Daily Value
---------------------------------------
SELECT Projects.project_id,
Projects.project_name,
Projects.project_value,
DATEDIFF("d",[Projects.project_start_date],
[Projects.project_end_date]) + 1 AS contracted_days,
[Projects.project_value] / DATEDIFF("d",
[Projects.project_start_date],[Projects.project_end_date]) + 1
AS contract_daily_value
FROM Projects;

Query: Contract Days By Fiscal Quarter
---------------------------------------
SELECT b.project_id,
b.project_name,
b.project_start_date,
b.project_end_date,
a.fiscal_year,
a.fiscal_quarter,
SUM(((DATEDIFF("d",IIF(.[project_start_date] < [a].
[quarter_start_date],
[a].[quarter_start_date],.
[project_start_date]),
IIF(.[project_end_date] > [a].
[quarter_end_date],
DATEADD("d",1,[a].
[quarter_end_date]),.[project_end_date]))))) AS Days
FROM Projects AS b,
FiscalQuarters AS a
WHERE (((b.project_start_date) <= [a].[quarter_end_date])
AND ((b.project_end_date) >= [a].
[quarter_start_date]))
GROUP BY b.project_id,b.project_name,b.project_start_date,
b.project_end_date,a.fiscal_year,a.fiscal_quarter;

Query: Contract Value BY Fiscal Quarter
---------------------------------------
SELECT a.project_id,
a.project_name,
a.project_start_date,
a.project_end_date,
a.fiscal_year,
a.fiscal_quarter,
a.Days,
b.contract_daily_value,
a.[Days] * b.[contract_daily_value] AS FY_contract_value
FROM [Contract Days by Fiscal Quarter] AS a
INNER JOIN [Contract Daily Value] AS b
ON a.project_id = b.project_id;