Newsletter - March '09
|
|
Spreadsheet Tips
|
|
VAT calculations with two VAT rates.
|
 |
Spreadsheet Tips - VAT calculations with two VAT rates
|
In December of 2008, the Chancellor of the Exchequer reduced VAT
from 17.5% to 15%. This created
a lot of work for IT people, but didn't seem to do much else.
They must know something we don't.
At least, it would be better if that were true. In any case,
we now have books with two VAT rates in them.
|
|
|
VAT Calculations with two VAT rates
|
|
|
In our taxi company, Working Night 'n' Data, it's been a busy
few months.
Now it's time for the manager, Frankie North, to put together
the calculations for the VAT man. Unfortunately, the VAT rate
for
December and January was 15%, whilst it was 17.5% for November.
This
makes it a little more involved to calculate the VAT in-take for
these three months.
However, with a
spreadsheet tip, we can cut down the time required.
Fig 1 shows
the compiled sales results for November '08 through to January '09.
|
|
| Fig 1 |
|
 |
|
|
The first thing Frankie needs to do is to determine how much the
sales are,
excluding VAT. He does this using the following equation.
('Total incl.VAT' means Sales Total including VAT, 'Total excl.VAT' means
Sales Total excluding VAT)
|
|
|
|
VAT rate is determined as a number between 0 and 1,
where 0% = 0, and 100% = 1.
|
|
|
Therefore,
17.5% is 0.175.
|
|
|
For November, with 17.5% VAT rate, the equation
reads as :-
|
|
|
|
|
To represent this in the spreadsheet we go to cell C6 which will
contain the Total excl. VAT amount for November.
|
|
We enter the equation =B6/1.175 as in Fig 2.
|
|
|
B6 is the Total incl. VAT.
|
|
| Fig 2 |
|
 |
|
|
Hitting return gives us our answer, £6,895.32, as in Fig 3.
|
|
| Fig 3 |
|
 |
|
To get the VAT amount, we subtract cell C6 from cell B6. In cell D6
we enter the formula =B6-C6
See Fig 4.
|
|
| Fig 4 |
|
 |
|
|
Hitting return gives us the VAT amount, £1,206.28, as in Fig 5.
|
|
| Fig 5 |
|
 |
|
We write in the equations for December and January.
The VAT rate for these two months, however, was 15%,
so the equation to determine the Total excl. VAT has
to change. Instead of dividing by 1.175, as we did for
November, we divide by 1.15. Therefore the equation in
cell C7 is =B7/1.15 . Fig 6 shows the completed table.
|
|
| Fig 6 |
|
 |
|
|
In Row 9, Frankie adds a TOTAL row. He uses the SUM function
to add the totals for each category together. See Fig 7.
|
|
| Fig 7 |
|
 |
|
|
Fig 8 shows the the final table that Frankie has prepared.
This shows the Total (incl.VAT), the Total (excl.VAT) and the
VAT itself. Now he knows what his turnover was, how much is his
and how much he owes for VAT.
|
|
| Fig 8 |
|
 |
|
|
If you would like us to cover any specific area in future
spreadsheet tips, please
contact us.
|
|
|
|
back to top |
|
|
|
|
