My wife has a need to linearly spread contract revenue across several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry

if by linearly, you mean each month gets the same amount, I would suggest
counting up the months for the contract period, then dividing that into the
contract value to get a monthly rate. Now, assign that amout to each of the
first 12 columns that fall in the contract period. You can then create an
array to represent 12 months for each of the successive years, and loop
through that array assigning the monthly rate to each one until the end of
the contract is reached/resources are exhausted. Then sum up by year and
assign to the remaining columns.

Of course, you could do this by doing some date math and figuring out the
proportion of a year that includes the contract period and taking that
proprotion of the total.

--
Regard,
Tom Ogilvy


"TerryG" wrote in message
om...
My wife has a need to linearly spread contract revenue across several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry

Because I used a subject that already existed, my post was tacked onto
an existing post. So I'm reposting with a different subject.


(TerryG) wrote in message . com...
My wife has a need to linearly spread contract revenue across several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry

Terry

Assuming: Your columns A:T are like this

name, startdate, duration (in months),amount,
1/31/04,2/29/04,3/31/04....12/31/05,12/31/06,...

Put this formula in E2 and fill across to P2, fill down for as many rows as
you need

=IF($B2E$1,0,IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY ($B2))E$1,$D2/$C2,0))

Put this formula in Q2 and fill across for your year columns, fill down for
as many rows as you need

=IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY($B2))Q$1,$D 2/$C2*12,$D2-SUM($E2:P2))

Make sure you date columns are the last day of the month/year. You can
format them to look however you want.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"TerryG" wrote in message
om...
Because I used a subject that already existed, my post was tacked onto
an existing post. So I'm reposting with a different subject.


(TerryG) wrote in message
. com...
My wife has a need to linearly spread contract revenue across several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry

Thanks for the great response, Dick. Your solution is close to what she
needs. Is there any way to pro-rate the revenue for the month the contract
starts? E.G., if the contract starts on the 15th of the month, ~ half the
monthly rate wold accrue. This is the part that we couldn't figure out.

Terry

--
v/r
Carol & Terry Garvey
"Dick Kusleika" wrote in message
...
Terry

Assuming: Your columns A:T are like this

name, startdate, duration (in months),amount,
1/31/04,2/29/04,3/31/04....12/31/05,12/31/06,...

Put this formula in E2 and fill across to P2, fill down for as many rows
as
you need

=IF($B2E$1,0,IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY ($B2))E$1,$D2/$C2,0))

Put this formula in Q2 and fill across for your year columns, fill down
for
as many rows as you need


=IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY($B2))Q$1,$D 2/$C2*12,$D2-SUM($E2:P2))

Make sure you date columns are the last day of the month/year. You can
format them to look however you want.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"TerryG" wrote in message
om...
Because I used a subject that already existed, my post was tacked onto
an existing post. So I'm reposting with a different subject.


(TerryG) wrote in message
. com...
My wife has a need to linearly spread contract revenue across several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry

Terry

Add a new column E (before January), leave it blank and hide it. Change the
formula under Jan to

=IF(AND(MONTH($B2)=MONTH(F$1),YEAR($B2)=YEAR(F$1)) ,($D2/$C2)*(F$1-$B2)/(F$1-
EOMONTH(F$1,-1)),IF($B2F$1,0,IF(DATE(YEAR($B2),MONTH($B2)+$C2, DAY($B2))F$1
,$D2/$C2,$D2-SUM($E2:E2))))

and fill across and down as needed.

Since we're prorating the first month, the last month is kind of a
catch-all. We need that blank E column as an anchor.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Terence Garvey" wrote in message
ink.net...
Thanks for the great response, Dick. Your solution is close to what she
needs. Is there any way to pro-rate the revenue for the month the
contract
starts? E.G., if the contract starts on the 15th of the month, ~ half the
monthly rate wold accrue. This is the part that we couldn't figure out.

Terry

--
v/r
Carol & Terry Garvey
"Dick Kusleika" wrote in message
...
Terry

Assuming: Your columns A:T are like this

name, startdate, duration (in months),amount,
1/31/04,2/29/04,3/31/04....12/31/05,12/31/06,...

Put this formula in E2 and fill across to P2, fill down for as many rows
as
you need

=IF($B2E$1,0,IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY ($B2))E$1,$D2/$C2,0))

Put this formula in Q2 and fill across for your year columns, fill down
for
as many rows as you need



=IF(DATE(YEAR($B2),MONTH($B2)+$C2,DAY($B2))Q$1,$D 2/$C2*12,$D2-SUM($E2:P2))

Make sure you date columns are the last day of the month/year. You can
format them to look however you want.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"TerryG" wrote in message
om...
Because I used a subject that already existed, my post was tacked onto
an existing post. So I'm reposting with a different subject.


(TerryG) wrote in message
. com...
My wife has a need to linearly spread contract revenue across
several
columns, each representing a period of time. The 1st 12 columns are
the months of the year (2004). The ensuing columms are calendar
years, 2005, 2006, 2007, etc. The contracts have varying known
start
dates and varying known periods of performance.

I'm trying to help her out. She spent the entire weekend playing
with
the math (while I was painting the house) but no single solution
correctly handled all situations. I've got to believe there is an
easier way to do this (I'm a former software type). Does anyone out
there have any ideas?

Thanks for all responses.

Terry