How to pro rate values by date?

[dj479794]

{Excel 2003}

Each Row is revenue by customer
Each column is a month {jan-07, feb-07, mar-07}
We have historical revenue for each month.
In a seperate column we have an identifier when the contract changes {JUL}.
(Different for each customer)
In another column we have a percent inc/dec/ expected over existing {10%}

How do I set it up so in the cell where I want an ouput. I have a formula
that says if this month or greater fill in a value that is x% more/less than
the average of the past revenue amounts?

 

[Fred Smith]

This should be straightforward, but we need more information to get you a valid
formula.

What do you mean by pro rate?
Is the contract change month a date in mmm format, or 3 alpha characters?
Is an increasing percent a positive number, and a decreasing percent a negative
number?
What months are used to determine the "average of the past revenue amounts"?

What output do you want for each customer -- a single amount? or projected 12
months?

 

[dj479794]

pro rate - probably an inacurate way to describe what I am doing. This is
more like forecasting. We know a contract runs till end of June. We think we
will renew at 10% higher than the existing contract. The contract will begin
in July and I want to capture it at the new higher rate from July to the end
of December. Each row (customer) contract could be up for renew in different
months.

date format

10% or -10%

months used woul be TTM. (trailing 12 months of revenue recorded)

Output would be in each month left for that calendar year

IF i have
Jan = 2,000
feb = 2,000
mar = 2,000
contract is scheduled to renew in April
April = {Insert formula}
May = {Insert formula}
...........
Amount for each month.

 

[Fred Smith]

This still isn't enough to come up with something definitive, but I hopefully I
can point you in the right direction.

1. The new projected contract amount will be: =Oldamount * (1 + PercentChange)
2. You will likely want an absolute address for PercentChange, so that when you
copy, its address doesn't change.
3. To determine when the contract changes, you are going to have to compare the
month number to something. If your data is in columns 1:12, then you can compare
it to the column number. In this case you would have something like:
=if(month(contractchange>column(currentcell),oldamount,oldamount*(1+percentchange)

Hope this helps.