Revenue Recognition

[barkeep15]

Hi

I am trying to build a revenue recognition formula within Excel. It's for a services company which can only consume service revenue at $40,000 per month.

So for a deal of $100,000 the revenue would be recognized as $40k in month 1, $40k in month 2 and then $20k in month 3. So the inputs we have are the date the deal is signed (the month the service revenue begins to be realized) and the total services revenue.

There are multiple deals all of different sizes but can only be recognized $40k per month. Each would start in the month the deal is signed.

Any suggestions?

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G	H
1	Revenue	Month1	Month2	Month3	Month4	Month5	Month6	Month7
2	100000	40000	40000	20000	0	0	0	0
3	70000	40000	30000	0	0	0	0	0
4	10000	10000	0	0	0	0	0	0
5	260000	40000	40000	40000	40000	40000	40000	20000
6	90000	40000	40000	10000	0	0	0	0
Sheet9 (2)

Cell Formulas
Range	Formula
B2	=MAX(0,MIN(40000,(($A2/40000)-(COLUMN(A1)-1))*40000))

 

[barkeep15]

Thanks for that! Amazing. How would I trigger the start month to differ between contracts? So I have a contract value and a contract start month. Multiple contracts with different values and start months.

Thanks again!

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Month	Revenue	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17
2	Mar-16	100000	0	0	40000	40000	20000	0	0	0	0	0	0	0	0
3	Aug-16	70000	0	0	0	0	0	0	0	40000	30000	0	0	0	0
4	Jan-16	10000	10000	0	0	0	0	0	0	0	0	0	0	0	0
5	Apr-16	260000	0	0	0	40000	40000	40000	40000	40000	40000	20000	0	0	0
6	Nov-16	90000	0	0	0	0	0	0	0	0	0	0	40000	40000	10000
Sheet9 (2)

Cell Formulas
Range	Formula
C2	=IFERROR(40000*MEDIAN(($B2/40000)-(DATEDIF($A2,C$1,"M")),0,1),0)

 

[barkeep15]

OMG thank you so much! You've made my night!