The Excel Expert

This year I attended a conference about John O Donohue , an Irish philosopher/mystic/poet who died in his early 50s. At that conference a friend of his called Dr Dan Siegel talked about how quantum physics and mysticism were now converging and  discovering what they had in common.

In his talk he referred to “the plane of all possibilities” – a mathematical location where all the solutions/answers/possible outcomes are to be found. (I am not a quantum physicist so if there is a more accurate term, happy to learn more).

At this point, you are probably wondering, “where are you going with this Anne and what does it have to do with Excel?” Valid questions indeed. I am telling you about this because an observation that frequently comes up in my classes is well “I don’t know what I don’t know” and “I don’t even know where to look for an answer”.

When it comes to Excel, the reality is that there is a solution somewhere. It may not be obvious, it may require work but someone has solved it somewhere but if you do not even know what to look for, that isn’t really of much help so I am going to suggest something rather unorthodox.

I am going to suggest that you tap into the “plane of all possibilities” by trying the following.

• If you have an Excel problem and you have no idea how to solve it, seek first of all to get calm and settled (even if it requires retreating to a  toilet cubicle for 5 or 10 minutes!)
• Close your eyes and ask that the “plane of all possibilities” shows you a solution to the Excel problem you are having.
• Within an hour you will get a prompt to look somewhere/ask someone – go with that no matter how strange or weird it seems. That may not have the direct answer for you but it will lead you to the answer…
• I use this myself and the prompts can come in many ways e.g. search for X or ask Y or try Z…

I also know that for some of you this may be veering into what a good friend of mine Maggy Whitehouse calls “wanky bollocks” but honestly, surely it’s at least worth a try 🙂

Continue Reading

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

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