With just over a year to go before Making Tax Digital VAT submissions are [probably] required, I am starting to think how I will deal with my Excel clients, like the ones that use a typical analysed cash book.
We will have columns for transaction date, description of transaction, gross, VAT and several columns to analyse out the net amounts.
I can then very easily import this type of spreadsheet, with a little bit of editing, into my account software e.g. Sage 50 or Sage Essentials, and submit a MTD VAT return in due course.
I know that I could get them to buy software or start using the cloud. However, I still like the simplicity of Excel for both myself and my clients.
I have had a play with Sage One Start, which costs £6 pm, and tried to import a bank statement (from an Excel sheet) or using a bank feed. It is a very slow and time consuming task.
I still find, for my small clients, the ability to receive an Excel spreadsheet, and for my own bookkeeping service, the ability to add columns, move amounts around etc is extremely powerful and flexible.
I would like to add an extra column to the Excel cash book that will insert a nominal ledger into the column based on if an amount is entered into one of the net amount analysis columns. The code would be based on the column heading.
This must be a very simple task for Excel and for anyone who has the know-how for tables and look up functions etc.
I’ve got an answer from a web company who provide 24X7 access to a quick and reliable way to get unstuck in Excel. Just visit www.excelchat.co enter a problem and get your solution in just 20 minutes. Normal cost is US$9.99 first question free.
Assuming that you want the nominal ledger code in column C (of row 10), and the cash book net (of VAT) amounts are analysed in columns F to K (of row 10) with the header code in row 7 then use the formulae
Copy the formulae down to as many rows as you have in your Excel cash book. Insert extra columns if you want between F and K