VAT, Rounding, decimal places & summing.

[Pish_Pash]

Catchy title eh? Ok, here we go....

If an item costs £9.99 including VAT....

The excluding VAT price is £8.325
The VAT is £1.665

Now I'm assuming that most retailer's Sales Receipts show only two decimal places? So we have to round (either up or down)

Problem - If you apply the same 'two decimal places' round up rule to both the VAT aspect & the excluding VAT price for the above sale, then the sales receipt will show wrong, you end up with

Unit Cost Exc. VAT...£8.33
VAT...........................£1.67

Total.........................£10.00

But the total isn't £10.00 ....it should be £9.99!!!!

I've kept it simple above, but if there are several different 'line items' in the transaction & also a shipping cost applied .......then the line cost excluding VAT, the shipping cost excluding VAT , the overall VAT total & summation thereof actually gets tricky due to rounding!

How do others approach this? (I'm coding MS Access to generate a sales receipt....but I don't need the code, just the general approach)

 

[kevin.doran]

Easier to link than type.

 

[Pish_Pash]

Thanks, but this extract from your link...

• If the VAT on any transaction comes to less than 0.5 of one penny, it should be rounded down.
• If the VAT comes to 0.5 of one penny or more, it should be rounded up.

Doesn't actually solve the issue......using HMRC's guidance, in my example above they'd both be rounded up, which means that if you show on the sales receipt the line cost Exc. VAT (£8.33) & the VAT (£1.67) would obviously total up to £10.00 .....but actually the transaction was only for £9.99!!!

It's going to confuse customers when they see that numbers don't add up on their sales receipt!

 

[john1989]

For 1p, nobody is going to care. Unless you have enough 1p's to make it material?

 

[Pish_Pash]

I realise 1p is insignificant, but if an error of 1p shows on the sales receipt, some customers care!!!!

I'll mull this one overnight...I now have devised have a cunning plan but it would take the thread in an 'access coding' direction (basically a lot of "if" statements), so I won't bog the thread down with my workaround.

Cheers for now.

 

[kevin.doran]

Thanks, but this extract from your link...

• If the VAT on any transaction comes to less than 0.5 of one penny, it should be rounded down.
• If the VAT comes to 0.5 of one penny or more, it should be rounded up.

Doesn't actually solve the issue......using HMRC's guidance, in my example above they'd both be rounded up

It says the VAT should be rounded up so the net by default would go down.

 

[Pish_Pash]

It says the VAT should be rounded up so the net by default would go down.

Exactly - simple if there's only one item (& no shipping charge in play). As I say it's a bit more involved than that, imagine a sales receipt with this layout...

SKU..........Quantity........Unit Cost (Exc VAT).........Line Cost (Exc VAT)

Then at the footer you have a Sub Total (Exc VAT), a Shipping Charge (Exc VAT), the amount of VAT & the Grand total inc VAT

Now imagine there are several SKUs on the receipt!

To implement, isn't quite as simple as saying if the VAT is rounded up, then the net goes down (becuase the VAT calculation applies to the whole order total, whereas the net figures are per SKU/Line total)

Anyway, to answer my own question (& tying in with John's input about it mattering)....I don't think it's actually possible (i.e. with rounding in play to two decimal places) to have every field calculated & exactly showing correctly ...therefore as long as the VAT is correct & as long as the order subtotal (exc. VAT) is correct, I'll bat this one back to a customer as "It's all correct,but the minor discrepancies are due to rounding" !!!!

here's the best I can do (notice how the subtotal hasn't totaled up correctly, but the Sub Total actually is correct if you remove the rounding applied above it)...

 

[kevin.doran]

Clear Books would round the VAT up on each SKU in your example:

 

[Pish_Pash]

Then clearly anyone who is VAT registered & using Clear Books is either going to pay the wrong amount of VAT or claim the wrong amount of VAT back! (depending which side of the fence you're on).....because on a £37.98 Total Order, the VAT should be £6.33 (& not £6.34 as your Clear Books screenshot shows!!!).

If I sent out those details to my retail customers, a certain percentage would say "You've calculated the VAT wrong....you've charged me too much VAT!"

(just imagine if there were 20 lines on the order...the VAT could show as whopping(!) 20p out!)

This illustrates my point nicely...on an order with several products - & where rounding is in play (limited to 2 decimal places ...i.e. whole pence) - I reckon a corresponding sales receipt can never calculate *and* show everything 100% correct.

Incidentally it'd be interesting to see how they'd show the figures if a Quantity of say 3 was purchased for each of those SKUs

 

[David Griffiths]

The way that I would do it (bearing in mind that you are doing this in MS Access outside a bookkeeping system as I understand it) is to start with the selling price £9.99

Work out the VAT = SP/6 = £1.665 Round down to £1.66 (It doesn't matter if you round up, your call)

Then arrive at the net price of the goods by subtraction, not by taking 5/6 of the selling price.

So net price = £9.99 - £1.66 = £8.34. The fact that 20% of £8.34 is £1.668 doesn't matter.

If you do it this way the net plus VAT will always equal the selling price, whether you do it on the totals or line by line.

In practice where the seller calculates the VAT on a line by line basis there is often a "discrepancy" of a few pence between the total of the individual VAT items and the VAT worked out on the total price. I've never come across anybody who cares about it. As Kevin says, it's only pennies anyway

 

[johndon68]

FWIW - the Tradebox software that I wrote takes the online sales and works out the Net & VAT the way that David describes above. It has been in use for over 10 years and hundreds of thousands of sales have gone through it by now and we've never had any problems.

As I understand it, HMRC are happy with the VAT being calculated either on a line by line basis or on the order total value.

Sage's accounting programs do it on a line by line basis which can mean that the total VAT is not 20% (assuming of course, all items are standard rated) of the total net but, again, this is not an issue for HMRC.

John

 

[Pish_Pash]

Thanks gents ....as it goes, that's the way I've been doing it (but it does mean that the net subtotal showing - whilst always correct - doesn't always match what's listed immediately above). Also the net shipping cost adds in another layer to the proceedings!

 

[Alan]

Seems such interesting topics get debated regularily at the Supreme Court and European Court of Justice

 

[LukeGSP]

Hi Pish_Pash,

Do you have any experience on this with QBO? I'm pretty sure I've read on your other posts that you use desktop only so understand you might not be able to help but here goes anyway.

I finally got Zed Axis (similar to TPI) to succesfully map everything over except for the fact that when a line total has a net value with the 3rd decimal as 5, (like in the £9.99 example you used above), QBO is rounding both up and giving me £10.

This is hugely frustrating and I can't see a workaround for it as QBO will only import the data to 2 decimal places, even if I change the data on the spreadsheet and it shows up on the importing software, it's still rounded up in QBO.

Are you or anybody else able to offer any help with this please?

 

[Pish_Pash]

I actually trialed zed Axis prior to TPI ....but TPI seemed to have better support (much larger user base, decent forum etc.).

I don't use QBO, but use the desktop version instead. When you say QBO is rounding £9.99 to £10.00, where is the data you are importing to QBO being sourced from? (because if you can ensure the data you are importing follows QBO's rounding rules, then that would be one solution.

My data into Quickbooks comes from MS Access,, in MS access if I want an app further along the chain to round up the third decimal place, I add 0.0000001 onto the number, therefore £9.995 becomes £9.99500001 & therefore gets rounded up to £10). The reverse would be true if you don't want it rounded up, so for a number like £9.995, if you want to round down, just subtract 0.0000001 from it (the result would then be £9.994999999 etc etc, which would 'round' to £9.99). This is easy to do in Access (or Excel), but might not suit your own situation/deployment/way of working.

Even now I'm still having rounding errors with Quickbooks desktop (though only with credit notes as due to a bug, they must be imported net of VAT).

My other suggestion would be to import your sales into QBO inclusive of VAT....that certainly eliminates rounding errors wrt VAT! (with Transaction Pro Importer, you can opt to import prices excluding VAT or including VAT .....hopefully Zed Axis has the same feature)

 

[LukeGSP]

Thanks for the reply.

Unfortunately TPI for QBO is a monthly sub whereas Zed Axis is a one of payment. May still try TPI for a month or two to see how much better it is and if it is worth the extra, and only if I can solve this problem....

The data is coming in from Excel which I have no problem making as complicated as it needs to be, but unfortunately this still doesn't help. There is also a bug on QBO sales receipts when you import, where even though you specify that VAT is included it will add it on again (possibly similar to what you mentioned on QB credits?). Zed offer a workaround by letting you convert gross to net, and on the import file shows it to 3 decimal places. However, once it is in QBO then it only takes it to 2 decimal places and has rounded both the net and VAT up, causing the problem.

For me I'm testing it on an item we sell for £54.99 inc VAT. Net is £45.825 and VAT is £9.165, and when both are rounded up you get £55.

I have to enter the net value due to the glitch on QBO, but you simply can't create a gross amount of £54.99! If you enter net of £45.82 you get £54.98, or £45.83 and you get £55.00. I did wonder if I could try and include mapping for the final VAT figure of the Sales Receipt, you can manually change this on QBO and pretty sure I could come up something on excel to make it work but the mapping option doesn't seem to be there.

So no matter what I do I would still need to be loading up sales receipts to edit them which with QBO loading times defeats the whole point of the importer!

The frustrating thing is that this problem doesn't occur when you just fill in the Sales Receipt manually on QBO. If you have VAT set to inclusive with line total £54.99 and then switch to Exclusive it will change to £45.83 net and £9.16 VAT, obviously doing the minus calculation as described by previous posters. I just can't get this to happen in any way with the import!

Pretty stumped at the moment and dreading the thought that if I can't make this work then will have to abandon QBO completely. It's essential I develop an importing process that works as manual order processing just isn't feasible anymore.

It's that or tweak all prices that this applies to which seems a bit over the top but I worked out that only every sixth number has this problem (£54.98 is ok but £54.93 or £55.05 aren't. It's only the numbers that when divided by 6 give you a 5 on the third decimal)

 

[Pish_Pash]

Yes, that the same glitch that Quickbooks desktop has on credit note ....you have to enter 'net'...& the QB rounding seems to be a law unto it's own.(fortunately I don't have to enter too many credit notes, but sorting it is on my ToDo list).

Funnily enough I'm approaching my year end & I was seriously considering just ponying up the monthly subscription for QBO (as year ends represent the best time to jump ship), I had a dabble with the free QBO trial, I liked it (the online bank connectivity & hourly currency rates was very appealing) but then realised it was a gravy train ...for example, TPI is then a monthly sub (for the desktop it's a one off). & another 'app' called qodbc is a monthly sub too (again, for the desktop it's a one off)...I came to the conclusion that the third parties are taking the piss & so will stick with Desktop (which for all it's quirks, I'm slowly but surely starting to use to it's full ability...for example, I've only just discovered that I can pull data into MS Access from the Quickbooks database using ODBC ....that's brilliant & allows me to do all manner of time saving things).

Coming back to your problem, it sounds like you have no option (I don't think TPI will help if QBO itself is disregarding the third decimal place - that said it's worth a pop as they have a free trial you can test this out with). Personally I find it shocking that you can't enter a sales receipt inclusive of VAT in QBO (what are they thinking?!) - this is an account software package...I'd be spitting nails. FWIW I trialed a couple of other 'cloud packages'.....Xero seemed the best, which for all it's higher cost (vs. the competition), you don't have to pay for the like of TPI Or Zed axis just to get your darned sales data into it!

 

[LukeGSP]

QBO does has a lot of benefits, being cloud based makes it more convenient to access on different laptops which is good for us as we have 3 people in the office. Haven't actually ever used the bank connectivity, I tested it once but found I was quicker entering things manually off of a bank csv download but I should probably give it another chance.

One thing cloud based apps don't ever mention is that your experience will only be as good as your internet connection (admittedly this is quite obvious, but if you just assume it's part of the natural progression of technology to shift everything over to cloud then you do it without really thinking that you could in fact be worse off). I tried to do some work from home this morning where my connection is intermittent and it was incredibly frustrating. If the internet drops then that's it you just can't access or do anything. So if you have any connection issues at all where you work then I would think twice about moving over to cloud based.

And you're right, once you make the move you end up having to pay everything on monthly subs. It sounds like you've got a good set up going on so I would say if it ain't broke don't fix it. My concern was that Intuit would eventually stop producing the desktop version and so I thought if it's going to happen then I might as well make all the changes before we grow much more as it will only get harder to move over. We had also hit the 14500 limit which even with that software you can get to consolidate customers I was finding it all a bit of a pain!

I'd be interested to hear if you do ever make the switch how you get on! Also, I've read a few times now how much you rate MS Access and it's made me think about taking a look (I've never used it). What are the main advantages of using it? If I could just get this importing issue resolved then I feel like we would have everything pretty streamlined/automated here just working with QBO, Zed/TPI and Excel, I'm curious as to what kind of things you find that MS Access helps you with if you don't mind me asking? For me QBO acts as enough of a database, with everything else being stored on spreadsheets, I understand that Access could tie all this together a bit more smoothly but am I missing other features?

Back to the issue, I hate the thought of moving from QBO but even just what you've said about Xero sounds tempting. I still can't believe that you even have to use 3rd party apps to import something so vital as sales, I wish QBO just came with this function then I wouldn't be having this problem.

I think my course of action is:

• Try using Zed a bit more and see how I get on, i.e. how many transactions are glitching and how many sales receipts do I have to edit therefore how much time I saving vs manual entry?
  
• For popular items that glitch, maybe consider tweaking the price by a penny. Sounds over the top but if it solves the problem then seems worth doing.
• Test TPI in the meantime to see if it offers a solution although as the problem is with QBO I don't think it will.
  
• Maybe test Xero when I get a chance. The cost for Xero Premium (I need the multi currency feature) is £25/month which I think is about the same as QBO anyway.
• Try and complain to Intuit about the problem although don't have much faith in them making any changes!

Thanks again for your advice, really helpful to sound this all out with someone who knows how it all works!

 

[Pish_Pash]

Re MS access....it's absolutely vital to my operation, let me try & explain.

When I started out I knew bo diddly about anything (not sure I know that much more now!). So I blindly started with Quickbooks (on account Sage seemed to be taking the piss with their pricing structure...& to my 'wet behind the ears' their user interface looked traumatic).

So Quickbooks it was - but it quickly became apparent that I needed some way of getting my sales into Quickbooks. Hence Transaction Pro importer....& whilst this app adds some 'value' to the raw data, I still need to 'pre-process' the data I give it.

I was using excel & cumbersome macros.....it was getting very unwieldly. A bit of research on the web suggested I should be looking at getting my own database together. SQL was just too big a technical leap for me, but Access has a lovely front end meaning bozos like me don't have to go 'all geek'.

So I started learning Access (I knew zilch about it...I remember opening it up & thinking ...."Hmm, what now"!!!!)

But now I couldn't be without it (I wince at how much time I wasting doing repetitive tasks back in those dark early days). What does it do?

A small early trivial example, all sales to customer in UK postcodes should have a VAT code of 'S', but sales to Channel Island postcodes should have a VAT Code of Z (why give the VAT man dosh he says he doesn't want). Therefore my data goes into MS Access & it makes all sorts of decisions based on the sales data I feed into it. After being 'handled' in access the data then gets passed to Transaction Pro Importer for import into Quickbooks.

So my initial driver was just to ensure my sales data got into my accounts software in good shape...but wait - there was a massive bonus (I wish I could take credit for having foresight, but nope, I stumbled my way through & had many lightbulb moments along the MS Access journey)

It occurred to me that since access I now had an ever growing database of my sales, I could then run all manner of cool 'queries' on it, for example a real basic one...

Show me how many of each SKU I've sold.

ok, ok that's not earth shattering & Quickbooks can do that to), but if you start building on that....

Show me how many of each SKU sold over the past month (& show me the daily sales) *and* also pull in the 'stock on hand' data in from Quickbooks.

......now Access will tell me when I'll run out of stock. But building on that, not only can I tell when I will run out of stock, it can predict how much stock I need (based on recent sales), so I know how much to order from my Asian supplier (I can build into the query, how long it takes my stock to get here from Asia). So Access is now a 'supplier stock predictor' -...yay! (put those Excel spreadsheets away!)

I also send stock to Amazon for them to fulfill (their FBA service). Again, Access tracks all of the sales on Amazon, & knows how much stock is at Amazon ...& so instantly shows me what to replenish so there'll be no stock outages - this used to take me yonks. Now it takes me 10 seconds. So, Access is also an 'amazon stock predictor' - double yay.

Amazon disburse money to me every fortnight - it used to take me upto 8hrs reconciling 1000+ transactions across the 5 Amazon marketplaces - by pressing Access into play & doing a lot of heavy lifting (looking for errors/mismatches between my Quickbooks data & their Amazon report data), I've got it down to 30 minutes.

I used to spend ages attending to emails from customers "Can I have a VAT receipt please" ...this was time burdensome. So I've now coded Access to automatically email *all* my customers a sales receipt at the end of each day (in their own language) with tracking numbers. And since everybody now gets a sales receipt via email I no longer get bogged down doing this boring admin task.

How about cross selling? No problem it would take 1 minute to write a query along this line....

"show me the email address of every UK customer who bought widget A over the past 3 months - tell them about the sibling widget B" (I don't actually do this, as I won't spam my customers, but the potential is at my fingertips)

Or how about....

Show me total sales per SKU on Ebay & compare them with sales per SKU on Amazon - this reveals which listings may need more attention on the individual marketplaces (price tweaks, more promotion etc).

I could go on, but it'd be boring & me bordering on evangelizing....but believe me, if there's one piece of advice I could give any small online retailer like myself starting out, it would be "get a database & make the data contained therein 'sing' for you".

If you've never used a database in anger, it's probably difficult to see all of the benefit, *until* you set off on this journey yourself & you then realise that just about every business decision you make (or repetitive task you do), can be helped with a database like Access.

 

[LukeGSP]

Wow lots of info, thank you so much I will definitely be trying out Access as soon as I get a chance. I particularly like the stock predictor that sounds really handy.

This is my year for an efficiency drive, have to cut down the unnecessary time spent on basic tasks!

I'll let you know how I get on.

Thanks again for all your help!

 

[LukeGSP]

One more question, if you were in my situation and just couldn't find a way to get transactions into QBO with no issues, would you consider just entering daily sales totals on QBO? Any downsides to this that you can forsee?

If I can eventually get everything happening in Access, including sales receipts and inventory management, then could I get away with just using QBO for accounts and ditch the idea of having every sale/customer stored individually? I've always been against this idea as I use QBO like a database - it's the central place I keep sales from ebay and own website, but if I move this over to Access then it might be the best solution for me.

As you can tell, still haven't found a solution to my original post. Have just had a play with Transaction Pro Importer and it looks like the same problem is occuring with the VAT being added on twice so I'm back to the drawing board!

 

[Pish_Pash]

There are two camps here...those that say "pah, why enter per customer - it's extra work" & then there are those like me that say "in my situation - 95% automated - it's very little extra work, so why not?". I prefer entering every transaction, as Quickbooks tracks my 'quantity on hand' & therefore allows me to know my profit on a daily, weekly, monthly basis (saves having to 'hope' you're in profit until you do a stock take).

If I can eventually get everything happening in Access, including sales receipts and inventory management, then could I get away with just using QBO for accounts and ditch the idea of having every sale/customer stored individually? I've always been against this idea as I use QBO like a database - it's the central place I keep sales from ebay and own website, but if I move this over to Access then it might be the best solution for me.

You are in theory correct - but for all those quirky customers that want specifics on their sales receipt, there's already a sales receipt in existence that you can quickly tweak to suit their needs (whereas if you were entering in bulk, you'd have to create from scratch). Where quickbooks wins over ms access (well at last for my relatively new access skills), is its user interface. Access is great for querying/analysing data, but if you want to give Access a slick/pretty UI, then that's a whole lot of work. Because it's just me & my missus, my access UI is very 'utilitarian'!

If I were in your shoes, I'd simply move to another accountancy software platform...& from the bit of a dabble I had with other packages, I like the feel, look & support of Xero (I'd need which ever package I moved to, to be ODBC compliant so that I can pull data into Access from it - Quickbooks online is, but as you're experiencing it's got bugs - the QB software team seem to really drag their heels over what I consider to be really painful issues)

 

[LukeGSP]

Thanks for the reply.

I also prefer entering every transaction and having accurate QOH within QBO. Appreciate the insight on Access, I'll have a think about what to do next.

One ray of hope - I emailed TPI yesterday re the VAT issue and they said it is a known issue and that only earlier that day they have emailed Intuit to request that it be fixed, as opposed to Zed who just shrugged their shoulders! Hopefully they're not just fobbing me off and can actually get somewhere with this!

 

[bovine]

vat rounding can be quite fun. I hadn't noticed this thread before, but as I work in epos, it does come up from time to time.
The problem always comes when calculating vat item by item. Small rounding changes can add up, so when the vat is calculated line by line you get a different total than if you just add up all the sales and work the vat from that. All of the sales are vat inclusive, we don't really get involved in systems that need to add on vat. Have had some interesting conversations about rounding.. Unfortunately it can be difficult to get some people to understand why the figures don't show what they think it should. But I have come across quite knowledgeable people make quite basic errors with VAT. The belief that the vat component of a £1 vat inclusive sale is 20p is fairly common.
There is a hmrc document that relates to epos and line or total rounding. Interestingly, both are "correct". As long as you're consistent in how you do it, it doesn't really seem to matter.

 

[Pish_Pash]

TPI are very reactive (& have a much larger user base than ZED). I approached them with tmy credit notes VAT import problem & they got onto it quickly, but ultimately they reported that it was down to a bug in QB Desktop that frankly was unlikely to be fixed (not a show stopper for me, I only have about 20-30 credit notes per month, of these maybe 4-5 have a rounding errors that I can quickly spot/address)

 

[LukeGSP]

If anyone is still interested in this thread - re this rounding error I was having specifically on Quickbooks Online, it has now been solved. No more workaround needed as I am able to import the data using the gross amounts.