I need help to create monthly projections

[lnn86]

hello excel gurus,

I have a number of contracts with different start dates, terms (monthly) and $$. What I would like to do is to have monthly projections of my revenue as I continuously accumulate more contracts. How do I go about this? Please help. Thanks in advance!

 

[cyrilbrd]

Hi lnn86,

In order to get more advise, you may consider posting a sample of your data as well as how you would like to data to be summarized.

Maybe a pivot table will be up to the task for your specific query?

click this link re how to post sample.

 

[lnn86]

I've attach the file I am working with. I would like to be able to calculate my monthly revenue at any given date based on the contracts expiry date. Any help would be greatly appreciated

http://www.2shared.com/file/_HzYZBV8/excel-samplexls.html

 

[cyrilbrd]

kindly explain row 30. Would you like the data to be linked to the month as stated in ColumnA? Mar and Apr share the same formula, is this normal?

 

[lnn86]

row 30 is just what the answer should be. Mar and April is the same because there we not that many contracts. I'm looking for a function to be able to calculate my monthly income as i periodically gain more contracts with various terms/durations.

I hope you understand what i'm trying to say

 

[cyrilbrd]

if data as follows:

Start Date	Fee	Term	Expiry	month	revenue
3-Mar-11	308.00	12	3-Mar-12	3/1/11	25.66666667
4-May-11				5/1/11	
6-May-11	200.00	12	6-May-12	5/1/11	16.66666667
7-May-11	100.00	12	7-May-12	5/1/11	8.333333333
18-May-11				5/1/11	
19-May-11				5/1/11	
24-May-11				5/1/11	
25-Jan-11				1/1/11	
30-May-11	149.00	12	30-May-12	5/1/11	12.41666667
10-Jun-11				6/1/11	
29-Jul-11	200.00	12	29-Jul-12	7/1/11	16.66666667
16-Sep-11	239.88	12	16-Sep-12	9/1/11	19.99
22-Sep-11	239.88	12	22-Sep-12	9/1/11	19.99
26-Sep-11	240.00	12	26-Sep-12	9/1/11	20
6-Oct-11				10/1/11	
17-Oct-11	285.00	12	17-Oct-12	10/1/11	23.75
17-Oct-11	239.88	12	17-Oct-12	10/1/11	19.99
18-Oct-11	290.00	12	18-Oct-12	10/1/11	24.16666667
1-Nov-11	800.00	12	1-Nov-12	11/1/11	66.66666667
2-Nov-11	599.70	36	2-Nov-14	11/1/11	16.65833333
14-Nov-11				11/1/11	
16-Dec-11	240.00	12	16-Dec-12	12/1/11	20
16-Dec-11	450.00	12	16-Dec-12	12/1/11	37.5
20-Dec-11	221.24	12	20-Dec-12	12/1/11	18.43666667
23-Dec-11	350.00	12	23-Dec-12	12/1/11	29.16666667

lower you can have:

Jan-11	Feb-11	Mar-11	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11
0.00	0.00	25.67	25.67	63.08	63.08	79.75	79.75	139.73	207.64	290.96	396.07

with in A30:
=IFERROR(SUMIF($E$2:$E$26,"<="&A29,$F$2:$F$26),"")

copied to the right till Dec-11
Would that work for you?