0

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 %

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.

 

 

 

 

 

 

 

 

 

Here is the Start File and here is the Finished File 

Any questions/comments most welcome…

 

Leave a Reply