Bookkeeping & VAT (2023)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

How to incorporate VAT submission in a spreadsheet bookkeeping?

Issues

It's now the end of March and I need to do the submission over February. I hoped that there are some figures that I just need to copy to do the submission. Unfortunately, I run into the following issues:

  • On sheet All I cannot just copy the numbers, because these also contains the numbers for (part of) March already
  • On sheet February I can see the numbers for February, but not whether a correction on previous declarations is necessary - That should be more or less automatic
  • I need a place where the running totals up to the relevant month are shown. That seems like a different kind of entity than what I currently have (months, all, opening balance, closing balance).

In earlier iterations of this spreadsheet bookkeeping system, I used to have an additional account BTW payed related to VAT fillings but also a separate column with nett figures, called Saldo: BTW to be payed over this period. However, that didn't work out either:

  • When there is a year that the company should get a nett reimbursement of VAT, the number appears on the right side of the balance: When there is only one nett column, it would logically be about disbursement of VAT. But in the unusual situation that the company actually gets VAT back, there will be a negative number here, rather than a positive number on the other side of the balance
  • Inevitably, errors in earlier periods will emerge and they need to be corrected in following submissions. However, that's not easily done with this additional column. It was possible, but it was messy and complex.

Lastly: I'm not really sure how VAT submissions appear in the spreadsheet. Do I need to use the cummulative amount over all months? Or only this month?

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

Question

When it turns March 25, and 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?

Solution: Sheet 'All'

Easy: It's the amount in cell I5:

  • 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 amountin cell I5 (more about that later)
It's the amount in cell I5, and it happens to be €0

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

Here the fun starts. The only difference with the first example: There is a transaction in the beginning of March. This will mess up the simple approach from the example above.

Mutations & issue

Transactions March - What will be the amount for the VAT submission over February?
Sheet 'All': The amount in I5 is now incorrect, as it already incorporates VAT over the first days of March

Solution

The solution is quite simple: On sheet All, add a section VAT submissions that basically contains the running sum in each row in column P

Example (3): Retro-active correction

One more complication: On March 1, I found out that I actually forgot to include an invoice from January. How to incorporate this?

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

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=33697"