+ Reply to Thread
Results 1 to 12 of 12

License Revenue Recognition model with Renewals

  1. #1
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    License Revenue Recognition model with Renewals

    Hi, I am trying to build a revenue recognition/renewal model as attached.

    It supposed to spread the Total Price over the Start and End dates.
    As an example, I highlighted row 15. Here, the Total Price is 1200. That first should be multiplied by the appropriate renewal rate of 80% and then be spread over the start and end date.
    However, at the end of the end date, the assumption is that the license is again renewed. Therefore the total of 1200*.8 *.8 should be spread over the start and end months as the first term. This should persist through perpetuity as long as there is a renewal assumption.

    Is there a clearer way to model this/equation that would work? Ive been spinning my head. Should we do if statements with 3 scenarios? This would be applied to a much larger data set. Thanks again for your help.
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 12-27-2019 at 03:38 AM. Reason: Removed urgent from title

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    I suspect that you have oversimplified your samples.

    Every start date is the first of the month. Is it?

    Every end date is the end of a month. Is it?

    You stated that renewals would normally follow on year after year, but it is NOT explicitly a rule. Is it?

    I have assumed for safety's sake that the answer to all of those Q's is NO. Therefore the formula is a bit longer.

    =IFERROR(IF(M$14<$D15,""+N("Pre-start"),
    IF(EOMONTH($D15,0)=M$14,(M$14-$D15+1)*$H15+N("Start month"),
    IF(AND(LOOKUP(2,1/($I15:$L15>0),$I$14:$L$14)+1=YEAR(M$14),MONTH(M$14)=MONTH($E15)),DAY($E15)*$H15+N("Non-renewal end month"),
    IF(M$14>DATE(LOOKUP(2,1/($I15:$L15>0),$I$14:$L$14)+1,MONTH($E15)+1,1),""+N("Post-end month"),
    $H15*M$12+N("Normal month"))))),"")

    copied across and down. The in-formula coments (in red) can be deleted, if you wish, but they're handy if you want to uderstand what each bit fo the formula is doing
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    Re: License Revenue Recognition model with Renewals

    Hi Glenn, Thanks for your help!
    Contracts for the most part are 1 year long. Start dates are usually at the beginning of the month and end dates end at the end of the month.
    Initially I would prefer to have renewals in perpetuity. However, it would be nice to have it as an option on a per line basis.

    For your model, for simplicity, if you change line 15 start and end date to 1/1/19-12/31/19, the first cycle should be 1200*80% and spread over the 12 months. The second cycle should be 1200*80%*80% spread over 12 months. third should be 1200*80%*80%*80% spread over 12 etc and in perpetuity as long as there is a renewal assumption.

    Although your model renews, it doesen't seem to be be incorporating the 80% of 1200. I would appreciate any further guidance. thanks
    Last edited by misterv; 12-27-2019 at 08:21 AM.

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    I didn't understand that... or see that... (not into financial modelling!!). I'll have another look.

  5. #5
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    Re: License Revenue Recognition model with Renewals

    thank you sir! I appreciate your help. This is something more complex than my skills can handle

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    Some qs.

    Contact starts in june 2020. The year 2019 80% cell should be blank. Correct?

    When does the first 80% apply? On Day one or after the first renewal?

  7. #7
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    Re: License Revenue Recognition model with Renewals

    Hi Glenn, Sorry I had to think about this. I hope it makes sense. Thanks again! The logic should be as follows:

    The 80% applys on day one. The percentages will all be there because of a vlookup that matches percentages to a product.

    It might make more sense to think of it On an annual basis. Here is my goal. However my task is to apply this on a monthly basis.
    1. If the contract term spans the full year 1/1/19-12/31/19, then 1200*80% spread over those 12 months. When it renews on 1/1/20, 1200*80%*80% spread over 1/1/20-12/31/20
    2. If the contract term stops mid year 6/1/18-5/30/19, then 1200*80% for the period 1/1/19-5/30/19. The license then renews 6/1/19 and it should be 1200*80%*80% spread over those next 12 months. On 6/1/20, it should be 1200*80%*80%*80% etc.
    Last edited by misterv; 12-27-2019 at 09:18 AM.

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    Clear (I think). Play with this and see where it breaks. The column headed "keep blank" can, of course, be hidden.

    I changed the numbers highlighted in yellow (overtyped) to have sensible numbers that facilitate manual checking!!

    =IFERROR(
    (POWER(0.8,1+INT((SUMPRODUCT(--(LEN($M15:M15)>0)))/DATEDIF($D15,$E15,"m")))+N("Multiplier"))*
    IF(N$14<$D15,""+N("Pre-start"),
    IF(EOMONTH($D15,0)=N$14,(N$14-$D15+1)*$H15+N("Start month"),
    IF(AND(LOOKUP(2,1/($I15:$L15>0),$I$14:$L$14)+1=YEAR(N$14),MONTH(N$14)=MONTH($E15)),DAY($E15)*$H15+N("Non-renewal end month"),
    IF(N$14>DATE(LOOKUP(2,1/($I15:$L15>0),$I$14:$L$14)+1,MONTH($E15)+1,1),""+N("Post-end month"),
    $H15*N$12+N("Normal month"))))),"")
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    Re: License Revenue Recognition model with Renewals

    Hi Glenn and everyone.

    Thanks for your help. I played around with your model and it was slightly off in some cases. Today I went through an example with my colleague. Attached is a version
    of how they like it done. The only problem is that in the first subscription term it is supposed to be X% or 80% of 1200 for line 15 spread over the first term, then
    80% x 80% of 1200 for the second term, 80%x80%x80%x 1200 spread over the third term. We decided that we would only do 3 terms and in perpetuity, we would
    multiply the last renewal rate x the previous year's month and so on.

    Would you be able to help with my logic/adjust my logic where I would have the following for line 15? I am bad with if statements.

    First term: rate 1 x 1200 spread over the term
    Second term: rate 1 x rate 2 x 1200 spread over the term
    Third term: rate 1 x rate 2 x rate 3 x 1200 spread over the term
    Perpetuity: the previous year's month x the rate 3

    Thanks for your help
    Attached Files Attached Files

  10. #10
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    So only changed the layout completely, then??

  11. #11
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    Re: License Revenue Recognition model with Renewals

    Hi Sorry,

    THey thought it was a bit too complex. I appreciate your help regardless.

  12. #12
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: License Revenue Recognition model with Renewals

    I spent rather longer at this than I should have. It's all the more discouraging when it all gets thrown back in your face. Someone will, no doubt, help you finish this, but I'll not be that person.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Deferred Revenue, Revenue Recognition --- integrating one time fee!
    By andrew.cloudsnap in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2019, 05:05 PM
  2. Revenue recognition and deffered revenue
    By saurabhwise in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-08-2013, 11:12 AM
  3. Revenue Recognition Scenarios
    By ELW1976 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2011, 01:48 PM
  4. Excel 2007 : Revenue Recognition/Projections
    By winniedebu in forum Excel General
    Replies: 1
    Last Post: 03-04-2011, 01:00 AM
  5. Excel 2007 : Revenue Recognition Model
    By Nishith in forum Excel General
    Replies: 0
    Last Post: 08-02-2010, 03:06 PM
  6. Revenue Recognition
    By pdiddy123 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-23-2007, 12:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1