Report Transformation for Reverse Charge

mpb · March 31, 2020, 3:57pm

I have created a new tax code to handle reverse charge purchases from Ireland - VAT 23% (EU). The Tax Summary report shows the new tax code and correct values. However, some of the VAT Calculation Worksheet values are incorrect. These are:
Box 3 - does not contain the VAT element of the reverse charge as it should in the UK.
Box 4 - does not contain the VAT element of the reverse charge as it should in the UK.
Box 7 - Does not include the net reverse charge value as it is a purchase from the EU
Box 9 - Does not include the net reverse charge value.

To correct thsi I modified the VAT Calculation Worksheet .json file to incorporate the reverse charge code.
See below

“ReportTransformations”: [
{
“Key”: “12e5e9fb-d8e8-4fce-aa33-8ba564117550”,
“Name”: “VAT Calculation Worksheet RCM”,
“Type”: “68e0d57b-4a59-453e-b8d4-6166f097eacd”,
“Script”: [
“{% assign VAT_0 = "6959fb01-3a48-486a-9bec-a0681a662f03" %}”,
“{% assign VAT_0_EU = "70364d69-174a-4804-881e-852bdbff59e2" %}”,
“{% assign VAT_20 = "b926c2d8-09e4-496c-9a2c-818c8aaa36ed" %}”,
“{% assign VAT_20_on_imports = "11e650b0-ec87-4e45-9049-10a83ea4bed6" %}”,
“{% assign VAT_5 = "56769971-405e-47bd-bd13-d64de0eae752" %}”,
“{% assign VAT_5_on_imports = "4ca35740-f9b7-4dc6-bc88-08bdc8715994" %}”,
“{% assign VAT_exempt = "42a5002c-5c8f-4def-8672-4e6f3fc09654" %}”,
“{% assign VAT_23_EU = "abf4ba39-20eb-425f-900c-8ca0df46bbb2" %}”,
“”,
“{% assign totalSales = objects | select: ‘TaxCode.Key’, ‘totalSales’ %}”,
“{% assign totalPurchases = objects | select: ‘TaxCode.Key’, ‘totalPurchases’ %}”,
“{% assign taxOnSales = objects | select: ‘TaxCode.Key’, ‘taxOnSales’ %}”,
“{% assign taxOnPurchases = objects | select: ‘TaxCode.Key’, ‘taxOnPurchases’ %}”,
“{% assign taxOnSalesMinusTaxOnPurchases = objects | select: ‘TaxCode.Key’, ‘taxOnSalesMinusTaxOnPurchases’ %}”,
“”,
“{% assign G1 = taxOnSales[VAT_5] | plus: taxOnSales[VAT_20] | plus: taxOnSales[VAT_23_EU] %}”,
“{% assign G2 = 0 %}”,
“{% assign G3 = G1 | plus: G2 %}”,
“{% assign G4 = taxOnPurchases[VAT_5] | plus: taxOnPurchases[VAT_20] | plus: taxOnPurchases[VAT_23_EU] | minus: taxOnSalesMinusTaxOnPurchases[VAT_5_on_imports] | minus: taxOnSalesMinusTaxOnPurchases[VAT_20_on_imports] | minus: taxOnSalesMinusTaxOnPurchases[VAT_23_EU] %}”,
“{% assign G5 = G3 | minus: G4 %}”,
“{% assign G6 = totalSales[VAT_0] | plus: totalSales[VAT_0_EU] | plus: totalSales[VAT_5] | plus: totalSales[VAT_20] | plus: totalSales[VAT_exempt] | minus: G1 | round %}”,
“{% assign G7_VAT_5_on_imports = taxOnSalesMinusTaxOnPurchases[VAT_5_on_imports] | times: -20 %}”,
“{% assign G7_VAT_20_on_imports = taxOnSalesMinusTaxOnPurchases[VAT_20_on_imports] | times: -5 %}”,
“{% assign G7_VAT_23_EU = taxOnSalesMinusTaxOnPurchases[VAT_23_EU] | times: -23 %}”,
“{% assign G7 = totalPurchases[VAT_0] | plus: totalPurchases[VAT_0_EU] | plus: totalPurchases[VAT_5] | plus: totalPurchases[VAT_20] | plus: totalPurchases[VAT_23_EU] | plus: totalPurchases[VAT_exempt] | plus: G7_VAT_5_on_imports | plus: G7_VAT_20_on_imports | plus: G7_VAT_23_EU | minus: G4 | round %}”,
“{% assign G8 = totalSales[VAT_0_EU] | | plus: totalSales[VAT_23_EU] | default: 0 | round %}”,
“{% assign G9 = totalPurchases[VAT_0_EU] | plus: totalPurchases[VAT_23_EU] | default: 0 | round %}”,
“”,
“{% capture exportToCSV %}{{ G1 }}”,
“{{ G2 }}”,
“{{ G3 }}”,
“{{ G4 }}”,
“{{ G5 }}”,
“{{ G6 }}”,
“{{ G7 }}”,
“{{ G8 }}”,
“{{ G9 }}”,
“{% endcapture %}”,
“”,
“{% assign G1 = G1 | format: ‘n2’ %}”,
“{% assign G2 = G2 | format: ‘n2’ %}”,
“{% assign G3 = G3 | format: ‘n2’ %}”,
“{% assign G4 = G4 | format: ‘n2’ %}”,
“{% assign G5 = G5 | format: ‘n2’ %}”,
“{% assign G6 = G6 | format: ‘n0’ %}”,
“{% assign G7 = G7 | format: ‘n0’ %}”,
“{% assign G8 = G8 | format: ‘n0’ %}”,
“{% assign G9 = G9 | format: ‘n0’ %}”
],
“Spreadsheet”: “{"colWidths":[537,32,149,109],"data":[["{{ business.name }}",null,null,null],["VAT Calculation Worksheet",null,null,null],["For the period from {{ report.From | date: ‘dd MMMM yyyy’ }} until {{ report.To | date: ‘dd MMMM yyyy’ }}",null,null,null],["{% if report.accountingBasis == \"AccrualBasis\" %}",null,null,null],["Accrual basis",null,null,null],["{% endif %}",null,null,null],["{% if report.accountingBasis == \"CashBasis\" %}",null,null,null],["Cash basis",null,null,null],["{% endif %}",null,null,null],[null,null,null,null],["VAT Calculations",null,null,null],["VAT due in this period on sales and other outputs","£","{{ G1 }}","#1 on the VAT Return"],["VAT due in this period on acquisitions from other EC Member States","£","{{ G2 }}","#2 on the VAT Return"],["Total VAT due (the sum of boxes 1 and 2)","£","{{ G3 }}","#3 on the VAT Return"],["VAT reclaimed in this period on purchases and other inputs (including acquisitions from the EC)","£","{{ G4 }}","#4 on the VAT Return"],["Net VAT to be paid to Customs or reclaimed by you (Difference between boxes 3 and 4)","£","{{ G5 }}","#5 on the VAT Return"],["Sales and Purchases Excluding VAT",null,null,null],["Total value of sales and all other outputs excluding any VAT. Include your box 8 figure.","£","{{ G6 }}","#6 on the VAT Return"],["Total value of purchases and all other inputs excluding any VAT. Include your box 9 figure.","£","{{ G7 }}","#7 on the VAT Return"],["Total value of all supplies of goods and related costs, excluding any VAT, to other EC Member States.","£","{{ G8 }}","#8 on the VAT Return"],["Total value of acquisitions of goods and related costs, excluding any VAT, from other EC Member States.","£","{{ G9 }}","#9 on the VAT Return"],[null,null,null,null],["<form method=\"POST\" action=\"download\">\n\t\t <input type=\"hidden\" name=\"filename\" value=\"VAT Calculation Worksheet for the period from {{ report.From | date: ‘dd MMMM yyyy’ }} until {{ report.To | date: ‘dd MMMM yyyy’ }}.csv\">\n\t\t <input type=\"hidden\" name=\"content\" value=\"{{ exportToCSV }}\"><input type=\"submit\" value=\"Export to CSV\" class=\"btn btn-warning\" style=\"font-weight: bold\">",null,null,null],[null,null,null,null],["To lodge this VAT return electronically:",null,null,null],["Export this report to CSV file by clicking on Export to CSV button",null,null,null],["Go to <a href=\"https://www.taxoptimiser.co.uk\" target=\"_blank\">https://www.taxoptimiser.co.uk and login",null,null,null],["If this is your first lodgement, in the left navigation click on HMRC, then VAT Settings and set Software Template to Manager.io",null,null,null],["In the left navigation, click on HMRC, then VAT Returns",null,null,null],["Click on the period you are making lodgement for",null,null,null],["Drag and drop exported CSV file to designated location",null,null,null],["After upload is complete, verify the figures and click Send VAT Return button.",null,null,null]],"cell":[{"row":0,"col":0,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":0,"col":1,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":0,"col":2,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":0,"col":3,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":0,"col":4,"className":" remove_col"},{"row":1,"col":0,"className":" htCenter alignment-center font-weight-bold mergeCells font-size-x-large"},{"row":1,"col":1,"className":" htCenter alignment-center font-weight-bold mergeCells font-size-x-large"},{"row":1,"col":2,"className":" htCenter alignment-center font-weight-bold mergeCells font-size-x-large"},{"row":1,"col":3,"className":" htCenter alignment-center font-weight-bold mergeCells font-size-x-large"},{"row":1,"col":4,"className":" remove_col"},{"row":2,"col":0,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":2,"col":1,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":2,"col":2,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":2,"col":3,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":2,"col":4,"className":" remove_col"},{"row":3,"col":0},{"row":3,"col":1},{"row":3,"col":2},{"row":3,"col":3},{"row":3,"col":4,"className":" remove_col"},{"row":4,"col":0,"className":" row_below row_below font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":4,"col":1,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":4,"col":2,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":4,"col":3,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":4,"col":4,"className":" remove_col"},{"row":5,"col":0},{"row":5,"col":1},{"row":5,"col":2},{"row":5,"col":3},{"row":5,"col":4,"className":" remove_col"},{"row":6,"col":0,"className":" row_below row_below row_below row_below"},{"row":6,"col":1},{"row":6,"col":2},{"row":6,"col":3},{"row":6,"col":4,"className":" remove_col"},{"row":7,"col":0,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":7,"col":1,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":7,"col":2,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":7,"col":3,"className":" font-size-large htCenter alignment-center font-weight-bold mergeCells"},{"row":7,"col":4,"className":" remove_col"},{"row":8,"col":0,"className":" row_below"},{"row":8,"col":1},{"row":8,"col":2},{"row":8,"col":3},{"row":8,"col":4,"className":" remove_col"},{"row":9,"col":0,"className":" row_below"},{"row":9,"col":1},{"row":9,"col":2},{"row":9,"col":3},{"row":9,"col":4,"className":" remove_col"},{"row":10,"col":0,"className":" row_below mergeCells cell-style-dark font-weight-bold"},{"row":10,"col":1,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":10,"col":2,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":10,"col":3,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":10,"col":4,"className":" remove_col"},{"row":11,"col":0},{"row":11,"col":1,"className":"htRight alignment-right"},{"row":11,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":11,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":11,"col":4,"className":" remove_col"},{"row":12,"col":0,"className":" row_below"},{"row":12,"col":1,"className":"htRight alignment-right"},{"row":12,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":12,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":12,"col":4,"className":" remove_col"},{"row":13,"col":0,"className":" row_below font-weight-bold"},{"row":13,"col":1,"className":"htRight alignment-right"},{"row":13,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":13,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":13,"col":4,"className":" remove_col"},{"row":14,"col":0,"className":" row_below"},{"row":14,"col":1,"className":"htRight alignment-right"},{"row":14,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":14,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":14,"col":4,"className":" remove_col"},{"row":15,"col":0,"className":" font-weight-bold"},{"row":15,"col":1,"className":"htRight alignment-right"},{"row":15,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":15,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":15,"col":4,"className":" remove_col"},{"row":16,"col":0,"className":" row_above mergeCells cell-style-dark font-weight-bold"},{"row":16,"col":1,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":16,"col":2,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":16,"col":3,"className":" mergeCells cell-style-dark font-weight-bold"},{"row":16,"col":4,"className":" remove_col"},{"row":17,"col":0,"className":" row_below"},{"row":17,"col":1,"className":" row_below htRight alignment-right"},{"row":17,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":17,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":17,"col":4,"className":" remove_col"},{"row":18,"col":0,"className":" row_below"},{"row":18,"col":1,"className":"htRight alignment-right"},{"row":18,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":18,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":19,"col":0},{"row":19,"col":1,"className":"htRight alignment-right"},{"row":19,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":19,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":20,"col":0,"className":" row_below"},{"row":20,"col":1,"className":"htRight alignment-right"},{"row":20,"col":2,"className":" cell-style-input font-weight-bold htRight alignment-right"},{"row":20,"col":3,"className":" htCenter alignment-center font-size-x-small"},{"row":21,"col":0,"className":" row_below row_below"},{"row":21,"col":1},{"row":21,"col":2},{"row":21,"col":3},{"row":22,"col":0},{"row":22,"col":1},{"row":22,"col":2},{"row":22,"col":3},{"row":23,"col":0,"className":" row_above"},{"row":23,"col":1,"className":" row_above"},{"row":23,"col":2,"className":" row_above"},{"row":23,"col":3,"className":" row_above"},{"row":24,"col":0,"className":" mergeCells font-weight-bold row_below mergeCells mergeCells font-size-normal"},{"row":24,"col":1,"className":" mergeCells font-weight-bold row_below mergeCells mergeCells font-size-normal"},{"row":24,"col":2,"className":" mergeCells font-weight-bold row_below mergeCells mergeCells font-size-normal"},{"row":24,"col":3,"className":" mergeCells font-weight-bold row_below mergeCells mergeCells font-size-normal"},{"row":25,"col":0,"className":" row_below mergeCells font-size-normal"},{"row":25,"col":1,"className":" mergeCells font-size-normal"},{"row":25,"col":2,"className":" mergeCells font-size-normal"},{"row":25,"col":3,"className":" mergeCells font-size-normal"},{"row":26,"col":0,"className":" row_below row_below row_below mergeCells font-size-normal"},{"row":26,"col":1,"className":" mergeCells font-size-normal"},{"row":26,"col":2,"className":" mergeCells font-size-normal"},{"row":26,"col":3,"className":" mergeCells font-size-normal"},{"row":27,"col":0,"className":" row_below mergeCells font-size-normal"},{"row":27,"col":1,"className":" mergeCells font-size-normal"},{"row":27,"col":2,"className":" mergeCells font-size-normal"},{"row":27,"col":3,"className":" mergeCells font-size-normal"},{"row":28,"col":0,"className":" mergeCells font-size-normal"},{"row":28,"col":1,"className":" mergeCells font-size-normal"},{"row":28,"col":2,"className":" mergeCells font-size-normal"},{"row":28,"col":3,"className":" mergeCells font-size-normal"},{"row":29,"col":0,"className":" mergeCells font-size-normal"},{"row":29,"col":1,"className":" mergeCells font-size-normal"},{"row":29,"col":2,"className":" mergeCells font-size-normal"},{"row":29,"col":3,"className":" mergeCells font-size-normal"},{"row":30,"col":0,"className":" row_below mergeCells font-size-normal"},{"row":30,"col":1,"className":" mergeCells font-size-normal"},{"row":30,"col":2,"className":" mergeCells font-size-normal"},{"row":30,"col":3,"className":" mergeCells font-size-normal"},{"row":31,"col":0,"className":" mergeCells font-size-normal"},{"row":31,"col":1,"className":" mergeCells font-size-normal"},{"row":31,"col":2,"className":" mergeCells font-size-normal"},{"row":31,"col":3,"className":" mergeCells font-size-normal"}],"mergeCells":[{"col":0,"row":10,"colspan":4,"rowspan":1},{"col":0,"row":16,"colspan":4,"rowspan":1},{"col":0,"row":0,"colspan":4,"rowspan":1},{"col":0,"row":1,"colspan":4,"rowspan":1},{"col":0,"row":2,"colspan":4,"rowspan":1},{"col":0,"row":4,"colspan":4,"rowspan":1},{"col":0,"row":7,"colspan":4,"rowspan":1},{"col":0,"row":31,"colspan":4,"rowspan":1},{"col":0,"row":30,"colspan":4,"rowspan":1},{"col":0,"row":29,"colspan":4,"rowspan":1},{"col":0,"row":28,"colspan":4,"rowspan":1},{"col":0,"row":27,"colspan":4,"rowspan":1},{"col":0,"row":26,"colspan":4,"rowspan":1},{"col":0,"row":25,"colspan":4,"rowspan":1},{"col":0,"row":24,"colspan":4,"rowspan":1}]}”,
“Source”: “https://www.manager.io/localizations/gb/12e5e9fbd8e84fceaa338ba564117550.json”
}
],
“TaxCodes”: [
{
“Key”: “11e650b0-ec87-4e45-9049-10a83ea4bed6”,
“Name”: “VAT 20% on Imports”,
“Rate”: 0.0,
“TaxRate”: “TotalRate”,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “42a5002c-5c8f-4def-8672-4e6f3fc09654”,
“Name”: “VAT Exempt”,
“Rate”: 0.0,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “4ca35740-f9b7-4dc6-bc88-08bdc8715994”,
“Name”: “VAT 5% on Imports”,
“Rate”: 0.0,
“TaxRate”: “TotalRate”,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “56769971-405e-47bd-bd13-d64de0eae752”,
“Name”: “VAT 5%”,
“Rate”: 5.0,
“TaxRate”: “CustomRate”,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “6959fb01-3a48-486a-9bec-a0681a662f03”,
“Name”: “VAT 0%”,
“Rate”: 0.0,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “70364d69-174a-4804-881e-852bdbff59e2”,
“Name”: “VAT 0% (EU)”,
“Rate”: 0.0,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “b926c2d8-09e4-496c-9a2c-818c8aaa36ed”,
“Name”: “VAT 20%”,
“Rate”: 20.0,
“TaxRate”: “CustomRate”,
“HasFlatRate”: false,
“IsReverseCharged”: false,
“ReverseChargedRate”: 0.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
},
{
“Key”: “abf4ba39-20eb-425f-900c-8ca0df46bbb2”,
“Name”: “VAT 23% EU”,
“Rate”: 0.0,
“HasFlatRate”: false,
“IsReverseCharged”: true,
“ReverseChargedRate”: 23.0,
“CustomSalesInvoiceTitle”: false,
“CustomCreditNoteTitle”: false
}
]
}

HOWEVER, although this corrected the VAT Calculation Worksheet, the balance sheet reported a value in the Suspense Account that was not there previously ssemingly from the 20% VAT transactions. Have I incorrectly coded the .json file or is there something else I should have done?

Any help or suggestions would be appreciated.

Tut · March 31, 2020, 4:53pm

You probably just did not assign your new tax code to a tax liability account. These assignments became discretionary a few months ago for added flexibility. They used to all go to Tax payable without the possibility of changing them.

mpb · March 31, 2020, 6:24pm

Thanks Tut. How do I assign it to it’s own account?

mpb · March 31, 2020, 6:53pm

How do I apply an account code to a reverse charge code. That facility does not appear to be available. If I select reverse charge code there is no account code option.

Joe91 · March 31, 2020, 7:37pm

Is it not in edit screen of the Tax Code?

mpb · March 31, 2020, 7:41pm

Not it the tax code is reverse charge

Tut · March 31, 2020, 7:45pm

@mpb, I apologize for a hurried answer earlier today. I focused on the most frequent problem with the new tax liability account flexibility and did not have time to address some nuances. So let me furnish more information.

Make sure you have completely read the Guide about reverse charged VAT: Reverse charge VAT (Value Added Tax) | Manager. In it, you will be reminded that no money actually changes hands under reverse charge schemes. Therefore, no tax liability account is allowed (or necessary) for such tax codes. (I had forgotten that fact.)

I just modeled a reverse charged VAT transaction with Manager v20.2.44. Everything behaved properly, and both the Tax Summary and Tax Transactions reports were correct. Nothing went to the Suspense account. So whatever caused your transaction(s) to end up there were not the result of a correct reverse charged tax code definition. (You should post your tax code Edit screen so we can see if you, in fact, defined it correctly.)

The VAT calculation worksheet is a transformation of the Tax Summary report. If your Tax Summary is correct, as you say it is, but your personally revised worksheet is incorrect, the fault lies with your own coding. That goes beyond my skill set. Nor would I debug your code even if I was able to. But there is no reason an incorrect transformation would send transactions to Suspense. So I think you should separate the two in your mind.

mpb · March 31, 2020, 7:52pm

Thanks Tut. I shall look into it later.

mpb · April 1, 2020, 11:08am

The problem I have is with what is required in completing the VAT Calculation Sheet as specified by UK HMRC. See image below

The VAT element of the reverse charge should be added to Boxes 1, 4, 6, 7 and I believe 9 (EU purchases).

The Summary report has the correct Tax on Sales (for Box 1), Tax on Purchases (for Box 4) and Net Purchases (for Box 7) but these are shown on the Worksheet without the reverse charge tax, and as it is from an EU country the reverse charge tax needs to added to the VAT 0% (EU) value for Box 9.

In essence the Tax Summary report is not transferring the values to the worksheet report correctly. That is what I was trying to do with the modified .json file.

Tut · April 1, 2020, 1:05pm

@mpb, the worksheet was never offered or represented as a legal form of tax filing, only as a worksheet. If you or your tax advisor believe it is incorrect, you are free to modify its output when completing your official tax filing. But, as I said previously, I cannot reproduce the behavior you are seeing in the program. So I believe it comes from some other problem. Identify why your transactions are in the Suspense account first. Those are always caused by errors upon input. Only then will you be able to identify and correct other errors, including any in your recoding of the *.json file.

mpb · April 2, 2020, 3:17pm

An Update. I appear to have found why the VAT 20% transactions were going into the Suspense account. When I upload my .json file for the modified VAT Calculation Sheet it changes the VAT 20% setting from the VAT control account to Suspense!!! When that tax code is corrected everything is fine, but why should uploading the file change the built in VAT 20% setting?

Tut · April 2, 2020, 4:55pm

This is exactly the problem I suggested in post #2. I do not know the exact inner workings of the program, but I suspect assignments of tax liability accounts are controlled by the transformation. If they were not, the program would not know where to look for various amounts necessary for the report. Many users have reported the need to reload localizations after the tax liability assignments became discretionary.

tiensinh2929 · April 3, 2020, 7:22pm

thank thank Tut <3