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 Video Tutorials
Get Last Price In Column Based On Criteria In Other Columns
 See how to get last price in column based on criteria in two other columns using the LOOKUP function. ...
Waterfall Profit Chart
 See how to create a Waterfall Profit Chart using a Stacked Column Chart, some formulas to create a second data set, and adding No Fill as a color to t ...
Data Table Creates 100 Formulas
 See how to create a Contribution Margin Income Statement Analysis across various units using a 1 Variable Data Table (What if analysis). See how to cr ...
Markup On Sell Price Formula
 See how to calculate Sales Price given Cost and Markup On Sales Price.
Sells Price = Cost/(1Markup On Sales Price)
Base = Part1 ...
Sells Price = Cost/(1Markup On Sales Price)
Base = Part1 ...
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 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
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.
I hope this isn't a silly question, but I'm new with Excel and can't find an
answer...
I'm after a formula that will calculate a buy price where I know the final
retail price and margin (eg: $? + 40% = $149, $? + 40% = $299). I want to be
able to enter a retail price and margin to calculate the buy price.
Also, a formula that will calculate the profit margin where I know the buy
and sell prices (eg: $200 + ?% = $400, $150 + ?% = $250). I want to be able
to enter buy and sell prices and calculate the profit margin.
Thanks!
answer...
I'm after a formula that will calculate a buy price where I know the final
retail price and margin (eg: $? + 40% = $149, $? + 40% = $299). I want to be
able to enter a retail price and margin to calculate the buy price.
Also, a formula that will calculate the profit margin where I know the buy
and sell prices (eg: $200 + ?% = $400, $150 + ?% = $250). I want to be able
to enter buy and sell prices and calculate the profit margin.
Thanks!
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
I have 3 cells; B1 (Cost Price), C1(Margin) & D1 (Selling Price), The formula in the cell D1 to calculate the selling price is =ROUNDUP(B1/(1C1/100),0). Let me take an example, if B1=50 (cost price) and C1=25 (Margin), my selling price derived from the formula is 67. Now if the selling prices changes to say 55, normally my Margin is same and the new selling price is 74. I need to have a choice to change the Margin to keep the selling price same, in this case to keep my selling price same as 67 the margin has to change to 17. {This can be done by using the formula =ROUNDUP(100*(1B1/D1),0)}. For this I want to have a checkbox, if UNCHECKED the MARGIN IS SAME and the selling price increases, if the checkbox is CHECKED the MARGIN CHANGES and the selling price remains the same.
I hope the above is clear and look forward to a solution
Thanks and Regards
KShora
I hope the above is clear and look forward to a solution
Thanks and Regards
KShora
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
Hi,
Can anybody help me.
I am trying to calculate my percentage of profit margin using my cost price and my sell price. The calculation formula is not my problem...my problem is that I would like to write in the cells for "cost" and for "selling price" the measurement units for cost and for selling price.........I would like to write those numbers like this: for cost (10 Kg), for selling price (12 Kg)......then my percentage of profit is 16.6. The 10 Kg means that I paid 10 dollars per kilogram and the 12 Kg means that I sell at 12 dollars per kilogram.
However, I do not know how to write a formula that can disregard the Kg unit and just consider the 10 and the 12, so to calculate the % of profit. My current formula gets an error message when I write in the cell 10 Kg instead of just 10....so, how can I fix this ?
Can anybody help?
Can anybody help me.
I am trying to calculate my percentage of profit margin using my cost price and my sell price. The calculation formula is not my problem...my problem is that I would like to write in the cells for "cost" and for "selling price" the measurement units for cost and for selling price.........I would like to write those numbers like this: for cost (10 Kg), for selling price (12 Kg)......then my percentage of profit is 16.6. The 10 Kg means that I paid 10 dollars per kilogram and the 12 Kg means that I sell at 12 dollars per kilogram.
However, I do not know how to write a formula that can disregard the Kg unit and just consider the 10 and the 12, so to calculate the % of profit. My current formula gets an error message when I write in the cell 10 Kg instead of just 10....so, how can I fix this ?
Can anybody help?
I have a spreadsheet that calculates profitability of wrecker calls. I calculate the cost based on the total mileage of each call, and I pay the driver/operator onethird of the total price charged. I want to make a certain profit margin on each call, but I want to be able to enter a percentage into a cell so I can change my profit %, if desired.
I need to write a formula that will solve for the price of the wrecker call based on the mileage and operator cost. What has me stumped is the fact that the operator pay is a percentage of the total price, so when the price changes, so does the operator's pay.
Can this be done, or is it circular?
I need to write a formula that will solve for the price of the wrecker call based on the mileage and operator cost. What has me stumped is the fact that the operator pay is a percentage of the total price, so when the price changes, so does the operator's pay.
Can this be done, or is it circular?
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 ?
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.
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 am trying to build a formula that would allow numeric values of cells A2H2 to affect the following formula, but I keep getting errors if I add too many "values/formulas" to a single string. Is there a better formula to use to build this? I need to be able to attach this formula to numerous spread sheets to modify data. The profit margin has to be different based upon cost of item.
Example:
A2=8% B2=9% C2=10%, etc etc
Cost $25(B5), Margin should be set to 20%(E2), New price is $31.39 (E5)
Cost $125(B5), Margin should be set to 10%(C2), New Price is $139.99 (E5)
*A2H2 represent gross profit margins
*B5B1500 represent cost of goods
*E5E1500 represent new price of goods rounded to the nearest $.99 or $.09
*F5F1500 represent new price of goods rounded to nearest $.09 when B5B1500<$10.
Formula for E5:
=IF(B5>=250,SUM(ROUND(B5/(1$A$2),0),0.99),IF(B5>=175,SUM(ROUND(B5/(1$B$2),0),0.99),IF(B5>=100,SUM(ROUND(B5/(1$C$2),0),0.99),IF(B5>=50,SUM(ROUND(B5/(1$D$2),0),0.99),IF(B5>=25,SUM(ROUND(B5/(1$E$2),1),0.09),IF(B5>=10,SUM(ROUND(B5/(1$F$2),1),0.09),""))))))
Formula for F5:
=IF(B5>=4, IF(B5<10, SUM(ROUND(B5/(1$G$2),1),0.09),""), SUM(ROUND(B5/(1$H$2),1),0.09))
Excel is not allowing E5 & F5 to be in a single formula due to its limits of number of formulas allows per line.
Example:
A2=8% B2=9% C2=10%, etc etc
Cost $25(B5), Margin should be set to 20%(E2), New price is $31.39 (E5)
Cost $125(B5), Margin should be set to 10%(C2), New Price is $139.99 (E5)
*A2H2 represent gross profit margins
*B5B1500 represent cost of goods
*E5E1500 represent new price of goods rounded to the nearest $.99 or $.09
*F5F1500 represent new price of goods rounded to nearest $.09 when B5B1500<$10.
Formula for E5:
=IF(B5>=250,SUM(ROUND(B5/(1$A$2),0),0.99),IF(B5>=175,SUM(ROUND(B5/(1$B$2),0),0.99),IF(B5>=100,SUM(ROUND(B5/(1$C$2),0),0.99),IF(B5>=50,SUM(ROUND(B5/(1$D$2),0),0.99),IF(B5>=25,SUM(ROUND(B5/(1$E$2),1),0.09),IF(B5>=10,SUM(ROUND(B5/(1$F$2),1),0.09),""))))))
Formula for F5:
=IF(B5>=4, IF(B5<10, SUM(ROUND(B5/(1$G$2),1),0.09),""), SUM(ROUND(B5/(1$H$2),1),0.09))
Excel is not allowing E5 & F5 to be in a single formula due to its limits of number of formulas allows per line.
I need a one cell formula that calculates a price based on the following conditions
Items costing the Contractor $50 or less are marked up 100% witha profit margin on the selling price of 50%
items costing 50.01 to 100 are marked up 80% with a profit margin on selling price of 44.44%
items costing over 100 are marked up 60% with a profit margin on the selling price of 37.50%
Items costing the Contractor $50 or less are marked up 100% witha profit margin on the selling price of 50%
items costing 50.01 to 100 are marked up 80% with a profit margin on selling price of 44.44%
items costing over 100 are marked up 60% with a profit margin on the selling price of 37.50%