Need help with formula to forecast revenue
 

{Excel 2003}
(Posted this yesterday and did not get any help)

Each Row is revenue by customer
Each column is a month {jan-07, feb-07, mar-07}
We have historical revenue for each month (TTM: last 12 months).
In a separate column we have an identifier when the contract changes
{JUL-07}.
(Different for each customer)
In another column we have a percent inc/dec/ expected over existing {10%} or
{-10%}
The output would be in each month after the contract expired

How do I set it up so in the cell where I want an output. 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?

pro rate - probably an inaccurate 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.


Example:

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.

Need help with formula to forecast revenue
 

=IF($P3B2,B3,B3*$Q3)

Col 2 - 13 are Jan-Dec (data is 1 - 12 in the cells)
P3 has the month# of the renewal (1-12).
Q3 is a percent of increase (1.1 for a 10% increase, .9 for 10% decrease)

The above formula would be for January. You would just copy it right for
the other 11 months.


"dj479794" wrote:

{Excel 2003}
(Posted this yesterday and did not get any help)

Each Row is revenue by customer
Each column is a month {jan-07, feb-07, mar-07}
We have historical revenue for each month (TTM: last 12 months).
In a separate column we have an identifier when the contract changes
{JUL-07}.
(Different for each customer)
In another column we have a percent inc/dec/ expected over existing {10%} or
{-10%}
The output would be in each month after the contract expired

How do I set it up so in the cell where I want an output. 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?

pro rate - probably an inaccurate 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.


Example:

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.

Need help with formula to forecast revenue
 

Great! Thanks.

"Mike H." wrote:

=IF($P3B2,B3,B3*$Q3)

Col 2 - 13 are Jan-Dec (data is 1 - 12 in the cells)
P3 has the month# of the renewal (1-12).
Q3 is a percent of increase (1.1 for a 10% increase, .9 for 10% decrease)

The above formula would be for January. You would just copy it right for
the other 11 months.


"dj479794" wrote:

{Excel 2003}
(Posted this yesterday and did not get any help)

Each Row is revenue by customer
Each column is a month {jan-07, feb-07, mar-07}
We have historical revenue for each month (TTM: last 12 months).
In a separate column we have an identifier when the contract changes
{JUL-07}.
(Different for each customer)
In another column we have a percent inc/dec/ expected over existing {10%} or
{-10%}
The output would be in each month after the contract expired

How do I set it up so in the cell where I want an output. 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?

pro rate - probably an inaccurate 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.


Example:

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.