I am developing a 5-year, monthly revenue projection about a set of contracts (a few thousand).
In Column B, I have the Start Date in the form MM/DD/YYYY
In Column C, I have the End Date also in the Form MM/DD/YYYY
In Column D, I have Expected Renewal in the form "Y" or "N"
In Column E, I have the current Per Unit Rate in the form $XXX.XX
In Columns F-Q (F is Jan, G is Feb, H is Mar, etc) I have the average #Units for each Calendar Month in the form XXX.XX
The contracts are all 5 year in duration but expire at different times, one contract might expire tomorrow because it has already been in place for almost 5 years; another contract if signed today would expire 5 years from today.
On each 1 year anniversary of a contract, the Per Unit Rate increases by a certain %. So, if the % increase put into place for 2011 is 5%, a contract signed on February 4, 2010 with a Per Unit Rate of $100 would in February 2011 have its Per Unit Rate increase to $105.
No matter what day of the month a contract is signed on, whether the 1st or the 28th, the Rate increase will be effective for the entire anniversary month until the next anniversary month.
All contracts will experience the same % increase to their Per Unit Rate in a given year. Therefore, a contract with a January 2011 anniversary would have its Per Unit Rate increase 5% in January 2011 and a contract with a December 2011 anniversary would have its Per Unit Rate increase 5% in December 2011.
So, to make a long story short, in the next 5 years, in each monthly period, I need to be able to do the following for each contract:
1. Check Column C to see if contract has Expired.
2. If contract has not expired, move on to step 3. If it has expired, check Column D to see whether Expected Renewal is "Y" or "N".
If "N", return zero.
If "Y", move on to step 3.
3. Check Column B to see if the current month is a 1-year anniversary of the Contract start date.
If current month is a 1-year anniversary month, apply the annual Per Unit Rate % increase to the Per Unit Rate used in the previous month, then multiply by the same month Average # Units from columns F-Q.
If current month is not a 1-year anniversary month, use Per Unit Rate used in the previous month multiplied by the same month Average # Units from columns F-Q.
I know that's quite a bit to digest; I am just trying to figure out the best way to approach this and keep it flexible enough to be able to add in new contracts later on.
Please note that I have zero VBA ability; don't really have the time to start learning it now...
Your advice much appreciated!
In Column B, I have the Start Date in the form MM/DD/YYYY
In Column C, I have the End Date also in the Form MM/DD/YYYY
In Column D, I have Expected Renewal in the form "Y" or "N"
In Column E, I have the current Per Unit Rate in the form $XXX.XX
In Columns F-Q (F is Jan, G is Feb, H is Mar, etc) I have the average #Units for each Calendar Month in the form XXX.XX
The contracts are all 5 year in duration but expire at different times, one contract might expire tomorrow because it has already been in place for almost 5 years; another contract if signed today would expire 5 years from today.
On each 1 year anniversary of a contract, the Per Unit Rate increases by a certain %. So, if the % increase put into place for 2011 is 5%, a contract signed on February 4, 2010 with a Per Unit Rate of $100 would in February 2011 have its Per Unit Rate increase to $105.
No matter what day of the month a contract is signed on, whether the 1st or the 28th, the Rate increase will be effective for the entire anniversary month until the next anniversary month.
All contracts will experience the same % increase to their Per Unit Rate in a given year. Therefore, a contract with a January 2011 anniversary would have its Per Unit Rate increase 5% in January 2011 and a contract with a December 2011 anniversary would have its Per Unit Rate increase 5% in December 2011.
So, to make a long story short, in the next 5 years, in each monthly period, I need to be able to do the following for each contract:
1. Check Column C to see if contract has Expired.
2. If contract has not expired, move on to step 3. If it has expired, check Column D to see whether Expected Renewal is "Y" or "N".
If "N", return zero.
If "Y", move on to step 3.
3. Check Column B to see if the current month is a 1-year anniversary of the Contract start date.
If current month is a 1-year anniversary month, apply the annual Per Unit Rate % increase to the Per Unit Rate used in the previous month, then multiply by the same month Average # Units from columns F-Q.
If current month is not a 1-year anniversary month, use Per Unit Rate used in the previous month multiplied by the same month Average # Units from columns F-Q.
I know that's quite a bit to digest; I am just trying to figure out the best way to approach this and keep it flexible enough to be able to add in new contracts later on.
Please note that I have zero VBA ability; don't really have the time to start learning it now...
Your advice much appreciated!
Last edited:
