Bookkeeping & VAT (2023)

Uit De Vliegende Brigade
(Doorverwezen vanaf Bookkeeping & VAT)
Naar navigatie springen Naar zoeken springen

How to incorporate VAT submission in a spreadsheet bookkeeping? And how to exactly incorporate VAT in a bookkeeping? Let's do this case-wise.

Example (1): Simple

Let's run an example to check how VAT works out during the year.

Bookkeeping facts

Starting with an 'empty' balance, these are all bookkeeping facturs:

  • Jan. 15: Sales invoice for € 2,500 (ex. 21% VAT)
  • Jan. 16: This invoice has been paid
  • Feb. 25: VAT submission over January
  • Feb. 26: Payment VAT submission over January
Transactions January
Transactions February

What's the VAT Due ammount for submission over February?

When it turns March 25, we want to do the VAT submission over February. What figures do we need, and where are they located in the bookkeeping?

Transactions March - What amount to fill in here?

Answer: 'VAT Due' on sheet 'All'

Easy: It's the amount in cell I5 - VAT Due:

  • It's the cumulative amount over the months January & February
  • This case is easy: There haven't been transactions during March 1-24 that could effect the amount in cell I5 - VAT Due (more about that later)
It's the amount in cell I5 - VAT Due, and it happens to be €0

Example (2): Cummulative amount doesn't work out

Let's introduce one new booking fact:

  • On March 3, a sale happens to Bob by invoice for € 1,000 ex. VAT
  • The corresponding VAT Due is booked on the usual account VAT Due
  • On the sheet All, the amount at VAT Due is now € 210

The problem

  • Again, March 25 arrives and we want to figure out what the amount VAT due would be
  • Unfortunately, we can't use the number on All » VAT Due anymore, as it now includes the transaction from March 3, and we need only the figures over February.
Transactions March - What will be the amount for the VAT submission over February?
Sheet 'All » VAT Due': The amount in I5 is now incorrect, as it already incorporates VAT over the first days of March

Solution (1)

On sheet All, add a section VAT submissions that basically contains the running sum in each row in column P - I don't understand

Solution (2)

This seems the easiest solution:

  • Have a separate sheet VAT (as mentioned earlier)
  • For each VAT submission, copy only the relevant figures. In this case: From February
  • The figures concerning the actual submission, are included on the monthly sheets. On the VAT sheet, use link to these figures, rather than copies of the numbers.

Example (3): Retro-active correction

And now the real pièce de resistance: How to incorporate corrections on earlier submissions?

What will now be the figure for VAT Due over February, at the submission at the end of March?

Mutations

Let's introduce a new mutation: On March 1, I found out that I actually forgot to include an invoice from January:

  • Jan. 20: Sale to Alice on invoice (2023-001b) of € 3,000 and € 630 VAT
  • Jan. 21: Alice payed by bank transfer.
Somewhere at the beginnning of March, I added the transaction & corresponding VAT (€ 630) that occured on Jan. 20 & 21. There are no VAT submission/payment yet related to this
The corrected amount VAT due just propagates through the system. Now at the end of March, I have to pay € 630, rather than € 0 as before. But there is still a problem: The revenue amount hasn't been corrected (cell O9). Solution: Calculate the numbers in column O from the number in column P - Don't make it more complicated

Notes:

  • The numbers in column O are not as they were submitted. They are as they are now. This might be confusing, but it's allright
  • If you really want to incorporate the submitted numbers: Good luck - I think that can get messy
  • The numbers in column P are up-to-date, as they are calculated as the running sum up to that month - That's the most important thing to remember
  • Numbers in O5 and O6 don't have a practical meaning - So don't use them.

Example (4): Like example (1) but including costs

  • This is like example (1) but simpler: Only the transactions from January are included. Let's see if we can see the VAT submission that we have to do at the end of February
  • At Example (3), some things were simplified by calculating revenue from due VAT. This doesn't work anymore when there are costs involved
  • No escape: We need to write somewhere the amounts of the actual VAT submissions (so there is a distinction between the 'real' numbers and the historically submitted numbers). This could be squeezed into a regular bookkeeping mutation but the trouble would be: How to also incorporate it into the All sheet? → Just make it two sets of data, eventhough there will be overlap

Bookkeeping facts

Starting with an 'empty' balance, these are all bookkeeping facturs:

  • Jan. 15: Sales invoice for € 2,500 (ex. 21% VAT)
  • Jan. 16: This invoice has been paid
  • Jan. 20: Invoice for Internet access
  • Jan. 20: Payed for Internet access
  • No transactions in February (except upcoming VAT submission - See below).
Transactions January

Question

  • When it turns Feb. 25, how does the VAT submission over January look like?
  • More specifically: Does it 'automatically' appear in the bookkeeping?

Solution

Easy: As there is no history that needs to be incorporated, the answer is in cells O8-R8. These are static formulas. The only drawback: Cells O8-Q8 contain copies values, thus creating redundancy
Subsequently, fill in the actual VAT submission figures. I included a date field to make it more obvious to myself that this is about actual submissions

Example (5): Like example (2) but with costs

The essence of example (2) was, that there were already transactions in February and March, before the date of the VAT submission around March 25. Let's see how that goes now.

The question - like before: Is there an obvious and reliable way to retrieve the VAT figures over February?

The solution is within cells Q10-T10, but it's getting less-and-less obvious. Let's see how it goes when we need to correct someting

Example (6): With retrofitted correction + costs

  • Like the one before, but now with an extra invoice in January. We only discovered this when we were about to do the VAT submission in April (over March)
  • This extra invoice is € 750, with € 157,50 additional VAT to be payed
  • Does this correction propagate all through the system?
It works, but it isn't very intuitive, because of some complications concerning causality: Should the section Actual VAT submissions where it is, or at the end? Actually both. Anyhow, see the corresponding file below for details. I'm wondering how this can be even more complicated when VAT is submitted per three months, rather than per month
Maybe this is more intuitive? Everything calculates by itself. Only the figures for the actual submissions have to be filled in manually
As a test, I entered fictional VAT submission numbers in July, so that I get about € 8.000 of VAT back. The system immediately suggests a corresponding correction - Very nice!

As an extra check: Let's add this scenario:

  • In April, nothing happens except for the VAT submission over March
  • In May, only the usual cost happen
  • Etc. - It works

How do others do this?

Files

All examples were done in these files:

  • The earliest files contain some errors and inconsistencies
  • The files don't correspond exactly with the examples - Use your commen sense.

Files:

See also

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Bookkeeping_%26_VAT_(2023)&oldid=36546"