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>

 

[asad]

Does this solve your problem?

Excel Workbook
	A	B	C
1	Start Date	1/08/2009	1/07/2009
2	End Date	1/09/2009	15/12/2009
3	Revenue	$ 100,000.00	$100,000.00
4	Rev Per Day	$ 3,225.81	$ 598.80
Sheet1

 

[Bill Rockenbach]

Enter this formula in the day one column and drag accross. format the cells as numeric

=Round($D$2/($C$2-$B$2),0)

you may have to adjust the last column if you need the total of all the days to equal 100,000

 

[Peter_SSs]

rmcshane

Welcome to the MrExcel board!

See if this is what you want. Note that my dates are in dd/mm/yyyy format.

Formula in E2 is copied across and down. There is still the issue, mentioned by Bill, of the amounts maybe not adding exactly to the value in column D as in row 3 of my example.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	Start	End	Revenue	30/07/2009	31/07/2009	01/08/2009	02/08/2009	03/08/2009	04/08/2009	05/08/2009	06/08/2009	07/08/2009
2	01/08/2009	05/08/2009	100,000			20,000	20,000	20,000	20,000	20,000
3	31/07/2009	02/08/2009	50,000		16,667	16,667	16,667
4
Spread Revenue

 

[rmcshane]

Thanks for the help. The formula is great.

 

[Trepide]

I have a similar problem. I'm attempting to apply the revenue across months, specifically accounting for the proper number of days in each month. (See below.) How would I revise the formula?


Spread Revenue

*

B

C

D

E

F

G

H

I

J

K

L

M

1

Start

End

Revenue

01/07/2009

01/08/2009

01/09/2009

01/10/2009

01/11/2009

01/12/2009

01/01/2010

01/02/2010

01/03/2010

2

01/08/2009

05/08/2009

100,000

*

*

20,000

20,000

20,000

20,000

20,000

*

*

3

31/07/2009

02/08/2009

50,000

*

16,667

16,667

16,667

*

*

*

*

*

4

*

*

*

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:71px;"><col style="width:64px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[Peter_SSs]

I have a similar problem. I'm attempting to apply the revenue across months, specifically accounting for the proper number of days in each month. (See below.) How would I revise the formula?

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.

Excel Workbook
	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
Spread Across Months

 

[Trepide]

This is exactly what I needed. Thanks!

 

[Peter_SSs]

This is exactly what I needed. Thanks!

Great! Thanks for letting us know.

 

[dsharp05]

I tried to copy your formula into an issue I am having but no luck. I'm sure it is something simple and you are the perfect person to figure this out for me!

The data below has columns of Expected Revenue, Recognize Date, Revenue Spread of Months. The columns after that are what I was trying to create to make any type of formula work correctly (like the one you posted above, but my issue may be edate??). I am trying to take the Expected Revenue dollars (col A) and spread it over the number of months (col C). For example, Expected revenue for January to December 2015 is $338K and I need to divide it evenly by 9 months. Sounds easy but this report changes monthly so I need formulas in the blank section to accomodate the changing months.

I hope you can help!

Dyan

01/2014</SPAN>

02/2014</SPAN>

03/2014</SPAN>

04/2014</SPAN>

05/2014</SPAN>

06/2014</SPAN>

07/2014</SPAN>

08/2014</SPAN>

09/2014</SPAN>

10/2014</SPAN>

11/2014</SPAN>

12/2014</SPAN>

01/2015</SPAN>

02/2015</SPAN>

03/2015</SPAN>

04/2015</SPAN>

05/2015</SPAN>

06/2015</SPAN>

07/2015</SPAN>

08/2015</SPAN>

09/2015</SPAN>

10/2015</SPAN>

11/2015</SPAN>

12/2015</SPAN>

Expected Revenue </SPAN>

Recognize Date</SPAN>

Revenue Spread</SPAN>

Recognize Date-Pivot</SPAN>

Recognize End Date-Test</SPAN>

Recognize End Date</SPAN>

Jan-14</SPAN>

Feb-14</SPAN>

Mar-14</SPAN>

Apr-14</SPAN>

May-14</SPAN>

Jun-14</SPAN>

Jul-14</SPAN>

Aug-14</SPAN>

Sep-14</SPAN>

Oct-14</SPAN>

Nov-14</SPAN>

Dec-14</SPAN>

Jan-15</SPAN>

Feb-15</SPAN>

Mar-15</SPAN>

Apr-15</SPAN>

May-15</SPAN>

Jun-15</SPAN>

Jul-15</SPAN>

Aug-15</SPAN>

Sep-15</SPAN>

Oct-15</SPAN>

Nov-15</SPAN>

Dec-15</SPAN>

338,000 </SPAN>

1/18/2015</SPAN>

9</SPAN>

01/2015</SPAN>

10/1/2015</SPAN>

10/2015</SPAN>

392,000 </SPAN>

4/16/2014</SPAN>

9</SPAN>

04/2014</SPAN>

1/1/2015</SPAN>

01/2015</SPAN>

950,000 </SPAN>

3/23/2014</SPAN>

9</SPAN>

03/2014</SPAN>

12/1/2014</SPAN>

12/2014</SPAN>

487,500 </SPAN>

4/30/2015</SPAN>

9</SPAN>

04/2015</SPAN>

1/1/2016</SPAN>

01/2016</SPAN>

635,000 </SPAN>

6/13/2014</SPAN>

9</SPAN>

06/2014</SPAN>

3/1/2015</SPAN>

03/2015</SPAN>

292,000 </SPAN>

9/5/2014</SPAN>

9</SPAN>

09/2014</SPAN>

6/1/2015</SPAN>

06/2015</SPAN>

244,000 </SPAN>

5/4/2014</SPAN>

9</SPAN>

05/2014</SPAN>

2/1/2015</SPAN>

02/2015</SPAN>

266,500 </SPAN>

3/29/2015</SPAN>

9</SPAN>

03/2015</SPAN>

12/1/2015</SPAN>

12/2015</SPAN>

450,000 </SPAN>

4/23/2014</SPAN>

9</SPAN>

04/2014</SPAN>

1/1/2015</SPAN>

01/2015</SPAN>

<TBODY>
</TBODY>