Percentages (VAT)

[fat controller]

Brief background - a good friend of mine has inherited a small business as a result of a fairly nasty family breakdown, and prior to that breakdown the family that were running the business literally ran it into the ground (knowing they were going to have to hand it over); in short, there is less than zero money in terms of capital, but the business is operating and slowly growing, so there is potential.

The admin side is a bit of a mess, and I have been asked if I can help out with a database to track the day and daily stuff (jobs, invoices, costs, customers etc); most of it is relatively straightforward (I think!) but the one thing that is puzzling me is VAT.

Currently, the business is operating below the VAT threshold, however the hope is that things will grow and they will become VAT registered at some point in the future. Therefore, I think it is sensible that I build in the ability to cope with VAT at this stage to save grief later on.

Now adding VAT on by a percentage (there are variable rates in their industry), seems straight forward to me.

Ex VAT Price x VAT% = Total (£100 x 20% = £120)

But, how would I do that in the opposite direction in Access? If something cost £120, and the user put in a rate of 20%, how would I get that to work out what the ex VAT cost was bearing in mind that the 20% is an applied rate?

PS - I am asking this before even building the relevant table(s), so I have no preconceived ideas or restrictions

Thanks in advance

 

[arnelgp]

you already have the formula there:

Evat x (1 + Vat%) = TotalCost

 

[fat controller]

So, that would be

Net Price = TotalCost / (1 + VAT%)?

Sorry if I am being utterly thick

 

[arnelgp]

you transposed it on the other side:

[Net Price] x (1 + VAT%) = TotalCost

then

[Net Price] = TotalPrice / (1 + VAT%)

remember our math:

xy = z
x = z / y
y = z / x

 

[fat controller]

Thank you

 

[gemma-the-husky]

fwiw, you ought to have a table for VAT rates, as it might not always be 20%. If it changes you need to know details of those changes.

22.5% wef 1/1/2018
20.0 % before that

I would also be inclined to store the VAT rate applying to each invoice in the invoice record.

Note also that a printed invoice must contain some particular things. Invoice Date. TaxPoint Date. The words "SALES INVOICE" etc. The various VAT notices explain more.