Excel question

  • Thread starter Thread starter TerryG
  • Start date Start date
T

TerryG

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.
 
Because I used a subject that already existed, my post was tacked onto
an existing post. So I'm reposting with a different subject.
 
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($B2>E$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,$D2/$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.
 
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
 
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($B2>F$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.
 
Thanks again, Dick. My guess is that Excel is having a problem with
"EOMONTH". I get a #NAME? error in the prorated month. I wish I had
the time to get up to speed on Excel. I must say I'm impressed.
Thanks again for the help.

Terry
 
Terry

That usually means that you don't have the Analysis Toolpack Add-in
installed.

If you don't want to install it, repalce this

EOMONTH(F$1,-1)

with this

DATE(YEAR(F$1),MONTH(F$1),0)

The zeroth day of the month is the last day of the previous month.
 
Dick,

Perfect!!! I just substituted your new string and the spreadsheet
works perfectly. I even tried different start dates going back to
last year, adding columns if needed, and it worked perfectly.

Thanks to you, I just scored big points with my wife ;-)

Terry
 
Back
Top