Deferred Revenue Formulas

[amartino44]

This might be hard to explain without a spreadsheet. I am creating a model for deferred revenue for 2 years (24 months of sales). Sales from each month earn out equally over the course of twelve months. Column A has the sales amount for each month for the first year, and column b has has the sales amount for each month for the second year. Then across the columns (starting in column C) are the months of "earned revenue" that correspond to the sales amounts in column A and B. I'm trying to figure out a formula that will calculate the earned premium in each month based on sales. The reason why this is complicated is that, as an example, in January of year 2, the values in the first row (associated with January) need to be calculated as a % of Year 2 January sales but the values in the second row (associated with February) will still need to be calculated off of sales from Feb. YEAR 1. I'm not sure how to create a formula that will do this.

 

[CROY1985]

Hi

I have done something similar to this. Without seeing your data it's hard to tailor it to your situation but here is a simplified example of what I've done.

Column A, Row 4 downward : Month Income Is Recognised (1,2,3,4 etc)
Column B, Row 4 downward : % of income recognised after each month (20%,30%,40%,5% etc up to 100%)
Column C , Row 1 going across: actual month
Column C, Row 2 going across: Total Sales by actual month
Column C, Row 4 down and across: recognised Income

Here it is in table form:

	Month	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13
	Total Sales	100	200	300	300	300	300	300	300
Recognised Sales	%
1	20%	20	40	60	60	60	60	60	60
2	30%		30	60	90	90	90	90	90
3	40%			40	80	120	120	120	120
4	5%				5	10	15	15	15
5	5%					5	10	15	15

<tbody>
</tbody>

The formula i use in C4:

=OFFSET(C$2,0,ROW(C$4)-ROW())*$B4

You can copy this down many rows and across many columns without having to change any references.

You can then add totals at the bottom of each column, which will show the total income recognised each month.

Also, if you pull this forward to the future you can use it as a forecasting tool.



Hope this is of use and can be applied to your data!

 

[amartino44]

This works, but when you drag down the formula there are errors. I used an iferror but sometimes it results in zero.

 

[jwittenbrook]

This is exactly what I need but I'm not able to copy the formula down. Please help!

 

[AkaTrouble]

copy the formula

select all the cells you want it in

then push CTRL + V keys together pasting formula in all cells

 

[jwittenbrook]

Thanks! That works, but doesn't allow me to add down because I get errors.

		Jan	Feb	Mar	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Total sales	62,367	62,367	62,367	153,107	153,107	153,107	10,000	10,000	10,000	10,000	10,000	10,000
Recognized sales
1	8%	5,197	5,197	5,197	12,759	12,759	12,759	833	833	833	833	833	833
2	8%	0	5,197	5,197	5,197	12,759	12,759	12,759	833	833	833	833	833
3	8%	#VALUE!	0	5,197	5,197	5,197	12,759	12,759	12,759	833	833	833	833
4	8%	#REF!	#VALUE!	0	5,197	5,197	5,197	12,759	12,759	12,759	833	833	833
5	8%	#REF!	#REF!	####	0	5,197	5,197	5,197	12,759	12,759	12,759	833	833
6	8%	#REF!	#REF!	#REF!	#####	0	5,197	5,197	5,197	12,759	12,759	12,759	833
7	8%	#REF!	#REF!	#REF!	#REF!	#####	0	5,197	5,197	5,197	12,759	12,759	12,759
8	8%	#REF!	#REF!	#REF!	#REF!	#REF!	#####	0	5,197	5,197	5,197	12,759	12,759
9	8%	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#VALUE!	0	5,197	5,197	5,197	12,759
10	8%	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#VALUE!	0	5,197	5,197	5,197
11	8%	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#VALUE!	0	5,197	5,197
12	8%	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#VALUE!	0	5,197

<colgroup><col span="4"><col><col span="3"><col span="6"></colgroup><tbody>
</tbody>

 

[CROY1985]

try this:
=IFERROR(OFFSET(C$2,0,ROW(C$4)-ROW())*$B7,0)