How to Coda with VAT — Part III
Relate your reduction to the price VAT Incl. or Excl.
Previously we demonstrated how to calculate VAT, also as part of reduction that includes VAT. The reduction could be the outcome of a percentage based calculation or a simple fixed fee.
In this blog we explore how we handle reductions related to the price with VAT — or — a reduction on the base price. In B2C it is common to let the government pay a bit of the reduction by using the end users price, thus VAT inclusive. In B2B relations, the offer is mainly related to the base price and thus more expensive for the provider of the goods.
In the previous example we used one Text Box to capture both the percentage and the fixed amount. I actually prefer two boxes so I can switch while playing with the buttons. Obviously the more variables we have, the more complex the calculations become. We have 4 scenarios.
In this logic we link each button to a check box and we we combine the option as you see below:
- Checked means price VAT exclusive and the same box unchecked VAT inclusive
- Checked means fixed fee and the unchecked version gives the percentage.
It is important to document these choices, you easily get confused and mistaken if you do not follow the initial pattern. My assumption is that the most positive values are VAT exclusive and the Fixed Amount,therefore I relate both of them to ‘True()’. Up to you to make different choices.
We created a table with two extra columns. One about VAT and one about the choice between the reduction as a fixed amount or a percentage. The buttons modify all the check boxes at once with the code you see above. This code manipulates the complete list (column).
This logic generates the 4 scenarios we mentioned:
- True — True
- True — False
- False — True
- False — False
We first define the reduction based on these 4 scenarios using a SwitchIf:
You might see directly that the the function only has 3 rules and not 4. That is because the fixed amount remains the fixed amount, only the percentage relates to the price with or without VAT.
In the tabel I reference to the states of the check boxes to set up the calculations as you can see below in which I made the choice to give a reduction of 100 Euro VAT Inclusive. We first relate the promotion to an amount, next we define the new base price based on the choice VAT Incl or Excl.
The beauty of this approach is that all out of a sudden you realize you can (un) check per product the boxes and new prices pop up, see below.
If you see this, you are ready to prepare yourself for a table that handles multiple promotion types at the same time. Before we go there, we first work out a scenario that permits for a reduction over the total. Our next blog (Part IV) deals with this.
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.