MacroExcel and VAT reporting in Europe

HMRC's 'Making Tax Digital for VAT' aims to avoid 'manual entry' mistakes, and that all underlying VAT line items of the VAT return are not only digitalized but at the end readily available so HMRC can perform soon closer to real-time tax audits. In 2020 the underlying digital VAT accounting data will be submitted to HMRC. That data will be used to review whether the numbers of the nine VAT returns submitted are correct. Not only a digital audit trail should exist when manual changes are made, but VAT controls should be run.

We have developed a MacroExcel to support the VAT return process for all countries in Europe and that MacroExcel is currently used by a multinational to extract the data and prepare the VAT returns, and perform vital VAT controls. The above means that we can quickly set the same process up for other countries.

Lets show you how our MacroExcel works for the UK

From the main worksheet, it shows it is a natural follow-up exercise. It is user-friendly, and the VAT return preparation is just done in five steps. The VAT controls highlight possible errors either in calculation or in data quality. Tracking manual changes is easy. The upload of the file for UK MTD is done with just one click.

  1. In step 1 you select the taxpayer and reporting period from the ‘drop-down’ menu.

  2. In step 2 you upload the source VAT data with one-click. You select just the source file ‘Standard SAP VAT report’ generated from SAP. The file is uploaded in a separate sheet called ‘SAP_VAT_report’ and includes both line items and summary. The sheet is password protected against any manual entry.

  3. In step 3 you upload similarly the source VAT accounting data. The file is uploaded in a separate sheet called ‘Standard SAP VAT report’ generated from SAP and included both line items, and the summary. Also, this sheet is password protected against any manual entry.

  4. In step 4 you generate the final file in the JSON format in one click. That file can be uploaded to our web-portal and submitted to HMRC.

  5. In step 5 you finalize your work and you save the file with password protection

An overview of the relevant VAT management features of our MacroExcel

The sheet is password protected against manual entries. The mapping in the MacroExcel provides a linkage between the tax code and the VAT return box. The MacroExcel captures corrections in separate columns. The sheet displays the VAT result from the ‘initial’ and the ‘final’ data. In the sheet, a reverse VAT calculation check is performed to identify any errors coming from source data. For example typing mistakes, wrong amounts on invoices, etc.

Multiple ‘cross’ controls are run within the MacroExcel to validate the final result. A linkage exists between the final VAT return data and in a separate sheet called the ‘VAT declaration the UK’.

An extra feature that could be set up as a tailored exercise is that all corrections are done in separate sheet ‘Corrections_final’ – and never on source data. Easy to proceed – enter data you need to correct, mark the correction type, i.e. ‘Include’ or ‘Exclude’. The sheet automatically transfers the result to ‘VAT_calculation’. Tracking changes in the VAT result will be easy.

An extra feature that could be set up as a tailored exercise is that the VAT return results are reconciled against the VAT General Ledger balance in a separate sheet called ‘Reconciliation’. All amounts are automatically filled in from source sheets, i.e. ‘VAT_GL balance’, ‘VAT_calculation’, etc. The sheet highlights differences. It can indicate that some errors exist in reporting. For example, VAT data is posted directly to the VAT account without the use of a tax code and was therefore not captured in the VAT report. Optional functionality can be added that requests the user to explain these differences.

An extra feature that could be set up is the VAT overview that provides an overview of the submitted VAT figures from previous periods. Amounts are automatically filled in. Any significant difference in reported values across periods can indicate that some errors exist in reporting. For example, the sudden drop-down on the sales VAT without any business reason might mean issues with the billing module, etc.). The sheet is password protected against any manual entry.

During the design, we anticipated on the next submission requirement. Besides the nine numbers of the VAT return to perform data analysis, HMRC will request soon to receive VAT accounting data. The MacroExcel we developed can simulate such a tax audit. Our Bridging software developed will be able to upload and display the VAT accounting data, and when internally approved, submit this data to HMRC.

MacroExcel is available for ‘All European’ countries

The VAT return MacroExcel is available for ‘All European’ countries. We standardized and optimized the entire European VAT return workflow. All the UK features explained are also included in the country VAT return MacroExcel.

Short video

Multinationals can have specific manual changes or methods to calculate the numbers of the VAT return periodically. The existing VAT return process has to be optimized to meet the new HMRC objectives. With the MacroExcel, we keep close to the existing operational VAT return process.

Companies with VAT return obligations in the UK often use spreadsheets nowadays during the VAT return process, and those spreadsheets have to be upgraded to meet the new HMRC requirements (see below image). However that is not the only reason to use our MacroExcel.


The advantages of our MacroExcel

Our MacroExcel meets the new tax requirements, and automated data transfer takes place. A digital audit trail exists when manual changes are made and also the MacroExcel protects against 'copy & paste', 'cut & paste', and 'overwrites'. That is vital functionality to be compliant with the UK 'Making Tax Digital' objective. The MacroExcel is archived in SAP and readily available on HMRC request.

The advantages go however a bit further than that as for the tax function it facilitates tax risk management. A company often extracts raw VAT data from SAP to calculate the numbers of nine boxes of the UK VAT return via downloading the data in Excel and subsequently the VAT process owner consolidates the numbers.

In the MacroExcel all relevant VAT data is centrally available and accessible. For solid 'Tax Risk Management' purposes VAT controls are set up by which unforeseen tax risks can be avoided, especially when VAT controls are run during the soft landing period. When gaps are found remediation can still take place before mandatory submissions. That is the time when HMRC will perform similar data analysis to combat VAT fraud, VAT leakages and other non-tax compliance.

Examples of VAT controls that are standard in the MacroExcel are:

  • Reconciliation (boxes of the VAT return and VIES, Intrastat Arrivals & dispatches)

  • Reverse VAT calculation checks performed

  • VAT amounts compared with the displayed net amount

  • Non-standard’ VAT rate checks due to discount processing

The MacroExcel is flexible, and we can add tailored VAT controls such as mathematical and logic VAT health checks at extra costs. 

Limitations of Excel and how to resolve

Excel has limitations with handling large datasets. Excel limits the number of rows in a spreadsheet to about one million. The current VAT return process and future requirements combined can result that Excel cannot be used as big data has to be stored. MS Access and ACL software are alternatives we use that both can handle more data.


Take aways 

Anticipate what users would want

We combine technical knowledge with industry understanding and knowhow of technologically advanced tools and methodologies available in the market or developed by ourselves.

What do we like to achieve

  • Focus on tax processes that could be improved
    • Manual process: same data requests are made by different stakeholders
  • As Is assessment
  • Anticipate future changes and the data needed
    • What are tax trends?
    • What is happening locally and what should be considered across jurisdictions where you operate?
    • Anticipate new stakeholders and their data needs or requests (internal and external)
  • Define scope and actions for short, mid and long term
  • Write business case for change
  • Realize sponsorship for implementation

‘As is’ assessment, actions and business case

  • What tax data is requested and by whom?
  • What tax process can be improved and what can be automated?
    • CIT, VAT, tax data warehouse
  • What is the Return on Investment?
    • Hard saving: process improvement
    • Meeting (new) tax requirement
  • What systems are in use: SAP, Oracle, etc
    • By which entities?
  • How many end-use computing tools (e.g. excel spreadsheet) do we have?
  • How do we avoid an ad-hoc solution?
    • Understand the bigger picture
    • Real problem and not the symptom

Risk and reward

Technology-related tax risk: understand and address the potential harms and benefits of (new) technology.

Technology tools & systems integration

Ascertaining proper IT support for ensuring efficient, timely and reliable reporting.

Change and project management

VAT should be considered in every aspect of the process, from concept through completion and beyond. Managing by design — looking at any process or transaction from end to end and factoring in all the requirements and controls essential to designing and optimizing a compliant VAT process.

Effective communication and teaming

We speak the language of the business and IT and no translation is needed.

Set up a project plan

  • Set up a project charter that will take effect preferable during feasibility but ultimately during design
  • Write a business case and problem statement
  • Define scope of the project
  • Define objectives and goals of the project
  • Involve stakeholders and define priorities
  • Set measurable milestones
  • Ensure that the right sponsors provide buy-in.
  • Identify (project) risks and how to manage them
  • Jointly validate and refine the project plan and develop a roadmap to success
  • Hold regular meeting to track progress of the various work streams