Calculating Price Based On Projected Profit Margin 


Calculating Price Based On Projected Profit Margin  Excel 
View Answers 
I am using this current formula to calculate my profit margin on product I sell.
=(A8B8)/A8
Price Cost Percentage $3.79 $2.00 47.23%
I would like to be able to put in the Cost of the Product and desired profit margin to get the retail price. Can anyone help Please?
Cost Margin Price $2.00 45%
=(A8B8)/A8
Price Cost Percentage $3.79 $2.00 47.23%
I would like to be able to put in the Cost of the Product and desired profit margin to get the retail price. Can anyone help Please?
Cost Margin Price $2.00 45%
Similar Excel Tutorials
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Copy one range and paste in another range
Below is a macro, just copy and paste it into a module in your workbook and go from there.This macro uses message b ...
Below is a macro, just copy and paste it into a module in your workbook and go from there.This macro uses message b ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Helpful Excel Macros
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel  UDF
 This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
 This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Format Cells as a Percentage in Excel Number Formatting
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Filter Data to Show the Bottom X Percent of the Data Set in Excel  AutoFilter
 This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
 This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Similar Topics
Hello All
I am currently using the following formula to calculate my profit margins on sales (where profit margin is defined as the percentage of the final selling price that is profit)
Margin=("Selling Price""Cost Price")/"Selling Price"
In the above, Selling Price and Cost Price are inputs in Currency, Margin is the output as a percentage.
I would like a formula that can calculate a final selling price from a Margin Percentage and the cost price (i.e. how much should I sell something for to make x% Margin) but I cant figure it out.
Any ideas?
I am currently using the following formula to calculate my profit margins on sales (where profit margin is defined as the percentage of the final selling price that is profit)
Margin=("Selling Price""Cost Price")/"Selling Price"
In the above, Selling Price and Cost Price are inputs in Currency, Margin is the output as a percentage.
I would like a formula that can calculate a final selling price from a Margin Percentage and the cost price (i.e. how much should I sell something for to make x% Margin) but I cant figure it out.
Any ideas?
Good morning all,
I am trying to develop a formula that can give me the cost price by inputting the sell price and profit margin.
My sell price is $1.25 I want to input a profit margin of 20% and have the result be my desired cost.
Any help would be welcome.
thanks
way~
I am trying to develop a formula that can give me the cost price by inputting the sell price and profit margin.
My sell price is $1.25 I want to input a profit margin of 20% and have the result be my desired cost.
Any help would be welcome.
thanks
way~
Hi
I am selling product ok Ebay and would like to create a spreadsheet will work out me selling price based on a fixed percentage profit
I currently had the spreadsheet set up so that when the sell price is changed the % profit is to my desired level
I need to incorporate the ebay 10% fee and the Paypal 2.9% + .20p to be included in the final selling price
so we have
sell price 40.83 ex vat
Buy price 26.19 ex vat
Ebay fees 4.90
pay pal fees 1.62
Profit 8.12
margin 20%
What I need is to be able to enter the buy price and margin % and get the spread sheet to calculate the sell price for me
Thanks in advance for your help
Hi all,
I have a dilemma that I hope someone will be able to help me with.
I need to work out the % profit margin from two prices: a cost price and selling price. The other threads I found have just confused me.
I.e. 16.87 selling price (Ex VAT) and cost price 12.9, expressesed as a % profit. I have three colums:
Selling price ex VAT'
Cost price
Profit Margin (%)
Please can someone help?
Many thanks for any assistance.
Andy
I have a dilemma that I hope someone will be able to help me with.
I need to work out the % profit margin from two prices: a cost price and selling price. The other threads I found have just confused me.
I.e. 16.87 selling price (Ex VAT) and cost price 12.9, expressesed as a % profit. I have three colums:
Selling price ex VAT'
Cost price
Profit Margin (%)
Please can someone help?
Many thanks for any assistance.
Andy
Is there an excel formula that can calculate a proposed selling price by entering a desired profit margin percentage and knowing the cost of goods sold?
I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?
I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?
Hi,
Can someone check if the formula I am using to calculate the percentage margin i have made is correct.
The information I have is the total sell price, the cost and the profit.
It appears like this:
Column H.....................Column J.......................Column k (margin)
total sell price...............(sellprice  cost).............=J2/H2
I have column k set to percentage on the formatting menu so that it automatically works out the forumal. I know this may seem a bit simple, but it is ages since I have done anything to do with margin and just have a new task that involves calcualting margin and don't want to get it wrong.
Thanks in advance for any help.
CC
Can someone check if the formula I am using to calculate the percentage margin i have made is correct.
The information I have is the total sell price, the cost and the profit.
It appears like this:
Column H.....................Column J.......................Column k (margin)
total sell price...............(sellprice  cost).............=J2/H2
I have column k set to percentage on the formatting menu so that it automatically works out the forumal. I know this may seem a bit simple, but it is ages since I have done anything to do with margin and just have a new task that involves calcualting margin and don't want to get it wrong.
Thanks in advance for any help.
CC
I really need help,
I am having trouble figuring it out how to add the formula for sales price column to calculate price get Net margin 10% if cost goes up or down want sales prices to calculate as price 10% net margin
in Net Margin column formula I have
Sales Price (Cost+Comm)/ Sales = Net Margin
up to now I add sales price manually, now I would to add formula to calculate sales price from net margin
my brain is not working , can some please help me with this, I am sure it must be really easy,
I have to add this to the file today, I really appreciate all the help in this matter.
hope my question is not confusing, please let me know if its does not make sense,
I just want Net margin to stay 10% no matter what the cost is and calculate the sales price from 10% net margin.
thanks in advance
Hi All
This is my first thread.
I need to work out the profit margin from goods i sell.
I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6
I need to work out the margin between A5 and A6
Thanks for reading this post.
This is my first thread.
I need to work out the profit margin from goods i sell.
I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6
I need to work out the margin between A5 and A6
Thanks for reading this post.
I have developed 3 simple formulas for calculating Cost, Sell Price, and Margin.
I was wondering if there was a way to create some buttons if you will telling which value / formula to use.
Here are the formulas:
To calculate sell price = B2/(1B3)
To calculate cost = B6*(1B7)
To calculate margin = 1(B10/B11)
Right now I have 3 different sets of cells, depending on what value to calculate, I fill in the variables.
I want to have just one set and then click either calc sell price, cost, or margin.
I was thinking about using a macros but was hoping there was another way.
Thanks in advance for any help.
I was wondering if there was a way to create some buttons if you will telling which value / formula to use.
Here are the formulas:
To calculate sell price = B2/(1B3)
To calculate cost = B6*(1B7)
To calculate margin = 1(B10/B11)
Right now I have 3 different sets of cells, depending on what value to calculate, I fill in the variables.
I want to have just one set and then click either calc sell price, cost, or margin.
I was thinking about using a macros but was hoping there was another way.
Thanks in advance for any help.
Hi All,
This may be a dumbo question or not even the right forum but I have cost price lists from suppliers which I want to calculate the selling price to make certain margins. So an oil filter costs me 1.99 and I want to sell at 3.58 to make 45% so I would have something like I have done on my attached sheet. Problem is I have to use trial and error to work out what the formula figure should be. How can I work out what the cost price cell needs to be multiplied by to give each profit margin e.g 25%, 30% etc? Thanks in advance.
I am trying to create an excel sheet that will calculate a variable based on a certain profit margin. It's for a manufacturing process where my formula looks like this:
Function 1 (cost)[(Material Cost*Part Weight)+(Hourly Labor Wage*CYCLETIME)+ ((CYCLETIME/60)*.75)] * Parts Needed = Cost
Function 2 (profit)Revenue Cost= Profit
Function 3 (profit margin)(Profit/Revenue)*100= Profit Margin
Everything will be given in Function 1 except CYCLETIME. I am trying to have Excel do it tell me what CYCLETIME will be with 10% profit margin and the same for 30% profit margin.
I'm stuck on this and can't figure out how to get all three formulas to tie together!
I would like to calculate different margins for a product. I have a formula to calculate the margin at a certain price, but I can't figure out how to calculate the price for a certain margin. I know this should be simple, but I can't get it. I have an approximate formula, but it's not perfect.
To calculate the margin of A, I use =SUM(A2B2)/A2. To get the 20% margin I have =SUM(B2*1.252); for the 15% margin I have =SUM(B2*1.177).
Can anyone help me, please?
A B C D E
Price Cost Margin 20% Margin 15% Margin
88 46.34 47% 58.02 54.54
To calculate the margin of A, I use =SUM(A2B2)/A2. To get the 20% margin I have =SUM(B2*1.252); for the 15% margin I have =SUM(B2*1.177).
Can anyone help me, please?
A B C D E
Price Cost Margin 20% Margin 15% Margin
88 46.34 47% 58.02 54.54
Hi all
Trying to calculate profit margin in excel 2007
Margin = (Sell  Cost) / Sell.
Have the following formula in excel
=SUM(O3N3)/O3
Where o3 is sell price and N3 is cost price
However, the formula comes out to 1 which is wrong.
what am I doing wrong here?
thanks
Trying to calculate profit margin in excel 2007
Margin = (Sell  Cost) / Sell.
Have the following formula in excel
=SUM(O3N3)/O3
Where o3 is sell price and N3 is cost price
However, the formula comes out to 1 which is wrong.
what am I doing wrong here?
thanks
Thank you for the response. I already have that formula. I guess what I'm trying to get to the point is that I leave Gross Profit open and say I feel I shoul;d be making .50 cents on the product. Then it will work with the other data I have formulas for and calculated Like Once I put it I want to make .50 on the product It changes what I should be charging per unit and it changes and calculates the gross profit.
So far I have =SUM(B8:B22) for the total to produce product B8 to B22, so B23 is all the total costs broken down to the cost to produce one unit
Then I have =(B31/B28) to calculate profit margin =B28 IS the price of the product right know retail
Then I have =(B31/B23) To calculate markup. =B31 is the profit margin.
I want to leave the profit margin field "open" with a formula" that would calculate everything else if I feel like I should be making .40 per unit one day or next year I feel like I want to make .50 per unit and then it would calculate what I should be charging based upon the cost to produce and everything else.
IS this possible and what kind of formula is it is it a What if or If statement.
Thank you in advance for all your help.
I've got a list of unique sales transactions with revenue $s, cost of production $s, the profit margin $s (a formula of revenue  cost of production), and margin % (profit divided by revenue.
I'd like to bucket the margin % for creating some charts.
Transaction......Revenue.....Cost.....Margin.....Margin %......"Bucket"
A1...................$575.........$450.....$125.......21.7%..........20+%
B3...................$107.........$90.......$17........15.9%..........1519.9%
C4...................$97..........$84........$13........13.4%.........1014.9%
I've got access formulas to calculate margin and margin %s, but I'm not how to map the margin % in to the appropriate bucket. I've got about 10 buckets, so i'm trying to create a long if statement.
I'd like to bucket the margin % for creating some charts.
Transaction......Revenue.....Cost.....Margin.....Margin %......"Bucket"
A1...................$575.........$450.....$125.......21.7%..........20+%
B3...................$107.........$90.......$17........15.9%..........1519.9%
C4...................$97..........$84........$13........13.4%.........1014.9%
I've got access formulas to calculate margin and margin %s, but I'm not how to map the margin % in to the appropriate bucket. I've got about 10 buckets, so i'm trying to create a long if statement.
Now I know the difference between markup and margin. What I need to do is set up a formula in excel to give the the proper markup on cost that will result in the specified margin for a product.
Example:
Cost = $150
Desired margin = 50%
Markup  ??
I want the the formula to figure out the markup and then apply that to the cost cost giving me my selling price to get the the margin levels I need.
Also, does anyone know of conversion table from marginto markup
Thanks !!
Example:
Cost = $150
Desired margin = 50%
Markup  ??
I want the the formula to figure out the markup and then apply that to the cost cost giving me my selling price to get the the margin levels I need.
Also, does anyone know of conversion table from marginto markup
Thanks !!
Hi
I have 3 cells, B1 (Cost Price), B2 (Margin) and B3 (Selling Price). There is a formula in B3 that is dependent on B1 & B2 i.e. If Cost Price or Margin changes selling price changes, that is ok.
Now if the Cost Price changes, I should be able to keep either Margin or Selling price same. Here I want to use a check box, if the check box is checked(ticked), the Margin changes keeping the Selling Price same and if it is unchecked the Selling price changes keeping the Margin same.
Your help is highly appreciated
Reagrds
KShora
I have 3 cells, B1 (Cost Price), B2 (Margin) and B3 (Selling Price). There is a formula in B3 that is dependent on B1 & B2 i.e. If Cost Price or Margin changes selling price changes, that is ok.
Now if the Cost Price changes, I should be able to keep either Margin or Selling price same. Here I want to use a check box, if the check box is checked(ticked), the Margin changes keeping the Selling Price same and if it is unchecked the Selling price changes keeping the Margin same.
Your help is highly appreciated
Reagrds
KShora
Hi all
I have created a simple spreadsheet to allow me to enter a sales price for a product. The spreadsheet has a cost price and I then key a sales price. In the next column it shows the margin as a percentage using (spcp/sp). So this works fine but there are times where I have a margin in mind first and I want it work out the sales price for me instead so sort of reversing what I have already. I could do a new spreadsheet which allows me to type in the cost price and the margin and then show the sales price but is there a way to do this on the same spreadsheet so I effectively either type in the sales price or the margin % and it then works out the other. The margin box has the equation in it at the moment so if I was to type something into that it would presumably delete the equation and I suppose the same would happen if I was to enter an equation in the sale price. Maybe I need some sort of 'what if' statement in each box. If the cell is blank then resort to the equation, if not then accept the value typed in. Hope that all made sense to someone!!
I have created a simple spreadsheet to allow me to enter a sales price for a product. The spreadsheet has a cost price and I then key a sales price. In the next column it shows the margin as a percentage using (spcp/sp). So this works fine but there are times where I have a margin in mind first and I want it work out the sales price for me instead so sort of reversing what I have already. I could do a new spreadsheet which allows me to type in the cost price and the margin and then show the sales price but is there a way to do this on the same spreadsheet so I effectively either type in the sales price or the margin % and it then works out the other. The margin box has the equation in it at the moment so if I was to type something into that it would presumably delete the equation and I suppose the same would happen if I was to enter an equation in the sale price. Maybe I need some sort of 'what if' statement in each box. If the cell is blank then resort to the equation, if not then accept the value typed in. Hope that all made sense to someone!!
Hi there
I've almost got no hair left trying out formulas for this, hope someone can help!
1. Setting a percentage margin to set the sale value
example
Cell B19 is cost (value is filled in)
Cell B20 is selling price (value not filled in)
Cell B21 is percentage I want to put in to change the B20 value (for example 30% profit margin)
I need to know what needs to go in B21 to for it to show up as a % and what needs to go into B20 for it to automatically fill in the sale price
2. Percentage Margin
example
Cell B19 is cost (value is filled in)
Cell B20 is selling price (value filled in)
Cell B21 is percentage increase in cell B21 (value not filled in)
B21 needs to show up as a % so I can see what the margin in
Thank you for your time
I've almost got no hair left trying out formulas for this, hope someone can help!
1. Setting a percentage margin to set the sale value
example
Cell B19 is cost (value is filled in)
Cell B20 is selling price (value not filled in)
Cell B21 is percentage I want to put in to change the B20 value (for example 30% profit margin)
I need to know what needs to go in B21 to for it to show up as a % and what needs to go into B20 for it to automatically fill in the sale price
2. Percentage Margin
example
Cell B19 is cost (value is filled in)
Cell B20 is selling price (value filled in)
Cell B21 is percentage increase in cell B21 (value not filled in)
B21 needs to show up as a % so I can see what the margin in
Thank you for your time
I have a rental business. From time to time, a customer damages my asset beyond repair. I want to develop a spreadsheet that helps me open negotiations based on a predetermined margin% that I enter.
I am attaching a mockup of what I currently am using. Right now, I am using a hitandmiss tactic to come up with a potential sell price to achieve the margin that I require. Ideally, I would like to type in the margin% and the spreadsheet calculate the MINIMUM Margin Sell Price to achieve that margin%.
These are the formulas that I am using currently:
Total Revenue = Rental Revenue + (Potential) Margin Sell Price (ie negotiated)
Total Costs = Unit Standard Cost + Commission + Freight + Repair Charges
Margin = Total Revenue  Total Costs
Margin% = Margin/Total Revenue
Can anyone help? I'm not sure if there are internal Excel functions that deal with margin...
Thanks,
CHAStats
I am attaching a mockup of what I currently am using. Right now, I am using a hitandmiss tactic to come up with a potential sell price to achieve the margin that I require. Ideally, I would like to type in the margin% and the spreadsheet calculate the MINIMUM Margin Sell Price to achieve that margin%.
These are the formulas that I am using currently:
Total Revenue = Rental Revenue + (Potential) Margin Sell Price (ie negotiated)
Total Costs = Unit Standard Cost + Commission + Freight + Repair Charges
Margin = Total Revenue  Total Costs
Margin% = Margin/Total Revenue
Can anyone help? I'm not sure if there are internal Excel functions that deal with margin...
Thanks,
CHAStats
Good morning,
This could well be a 'no brainer' for some but Monday morning and as yet no coffee. My mind has not kicked over yet and is still coughing and spluttering.
Can somebody please assist with a formula please?
I have a cost summary work sheet, the cost prices are fixed, there is a standard margin. I wish to be able to set the margin to alter the sell
summary portion 2013 Dec.jpg
I would like to be able to enter a percentage into the 'Margin' column, and have it change the 'Sell' column figure to suit. Does anybody know the formula to use? Currently the formula from cost to sell is 'Cost/.6' and the margin is calculated as '=(sellcost)/sell'
Best regards
Geoff
This should be easy but i'm having the hardest time trying to figure this equation out. Hope someone can help me.
I'm trying to create a spreadsheet that will give me a specific margin for prices. Here's my sample:
I have three cost items. Let's call #a production, #b marketing, #c tax. All three components make up the final price. However, the #c tax is a fixed number which is 15% of the final price. The final margin that I want to get is 40%.
Margin = (price  a  b  c)/price (I want margin to be 40%)
So for example, let's say production = 500, marketing = 500, tax is unknown because we don't have final price.
I'm able to get a 40% margin with the formula (a+b/.60) without the tax portion BUT when I try to put formulas in there to include the tax...i'm stumped. Either I can't get the margin to equal 40% or I can't get the tax to be 15% of the final price.
I hope I explained this clearly. Probably not but please ask me more questions! Thanks!
I'm trying to create a spreadsheet that will give me a specific margin for prices. Here's my sample:
I have three cost items. Let's call #a production, #b marketing, #c tax. All three components make up the final price. However, the #c tax is a fixed number which is 15% of the final price. The final margin that I want to get is 40%.
Margin = (price  a  b  c)/price (I want margin to be 40%)
So for example, let's say production = 500, marketing = 500, tax is unknown because we don't have final price.
I'm able to get a 40% margin with the formula (a+b/.60) without the tax portion BUT when I try to put formulas in there to include the tax...i'm stumped. Either I can't get the margin to equal 40% or I can't get the tax to be 15% of the final price.
I hope I explained this clearly. Probably not but please ask me more questions! Thanks!
Hi,
I need help with below headbreaker
I have a pivot table in which I have all sales transactions.
One of the columns is calculated field for the margins
For sales where the price is higher than the cost I have no problem to calculate and properly display the margin but for below 2 transactions I have :
1. Invoice Sale = 100 Cost = 150 Margin = 50 Margin % = 50 %
2. Credit note Sales = 100 Cost = 50 Margin = 50 Margin % = 50 %
Is there anyone that can help me out with the formula that correctly calculates and displays the margins ?
I need help with below headbreaker
I have a pivot table in which I have all sales transactions.
One of the columns is calculated field for the margins
For sales where the price is higher than the cost I have no problem to calculate and properly display the margin but for below 2 transactions I have :
1. Invoice Sale = 100 Cost = 150 Margin = 50 Margin % = 50 %
2. Credit note Sales = 100 Cost = 50 Margin = 50 Margin % = 50 %
Is there anyone that can help me out with the formula that correctly calculates and displays the margins ?
Hello,
Am hoping for whizz to be able to help me.
We are trying to create a formula to calculate price based on margin. We have 2 tables; one that has the margin info, based on price value and the second table has the initial price, before the margin is applied.
Basicaly I need a formula which finds the initial price, say 32, and then looks this up on the Margin table and finds the closest value to this, which has a margin value against it. It will then multiply the margin by the initial value.
eg Initial value
32 43 51 55 59 63 100 317 355 521
eg Margin
025 50%
2650 49.67%
5175 49.34%
76100 49.01%
301325 46.70%
351375 45.38%
501525 43.40%
So for value 63, this would look up margin range 5175 and applie the 49.34% margin; 63 x 49.34% = 94.08
I have tried nested if/and, but this has not worked.
HELP!!!!!!
Am hoping for whizz to be able to help me.
We are trying to create a formula to calculate price based on margin. We have 2 tables; one that has the margin info, based on price value and the second table has the initial price, before the margin is applied.
Basicaly I need a formula which finds the initial price, say 32, and then looks this up on the Margin table and finds the closest value to this, which has a margin value against it. It will then multiply the margin by the initial value.
eg Initial value
32 43 51 55 59 63 100 317 355 521
eg Margin
025 50%
2650 49.67%
5175 49.34%
76100 49.01%
301325 46.70%
351375 45.38%
501525 43.40%
So for value 63, this would look up margin range 5175 and applie the 49.34% margin; 63 x 49.34% = 94.08
I have tried nested if/and, but this has not worked.
HELP!!!!!!
I have a pivot table with the categories of Sales, Cost, Profit, and Profit Margin. However, I can't seem to list the Profit Margin category to list as a Percentage of the total sales (In other words, as an actual Profit Margin). To get this column, I simply drag Profit into the values column again and try to change it to % of sales. Every time I try to do this, however, I never get to choose the sum of the sales, but rather individual items that make up the pivot table.
Is there any way to list a total profit margin percentage on a pivot table? Any help on this matter would be greatly appreciated.
Is there any way to list a total profit margin percentage on a pivot table? Any help on this matter would be greatly appreciated.