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

A common issue for many organisations is tracking time-in-lieu i.e. someone has a certain number of days/hours holidays(vacations) allocated but they can increase that by doing overtime and then taking time off – “in-lieu”.  While some organisations have a system in place, a lot don’t.

This tutorial will show you the following:

  • How to set up a sheet for each employee that automatically tracks and summarizes their time in lieu (all they have to do is enter the date, number of hours overtime and time taken off).
  • This sheet will also calculate a balance so they can see how many hours holiday time they still have outstanding.
  • How to set up a sheet that summarizes all those hours for the manager so that he/she can get the big picture at any time.

The tutorial below takes you through it step by step. If you want to view the file straightaway you can download the file with dummy data (so you can see how it works) or without dummy data when you are ready to experiment with your own data.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

Scenario

  • Wylie Coyote is a manager. She has 5 staff .
  • Each member of staff has an allocation of total holiday hours (cell I1 in the file below)
  • Each day is taken to be 8 hours
  • Extra hours can be done per day – they are recorded as Overtime. Every time an employee does overtime, the date will be entered in the Date column (Column B), and the number of hours entered into the Overtime column (Column C)
  • When the employee takes time off – this is entered in the Time in lieu/holidays column (Column D).
  • The balance is calculated automatically – based on the total number of hours for holidays, plus overtime minus Time in lieu/holidays.
  • The month of that date is entered automatically in Column A – this will be used later for summarizing hours. This is done via a formula

Step 1 – Set up the Employee sheet

View the video here to see how this is done. Some sample data has been entered. Thanks to RGonzo1971 of Experts’ Exchange for help with the dynamic formula.

You can download the file at this stage.

Step 2 – Summarizing the data month by month.

So right now our sheet shows the employee how many hours they have outstanding at any point in time (assuming they have entered all their hours up to date)

However, Wiley Coyote wants to see two things – she wants to see a monthly summary for each employee on their sheet and she also wants to have a picture overall of how much overtime and time in lieu has been taken.

So in the same sheet we set up the formulas to summarize on a monthly basis how much overtime and time-in-lieu each person has taken.

See video below to see how this is done. I have used the Sumifs() function and you can view the tutorial on it here.

  1. Fill in the months of the year from K6 to K17 down
  2. In cell L5, we will use a SUMIFS() function to calculate the amount of overtime per month
  3. In cell M5, we will use a SUMIFS() function to calculate the amount of holidays/time-in-lieu each person has taken.

You can download the file with the summary completed at this stage.

Step 3 – Creating one for all our employees

We now have our first sheet set up – next thing we have to do is make a copy of this for every other employee.

See a video here that shows you how to make a copy of the sheet and rename them accordingly.

You can now download a file where it has been set up for 5 employees.

Step 4 – Creating a bird’s eye view sheet 

The last step is to create a sheet that allows the manager to see how much overtime is taken by each employee by month. This is to help her to do the following:

Make sure that employees are taking their time-in-lieu within the month as far as possible and to make sure holidays are taken within the year.

The summary sheet will summarize by employee and month what their current balance is.

View both completed files here. Again, if you have any questions, just get back to me.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

 

Continue Reading