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

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_QB_Step_04.mp4″]

Here is the start file and finished file

Step 5 – Calculate not deferred income

That means the income is recorded as “earned” on the first date of the subscription.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_QB_Step_05.mp4″]

Here is the start file and finished file

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.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_Video_06.mp4″]

Here is the start file and finished file

Step 7 – Get it all together with a pivot table

Put all these bits together.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_QB_Video_07.mp4″]

Here is the start file and finished file

Step 8 – Throw in a date to defer and earn…

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_QB_Video_08.mp4″]

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.

Here is the Start file and finished File

I hope you found this useful …and as always comments and questions welcome.