Sometimes the numbers in your spreadsheet do not exactly match the numbers that are displayed on the VAT100 form that is generated. There are several possible reasons for this which are outlined below along with techniques that can be used to minimise the risk of error.
Ultimately, the surest way to to get the numbers to match exactly what is in your spreadsheet is to upload just the numbers. As soon as we start processing a spreadsheet with thousands of rows and calculations, we start to introduce the risk of errors. Every spreadsheet is unique and we can't write code to deal with every possible combination of spreadsheet manufacturer, spreadsheet software version, configuration and customer preferred methods of laying out and calculating the final values.
Possibly the most common occurrence is for boxes 6-9. HMRC do not accept returns if these boxes are not in whole pounds. If pennies are included or a decimal point, HMRC reject the return. To minimse the chance of this happening, we round the boxes to the nearest whole pound. Check your formatted result and the whole pounds are the same (after rounding). Format your calculated values to have 0 decimal places and then upload the spreadsheet again.
If you really want to ensure the numbers match, just upload the values for the boxes and nothing else. Export the sheet as a CSV and upload that. Don't create a new spreadsheet from the tab - it will not export the values, it exports links to the original document which do not work.
Generally these boxes do not match because of rounding errors in the processing of the spreadsheet. The values can be off by a penny or several pounds with complex spreadsheets. The best solution to this problem is to keep your spreadsheet simple and just upload the values for the boxes. Don't upload the formulas, just the values. mtdvatbridge.uk reads CSV's just fine.
Check your spreadsheet configuration. Some spreadsheets have 'Precision as Displayed' or 'Precision as Shown' set. This forces the spreadsheet to use exactly the values that are displayed rather than the calculated values. This isn't a big problem if all your data is manually entered and rounded to two decimal places (e.g. currency) and you are doing simple adding up and subtracting of the columns. As soon as you start bringing in percentage calculations (e.g. VAT 😓) the problems start. It can often mean that VAT amounts get rounded and this rounding affects all subsequent calculations that use it, i.e. the error is compounded.
If you turn off 'Precision as Displayed' and view your spreadsheet you will see that the numbers change (quite significantly for complex spreadsheets).
Do a search on the web for 'Precision as Displayed' for some pretty eloquent articles on pros and cons(mainly) of using it.
If you have 'Precision as Displayed' set and you don't want to turn it off then you will have to export the VAT100 values as CSV and upload just the raw data. mtdvatbridge.uk doesn't have the ability to detect 'Precision As Displayed' so will only work out the calculations using full precision maths.
Exporting your data as CSV is much simpler than modifying the spreadsheet configuration or poring through the data to find out where the rounding errors originate. Make sure you have your spreadsheet open with the values for the VAT100 as the selected worksheet and click 'File' -> 'Save As' choose 'CSV' as the file type and click save. And that's it. Saving as CSV doesn't save the whole spreadsheet, just the currently active sheet and it doesn't save formulas, just the results if the formulas. Upload the newly created file and it should work perfectly.