VAT invoice correct, wrong bookings general Ledger

I generate a sales invoice of € 2.250,00. VAT excluded. VAT is 21% = € 472,50.
Invoice shows: amount € 2.250,00, VAT € 472,50 Total € 2.722,50
So far so good.

I receive the payment at the bank from the customer € 2.722,50.
I book this amount including 21 % VAT.
The sales invoice looks fine

Problem:
The outcome at the Ledger Account Sales: Credit € 2.150,77 instead of € 2.250,00:
The Payable VAT shows € 571,73 which is wrong and should be € 472,50

This means that the system calculates 21% VAT over € 2.722,50 = € 571,73 instead of:
€ 2.722,50 - € 2.722,50/(1+21%) = € 472,50 and hence also the wrong amount of credit.

Has anyone a solution or do I make a mistake in the setup?

Regards

Gerard

The invoice already has the VAT requirements encoded in it. No further VAT allocation is required so when your customer pays, it is allocated to account receivable, that customer (that invoice) with no additional VAT

Dear Path,

Thank you for sending me your information.
I have set-up a test company and indeed all works fine.
In my operational company I have deleted the invoice and also the from the bank received amount and GL and VAT is cleared. So far so good. I make the same invoice and enter the received money and problem is the same as before. Hence it works in a test situation (new company) but not in my operational system. It looks that some items are not fully cleared in the system. ï’m reluctant to start all over again.

This seems to be related to tax included and tax excluded pricing - have you read the guide Choose between tax-exclusive and tax-inclusive prices

and/or using Cash or Accrual accounting - see this guide Choose between accrual or cash basis accounting

Thanks Joe91,
Yes I understand. As it does not occur in the test system, I think it is a one-off problem in my operational system as I do exactly the same as in my test-system. The test system is ok but not my operational system.
I’m using accrual accounting.

If you post screen images of the relevant edit transactions, we can give more specific help

@G.Hermans, your description of what you did is a bit unclear. You say you deleted both the sales invoice and the receipt for the payment, then re-entered them. But did you re-enter them the same way as originally? It sounds like you did. Let me reinforce what @Patch wrote:

  • When creating the sales invoice, apply your VAT tax code. The invoice can be tax-inclusive or tax-exclusive, as @Joe91 mentioned. That choice depends on whether the VAT is included in your unit prices or added to them. The sales invoice is the transaction recording the sale and imposing VAT.
  • When entering the receipt for money from the customer, do not apply any tax code. The receipt is not recording a sale, only payment of a debt by the customer. The receipt is posted to Accounts receivable, not any income account.

Dear Joe91 and Tut,

I shall try to describe as clear as possible:

1 Test-system:
a - I make up a sales-invoice with VAT excluded from the amount
b - I selected the VAT of 21 %
c - The invoice gives:
i - Sub-total: € 2.250,00
ii - VAT High: € 472,50
iii - Total: € 2.722,50
d - I select at the sales-invoice: New receivable:
i - The indicator ‘Amounts are inclusive VAT’ is automatic selected.
ii - I have not selected any VAT at the end of the line of the GL: Debtors.
iii - I specified that the full amount was paid
iv - The invoice shows:
1 The above of 1.c
2 Received with date for the amount of € 2.722.50
e - By doing so, the invoice is complete and correct.
f - I check the GL:
i - Gl Sales gives the right answer of € 2.250,00
ii - VAT Payable give the correct answer of € 472,50
iii - Conclusion the invoice and trx’s are correct.
2 - The operational system:
a - As the system has already trx’s in it I cannot change too much.
b - This particular setup is in use for a forensic valuation of a business and I have loaded first all the bank transactions.
c - Then a generated the invoice as above.
d - After that I matched invoice with payment.
e - I find the problem of VAT after which I deleted the bank trx and then the invoice.
f - I generated first again the invoice and then the payment of the invoice and the same problem showed up. The invoice is ok, just as described under 1. a-e. The VAT payable ( € 571,73) and the GL-sales (€ 2.150,78) are wrong amounts.

I hope this clarify what I did.

Dear Joe91 and Tut,

It might well take quite some time to solve the problem. To me it looks like a one-off situation as the test system works well. Possibly something is locked.(hardware - disk spot failure?)
If it would take too much time I can start over again and generated a new company and load again all the information. It takes time but maybe less than looking for the solution of the problem (of course I would like very much the reason of the problem) as I have limited time and under time pressure.
Notwithstanding this, I appreciate very much your effort to guide me to solve the problem, specially your response during the weekend. But I must deliver in time. So If I cannot get it solved by 1 to 2 days from now, I need to start all over again in order to meet my schedule.

Kind regards,

Gerard

What computer operating system do you use, windows, Mac, Linux? The procedure to do a screen capture differs in each. You can then use a paint / bit mapped graphics editor to select the part of the screen image of relevance and obscure any confidential information. The image can then be uploaded to the forum using the up arrow icon when posting

As @Joe91 indicated, Manager edit screens are most informative

We really need to see screen images of the edit transactions, anything else is just guesswork

You have not mentioned whether there are other transactions in the operational business. Your complaint seems to be that the Tax payable account is incorrect. So drill down on it by clicking on the Summary page. See what transactions contribute to that balance. It sounds to me that the problem does not originate with this receipt and invoice, because your procedure seems correct and produced the correct result with your test business.

I’m using win 7 - 64

I have generated in the operational system:
1 -new bank account for test
2 generated a new customer
3 generated a new sales invoice
4 payed the invoice
5 checked VAT payable an GL sales.

The pictures are numbered in the following order as I worked in the system.

I hope this made it clear.

The problem still exist, also with the new customer/invoice and payment.

Kind Regards,

Gerard

Over here in The Netherlands it is now Sunday, nearly 24:00 hrs. I cannot replay if you have questions as I will be out of the office for the next 2 days.

Thanks, Kind regards

Gerard

@G.Hermans, I think I know where you went wrong, though not yet exactly how.

You seem to be using a custom tax code, BTW Hoog - 21%. If so, why are you doing this? You should be using the Netherlands localization package, which already includes four built-in 21% VAT codes. See https://www.manager.io/guides/22137.

One reason is that custom tax codes are not picked up on any reports, because the program does not know in advance they will be there. While this will not affect a simple invoice and receipt, it will cause other problems.

To see exactly what you have done, please post a screen shot of the Edit screen of this tax code from the Settings tab.

Hi Tut,

In between things; I did not known that this existed as an answer to your question why to use this tax code.
I will install it and come back to give you the results.

Hi Tut,

Thanks very much of inform me about this link. The problem is solved, It works perfectly.
Thanks to you and to Joe91 and I’m very satisfied the way you responded.
I will keep such an update in mind!

Best regards

Gerard