Reporting on deferred revenue installments 1

[travisbrown]

I'm not sure exactly where to start, so bear with me as I try and explain.

I have a table of transactions for a quarterly magazine subscription.

With magazines, you have deferred revenue - i.e. you get $40 and owe the subscriber 4 issues (your liability). If it was done in the second quarter of 2006, you'd get $20 for 2006 and $20 for 2007. If it was a two year subscription, it would be deferred to installments over a few years.

I'm trying to figure out how to report on how much revenue from each transaction is allocated to each year for the duration of the subscription. I guess it needs to go in some kind of pivot table. Any ideas how to get started?

All I need in the end is the received and deferred revenue for each year for a given date range.

Below is what I think the relevant fields are. Any help is appreciated.

LIABILITY	TOTAL	CURRENCY	DATE	
4		12.5	CAD		5/19/2005	
4		12.5	CAD		5/19/2006	
4		12.5	CAD		2/19/2005	
4		12.5	CAD		4/5/2005	
4		12.5	CAD		5/29/2005	
8		44.95	CAD		5/9/2005	
4		12.5	CAD		8/12/2005	
4		12.5	CAD		6/19/2005	
4		12.5	CAD		1/20/2008

It's a little beyond anything I've had to do before, and maybe I need to hire a reporting genius. I'd usually do everything I need in SQL Server, but I think this is better suited to Excel.

 

[SkipVought]

hi,

make a table with dates by quarter, 3 quarter before the earliest subscription date, and one quarter longer than the latest subscription END DATE, using the DATE function like...
[tt]
F3: =DATE(YEAR(F2),MONTH(F2)+3,1)
[/tt]
where the starting date is in F2
[tt]
Dte
4/1/2004
7/1/2004
10/1/2004
1/1/2005
4/1/2005
7/1/2005
10/1/2005
1/1/2006
4/1/2006
7/1/2006
10/1/2006
1/1/2007
4/1/2007
7/1/2007
10/1/2007
1/1/2008
4/1/2008
7/1/2008
10/1/2008
1/1/2009
[/tt]
Change the column labled DATE to SubDATE.

Name your ranges, using Insert > Name > Create - Create names in TOP row.

This is the formula in the 1/1/2005 row in column G
[tt]
G6:
=SUMPRODUCT((SubDATE>=$F6)*(SubDATE<$F7)*(TOTAL/LIABILITY))
+SUMPRODUCT((SubDATE>=$F5)*(SubDATE<$F6)*(TOTAL/LIABILITY))
+SUMPRODUCT((SubDATE>=$F4)*(SubDATE<$F5)*(TOTAL/LIABILITY))
+SUMPRODUCT((SubDATE>=$F3)*(SubDATE<$F4)*(TOTAL/LIABILITY))
[/tt]


Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[travisbrown]

Hmm... I thought I sort of understood, but maybe not. Can you explain what this is giving me? I'm getting a Div by Zero error.

I've attached a link to what I've done so far.

Thanks Skip.

 

[SkipVought]

Your named ranges must ONLY only include rows with DATA, and most importantly rows where Liability is not ZERO or EMPTY.

Use the Select Current Region ICON...

Right-click the toolbar and select CUSTOMIZE.

Select the Commands Tab

Select Edit Category

Scroll down in the Commands to the very BOTTOM and drag the Select Current Region to any toolbar.

Use this tool to select all contiguous data, prior to Insert > Name > Create > Create names in top row.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

Ah, that fixed it. I really appreciate the help. I'll poke at it a bit more now.

The SUMPRODUCT is a little more complicated than I've used before. Can you explain what I'm looking at? I sort of understand the three back/one forward thing, but is this deferred or actual revenue?

 

[SkipVought]

formula 1 is the 1st quarter's deferred values, formula 2 the second quarter's etc.

It will be easier to see if you truncate the named ranges to encompass only ONE row of data.

I noticed that your data has LIABILITY other than 4. This is a problem, as there are only formulas for four quarters.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}