Calculating % GP anyone??

**Simon2k_01** · 11-04-2009, 10:18 AM

Hey guys,

Basically im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver and i hope one of you experts can help me out!!

Thanks in advance guys!!

Simon

**somesoldiers** · 11-04-2009, 10:21 AM

assuming your list starts in A2, enter =A2+(A2*0.8) in B2 and drag down as far as you need

as a check adjust your old formula to =SUM(B2/A2)*100-100 and enter in C2- will give you 80 every time

**Simon2k_01** · 11-04-2009, 10:32 AM

I dont think thats right, this calculates an 80% mark up which would be useless as a drink costing you 50p to make, this formula tells you to sell for 90p. An 80% GP on a drink costing 50p is £2.50

**somesoldiers** · 11-04-2009, 10:39 AM

isn't that a 400% mark up? anyhow just change to =A2+(A2*4)

**Simon2k_01** · 11-04-2009, 10:47 AM

Yeah that seems to work, is there an easy way to change that, for example obviously food and wine have a differant gross profit margins than beer and spirits, is there a way to calculate how to acheive any percentage from 70 - 90%?

**ChemistB** · 11-04-2009, 10:59 AM

If you can tell me how 80% on a 50p cost correlates to 2.50, then I can tell you how to have your formula be easily modified.

**bluerog** · 11-04-2009, 11:19 AM

Understand two formulas:
Gross Profit Percent = 1 - (Cost / Price).
Price = Cost / (1 - GP%)

(algerbra will confirm this).

So to get price when Cost is known and GP% is known use the formula on the attached worksheet for price.

**ChemistB** · 05-23-2017, 10:51 AM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Since this thread did not answer your question, I would also suggest you upload a sample workbook with your question so others can see exactly what you are trying to achieve. Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.