Excel assistance please

[BensGrandad]

I am helping a friend do his stocktake and we need to calculate the GP % of each item. I know I could do it manually but for 200 items it would take an age.
Does any body know the formula to use for excel ;

The calculation is Selling Price -17.5% (vat) minus cost price divided by the selling price and the %.

Or

£2.50(selling price) - £0.85(vat) = £1.65 - cost of £1.10 = Profit of 55p, divided by the Selling price ex vat £1.65 = 33.3%. How do I do this as a formula substituting cell numbers for the actual numbers shown. Many thanks for any help.

 

[Kylies Stunt Arse]

What information do you have already entered into the spreadsheet? I mean do you just have the selling price, or selling price and VAT etc?

 

[Normal Rob]

85p VAT? that 34% isn't it?

 

[fleet]

The calcualtion you are asking for is wrong I think as 17.5% taken off something is not the same as adding 17.5% VAT onto something. But assuming you want to do excatly waht you say:

If you put the selling price in column a (starting at line 1) and the cost price in column b (starting at line 1), then in column c put

=a1-(a1*0.175)-b1

Then hold the bottom right hand corner of this cell and pull it down for each row you want to calcualte and it should work

 

[dahdevon]

FFS,Its the weekend mate.
get a f***ing life.

 

[fleet]

If you put the selling price in column a (starting at line 1) and the cost price in column b (starting at line 1), then in column c put

=a1-(a1*0.175)-b1

Then hold the bottom right hand corner of this cell and pull it down for each row you want to calcualte and it should work[/QUOTE]

Ignore this - I only looked at part of it - I'll redo it!

 

[BensGrandad]

What information do you have already entered into the spreadsheet? I mean do you just have the selling price, or selling price and VAT etc?

I have all of the figures required to calculate it manually but would prefer, as there are numerous 200+ items to use a formula.

The first item is listed as C4 being the cost price.F4 Selling Price Inc Vat,K4 Total items sold and Q4 is total sold at Cost. R4 needs to show the % GP.

Thank you fleet I shouldnt have taken the 17.5% off as the Vat IS 37p Selling ex Vat is £2.13 the calculation should have been £2.50 divided by 1.175 to give the ex vat price.

 

[fleet]

Try this as an approximation:

=(a1-(a1*0.14895)-b1)/a1*100


a1= selling price
b1= cost price

DOes that work?

 

[The Large One]

In these calculations, if you're taking the VAT off the selling price, shouldn't you be taking VAT off the cost price as well?

 

[rool]

In these calculations, if you're taking the VAT off the selling price, shouldn't you be taking VAT off the cost price as well?

BG is probably using the net price taken from suppliers invoices whereas the sales price would be VAT inclusive.

I assume that's the case anyway

 

[rool]

BG, I don't have it here as I'm off today but I do have an excel model that calculates what the sales should have been from stock movements.

This not only gives me a GP but also highlights any wastage or pilferage problems,

 

[sully]

Haven't looked at the detail, but don't forget that when dealing with money, you should always use the ROUND command around the formula, or any totals may not add up correctly. It might just display two decimal places, but Excel will store an infinite number of decimal places.

 

[The Large One]

The formula Fleet uses looks right, except it looks like it's the (Selling Price less VAT) minus (Cost Price) divided by (Selling Price inc VAT).

To calculate, should you use either Selling Price less VAT in both parts of the calculation OR Selling Price inc VAT in both parts of the equation. Don't you get a false GP otherwise?

 

[Gazwag]

You have to take out VAT on both buying and selling price. Also you need to bear in mind that the majority if not all of the items in stock will have been bought at 15% not 17½% VAT unless they were purchased before 1/12/08 or after 31/12/09.

 

[drew]

I have all of the figures required to calculate it manually but would prefer, as there are numerous 200+ items to use a formula.

The first item is listed as C4 being the cost price.F4 Selling Price Inc Vat,K4 Total items sold and Q4 is total sold at Cost. R4 needs to show the % GP.

Thank you fleet I shouldnt have taken the 17.5% off as the Vat IS 37p Selling ex Vat is £2.13 the calculation should have been £2.50 divided by 1.175 to give the ex vat price.

How about

=sum(((F4/1.175)-C4)/(F4/1.175))

Or, if you need to vary the VAT (should have been 15% for the last few months?) you could make A1 the VAT amount and show it as

=sum(((F4/A$1$)-C4)/(F4/A$1$))

Copy it down the column you want the answer in which should be 48.3%

I think!

 

[BensGrandad]

Thank you to all of you for your asssistance. With the help of Kylies Stunt Arse I now have the spreadsheet complete.

To answer TLO I will be using invoices that show the price ex VAT and I dont have a problem with VAT as this is a setting up program for future stocktakes when Vat will be at 17.5% for some months.

 

[Shropshire Seagull]

Might be an idea to store the VAT rate in a seperate cell (as a variable), so when it changes (and it will soon) it will be easy for the next stocktake, and thus future proofed.

If the 17.50% is in cell Z1 substitute the "1.175" in the formula with (1+$Z$1) - the $ enables locking on to the (absolute reference) cell so that the formula can be copied - sorted

 

[BensGrandad]

Is there not a suggestion that VAT will be changed soon to get in line with the rest of Europe.

 

[The Large One]

Is there not a suggestion that VAT will be changed soon to get in line with the rest of Europe.

Unlikely, I'd have thought, seeing as there isn't a flat rate across Europe.

http://en.wikipedia.org/wiki/VAT