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, but that will be for another post…:-)
There are seven steps in this so I have published the first three. You can view Part Two here…
Calculating Deferred Income
Recently I was asked to design a spreadsheet to calculate deferred income. The term deferred income in accounting refers to a basic accounting principle that even if you have sold something, you can’t claim it as a sale until certain conditions are met.
The example I had here was around a subscription model. For example if you purchase anti-virus software (I do hope you do!) it 
will be for a year or so.
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.
It’s a bit of a tricky bugger to be honest but here I outline the steps I used.
Step 1 – Clean up QuickBooks data
You can find a link to a tutorial here that shows you how to do clean up your accounts data – which nearly invariably will come in with unwanted blank rows, columns and totals. Here is the file with both the original data (see sheet QB_Sample_file) and cleaned up data (see sheet Cleaned QB File)
Step 2 – Amend the QuickBooks data to show subscription end dates and subscription %
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/deferred_Income_Video_01.mp4″]
Here is the Start File and here is the Finished File
Step 3 – Set up a date for every entry – using Power Query Unpivot Function
In this video I show you how you add a set of dates so that you end up with a new data set that has an individual entry for each day for each invoice. This is to allow us to calculate the daily amount allocated for each subscription.
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Deferred_Income_QB_Video_Step_03.mp4″]
Here is the Start File and here is the Finished File
Any questions/comments most welcome…