Monthly irrecoverable VAT adjustments with Xero and Power Query: Part 1.

I asked on LinkedIn the other day whether people would be interested in my method for posting monthly irrecoverable VAT adjustments and was pleasantly surprised by the responses.

So here goes.

As there’s a bit to cover, I’ve broken this into two parts. In this first post, we’ll look at why you might want to do this, and how to get the right report from Xero. In the next post, we’ll look at the Power Query part.

DISCLAIMER: This post does not constitute VAT advice. I am assuming that you already know what expenses are recoverable and non-recoverable in your charity or organisation and can classify them appropriately. If you do not know this, speak to a professional who does before attempting any of this. This post just sets out the mechanics for analysing what you have posted and creating a monthly journal.

What do you mean by posting monthly irrecoverable VAT adjustments?

Over the course of my professional career, I’ve noticed that there are two main approaches for charities when it comes to dealing with irrecoverable VAT:

• You post all expenditure to the ledger including the VAT. That is if an invoice for a meeting room was £100 + VAT, you would post £120 to the meeting room expenses code.
• Alternatively, you use tax codes or other methods to classify the VAT element of each transaction into non-business, recoverable, irrecoverable, or unattributable expenses. Then at quarter end you do a calculation and make adjustments.

With the second option is that you often end up with an “irrecoverable VAT” lump sum in the accounts. The problem is this can be a significant line of expenditure that you are not allocating to relevant restricted funds or budgets.

Also, if you only make this adjustment quarterly but your management accounts are monthly, they might not be accurate.

So over time, I have evolved a hybrid, semi-automated route. I use option (2) when book-keeping, but I do a monthly irrecoverable VAT adjustment where I allocate all the irrecoverable amounts to the right budget and funding lines.

What do you need to be able to make monthly irrecoverable VAT adjustments?

Firstly, you need to have a way of identifying systematically on the accounting system whether transactions are non-business, irrecoverable, recoverable, inattributable.

In this example which is specific to Xero, I’m using a tracking category which I’ve called “Funding Category”. (On other systems it might be a Department or Cost centre code.) Maybe you do it by account code where you are. In the real life example that I’ve based this on, I use the Funding Category to keep track of different restricted funds, and then I have several Unrestricted funds (eg Unrestricted – Recoverable) and this allows me to map each item to a specific VAT treatment (while also using them for Fund Reporting).

This is not to be confused with using tax codes – I also use these. (See an earlier blogpost for the details of how I do this in Xero.) However, as you will see, you can’t pick these up in the transaction report that you need to run in Xero, so you need something to be present either in account code or in the tracking codes.

There is also a “VAT report by tax code” within the VAT return part of Xero. However, this doesn’t show tracking codes, sadly! That said, if you were not using tracking codes at all in your organisation, you could use this report by tax code, which does include the account code within it. While the instructions that follow won’t exactly apply in this case, you might be able to see how you could apply the same principles. Anyway, back to my example.

The mapping for the Funding Category tracking code in this example is as follows:

Funding Category	VAT Treatment
Grant 1	Irrecoverable
Grant 2	Irrecoverable
Grant 3	Non-business
Central Costs	Inattributable
Consultancy	Recoverable

Secondly, you need to then be able to extract a report that shows what has been posted to the VAT control account with the appropriate level of transaction detail. I know I can do this in Xero with a custom report; I haven’t experimented enough with other systems. In theory though, if the data is in there then it should be extractable! Have a look through Transaction reports and/or “audit file” reports and see if you can get something similar.

Creating a custom report in Xero

These instructions are specific to Xero. You need to tailor the “Accounts Transactions” report as follows:

• Select account code 820 – VAT.
• Amend the columns to include the tracking categories (circled in my example below) and “Related Account”. I have also removed “Gross” and “Running Balance”. You actually don’t need all of these columns to do the journal but I also like to review the VAT postings at this point so I keep some of the detail in.
• Change Grouping/Summarising to “None” (it defaults to “By Account”).

Save this report as a Custom report – you’ll need it again in future months.

This creates a report that includes the seldom-used Related account column, which looks like this:

This column is almost perfect for what we need, as it lists the other account codes involved in the transaction. Using Power Query we can get rid of the Accounts Payable / Accounts Receivable entries, which we’ll see in the next blog post.

A word of warning though – if you have an invoice or transaction that uses multiple account codes, then annoyingly it only lists the first code used, and against each line item regardless of which code was used on which line item.

To be honest, I don’t find this a problem with my data as it’s pretty rare that I will have an invoice from a supplier where I have multiple account codes. If it does happen I make a note and then manually adjust the final journal – if it’s material.

In part 2, we will look at using Power Query to create a journal from this report.