Spreading Total Revenue Evenly Over Time

[SheCeo]

Hello,

I'm sure this can be done pretty easily but I can't seem to come up with a simple way to do this.

Sales Amount = Amount ($1,500)
Number of Sales (can vary depending on current month sales and prior month sales)
# of Payments = 6 (same # of payments for all sales)

What I'd like to do is calculate the total amount of sales each month (Jan - Dec) but spread the amount evenly given the number of sales made in the current month and the previous months.

For example, if 1 new sale is made in January (that amount would be included for 6 months), 2 new sales in February (would show 2 new sales and previous amount for January) and no sales in March (would show previous sales for Jan and Feb).

The outcome would be the following by month: $1,500 January, $4500 February, $4,500 in March

I want a formula that will calculate the total but also take into a count that each sale is to be spread over 6 months.

Any help in solving this is greatly appreciated!

 

[Marcelo Branco]

Maybe something like this...

Book1
	A	B	C	D	E
1	Month	Sales	Total		Sales Amount
2	Jan	1	1500		1500
3	Feb	2	4500
4	Mar		4500
5	Apr	1	6000
6	May		6000
7	Jun		6000
8	Jul		4500
9	Aug		1500
10	Sep		1500
11	Oct		0
12	Nov		0
13	Dec		0
Plan2

Cell Formulas
Range	Formula
C2:C13	C2	=SUM(B2:INDEX(B$2:B$13,MAX(1,ROWS(C$2:C2)-5)))*E$2

Hope this helps

M.

 

[SheCeo]

Maybe something like this...

Book1
	A	B	C	D	E
1	Month	Sales	Total		Sales Amount
2	Jan	1	1500		1500
3	Feb	2	4500
4	Mar		4500
5	Apr	1	6000
6	May		6000
7	Jun		6000
8	Jul		4500
9	Aug		1500
10	Sep		1500
11	Oct		0
12	Nov		0
13	Dec		0
Plan2

Cell Formulas
Range	Formula
C2:C13	C2	=SUM(B2:INDEX(B$2:B$13,MAX(1,ROWS(C$2:C2)-5)))*E$2

Hope this helps

M.

Thank you so much! I'll definitely try that.