Spreading revenue over time

[rmcshane]

I am building an excel file to track revenue over time. We get paid at the end of a project but i want to spread the cash out over the life of the project. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Example:<o></o>
<o></o>
Start date (B2):8/1/09<o></o>
End date (C2): 9/1/09<o></o>
Revenue (D2): $100,000<o></o>
<o></o>
I have dates starting at 7/30/09 running across the top row (E1:ZZ1)<o></o>
<o></o>
I am trying to figure out a formula that I can drag across so that the project revenue will be divided by the number of days and put into the appropriate cell. So for the example above, $3,225 (100k/31 days) will be put in each cell under the corresponding dates between 8/1/2009 and 9/1/2009. If I change the dates, the formula will adjust accordingly. I am going to use a flat distribution (each day will have the same dollar amount).
<o></o>
Thanks for the help. I am really lost on this one. <o></o>

 

[Peter_SSs]

Welcome to the MrExcel board!

Not certain just what is in some of those date cells but see if this does what you want.
Formula in G3 is copied across & down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Expected Revenue	Recognize Date	Revenue Spread	Recognize Date-Pivot	Recognize End Date-Test	Recognize End Date	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15
3	338,000	18/01/15	9	Jan-15	1/10/15	Oct-15													37555.6
4	392,000	16/04/14	9	Apr-14	1/01/15	Jan-15				43555.6	43555.6	43555.6	43555.6	43555.6	43555.6	43555.6	43555.6	43555.6
5	950,000	23/03/14	9	Mar-14	1/12/14	Dec-14			105556	105556	105556	105556	105556	105556	105556	105556	105556
6	487,500	30/04/15	9	Apr-15	1/01/16	Jan-16
7	635,000	13/06/14	9	Jun-14	1/03/15	Mar-15						70555.6	70555.6	70555.6	70555.6	70555.6	70555.6	70555.6	70555.6
8	292,000	5/09/14	9	Sep-14	1/06/15	Jun-15									32444.4	32444.4	32444.4	32444.4	32444.4
9	244,000	4/05/14	9	May-14	1/02/15	Feb-15					27111.1	27111.1	27111.1	27111.1	27111.1	27111.1	27111.1	27111.1	27111.1
10	266,500	29/03/15	9	Mar-15	1/12/15	Dec-15
11	450,000	23/04/14	9	Apr-14	1/01/15	Jan-15				50000	50000	50000	50000	50000	50000	50000	50000	50000
Revenue Spread

 

[dsharp05]

You are brilliant! This works perfectly and you made my day. Thank you for responding so quickly..

Dyan

 

[Peter_SSs]

Glad it worked so well for you. Thanks for letting us know.

 

[sws75]

this is monthly but how can you spread it over weekly format

 

[Peter_SSs]

this is monthly but how can you spread it over weekly format

Welcome to the MrExcel board!

Just put weekly dates instead of monthly dates in the header row?

 

[klawrence]

I am trying to spread revenue over 12 months and then the spreadsheet switches to quarters. I don't know how to adjust the formula to work when I hit the quarters. Can anyone help with this?

Welcome to the MrExcel board!

Like this?

Row 1 Dates are the 1st of each month (eg 1/07/2009). I've formatted those dates to show only month & year.

Formula in D2 is copied across and down.

If this is not what you want, some more details, sample data and expected results would help.

Spread Across Months

	A	B	C	D	E	F	G	H	I	J	K
1	Start	End	Revenue	Jul-09	Aug-09	Sep-09	Oct-09	Nov-09	Dec-09	Jan-10	Feb-10
2	1/08/09	5/08/09	100,000		100,000.00
3	31/07/09	2/08/09	50,000	16,666.67	33,333.33
4	25/08/09	20/12/09	20,000		1,186.44	5,084.75	5,254.24	5,084.75	3,389.83

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(OR(D$1<=$A2-DAY($A2),D$1>EOMONTH($B2,0)),"",MAX(MIN(EDATE(D$1,1)-$A2,EDATE(D$1,1)-D$1,$B2-D$1+1,$B2-$A2+1),0)*$C2/($B2-$A2+1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4