Split Revenue Into Monthly Splits By Contract Period

[ooch1]

Hello,

I'm currently trying to create a query which enables me to show the forecast monthly revenue, based upon the forecast annual Revenue received.

For Example

If we gain a contract starting in Jan04 and ending in Dec04 and total annual revenue is 120k, then i would like to split this 120k into 10k splits for the year. Shown in a similar format to:
Jan04 Feb04.....
Revenue 10k 10k

I can obviously acheive the monthly value ok, but cannot work out how to get the spread of revenue over the contract months???

My current thought process is that to maybe calculate a daily rate and then maybe calculate a total based on the month end date, and then minus the total up until that point. This would get quite messy for each months calculation.

...I look forward to the replies

OOch

 

[M8KWR]

My thought, is that you could have insert some fields into a table

Revenue = 120
Months = 12
Monthly Revenue = 120 / 12 = 10
End Date = 31/12/01

You may have some of these field already. I would store the Monthly Revenue in a seperate field, this way you can call on it, without having to do calculations all the time. There would be only one, at the point of when the data is entered.

Then you could say If the end date is less then the report you are running, then it does not include the value, but if it is then include the Monthly Revenue Value.

I hope this helps, let me know your thoughts.