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…

 






Sign up to get your free training records tutorial and template

We respect your email privacy