VAT Report / Reconciliation

Don't have time to read?  Watch the instructional videos here:

VAT Report:  https://www.youtube.com/watch?v=FfWhRwYiNlo&list=PLqp8yj8sr8u4Mmwdo8I6jez_YMYMNeaXg&index=4&t=59s

VAT Account Reconciliation:  https://www.youtube.com/watch?v=gwxFJBxCEt0&list=PLqp8yj8sr8u4Mmwdo8I6jez_YMYMNeaXg&index=5&t=0s

See our VAT help section here: Digital VAT

VAT Report Explanation

• For every transaction in the VAT Control Account, the system looks at the Tax Flag and it’s Transaction Number and calculates the total based on this.

• The VAT Report calculates the NET amounts backwards by looking at every tax flag coded to each transaction number in the VAT account, it then uses this transaction number to calculate the NET amount of each transaction.

• This report is prone to error when using multiple entries that use the same transaction number, such as Journals and Distribution Changes. See this guide for how you can correct this issue for Journals:

• If you want to run a VAT report over the entire Ledger (not only the VAT Account) you can run a BAS Report here. This report does not have the same issue with Journals and Distribution Changes using the same transaction number, but it requires you to diligently tax flag all transactions correctly, along with doing a period close at the end of every VAT reporting period as outlined here.
  
  

For example, if you pay a TPH invoice and flag all the lines to your payroll control accounts with a V then it will massively overstate the net.  These would need to be flagged with a Z or another flag you are using to indicate out of scope, or not applicable, with only the service fee and vat lines having a V.

Similarly, if you are doing distribution changes between VAT control accounts you would need to flag the original line and reversal as out of scope and only include transactions in scope on the report by setting a range in the TX field on the report. If lines are doubling up on your reports, you will need to set the VAT Report range to the out of scope code and all you will need to do is fix the flagging on the items that are doubling up.

It is best to look at all the lines for a transaction together, so you can see in context all the flagging. The best place to do this is in Ledger Inquiry, here you can also update the Tax flags.

Setup Eclipse to Run the Vat Report

1. Enable the Auto Create and Balance Tax Lines option in your System Default by following this guide: . This is required so that tax lines are posted directly into your VAT Account (even if the tax amount is zero) for the VAT Report to display the NET on the report correctly.

Running the Report

1. From the main menu bar select General Ledger > Ledger Inquiry. Select Reports button

2. Select Preview from the Report Type drop-down menu

3. Select Vat Ledger from the Report Defaults drop-down menu

4. In the Report Layout section, select Vat Account Report.  Insert your VAT ACCOUNTS (purchases & sales) for the account range. You can run purchases and sales reports separately if needed.

Note: When entering transactions, use the tax balance button in the Unposted Batch List window to ensure your coding and flagging are correct.  If items are not flagged correctly an error window will appear listing each transaction number out of balance.  You can also tick Balance Tax Before Posting checkbox (System Defaults, Tax Balance tab) to automatically check before each posting.  It's easier to use the tax balance button or force a tax balance during a post to sort out any problems at the time of entering, rather than waiting for month end.

Open

5. Click the Selection tab, in the Date fields enter the end date you are reporting on.

6. Tick the Calculate Net Tax Amount and Exclude Reconciled Items checkbox.

Open

7. Return to the Report Details tab and click the Save icon. Press OK to run the report. Here you can also export your ledger into Excel so you have a record for audit. The data is summarised and it will show if it reconciles back to your VAT account. If it doesn't balance you can inspect the transactions to see if there are wrong flags or items have not been split.

Note: A question that comes up a lot is when the VAT amount per transaction in the VAT Report doesn't reconcile back to 20% or items without VAT are not shown on the report.  The report uses the TX to calculate the net amount of all transactions.  For amounts not calculating correctly it is because a line has not been split and flagged separately between VAT and non-VAT. This then introduces another problem, you then need two lines in your VAT account for this transaction even though the non-VAT line will be zero. There is an option in System Defaults, Tax Balance tab to automatically add two lines to every transaction to make this process easier. This will also solve the problem where items with no VAT are not shown on the report.

8. As an extra precaution, once you have the report and have prepared your return for HMRC, immediately perform a Vat Reconciliation (see below). If you have trouble balancing your VAT next period you will be able to see all the unreconciled items in your VAT account to work out what is not being included in your report.

VAT Reconciliation

Immediately after paying your VAT you will need to reconcile the payments that have been made.  If you wait to reconcile, getting the figures to match up can be quite cumbersome.  If you are not closing periods at the end of your VAT reporting periods you can use the reconciling features in Eclipse to isolate these transactions. So instead of putting in a period range you would exclude reconciled items.

1. Create, enter and post a Journal to match your VAT Report, moving the expenses from the VAT Purchases TB account to VAT Control/ Suspense account.  VAT purchases should be a credit and VAT sales should be a debit.  VAT control is a debit if you are expecting money back or a credit if you owe money to HMRC.

2. In Eclipse from the main menu bar select General Ledger > Budget Worksheet.  Select your VAT Purchases TB code and click the Transactions tab.

3. Tick the Select All checkbox and then click the Reconcile button. If all transactions do not equal zero you will not be able to reconcile them and must find the problem.

See the guide here for how to Reconcile Transactions:

See the guide here for how to Unreconcile Transactions to correct mistakes:

4. If you are going to use this VAT Reconciliation method in your VAT Account we recommend turning off these two system defaults:

Untick 'Flag Dist Changes as Reconciled' and 'Flag Cancelled Cheques as Reconciled' under the General tab.

If these system defaults are turned on and you do a distribution change or cancel a cheque or invoice, then it's going to remove the original transaction line from its previous reconcile number and add it under a new reconcile number with the reversal transaction, so the reconcile sums to zero. This is going to cause a variance in the original reconcile number and will throw out your VAT account.