Find formula to drive sale price based on entering gross profit percentage (not markup%). We know the cost price

Neilads

New Member
Joined
Jun 29, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi

I’m after some expert help to find a formula that will do the following:

I want to know the sale price of my goods, when I enter the gross profit percentage that I want to make (not markup%).

I know the cost price of my goods.

For example, I want to make 50% gross profit on something that costs me £1.00. The answer I know is not £1.50 as that is only a gross profit of 33%. But I know that if I sell at £2.00 I am getting my GP at 50%, but what is the formula to work that out!

Please help!
Neil
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Neil,

Divide your cost by percentage profit that you want to generate (e..g. 1 / 50%).


Kind regards

Saba
 
Upvote 0
Divide your cost by percentage profit that you want to generate (e..g. 1 / 50%).
That is not correct. You divide by the cost percentage, not the profit. For 75% gross profit, you would divide by 25%

=cost/(1-profit%)
 
Upvote 0
That is not correct. You divide by the cost percentage, not the profit. For 75% gross profit, you would divide by 25%

=cost/(1-profit%)
Does this work if you want to make 200% profit?
 
Upvote 0
Does this work if you want to make 200% profit?
With the way that you say you want to calculate your profit, 200% is impossible.

The maximum is 100%, and that is only with a zero cost value.

For a 99.99% margin (which you referred to as gross profit) an item with a cost of £0.01 would need to be sold for £100.00
 
Upvote 0
With the way that you say you want to calculate your profit, 200% is impossible.

The maximum is 100%, and that is only with a zero cost value.

For a 99.99% margin (which you referred to as gross profit) an item with a cost of £0.01 would need to be sold for £100.00
That’s where we got to here. It seems that there needs to be a non mathematical treatment to this problem. Can you think of a way around this dilemma. I think that some of our products will attract a GP of higher than 100%
 
Upvote 0
I think that some of our products will attract a GP of higher than 100%
You can have greater than 100% for mark up, but not for margin. Even with a negative cost (somebody pays you £100 to take something that they don't want, then you sell that item for £100), the £200 that you have made from it is still only 100% (technically it is less if you deduct labour costs, etc).
 
Upvote 0
Solution
You can have greater than 100% for mark up, but not for margin. Even with a negative cost (somebody pays you £100 to take something that they don't want, then you sell that item for £100), the £200 that you have made from it is still only 100% (technically it is less if you deduct labour costs, etc).
Thank you so much for this. It has helped put into words what we couldn’t do in excel!
 
Upvote 0
Even though it's not the answer you were looking for, hopefully it gets you moving in the direction that you need it to.

One thing that I should point out is that you can not use the =cost/(1-profit%) method if you do not have an actual cost to base the price from to begin with. For a zero or negative cost you would need to give the product / service either a physical value, or an artificial cost to provide a base for the calculation.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top