Deferred Revenue Recognition

shazam17

New Member
Joined
Feb 22, 2019
Messages
1
Hello! Seeking some urgent help. I have data by month 1,2,3.....across over 60 months. I need to defer the recognition by n (number of months) fixed throughout the term but can be changed and the entire sequence is corrected to reflect this. As an example all data in months 1,2,3 need to be deferred by 2 months then data in month 1 would then fall into month 3 data in month 2 then falls in month 5 and so forth and needs to stop when the total is reached.


12 24
Month1234567891011121314151617181920212223242526272829303132 Y1 Y2
data233545667845919004567898 2,563 -
WHAT NEEDS TO HAPPEN233545667845919004567898 517 2,046
Revenue Delay2Mths

<colgroup><col><col span="6"><col><col><col><col span="23"><col><col><col></colgroup><tbody>
</tbody>
12 24

<colgroup><col><col span="6"><col><col><col><col span="23"><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
Hi Shazam and welcome to the Forum

Now that's a deferred revenue recognition methodology I've never seen in nearly 40 years of accounting!!:eek:

And it took some hard thinking and experimentation to implement in Excel - but I think I've got it.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
3
Revenue Delay (Mths)
2
4
Month No.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Y1
Y2
5
Actual revenue
23
354
56
6
78
45
91
900
45
67
898
2,563​
-​
6
7
Recognise in Mth
3​
5​
7​
9​
11​
13​
15​
17​
19​
21​
23​
25​
8
Recognised revenue:
-​
-​
23​
-​
354​
-​
56​
-​
6​
-​
78​
-​
45​
-​
91​
-​
900​
-​
45​
-​
67​
-​
898​
-​
-​
-​
-​
-​
-​
-​
-​
-​
517​
2,046​

<tbody>
</tbody>
Sheet: .

<tbody>
</tbody>

C7 =C4+COUNT( $B5:B5) + $C$3
C8 = IFERROR( INDEX( $C$5:$AH$5, MATCH( C4, $C$7:$AH$7, 0 ) ), 0 )

Please advise if this achieves your objective.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,819
Messages
5,525,080
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top