Use the function ROUNDDOWN rather than ROUND in your formulae.
.......A 1 26.9 2 (8.8) 3 15.5492
For example, in column B1 put the formula =rounddown(a1,0) would result in 26, not 27 being returned Similarly =rounddown(a3,2) would result in 15.54 rather than the 15.55 that would normally be returned when taking precision down to two decimal places.
Shaun.
P.S. had to edit it as it scrunched all of the spaces together. In the real world ignore the dots. Basically A is the column, 1,2,3 are the rows.
-- Edited by Shamus on Tuesday 19th of January 2010 02:00:26 PM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Use the function ROUNDDOWN rather than ROUND in your formulae.
Similarly =rounddown(a3,2) would result in 15.54 rather than the 15.55 that would normally be returned when taking precision down to two decimal places.
Great, thanks mate.
This is what I've used for the VAT column ... =ROUNDDOWN((F15*0.175),2)
The actual answer to .21 *.175 is .3675 * 5 is .18375 rounded down is .18.
Both answers are correct. On an individual basis .04 is the closest. On an accumulated basis it's .18.
Calculated in reverse from .25 the answer is different. .25 *7/47 = .037234 resulting in a net figure of .212766 which would have resulted in a total of 1.06 rather than 1.05.
In this instance where every value is the same you have a problem in that rounding up and rounding down don't average the figures as would be the case in most real life situations.
If you want the figures to add correctly the answer for your case is to use the calculations in the VAT field rather than typing .04 in the field. When totaled the 5 * .04 will total .18, not .20.
Same with the Gross column. Add calculations rather than typed in values from the book.
You could show the precision to more than two decimal places but we don't tend to do that in normal bookkeeping and rounding errors are something that is born providing that such is reasonable.
If you think about the VAT return they don't even want to know about the pennies, only the pounds.
Hope that this helps mate,
cheers,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Thanks for that, but I'm still not clear about the right way to set up VAT calculations on a spreadsheet to do some BK excercises.
Maybe I'm being confused by ... on an invoice it is possible to have items that should go to different Accounts in the books. eg: supply of a new computer that also has paper and spare ink, staples. etc.
see what you mean in that for some items on an invoice may be VATable whilst others may not.
Good example might be an invoice for Software at 17.5% VAT and books to train you in use of the software at 0% VAT.
The HMRC website states that :
For each different type of item listed on the invoice, you must show:
* the unit price or rate, excluding VAT * the quantity of goods or the extent of the services * the rate of VAT that applies to what's being sold * the total amount payable, excluding VAT * the rate of any cash discount * the total amount of VAT charged
Therefore, it would seem for a mixed invoice you need to put the VAT rate (17.5%, 15%, 5% etc.) against each line item rather than on the line directly below net as is normal practice.
The net total would be the total of all line items as normal but the VAT figure would be the sum of all VAT items at each applicable rate. So, in the freaky situation where you had VAT at 17.5% and 5% and Exempt you would have seperate VAT totals for 17.5% VAT and 5% VAT.
There of course wouldn't be any confusion from VAT line items not totaling up to the VAT total.
Example (1) Standard Case
Description
. Price
. Qty
. Amount
Office 2007
75.00
5
375.00
Net
375.00
VAT @ 17.5%
65.63
Invoice Total
440.63
Example (2) Simple Mixed Case
Description
VAT Rate
. Price
. Qty
. Amount
Office 2007
17.50%
75.00
5
375.00
Teach Yourself Office 2007
0.00%
15.00
10
150.00
Net
525.00
VAT
65.63
Invoice Total
590.63
Example (3) Multi rate mixed case
Description
VAT Rate
. Price
. Qty
. Amount
Planning software
17.50%
75.00
5
375.00
Heating System Valve
5.00%
12.00
2
24.00
Heating System Widgets
5.00%
4.00
20
80.00
Teach yourself plumbing
0.00%
15.00
10
150.00
Net
629.00
VAT @ 17.5%
65.63
VAT @ 5%
5.20
Invoice Total
699.83
Hope that the above makes sense,
cheers,
Shaun.
P.S. had to edit it a couple of times as lost some of it's formatting. Contents are still the same though.
-- Edited by Shamus on Monday 1st of February 2010 01:41:50 PM
-- Edited by Shamus on Monday 1st of February 2010 01:44:40 PM
-- Edited by Shamus on Monday 1st of February 2010 01:48:23 PM
-- Edited by Shamus on Monday 1st of February 2010 02:07:04 PM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
