Add and show vat to an exisiting amount and show total

[Guest]

I have an "invoice amount" field on a form and need to get it to calculate
VAT (17.5%) into the "invoice VAT" field and then show the total of both
fields in the "invoice total" field. I created the form from a table if that
helps or hinders! All calculations need to be to 2 decimal places. I've
looked at help but I'm more confused now than ever.

Thanks

 

[Guest]

Hi Lea, There are two ways to do this, either through a query or using the
controls on the form.

Query first. Pull through the data you require via a query, including
'invoice amount', create a new column in the query by typing in the following
- Invoice_VAT: Round([invoice amount]/1.175*0.175,2)
In another column type in Invoice_Total: Round([invoice_amount]/1.175,2)

You can then use these fields on your form.

On a form ensure the invoice amount field is on the form and has a
datasource of invoice amount and a name of invoice amount.

Create a new control and name it as Invoice_Total. In the datasource type in
=Round([invoice amount]/1.175,2)

Create the next control and name it Invoice_VAT. In the datasource type in
=[invoice amount]-Invoice_Total

If you can, I recommend you avoid using spaces in field and control names.

Also, under VAT regulations, VAT is actually calculated and rounded on each
invoice line. Consequently the above may not give quite the same answer if
comparing with a supplier invoice for example since you are rounding a
potentially larger number.

Hope this helps

regards

Ptero

 

[Guest]

Hi Lea,

Sorry, just realised you are adding the VAT.

For forms ensure you have the invoice amount on the form as before

Create a new control and call it Invoice_VAT
in datasource type =Round([invoice amount]*0.175,2)

next control call Invoice_Total
in datasource type =[invoice amount]+Invoice_VAT

My prevoius comments about VAT still apply. If you are trying to develop a
basic accounting system, I recommend you store the VAT rate, VAT amount and
Invoice Total in the same table as the invoice amount because if the VAT rate
changes you will have a problem if you need to look at the invoices again

regards

Ptero

 

[BruceM]

I started to respond to this posting, then decided to wait and see another
response. The reason I hesitated is because things such as VAT are subject
to change, and if invoice information needs to be maintained over
time,different tax rates at different times will need to be taken into
account. I wonder about the best way to accomplish this. Saving the tax
rate seems redundant, especially if it changes infrequently. I wonder if a
calculation based on date ranges would make sense:
VAT_Rate: IIf([InvoiceDate] > #01/01/00# And < #01/01/04",.150,.175)
then use that in the calculation.
I'm just trying to gather a little information here for future reference.

Hi Lea, There are two ways to do this, either through a query or using the
controls on the form.

Query first. Pull through the data you require via a query, including
'invoice amount', create a new column in the query by typing in the
following
- Invoice_VAT: Round([invoice amount]/1.175*0.175,2)
In another column type in Invoice_Total: Round([invoice_amount]/1.175,2)

You can then use these fields on your form.

On a form ensure the invoice amount field is on the form and has a
datasource of invoice amount and a name of invoice amount.

Create a new control and name it as Invoice_Total. In the datasource type
in
=Round([invoice amount]/1.175,2)

Create the next control and name it Invoice_VAT. In the datasource type in
=[invoice amount]-Invoice_Total

If you can, I recommend you avoid using spaces in field and control names.

Also, under VAT regulations, VAT is actually calculated and rounded on
each
invoice line. Consequently the above may not give quite the same answer if
comparing with a supplier invoice for example since you are rounding a
potentially larger number.

Hope this helps

regards

Ptero

I have an "invoice amount" field on a form and need to get it to
calculate
VAT (17.5%) into the "invoice VAT" field and then show the total of both
fields in the "invoice total" field. I created the form from a table if
that
helps or hinders! All calculations need to be to 2 decimal places. I've
looked at help but I'm more confused now than ever.

Thanks

 

[JK]

Hi Lea

A few point in relation to VAT:

In general VAT is applicable to only which non-exempted item *and* to non
exempted customers.

1. Applying VAT to the *whole* invoice, as distinct from individual invoice
lines suggest that you *always* charging VAT. That is you neither invoice,
nor you likely to invoice VAT exempted items.

2. It also implies that you don't have, nor likely to have VAT exempted
customers, *never!*

3. It is not a good idea to "hard code" the VAT rate because when it
changes, you would need to ament the program.

If 1 above is true then store the VAT *rate* in an exiting table, say,
where you are holding your company's details, or in a VAT table, otherwise
in your product table, against each product where VAT can be 0 or 17.5% (for
now). In such case you will need to store the VAT amount in each invoice
line.

If you *do* have customers who don't pay VAT, you need to create a (Yes/No)
field in the customers table to flag whether a customer is paying VAT or not
and test for it at the time of invoicing.

Contrary to what is suggested here, your "Invoice VAT" must be stored in a
table to enable you to do VAT reconciliation with the tax man.

Your situation in to 1, 2 and 3 above will determined how you would
calculate the VAT.

Regards/JK

 

[Guest]

Folowing on from the comments by JK, I agree. There are I think from memory
about 7 VAT rates to include exempt, zero rated, standard rated, special rate
(eg 5% for utilities) not applicable (outside the scope of VAT) and
sales/purchases to the EU (which have to be accounted for separately). Also
as an aside, special cases exist for the sales within the construction
industry for supplies to new build and grade II listed buildings.

I agree you are best advised to maintain a separate VAT table as a lookup
for the current and appropriate rate. I still recommend using a field to
store the selected rate for later auditing purposes.

Seemed such a simple question!

Ptero

 

[BruceM]

I agree with the observations, but would like to clarify something regarding
storing the VAT. What has been suggested is that there must be a way of
storing the rate; however, storing the actual amount of the VAT is neither
necessary nor desirable. The result of calculating amount times rate can be
displayed whenever it is needed.

 

[JK]

Bruce,

I fully agree with your comment *provided that* the invoicing is a
*stand-alone* system.

Failing that, all relevant amounts, whether calculated or not, must be
posted into a Ledger and balanced. eg.

Invoice Before VAT ->Credit Sales Account
VAT Amount -> Credit VAT Clearing Account
Invoice Total -> Debit Debtors, Bank or Cash Account

Doing it without storing calculated fields is a programming nightmare when
one consider that amounts which end up in the ledger can be a result of
other calculations such as Discounts, Cost Of Sales (If applicable) Currency
Conversions, Non flat delivery charges to name a few.

Being as it may, we are yet to hear from Lea (or not)

Regards
Jacob

 

[BruceM]

Now that you mention it, there has been no word from the OP.
As I do not come from an accounting background I don't know what you mean by
"posted into a ledger and balanced". If an invoice is for 5 items that cost
$20 each, and the invoice total is $133, I would want to account for the
difference rather than just accept that the difference of $33 is correct.
How did a subtotal of $100 lead to an invoice total 33% above that amount?
If it is a result of VAT plus currency conversion plus delivery charges,
those factors need to be stored. A person with a pencil and paper (and math
ability) should be able to reproduce the amount by looking at information on
the invoice. If they can, storing the result is redundant. If they cannot,
the invoice is missing important information. I am making a distinction
between storing the total and displaying the total; it does not need to be
stored in order to be displayed.
Having said that, I acknowledge that it is possible this is one of those
rare occasions when storing a calculation result makes sense from a database
design point of view, but I don't see it from here.

 

[JK]

Hi Bruce,

Insofar as invoice itself is concerned and when the invoicing is a stand
alone (i.e. not a part of an Accounting system), I already agreed with you.

The currency conversion was an example of a calculation that would need to
be posted into the ledger.

The currency conversion would *not* normally be part of an invoice. The
invoice is created in *one* currency. If that currency is the "home
currency", meaning the currency that you keep your books in, there would not
be a currency conversion. If a business has no international transactions,
such as buying and selling within it's own country there would never be
currency conversion.

If this is not the case, nearly always currencies will come into play, but
that would be done in the General Ledger


"posted into a ledger and balanced"
-----
All accounting systems are based on what is known as "double entry" whereby
every debit has a corresponding credit, meaning at all times the total
debits *must* be equal to the total credits.

A General Ledger is broken down to Accounts eg; Sales, Cost of sales, if
applicable, salaries, Electricity Cash, Plant and equipment etc.

An invoice will be posted to the ledger as follows

Say the invoice before VAT is $100.00 + 17.5% Vat= Total $117.50 (bearing in
mind that the $100.00 quite often is a calculated field totalling individual
in voice lines *and* the line total is also a calculated field
Price*Quantity at best). On the invoice itself you may decide not to keep
the results of calculation and display them as and when required, I have no
problem with that in principle - although there are other reasons not to do
that.

Now, when an invoice is generated, it has to be posted to the General Ledger
as follows:

Account Details Debit Credit
Salsas Inv 1234 100.00
VAT Clearing Inv 1234 17.50 (This money
has to paid out)
Cash on Hand Inv 1234 117.50 (assuming
Cash sale)

(Note: total Debits = Total Credits)

All of those are results of a calculated field, maybe even the Invoice
number)

Incidentally, If the invoice is in foreign currency you will convert it
before post with an appropriate comment such as "$A 133.00 @ 0.75"

Trying to look up the original calculation in the Invoice table and produce
the result in a calculated fields in the General Ledger with soon turn into
a programming nightmare because apart from invoices there other transactions
types which will have other types of calculation, or no calculations at all

Say you get electric bill $200.00 *Including* 17.5% VAT and you pay it. You
will post that bill asf:

Account Details Debit Credit
Electricity Nov Bill 170.21
VAT Clearing Elect Nov Bill 29.79
Cash On Hand 200.00

I guess that you can start see the programming problem combining the 2 type
of transactions to calculate results in the general ledger. if you are not
convinced yet Add to this, Purchases, Payroll, Inventory, Manual
Adjustments (Posting directly to the ledger).

Now, think about this: you can have literally thousands of invoices with
multiple invoice lines, same for other types of transaction. When you try to
open the ledger - you might as well go to lunch, make it a long one, before
it will open.

AND a Big "AND", if somehow you manage to do it, the taxman will never
accept a system like that, never!

I hope that I did not confuse you too much.

Regards
Jacob

 

[Guest]

I really need to ask a question regarding this subject. I cann't get ask
question page to come up, sorrry.

I need to calculate the field "daily rental fee" with 7% tax included in the
total. This for a course assignment. I thought thats what you were
discussing but you lost me by the time I finished reading the replys.

Thanks for and help

 

[JK]

Donna,

We don't do homework here. You will have to find it for yourself. If you
haven't found the answer here, try Access help or your fellow students.

Sorry/JK
(Who never did his kids homework)

 

[Guest]

Thank you, yes I did get some help from this site. I have never done my kids
or grandkids homework, The help section was where I started. My classes are
on-line. It was the instructor who, suggest this site. I didn't ask you to
do my work, I just asked for a little help! Sorry

OK! OK!

 

[JK]

OK! OK!

Firstly, VAT is almost identical to Sales Tax for the purpose of calculating
it, thus the qualifications I had about VAT (how, when applicable etc) are
the same.

Now, depending how you are storing the 7%, as 0.07 (which is the correct
way) or as 7.00

The calculation is:

If 7% stored as 0.07

DailyRentalFee=TaxFreeRental*(1+SalesTax)


If 7% Store as 7.00

DailyRentalFee=TaxFreeRental*(1+SalesTax/100)

Regards/JK

 

[JK]

Oops

You also want to round the result to the nearest cent

If 7% stored as 0.07

DailyRentalFee=Round(TaxFreeRental*(1+SalesTax),2)


If 7% Store as 7.00

DailyRentalFee=Round(TaxFreeRental*(1+SalesTax/100),2)

Regards/JK

 

[Guest]

JK,

Thank You for your explaination this time. I've gotten a better
understanding of what I will need to do, if I come across this type of
calcutation in Access in a job. Not only from reading this posting but,
also, reading other.

Donna

 

[JK]

My pleasure Donna
My apology for jumping into conclusion, I guess that under different
circumstances it would have been a complement

Regards
Jacob