Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to pro rate values by date?
{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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to pro rate values by date?
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? -- Regards, Fred "dj479794" wrote in message ... {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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to pro rate values by date?
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" wrote: 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? -- Regards, Fred "dj479794" wrote in message ... {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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to pro rate values by date?
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(contractchangecolumn(currentcell),oldam ount,oldamount*(1+percentchange) Hope this helps. -- Regards, Fred "dj479794" wrote in message ... 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" wrote: 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? -- Regards, Fred "dj479794" wrote in message ... {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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on RATE()-like method for non-straightline values | Excel Discussion (Misc queries) | |||
Prime rate/Liber rate into sheet automatically? | Excel Discussion (Misc queries) | |||
Real RATE of return using =RATE illusive, inflation adjusted inflo | Excel Worksheet Functions | |||
How do I convert date values to non-date format e.g. 01-06-78? | Excel Discussion (Misc queries) | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions |