Now I am going to say this from the start. I am not an accountant I just happen to be very good at finding solutions to problems and relatively good at Excel.
I say relatively good because I know people who are far better than me.
I help a small family run business optimise their VAT process. Previously everything was written down on a bit of paper and then all the receipts put into a file with invoices and finally the analysis on top.
Now how time consuming does that sound. Maybe a bit too old school given the fact that computers have been affordable for many years. It took some convincing but, after interviewing and documenting the process I simply created a spreadsheet and taught they how to use it. Thus saving 40 hours a quarter on VAT returns alone.
Am sure that everyone knows someone, a small business perhaps, a relative, who is doing something in a way which hasn’t been reviewed for years maybe even decades. Every time you see them you just think to yourself “I know how to do that better, but he/she is happy doing it their way”.
I certainly do!
No imagine the scene.
Inputting data into a spreadsheet is dull yes, but you got to remember that data input is a necessary evil. You also have to remember that the input isn’t the end result.
So I have the following columns:
- Column A = Item Number
- Column B = Description
- Column C = Gross
- Column D = VAT
- Column E = Net
- Column F = Indicator
Now I’ve highlighted the Net field because this is the main calculation field. When you look at receipts what most people would do is enter the gross figure. So if you look at the above screengrab – all that is required is a “Description” and “Gross”. The rest can either be filled-down or calculated.
NET calculation is GROSS / 1.2
VAT calculation is GROSS – NET
Obviously, you have to do a cross check against the total VAT of the receipt (some items are VAT free and other have a different rate). The input needs to match the receipts.
Don’t be afraid if you need to manually enter the VAT figure. The calculations are simple enough to either COPY and PASTE FORMULA or re-write the calculations.
Now we come to the indicator. This may seem something that can be ignored but, it can’t. This indicator (“I” or “E”) means Income and Expenditure. You can also have “R” for Refund/Credit but, let’s keep this example simple.
Now let’s fast forward….
You’ve input all your information you want to sum up that information – however you want to use criteria.
The answer is the SUMIF function.
the SUMRANGE is the column with the indicator in
the CRITERIA is “E” (in quotes) or “I” if you’re looking at income
Finally SUMRANGE is the GROSS, NET or VAT. You’ll need to do a seperate SUMIF for each of them and another three for INCOME.
It’s really simple (once you know how) and can really save hours in doing your VAT return.
Then all you need to do is one final calculation
REVENUE – EXPENDITURE (on the VAT)
and then pay the HMRC.
Should you have any questions then please do get in touch via the Numberminds Contact Us form. We also offer CIS and PAYROLL consultancy.