Spreadsheets & VAT

[Meg888]

Hi, can anyone give me the formula for getting the VAT element off a gross figure. I know when VAT was 17.5%, you would multiply the gross amount by 7 then divide by 47 and this would give you the VAT amount - but I cannot work it out on 20%. thanks in advance

[Wak]

If VAT is 20% then gross amount = 120%
therefore divide Gross amount by 12 and multiply by 10 for the nett price.

[Jonathan-Joe]

Divide the gross amount by 6 to give the VAT element.

[Wak]

for VAT amount divide Gross by 6.

[Meg888]

Doh!!! Thanks folks - it's so obvious! The 17.5% formula seemed so complicated I was assuming this one would be the same!

[New Judge]

Please let me say, meg, before I ask my question, that I am not being at all critical or having a go at you or trying to insult you. You wanted to know something and asked the question. That’s how we all learn. It’s just that I have an interest in education (particularly the relative effectiveness of today’s education compared to that of some years ago).

To me (an old fart who was educated in the 1960s) your problem was a straightforward one and easily solved. It obviously wasn’t for you and I’m really interested to know why. Wak’s answer explained the logic in an easy to understand manner. I’m just intrigued why you were not able to develop that logic yourself. Is it that you were not taught how to tackle such problems? How did you get the formula for the 17.5% calculation?

Please don’t bother to answer if you feel I should mind my own business. It’s just that I’m really interested.

[factor-fiction]

New Judge- I think the 7/47ths method was a fairly well known one in business that was passed on by 'word of mouth'.

I can assure you that pupils are taught percentages every year from the age of around 10 to 16 I wish I could put my finger on why some just don't understand the concept.

This example here is a 'bit' more complicated than a standard 'percentage of an amount' calculation (25% of £80) or a 'percentage increase' problem ( a CD costs £12 and then the price goes up by 15%- what is the new price?).

Meg888's example is known as a 'reverse percentage ' problem and you may be surprised to know that this is considered too hard now for a Foundation GCSE Maths paper (up to grade C)- it would only feature on a Higher tier paper for students aiming for B or A .

I'm not convinced, though, that this is just a current problem. I remember seeing a programme on TV that showed that a significant proportion of adults actually had little understanding of percentages and had no idea how to apply a percentage increase/decrease.

[factor-fiction]

Actually I do have an idea why so many students struggle with fairly basic mathematical concepts (and English, other languages etc). One of the main problems is that they cannot concentratefor me than a few minutes or are simply not interested in learning- it's not cool to learn and they don't see the point until it's too late

[vallaw]

As another old fossil, I think a lot of the problems with maths started with decimalisation and were compounded by the introduction of calculators and spreadsheets. Pre-decimalisation, we developed mental flexibility by the necessity calculate in £sd and stones, pounds and ounces. Who on earth is going to bother to calculate a percentage when there is a button on your calculator which will do it for you? So you don't learn the basic rules because you don't need them.

[factor-fiction]

vallaw- I agree that pupils are too reliant on calculators (actually mobile phone handsets) at times and see little point in learning tables or simple divison facts when they can use a calculator.
However, a calculator wouldn't have help someone in Meg888's position with her problem. To use a calculator you still need to know what calculation to put in.

[New Judge]

Yes, that's the issue, factor. It's not so much the calculation that seems to be a problem but more knowing what calculation to perform. We were taught this sort tof thing in the form of "problems" (in primary school, believe it or not).

[scotman]

I tend to agree with F30 and to a lesser extent with Vallaw. I am of an age to have been at school when Arithmetic had its own O Grade(Scotland) and to have been at school long before calculators.
I learned multiplication tables by rote and we did lots of practice, both in class and as homework so we all understood the concepts and were well-versed in doing the calculations longhand.

[ChuckFickens]

I think the biggest problem is people seem to think the result is the most important part now, not the process.

in my mind frame the OP should have asked OP why did "multiply the gross amount by 7 then divide by 47" give the correct answer and how can I modify this to work with 20% VAT.

Although "multiply the gross amount by 7 then divide by 47" seems a really long winded way when you can just do gross / 1.175 (or for 20% vat gross / 1.2)

[ChuckFickens]

if anyone wants a spare "op" I seem to have left one up there↑

[Meg888]

Hi New Judge, yes the answer was obvious, and if I'm honest, unless it was pointed out I probably would not have worked it out for myself. As Factor30 said, the 7/47 was a common method well known in accounts. When the VAT dropped to 15% I spent a long time trying to work out the new method to no avail - I could not do it, even aided with a calculator. At 41 I was educated in the 70/80's - and was appalling at maths, leaving school without even being entered for an exam (teachers choice). However, I've worked in accounts since and self taught multiplication & division - both baffled me at school, I could add no problem and do basic take-away (is it still called that??). I was self employed bookeeper at one point and taught myself depreciation among many other things (I have no reconised qualifications). My job at the mo is running a small accounts dept., which I do a lot of spreadsheet work and ensuring ledgers all reconcile - when they don't I enjoy 'looking' for the problem. I consider myself fairly intelligent and quick (usually) at problem solving numbers. At school I just couldn't fathom maths at all, therefore hid away from the subject & concentrated on those I was good at and did well in.

[scotman]

Hi Meg

Have you ever considered that you may have something called 'discalculia'?
Are you OK with other subjects?

http://www.independen...-dyslexia-842781.html

[RATTER15]

Scotsman, reading these posts brought me to the same conclusion that discalculia could be playing a part here, I suffer from it along with other aspects of Dyslexia.

Meg888, this may not apply to you, some people are just not good with numbers, it may be worth looking at though.

[Meg888]

Hi Scotman, I'm not sure, someone once told me I may have a problem (years ago, when things were more manual) as I always flipped the numbers. So if I cannot balance something up, the first thing I do is work out whether or not it's a multiple of 9, then I know I've switched the figures (still quite chronic at this and at reading them as such). I once spent weeks looking for a £450 difference in my ledgers - going through piles of papers, eventually it was someone else who found it - alas a multiple of 9! My daughter has dyspraxia, and she struggles to understand any sort of logical problem, and although she's bright, she appears to be confused as she cannot explain things well and even on hearing a conversation - she could not relay a sentence of it back to you word for word straight after. It's all there in her head, just jumbled up. So maybe I do and maybe she is like this genetically, possibly??

[Davethedog]

The devide by 6 works when vat is 20% however on a spread sheet this works whatever the rate. =+(120/120)*100 The second 120 is 100 + the vat rate so if it went back to 17.5% =+(120/117.5)*100.

If you use the amount in a cell and apply were you need it =+(A1/117.5)*100 it is then universal.

[RATTER15]

Meg888, I certainly think that you should both be tested for Dyslexia, I suffer from all the symptoms that have mentioned, I have only recently found out that im Dyslexic. Get tested, when you know what the problem is you can do something about it.

Loads of stuff on-line, do some research, you will get lots of usefull advice and help.

[EvianBaby]

I would have to ask the same question as Meg did. I also do bookkeeping and manage the accounts of our company but I thankfully have an excellent set of equations to work out VAT amounts that is easily changed when the VAT amount changed. If it wasn't for this I would struggle. Thanks to the accounts system I use (Quickbooks) which is in essence a giant calculator, I do my job quite well.

However, I am painfully poor at maths. I did my GCSE's about 11/12 years ago and for foundation maths was initally ungraded. This was later revised to an E. I have no idea why I was so bad at maths but it terrified me and I think once you are scared of a subject at school, there are some of us who let the fear of it get in the way of allowing ourselves to try and learn it. I did often wonder if I had some sort of Dyslexia assosiated with maths and still do sometimes. I often mix up numbers. i.e., if someone is reading out some numbers I will hear a 9 but write a 6.

Like Meg, if I encounter an error somewhere in my sums I do now enjoy working out where that error lies rather then being 'scared' about it although perhaps that's because I'm not in a school environment where I feel like I have 5 minutes to work it out and I'll get a rollocking if I don't.