• blog/projects/accotron

Sales tax comparison report

Small adjustments to your value added tax (VAT) balance is not something that’s uncommon. In fact, governments let you write down the differences on your next return without even letting them know (if this difference is below a certain threshold, such as £10,000 in the United Kingdom or €1,000 in the Netherlands). However, you must make sure you correct them in time and do so properly.

An annual closing process for your firm will involve a control on your VAT balance and your accountant will close the gap with either a supplemental filing (re-reporting your past returns), or add the difference up to the next period’s return.

How differences come up

Ultimately by the time you are filing your tax returns, your bookkeeping should be ‘closed’ to future changes. Your bank and cash balances must be up to date, purchases and sales accounted for, and anything that might affect your VAT balance should already be final.

However, this is usually not how it goes if you’re a public accountant. If your clients manage certain parts of their bookkeeping, like booking sales invoices and credit notes, then open communication on how you are operating on the books is very, very important.

Unfortunately:

• a stray invoice that was sent out, but not booked,
• a credit note that was applied over email¹, and never touched your ledger,
• an incorrect tax rate on a purchase bill,

may just ruin your day. It happens, we’re all human², but it’s preventable.

How they are identified

With regards to your VAT balance, most bookkeeping tools will do one of these:

1. Not even tell you what the transferrable tax balance is (due to adjustments in the past).
2. Tell you what the amount is:
   a. and let you handle it manually,
   b. integrate into their already highly capable VAT filing system where it gets automatically filed.

Although the second option is already useful in itself and reduces menial work by quite a lot, there are still some issues.

The net amount by itself doesn’t tell you the what and the why. In order to be able to determine these, you will need both older copy and the new one of the same report parameters. These copies also need to include a full list of all relevant transactions. Manually (or by table diffing tools), you must compare these transactions.

Exports may not contain a full transaction breakdown attached to them, and may also lack the necessary metadata (such as unique identifiers) to get an accurate list of the differences.

It is important to know these in order to prevent mistakes from happening again, by means of changing your process of reviews and collaboration.

What can be done about them

Whether by means of communicating with the client and team what these differences are—and requiring them to always documents the changes they have made—or through improving your tooling, you can prevent such mistakes from happening again.

However, this is not a complete solution. Collaborators may end up breaking the documentation process for changes or lift lock dates³. Sometimes you have to, too.

Additionally, and alternatively, we can also build better software for auditing your books’ history. Besides improving the quality and efficiency your operational processes, we can use still computers to perform a majority of this difference-identification task. The results of which will feed into better processes too.

Software engineers have been using version control processes for decades, and nowadays it’s simply industry standard protocol to keep track of changes to your code over time via specialised tooling. Git, for example, is the most popular version control software out there, and the major code hosting platform GitHub has 200 million projects hosted on it. It’s very popular, because it is very useful.

Accounting follows similar principles in the way that it tracks adjustments to books, just like we track adjustments to code. In tracking all these changes and chaining them together, we can apply ideas from version control, like displaying changes side by side or exposing a ‘commit’ history (where a commit is analogous to an accountant’s journal entry).

How version control helps

The concept of a blockchain ledger is only unique in the fact that it uses a Merkle tree to validate its chain⁴. Besides that, the practice of immutable ledgers well predate blockchains. Accounting processes and software that implement these processes must retain an immutable chain which is timestamped on not just the date of the journal but also the calendar date on which this journal was made.

The combination of these two concepts grant us the ability to look back in time. If you analyse the group of all journals that are made between a certain period in time (like a financial year, for your income statement, or a quarter, for your VAT return) and also filter the journal creation date up to a point in time, you can effectively travel back to how your books looked like at that point.

A basic example is the following modification, which is an adjustment of a sales invoice to increase its amount from 100.00 to 120.00. Your process should be creating a reversal of the original journal (#1 reversed with #2), accompanied by a separate journal that is the modified version (#3). For example:

would be followed by

and

Because #1 and #2 cancel each other out in credit and debit, if #2 and #3 are posted at the same time the result in your books will be simply #3. In turn, we know that if #1 is posted on, say, 1 Jan 2022, and #2 and #3 on 10 Jan 2022, we can look at all journals made between 1 Jan 2022 and 31 Jan 2022 to find our culprits, #2 and #3. They are the reason why our revenue account is +20.00 and VAT payable is +4.20 relative to when we looked at our reports before 10 Jan 2022.

Having an immutable history by such means allows you to not only have an audit trail, but an audit trail that you can extract answers from. Exposing this information and visualising in detailed reports is effectively the same process as reviewing a pull request (with some caveats, not all tools support you performing actions like branching or merging).

Integrating insights into the process

There is another question about #3, after we find it, we should also go ahead and find out why it was adjusted after the fact. If this is a duplicate, then we should make sure that we have a better system in place to identify similar invoice references (e.g. INV0010 versus INV-0010, your tool may not alert you of a similarity like this because it’s not an exact match). Or if it was a genuine typo made during the booking process, then it may be useful to make it the norm to issue a supplemental invoice to correct this issue at a later date (such that your old tax returns are not impacted).

With such mistakes happening, it is very important to educate staff and have multiple eyes on the books at all times. Both are a luxury in certain ‘busy periods,’ but neither should be dismissed. There are real costs associated with late reviews, such as the mistake being repeated over time (which makes it harder to identify in the wild and correct fully) or a potential fine due to incorrectly filed returns.

Following your insights, you should make sure that processes are well understood by your collaborators (client or teammates) and documented for future reference and newcomers.

A sales tax comparison report?

For the software solution, it seems obvious what to do. We need to embrace the fact that version control is a very useful concept for bookkeeping. As a matter of fact, the standard practices of pull requests, branches, merges, etc. are just extremely foreign concepts to accountants.

There are certain constraints for our migration toward version control being standard, just as it is in software engineering, but these I discuss on another article.

Well, I’m now on a quest to build it! Let’s talk about an ideal report for our purposes. I will call it the sales tax comparison report (SCTR for short).

Things you want to see on the report

The most crucial components of this report are:

1. Inputs
   1. Period start/end dates
   2. Before/after dates
   3. Filtering by tax rates
2. Results
   1. Report at before/after dates
   2. Numerical differences by tax rate (After − Before)
   3. Culprits for chosen tax rates

With this set of parameters, the results you get help you out significantly in identifying the exact changes, and take action to fix the errors, transfer tax balances, or file supplements. For this very specific problem, it ends up being an incredibly useful tool.

A useful prototype

Essentially, what I designed is something like the following table (but a bit more interleaved and complex with multiple tax rates, etc.):

I am still iterating on the interface and that’s just a bad markdown sketch, but I found this layout to be helpful enough to assess the situation and clearly identify the changes made.

As for the ‘culprits,’ this is the name I chose for the journals created between the before and after dates. They are grouped by parent resource and brought into a chain. I’m currently working on preparing additional metadata heuristics to tag deletions/modifications in chains, to present them better in the interface.

git blame⁵ for your journals

Besides being able to identify these individual changes, it would also be very helpful to figure out who made these changes. A common critique of git blame is first of all, its name. It gives some people the idea that we are trying to point fingers and find out who’s ‘guilty.’ That is in part true, to be honest.

It does make a difference if the source of the change is a rogue intern, making a bunch of journal entries on a coffee binge, or the senior, who’s correcting the minor differences in these journals.

git blame works on code like STCR would on your sales tax reports.

This is effectively the same as picking out a quarterly report and asking ‘why is the expense account higher’ to get a list of all journals that made an impact on the specific tax rates or ledger accounts, and get a note on when and by whom these changes were made.

This is just as when you would be developing a new feature and you cannot tell why the behaviour of a certain function call is now different. But by means of looking at the history of those specific lines of code, you can reconstruct the history of why and how it has changed (and point fingers).

I discuss matters concerning rebuilding the history and visualising data over at re-stitching the chain.