EU VAT tariffs and blanks
I enjoyed reading your insights into returning the position for the first true statement (ie the first non-blank cell). What is unclear to me is how your DB VAT is set up to benefit from this logic. Would love to read more in a future post maybe ;-)?
In the European Union, residential clients pay for goods & services they order online the same amount of VAT as in their home country. That is bad news for the Danish who pay 25% VAT and good news for the inhabitants of Luxembourg (17%).
The objective is to avoid online competition on price between EU countries based on VAT tariffs. A book sold via Luxembourg under previous rulings was way more interesting than via Danmark. This is no longer the case.
How to start correctly?
Once you see VAT tariffs, you don’t directly see that the rate is related to un underlying VAT Type. There are 5. Below the set up of the main block in my VAT architecture.
Instead of comparing percentages per country (high with high, low with low), we compare VAT types, lookup the associated percentage per country and apply the tariff in our calculation. In Coda terms we chain. We jump from table to table via the famous dot. Below the main structure.
The thing is that not all countries have Reduced and Lower VAT rates. I guess that most solutions simplify the calculation by looking for the max tariff in case there is not a corresponding VAT type. As such you are sure the governments overseeing your application of their rules remain happy. Better save than sorry.
But this does not seem fair to me. So instead of looking for the max value, we look for the next value which is in line with the logic of having different VAT types.
However the only way to benefit from this is if you do not have a Reduced VAT Type (like 6% in Belgium for Tea or 9% in The Netherlands) but only a Lower VAT (like 12%). This fact I did not check before setting up the calculation and it seems not to happen when I search through all the VAT tariff variations. In fact the reality is that for all cases where no corresponding VAT type is found, the fall back is the standard VAT tariff. In Danmark there is only one variation and in countries where one is missing it is always the Lower VAT type that is missing.
The relevance for this exercise so it seems is only indirect. The fall back option I judged as unfair is apparently practical and just due to the actual EU VAT logic. However once more VAT types got introduced (and systems like this tend to evolve from simple to complex) this logic will serve well.
Looking for a
Blank() and taking the next in line is however a real problem in supply and stock management. If you want to rent a bus for 50 people you can take one that allows for 52, not one that only takes 48 pax. But if a bus for 50 and the one for 52 is not available, you take the next in line which might be one for 60 or even a Double Deck that allows for 76 or more people. Each of them is more expensive but still cheaper than two buses for 30 people with 2 drivers etc.. The human mind of a planner knows that if he does not have a fitting bus, he should up grade. If there is no bus in their own fleet, hiring one might still be a good option.
The article about skipping a
Blank() was about telling Coda to inform you likewise and about proposing smart solutions. When having 100+ buses on the road, it happens planners loose oversight and Coda might help out. My job is generate solutions that only appear relevant once the human mind delivers sub optimal or even the wrong solutions.
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.