Hi,
I need help with a circular dependency issue. I have a Revenue Recognition Table that used a declining balance formula to calculate the current month's Revenue to Recognize. In any given month we Defer Service Revenue and recognize the revenue over the life of the service contract. The Service revenue is non-refundable, meaning that if a contract is canceled for any reason we will recognize the remaining balance of the Deferred Revenue related to that specific contract. Additionally, there are time when the service contract length is extended or made shorter (at no additional cost). The three main drivers for recognizing revenue are Beginning Def Revenue Balance, Length of contract (Longevity) and Rate of Contract Termination (Attrition). In order to calculate the current month's revenue recognition we have to start with the prior month's, because their is a potential that we could have made changes to the contract so we always start with the ending book value of the prior month i.e. we always calculate prospectively instead of retroactively.
Things to know about this table:
Service = Name of the Service
Contract Date = Contract Sign date
Primary Key = Service name & Contract Date
Longevity months = Current Total # of months of a given contract (as I mentioned above the contract length can change)
Beginning Month = 1 month after contract date (we start recognizing revenue one month after the original contract date)
Rev Rec Month = EOMONTH(Contract Date, Longevity Months), this would represent the actual months that we have to recognize revenue based on the Longevity.
Total Longevity = This is the Current total number of months that we need to recognize the revenue over
Attrition = This represents the % of service cancelation that we expect on a monthly basis.
SSP = Cost of the Service
Units = # of service contracts
Original Def. Rev = This is equal to the SSP x Units and will not change throughout the life of the contract.
Remaining mths Amort-Longevity = Based on the current months Longevity I.e. in this specific contract the contract length (Longevity ) changed 4 times and since we calculate prospectively we need to be able to account for each change
Months Amort- Longevity = This represent the Total Months of Longevity (MAX) for that particular month.
Monthly Rev Rec = This is where i need your help!
Ideally, the formula should be as follows =
Month 1 = ((DefRev[Original Def. Rev.]*(1-DefRev[Attrition]))/(DefRev[Months Amort-Longevity]-(DefRev[Longevity Months])+1))+(DefRev[Original Def. Rev.]*DefRev[Attrition])
Month 2 and beyond =
((DefRev[Def. Rev. Beg. Bal.]*(1-DefRev[Attrition]))/(DefRev[Months Amort-Longevity]-(DefRev[Longevity Months])+1))+(DefRev[Def. Rev. Beg. Bal.]*DefRev[Attrition])
Ideally, I would need to create a new column that calcualtes the Beginning Def Revenue Balance based on subtracting DefRev[Original Def. Rev.] from DefRev{Monthly Rev Rec] in the 1st month and for subsequent months subtracting DefRev[Def. Rev. Beg. Bal.] - DefRev{Monthly Rev Rec].
I would also like a Cumulative Rev. Rec. Column that shows the total cumulative Revenue Recognized to date.
Hi Ibendlin,
Basically, what I am trying to build a calculation/measure for in Power Pivot/Powey Query - Columns S, T & U are depndent on each other - See excel formulas below. for each row of caclualtion is dependent on the
Formula for S (Rev. Rec. Straight-Line (Declining Balance)) =-((U2*(1-L2))/(Q2+1-O2)+(U2*L2))
Formula for T (Accum Rec. Rev.)=SUMIFS(S:S,A:A,A2,B:B,B2,E:E,">="&E2,G:G,"<="&G2)
Formula for U (Def. Rev. Balance) =SUMIFS(T:T,B:B,B2,A:A,A2,C:C,C2,G:G,(EOMONTH(B2,D2-1)))+R2
Below is a screenshot of the Excel table that has the caclualtion.
I'm not sure if I can send you the file tht I am working with becasue I think it would be easier to see the formulas.
