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..

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

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.

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.

Here is the start file and finished file

Step 7 – Get it all together with a pivot table

Put all these bits together.

Here is the start file and finished file

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.

Here is the Start file and finished File

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

 

Continue Reading

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…

 

Continue Reading

0

I recently got a request through LinkedIn from someone I met at a conference who wanted to know how she could do the following..

She wanted to calculate exam results given the following parameters.

” If they do not fail any module but can now have up to a maximum of 3 modules that falls between 35 and <40 they can pass by compensation, my question is how do I get excel to understand it is up to 3 and no more. For example, if they have not failed any modules and have 3 or less modules that come in between the above they would ‘Pass by Compensation’, if they have not failed any modules but have 4 or more between 35 and <40 they fail. But if they fail any module, they fail regardless. ”

This required a combination of IF/And/CountIfs.

You can download the file here…Compensation pass fail question

And in this video I explain how I came up with the formula.

Continue Reading