Revenue forecasting based on contracts

[ericrhuggenaath]

Hello Excel forum, i need some help.

I have an excell file with contracts with different start dates, amount to be received (revenue) each month and number of months (end dates are not known but could probably be calculated)
What i would like to see is what the revenue income will be for each of the coming months for the coming 2 years

for example:

a B C D E F G H
debtor # Name total amm due paym each month number of months diff. first paym diff. last payment start date paymts
1 bla bla 10.000 2000 5 0 0 1-1-2015
2 BlaaBlaa 900 100 9 0 0 30-3-2015
3 Mami 7191.20 1000 8 191.20 0 27-02-2015


Expected Rev:
Jan: 2000.00
Feb: 191.20 + 2000 = 2191.20
Mach: 1000 + 100 + 2000 = 3100.00



I was thinking of modifying the file so that the diff. first / last payments have a certain date and that the number of months column will only refer to the CONSTANT AMOUNT to
be received each month.

Please help... Kind of time sensitive.

[simarui]

it is a lot easier to help if you upload a sample workbook rather than typing out your fake data in the question itself.

the answer will likely be something using sumifs()... it's hard to follow your columns as displayed above, but i'd suggest adding an end date in column I and using specific dates for your "month" field (which can be formatted to just show the month value) then your formula would look something like

Please Login or Register  to view this content.

(untested)

if that doesn't work, upload a sample.