How to Coda with VAT — Part I
Basic rules to keep your VAT in order
In my role as controller using Coda I deal with VAT all the time. One of the great advantages of the tables in Coda is the column logic combined with a lookup towards an other table. First I create tables that contain the VAT values (even per country) and next I link these to the table in which the calculations take place. Most countries have various VAT rates. Below the ones for Belgium.
When you start using VAT you often have to add the VAT and sometimes to subtract the VAT to get the base price. Below how this goes. First we add, then we subtract.
On purpose I applied two notations. The first one using the
Sum() and the second one with the (1 + ….) . Both do the same. In simple formulas you can use the “ + “ and the “ / “ and the “ * “ without any problem. These operators keep the functions readable.
In the above you see how the “ * ” — written as
Product() — is replaced by “ / ” which we could also write as
Quotient() . The result is what you see below: a rather formal notation with one major advantage
The advantage is that you see the difference in the blink of an eye:
This insight is helpful to calculate for example the VAT part in any reduction. We take the reduction and we cut out the price part and the VAT part as you can see below.
We first turn the reduction into a negative number that permits us for getting the new base price by relating the Reduction Part (which is a negative number) to the amount we started with. In the row we have this:
100 Euro with 21 Euro VAT, resulting in 121 Euro. We reduce the number by 10 Euro and so we are left with 111 Euro. In this 10 Euro we have a VAT part (- 1,74 Euro) and a Reduction Part (-8,26 Euro).
We take the 100 Euro (the amount without VAT) and reduce it with the Reduction Part. This is the new base price. The last column is the check, we simple add again the VAT and the outcome confirms we got it right!
This is all together a rather straight forward approach. It is high school math and basic book keeping, although if you only once in a while have to do this, you easily make mistakes when you do not formulate properly. It is here fore I prefer the formal notations a bit more than the easy to read “ / “ and “ + “.
In a next blog I’ll show how to use buttons to calculate the new base price while you have the choice between a fixed reduction or a percentage.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.