Monthly irrecoverable VAT adjustments with Xero and Power Query: Part 2

This blog post probably won’t make a lot of sense unless you read Part 1 first.

Having customised our VAT report in Xero in part 1, we’re now going to use Power Query to turn this into a journal.

Very quick recap on Power Query, if you’re not familiar with it. It’s a feature that’s been in Excel since 2010 and allows you to get data from multiple sources, transform it in myriad ways and repeat the process really easily. The official Microsoft guide makes it sound terribly complicated and techy, but it is emphatically not just about Big Data or management accounting. If you have repetitive processes that involve lots of copying, pasting, lengthy formulas etc then Power Query will probably save you time. Here’s me on Why You Need Power Query and here are more blog posts on the topic.

Overview of approach

Before diving into a query, or indeed any bit of spreadsheet design, it’s always worth thinking about what you’re trying to achieve.

We have a transaction listing from the VAT account in Xero, with related accounts next to each line item.

Our ultimate goal is to remove items that cannot be reclaimed and post them to the relevant expense codes, leaving the VAT account to be a more accurate reflection of the amount owed to or from HMRC. We want to create a journal to do this, so the journal needs to have only the non-recoverable items in it.

So the process is

1. Tidy our report so we can filter by account codes.
2. Filter report to just the expenditure codes.
3. Filter report via the tracking code so we are just left with non-business and irrecoverable items, which is what you want to journal out of the VAT account.
4. Transform the report so it looks like the Manual Journal template in Xero.
5. Make any adjustments and create the other side of the journal.

1. Tidying and filtering

Having saved your VAT report somewhere (in my case, in my downloads folder, for easy repeatability the following month), open a blank workbook and using Get Data > From File > From Excel Workbook, bring the data into Power Query (Transform Data after you’ve pointed it at the right filepath).

Remove top rows and bottom total row. At this point I also remove the Source and Account columns. (Probably should have done it in the original report really!)

2. Filter report to just expenditure codes

Now we want to extract the relevant account code from “related account”. We want a way to ignore the Accounts Payable and Accounts Receivable and just keep “the other side”.

The source data is fairly well structured in that a comma separates the account codes, but sometimes the account code that I want is in first position and most of the time it is in the second position. There are LOTS of different ways to address this and I am definitely not claiming mine is the best – it feels a bit of a hack! But it’s done this way to avoid using code.

Firstly, I use “replace values” replace the string “800 – Accounts Payable, ” with null and do the same with “610 Accounts Receivable, “. (If you also post VAT transactions directly from the bank you’ll also want to do the same with the bank code).

I’ve now got a set of data that has the account code that I’m interested in at the front.

I can now extract the account code using the first 3 characters.

(There are ways to use code to extract only numbers from strings, or only letters, see here for example.)

Now I have the expenditure codes, I can also filter out the bank codes and income codes. I do this using “text begins with” for 0 and 2 codes.

3. Filter the report to remove recoverable items

If you don’t have many recoverable tracking codes, you could do this just by applying another filter, this time in the relevant tracking column.

If you do have a lot, you might want to create a lookup table to do this. You can then use Merge Queries to bring through the correct categorisations.

As this is a simple example I’m doing the first option.

You also have a choice as to whether you want to remove the unattributable items. Whichever way you do it, you will have an adjustment to make when you actually do the calculations at period end. In most places I’ve worked, the recoverable amounts from unattributable items are pretty tiny, so I treat them as irrecoverable in the monthly journal and then I’ve got a write-back when I do the actual VAT return.

So, long story short, for this example, I filter the report so that the recoverable Funding Category “Consultancy” is removed and everything else (non-business, irrecoverable and unattributable) stays.

My data now looks like this:

4. Create the top half of the journal

We’re now ready to create our journal. The fields for journal import in Xero are:

*Narration, *Date, Description,*AccountCode, *TaxRate, *Amount, TrackingName1, TrackingOption1, TrackingName2, TrackingOption2

Narration: I create a custom column that says “Irrecoverable VAT Journal” (this is the name of the journal). I also create custom columns for TaxRate (“No VAT”), TrackingName1 (“Department”) and TrackingName2 (Funding categories).

Date: I transform the date column using the End of Month date transformation.

Description: – I just keep the existing Description but you could also use the Reference column, or combine them, or indeed come up with something completely different.

AccountCode: I rename the existing “relating account” column so that it matches the heading from the Manual Journal template.

Amount: I create a new column by subtracting credits from debits (and then delete the Debit and Credit column)

Finally, I rename the “Department” column heading to “TrackingOption1” and “Funding categories” to “TrackingOption2“.

I then shift around the column order until it matches the Xero journal import template. Voila!

5. Other half of the journal

This part of the process depends on how you like to see the entries in the 820 VAT code. Personally, I just import the journal as it is into Xero and then add the balancing entry at the bottom. If you like to have all the line items in 820, you could duplicate this query, replace the account code with 820 and multiply the amount by -1, and then merge this new query with the old one to create the full journal. Or summarise (“Group by”) by tracking category.

What to do at VAT return time

The process above should speed up the VAT return review, as you’ve already identified your non-business and non-recoverable items. You will still need to amend the VAT return for these journal values (but do NOT create an accounting entry). You will also need to do your usual partial exemption calculations and make adjustments for that.

I actually use a similar but reversed process to the above to reconcile and check the VAT account before I start making adjustment – that is, I check that the total in the VAT account is equal to all of the outputs and recoverable inputs over the past three months.

I hope this has been useful. If you work for a charity, I am very happy to send you the files from this example – just contact me via the contact form with your charity email address.