Update Your VAT Rates In Excel

Tue 4th January 2011

With any change in VAT, prices across the land will have to be adjusted accordingly. This simple spreadsheet shows how easy it is to update your VAT column.

When VAT rose from 17.5% to 20%, retail outlets across the land were adjusting computerised records, calculating new prices and applying new tags to goods.

Political analysts and financial gurus discussed the rise in the news. As they debated whether the rise was (as the government said) a necessary evil or they sided with the opposition and claimed it was the wrong tax at the wrong time, the only thing that could be said with any certainty at this juncture was that we will all have to pay it.

When there is any change with the VAT, prices across the land will have to be adjusted accordingly.

So like retailers who are taking steps to adopt the new rate, let us make a simple Excel spreadsheet containing a basic formula to show the cost of four items, all of which have the new VAT charge added.

In column A1 type the label Cost. In B1 type Qty, C1 Sub (for subtotal), In D1 type VAT and finally in E1 type Total. Now add some figures to column A, say £77.50, £28.00, £39.99 and £100.00. This last one will show that we have got our calculations right as we know the VAT on a hundred pounds. Once you have typed these figures in you may like to highlight them and right click. Then select Format Cells from the menu and in the Category box, click on Currency. You can also do this for the other columns that will contain a monetary value

In the Qty column enter (going down from B2) 1, 3, 2 and 1 and go to C2 to make our first formula. Type in =A2*B2, which will multiply the adjacent cells from the two columns together, and press Enter. Highlight the cell C2 again and you will see that it has a thicker black border than surrounding cells and a small square at the bottom right hand corner. Click on this square and hold down the left mouse button and drag the border down to enclose all of the cells you will be doing calculations for, in this case four. Release the mouse button and the figures will automatically appear within column C. These are the subtotals for the quantity of items we bought.

Now to add the VAT go to the cell D2 and type in the following: =C2*0.2 and repeat the process as before by pressing enter and then dragging the small black square down the required number of rows. Release and this column will now detail the amount of VAT to be paid on the goods that were bought. While Excel has calculated that three times £28.00 plus 20% VAT comes to £100.80, but we know this calculation is correct because we can see that the VAT charged on our £100.00 item is £20.00, which we know to be 20% of 100.

For our final column we want to add together the total cost of goods with the VAT. To do this type into E2 the following: =C2+D2 and then drag the black square down once more to reveal the total costs.

If we want to see how much VAT we have paid in total then go to a cell below the VAT column, say D7, and start typing in this slightly different formula: =sum(

Now go to cell D2. You will notice that when you click on it there is now a dotted line around it. As with the thicker solid line before, hold down the left mouse button and drag the dotted line down the column to include all of the cells containing the VAT totals. Release the mouse and press Enter and the total VAT paid will now appear in D7. You could repeat this in other cells to see the totals spent etc.

A simple spreadsheet that shows the new rate of VAT on goods is hardly pushing Excel to its limits but it is an effortless and currently topical way of showing just how easy it is to make this fantastic application work for us.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1367-update-your-vat-rates-in-excel.html