Excel - working out net and vat?

[louise1989]

Hi all,
Firstly this is my first post and i would like to say i have a pretty good understanding of computers until i got stuck on excel.

Basically my problem is i want to work out the net and vat to equal the total.
I have searched numerous things and the formulars seem to be helping everyone else out except me i seem to be making a very simple mistake but i cant figure it out. Im new to excel so please try to explain the formular as simple as possible.

Thanks
Louise

P.S i have net in C2 vat in D2 and total in e2 (so as i can get it hopefully)

[haydnwalker]

It should be pretty simple. However, are you entering the total and want to extract the net + VAT amounts from that?

eg: enter 100 in cell a1 and b1 gives the net and c1 gives the amount of VAT charged?

---------- Post added at 23:32 ---------- Previous post was at 23:30 ----------

Just googled and found this

Enter sale price in cell A1: 100.00

Enter VAT / Tax in cell A2: 18.00% 


Use the following function to calculate the net sale price: =A1/(100%+A2) 

The result: 84.75 


To calculate the VAT / Tax use the following function: =A1-A1/(100%+A2)

The result: 15.25

[louise1989]

See now iv'e tried that and i edit the cells as im using differnt cell numbers and the vat one works but not the nett ??

---------- Post added at 23:41 ---------- Previous post was at 23:35 ----------

sorry and yes in regards to what im trying to achieve

---------- Post added at 23:57 ---------- Previous post was at 23:41 ----------

Im now very confused if the calculation u provided from google is right i will need 4 columns instead of 3 i would need 2 for vat ?? or am i missing something ??

[chris9991]

Don't know if this thread might help


http://www.cableforum.co.uk/board/19...-question.html

[louise1989]

I have already looked at this thread and it confused me i think i need someone to tell me like abc because for some reason i just cant seem to understand this.
If someone one could tell me exactly what to put in what cell to work out 17.5% vat i would really appreciate it.
The cells i have at the moment are;
Net in cell C2, vat in cell D2 and total in cell e2.

Any help much appreicated

[haydnwalker]

I've just tried it, and I've uploaded my code for you to download and play with

hope that helps a bit

[rogerdraig]

if i get you right your entering the total and want the others to work out ?

if so

put the formula =SUM(E1/100)*82.5 in cell C2

and

put the formula =(E1/100)*17.5 in cell D2

lock both the cell then protect sheet

[louise1989]

Ok im starting to understand but still having problem manage to get the fomular working in the sence of actually making number but its the wrong calculations ???????

Im going to attach what if done if u could have a look and tell me what im doing wrong id greatly appreicate it !!

Thank in advance
Louise

---------- Post added at 00:27 ---------- Previous post was at 00:26 ----------

Ok now i cant even attach a file im having such a bad computer day its unbeliveable !!
When i try to attach the file it say invaild file ??

---------- Post added at 00:29 ---------- Previous post was at 00:27 ----------

i dont know if this will help but and the end of the file name it says xlsx im using excel 2010 is it not compatible with this website or something ??

[Tezcatlipoca]

Originally Posted by rogerdraig

if i get you right your entering the total and want the others to work out ?

if so

put the formula =SUM(E1/100)*82.5 in cell C2

and

put the formula =(E1/100)*17.5 in cell D2

lock both the cell then protect sheet

The price excluding 17.5% VAT isn't simply 82.5% of the final price.

With a VAT rate of 17.5%:

Final price / 1.175 = Net price (excluding VAT)

Net price * 1.175 = Final price (including VAT)

e.g. An item costing £100 wouldn't be £82.5 excluding VAT, it would be £85.11


With the current VAT rate of 20%:

Final price / 1.2 = Net price (excluding VAT)

Net price * 1.2 = Final price (including VAT)

[louise1989]

lock both the cell then protect sheet ????

---------- Post added at 00:33 ---------- Previous post was at 00:31 ----------

To matt d
I hope that wasnt too me because you might as well spoke french !!

[Tezcatlipoca]

So, with C2 = Net, D2 = VAT, & E2 = Total, & where you are starting with the Total -

C2 = E2/1.175

D2 = E2-C2

E2 = Whatever you enter

---------- Post added at 00:35 ---------- Previous post was at 00:34 ----------

Originally Posted by louise1989

lock both the cell then protect sheet ????

---------- Post added at 00:33 ---------- Previous post was at 00:31 ----------

To matt d
I hope that wasnt too me because you might as well spoke french !!

It was to rogerdraig, because the net price, when VAT is 17.5%, isn't simply 82.5% of the final price.

EDIT:

VAT of 17.5% -

VAT = 17.5% of the net price = net price * 0.175 [17.5/100=0.175]

So, the total price (net + VAT) = net price * 1.175 [Multiplying by 1 plus VAT]

To work backwards from a final price, and find the net price excluding VAT, you divide by 1.175 instead of multiply by 1.175:

Net price = Total price / 1.175



VAT of 20% -

0.2 instead of 0.175, so you multiply the net by 1.2 to find the total, or divide the total by 1.2 to find the net.

[haydnwalker]

Originally Posted by louise1989

i dont know if this will help but and the end of the file name it says xlsx im using excel 2010 is it not compatible with this website or something ??

No I don't think the office 2007/2010 formats are compatible here, you would need to do a File > SaveAs and change the type to xls (excel 2000-2003)

---------- Post added at 00:39 ---------- Previous post was at 00:37 ----------

Originally Posted by Matt D

So, with C2 = Net, D2 = VAT, & E2 = Total, & where you are starting with the Total -

C2 = E2/1.175

D2 = E2-C2

E2 = Whatever you enter

---------- Post added at 00:35 ---------- Previous post was at 00:34 ----------



It was to rogerdraig, because the net price, when VAT is 17.5%, isn't simply 82.5% of the final price.

@MattD: Confirmed by my upload above

[rogerdraig]

Originally Posted by Matt D

The price excluding 17.5% VAT isn't simply 82.5% of the final price.

With a VAT rate of 17.5%:

Final price / 1.175 = Net price (excluding VAT)

Net price * 1.175 = Final price (including VAT)

e.g. An item costing £100 wouldn't be £82.5 excluding VAT, it would be £85.11


With the current VAT rate of 20%:

Final price / 1.2 = Net price (excluding VAT)

Net price * 1.2 = Final price (including VAT)

quite right was rushing there should have been

C2 =SUM(E2/117.5)*100
D2 =SUM(E2/117.5)*17.5

was doing it that way to make it easier to understand but then my brain switched off lol ( was laughing at hot fuzz may have been the culprit ther lol )


was using the 17.5 because i think they said thats the value they wanted

[louise1989]

Haydnwalker

I tried to convert to but it cant seem to find that i tried several other like 97-03 addi in and template but no joy there.

Mat D

even with it as simple as u explain still manage to get it wrong if i could upload my file i could show u exactly what i mean i must be having a stupid day !!

Im afriad tiredness is getting to the best of me i will have to retire tonight and try again tomorrow

Thanks everyone for your help

---------- Post added at 00:50 ---------- Previous post was at 00:48 ----------

To rogerdraig

Yes thats right i need the 17.5% as im calculating the material bought for my dads company for the last quater as i needs to go off to the accountant.
I thought it would be easyier but i am seriously thinking about giving up and going back to pen and paper !!

[rogerdraig]

its late lol i got it wrong too have another go in the morning once you get it its fairly easy to do