Blog

Posted on

Why does my pivot table default to Count?

If your pivot table defaults to Count instead of Sum, then you have inconsistent numerical data.

I found this out on a webinar about five years ago, and it still seems to be a mostly unknown feature.

And yes, I call it a feature, not a bug, because it is ultimately helping you.

A pivot table showing a Sum of Amounts column.
Pivot table showing a Sum of Amounts column. If this defaults to Count, watch out!

What it is doing is giving you an immediate message that your numerical data has gaps, or perhaps has numbers formatted as text, or other errors. If you ignore this, you might produce inaccurate analysis.

So – if your pivot table defaults to Count, don’t do what I used to do, which was to tut and manually change it to Sum.

Investigate your data carefully until you’ve found the inconsistencies and then try it again until it defaults to Sum. Here’s a link to some suggestions for converting text to numbers.

I love training finance teams in Excel and Power Query – have a look here at what I can do for you.

Posted on

Good data saves money: smart meters

a light bulb

We’ve had a smart meter for over a year now and it has led to a 20% reduction in our electricity use.

We had thought we ran a pretty efficient household, at least on the electricity front. From analysing bills before we had the smart meter, I estimated we were using 5-6 units a day, which is well below the “average energy use” that you hear so much about. We always used less than those helpful energy providers predicted, anyway.

However, the smart meter gave us (a) more data and (b) more ways to look at that data. One thing that became clear fairly quickly was that we were using almost as much electricity when we were out of the house than when we were in it.

We became religious about switching things off at the wall, particularly when going away for a day or more. This is what has made the biggest impact, which surprised me, as I thought that all of our devices were energy efficient.

It also helped to pinpoint particular devices that use more power. It wasn’t surprising to see that the oven and the washing machine were power suckers, but the daylight lamp bulb was a nasty shock.

Having good data saves money, particularly if you know how to analyse it!

Photo by Christian Dubovan on Unsplash

Posted on

VAT partial exemption on Xero

Here are some practical tips for accounting for VAT partial exemption on Xero.

What is partial exemption?

A basic principle of VAT is that you can only reclaim VAT that relates to “taxable supplies”. A “taxable supply” is a sale or other income line where you charge VAT. If you only sell products where you charge VAT, then you can reclaim all of your VAT as a business. However, if you have sources of income that do not attract VAT – for example residential rent or grants – then you cannot reclaim all of your VAT.

I am not going to go into further details of partial exemption methods or calculations – that’s for HMRC to explain in pages of copious guidance.

There are essentially three parts of the process. Firstly, recording the VAT correctly, secondly carrying out a partial exemption calculation and finally adjusting the VAT return. This process applies whichever system you use, however this post sets out how it applies to Xero.

Continue reading “VAT partial exemption on Xero”