Note: Since I wrote this I attended the Excel MVP Conference in Amsterdam – and the techniques I learned there made me realise I could have done this more effectively..
You can view Part one here (Steps one to three)
Just a reminder about what deferred income is…
You have paid the company (for example) 365 of your currency for a year’s subscription at the beginning of January 2018. The company hasn’t “earned” that because the service hasn’t been delivered so their revenue is only “earned” at specific dates.
From a sales point of view, at the end of January, the amount of earned income will be 365 X 31/365 = 31 . The amount of deferred (i.e. paid for but not yet “earned”) income will be 334. At the end of February, the amount of “earned income” will be (28+31)/365 X 365 = 59. The amount of deferred income is now 365 – 59 = 306. So on for each month.
Step 4 – Calculate “totally deferred” income
This is income that is spread over the age of the subscription e.g. for a one year subscription that would be 100/365
Step 5 – Calculate not deferred income
That means the income is recorded as “earned” on the first date of the subscription.
Step 6 – Calculate partially deferred income.
This will depend on the percentage e.g. if it’s 20% deferred that means that 80% is recorded as “earned” on the day of invoicing. The balance i.e. 20% of the sale will be divided by 365.
Step 7 – Get it all together with a pivot table
Put all these bits together.
Step 8 – Throw in a date to defer and earn…
For this one just wanted to say thanks to Mynda Treacy of www.myonlinetraininghub.com and her excellent Power Query course who introduced me to this method.
I hope you found this useful …and as always comments and questions welcome.