Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Calculating Price Based On Projected Profit Margin

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am using this current formula to calculate my profit margin on product I sell.

=(A8-B8)/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%

View Answers     

Similar Excel Tutorials

Goal Seek in Excel
Find a desired result by having Excel automatically change input values in Excel. Using Goal Seek, you setup a for ...
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 ...
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 ...
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 ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Select Ranges of Cells in Excel using Macros and VBA
This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This means that you will learn ...

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
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
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
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
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

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?


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~


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


Hello,

I'm having a heck of a time figuring out how i can set up my commission rate in excel for my company. Our scale is as follows:

Margin % is in cell F7
Profit Dollar amount is in cell F6

under 20% margin pay 15% commission on profit
21% to 30% margin pay 20% commission on profit
31% to 40% margin pay 25% commission on profit
41% to 50% margin pay 30% commission on profit
over 50% margin pay 35% commission on profit

Any help would be greatly appreciated!

Thanks


I know this is the kind of question that will leave me kicking myself, but i'm having a mind blank...

I want to know what i need to set my revenue at to hit a target margin (the target margin is a cell that will be editable).

The formula I came up with is


(Z13*(1+$AP$11))-'Pricing Data Original'!K13)/'Pricing Data Original'!K13)

Or

(Unit Cost*(1+Target Margin))-Current unit revenue)/'current unit revenue)

If I enter 50% as target margin it sets the revenue at 150% of Cost which = a margin of 33%, Setting the Target to 100% sets the revenue to 200% of the cost which equals a margin of 50%

Clearly what I've done is created a target profit not margin, but I can't work out how to convert it to a target margin.

any ideas

thanks


I am looking for a template that allows me to calculate a selling price when
i posess known cost factors such as: Cost of Product, Freight In, Handeling,
Storage, Freight Out, Overhead %, Gross Profit Margin%

It would be great of I could calculate several (up to 5) prices on one
worksheet



What formula do I use to determine a margin goal?

For example if my retail price has to be $99.99 and my margin has to be 45% what formula do I use to determine what my cost is?

Thanks!


Hello,

Can someone help me with the following:

I am working on a spreadsheet with sales figures

For calculating the margin I take the sales value - cost
For calculating the margin % I take the margin /sales value

When I have a sales value > 0 there is no problem

When I have a sales value which is 0 I am getting in trouble
With a sales value of 0 and a cost >0 I am getting #DIV/0!
With a sales value of 0 and a cost of 0 I am getting #DIV/0!

How can I get a negative margin of 100% when I am selling at 0 with a cost > 0
How can I get a 0% margin when I am selling at 0 with a cost of 0
(I know it sounds mad but we have transactions like this)

many thanks in advance for your help


I am trying to insert a formula to calculate a percentage of margin/markup.
Example cost = $7.00 divided by 30 % = $10.00. My cost is 7.00, my sale price
is 10.00. Can anyone help with this formula?



Sale = A1
Cost = B1
Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?

Alex


I would like to calculate selling prices in different ways, based on a choice made by the user.
Choices would be as follows:

1. Fixed Profit Amount - eg.15
2. Cost Plus eg.10%
3. To give a fixed % profit based on selling price - eg.10%

The complication is that part of the cost of the sale is variable based on the selling price - eg. a sales commission.

I've attached a worksheet.

Would really appreciate any help with this conundrum!


Hello,
I am trying to figure out the formula for calculating retail price if I have the cost and margin (%)/

Example:
cost is $10.00 (known figure)
Markup is : 40% (known figure)
Retail should be: 16.66

Thanks JR


please help, i need a formula to calculate profit.
these are the colum headings...

item, units bought, buying price, selling price, units sold and profit.

i want to be able to update the spreadsheet regularly and know how much profit i have made..
please help me


If in column (A) I have a "B" for buy and a "SS" for sell short. Then in column (B) I have the fill price of a certain stock such as "$98.5" for example. Then in Colomn (C) I have the Closing price for this particular stock such as "$99.5."(since I will hold to the end of the day). In Column (D) I want to have the profit or loss. The problem is that the formula needs to be different depending if the opening transaction is Buy "B" or a "SS" Sell Short. So in other words I need a formula that says if in column (A) there is a "B", then take the closing price column (C) and minus the Fill price in column (B) to give the Profit or loss in Column (D). But at the same time or in the same formula cell if in column (A) there is "SS" then it needs to be reverse and take the fill price Column (B) and minus the Closing Price Column (C) to put the Profit or loss in Column D. Is there a formula I can put in the cells in column D that can do this? Any help is much apreciated.




Hello all! I am very new to the forum and even newer to excel macros and VBA. What I am trying to do is have excel calculate the amount of pip profit of a trade in forex. For instance if I was making a short or sell trade and the price was 1.3000 and price moved to 1.2950 then that would equal 50 pips profit so I just want this calculated automatically by excel. I will add the entry and exit prices manually. Also the formula has to know if it's minus profit. So with the same numbers if I was making a long or buy trade and it went from 1.3000 to 1.2950 then it should show this as -50 pips.
My cells are as follows:

B15 long or Short
B16 Entry Price
B20 Exit Price
B21 Profit/Loss in pips

Hopefully I explained this well enough and I appreciate the help. Thanks!

Greetings,

Now, the calculation for a bond is quite obscure. For example, a move from 119.00 to 120.00 is a total of 32 points. so, once the price is at 119.32, the next one point move will take price to 120.00, and so on 120.01, 120.02, 120.03 to 120.32 then 121.00 etc.

Here is my original formula and how it was incorrect with a given example;

A B
1 Sell 125.08
2 Buy 124.32
3 Profit 24.27

The formula I used was: (B1-B2-0.0015)*(0.32*100), the answer excel gave in B3 was 24.27.

The correct answer should be 8.9985, the 0.0015 is simply the commission fee. The reason the answer should be 8.9985 is because once price gets from 125.08 to 124.32, that is 9.00 point profit, minus 0.0015 commission would equal 8.9985.

Example: 125.08 to125.00 = 8.00 , 125.00 to 124.32 = 1.00 = Total Profit 9.00 less 0.0015 commission = 8.9985

This formula I used seemed to work well with certain scenarios, but once price gets to around that 32 tip off point, the formula I use is not correct.

If anyone can assist me with a formula I can use in one cell only, I would greatly appreciate it. Look forward to hearing from you.

Regards,

Vladmir.


Hello,

I need a formula where I can say to Excel in a cell I want a profit of $2 for example and it'll calculate the item price I need to set to the product.

Thank you all in advance!

Dan.


Greetings,

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Whe
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

Any help would be greatly appreciated.

Scott


Hello,

I have a product ordering form where I need to calculate totals in this way:
Qty @ unit price
100-249 @ $1.10
250-499 @ $0.45
500-999 @ $0.24
1000+ @ $0.20

Qty Total = _______
Cost Total = _______

So for example if the Qty Total is >250, the Cost Total is calculated at a rate of $1.10.
But if the Qty Total is 250 - 499, then the Cost Total would be calculated at a rate of $0.45. Etc.
How do I do that? Is there a formula that I can plug into the Cost Total cell that would calculate it for me?

Please, if anyone could help, I'd very much appreciate it.
Best,
Taylor

Goal seek and solver are great to solve this equation, but I need to see the formula for further manipulation. Any ideas? Any help is appreciated.



50,000 revenue
00000 discount
-10,000 cogs
40,000 profit
80% margin
60% target margin

Solve to find maximum discount to achieve target margin


When printing a document of various pages in length, using VBA code to set it, I need:

Odd pages of document (page 1, 3, 5, etc) to have:
Right margin of 4.375
Left margin of 0
Top margin of 0
Bottom margin of 4.75

Even pages of document (pages 2, 4, 6, etc) to have
Right margin of 0
Left margin of 4.375
Top margin of 0
Bottom margin of 4.75

Can someone help? thanks, mikeburg


Im trying to calculate the commission on sales. The commission is 6% on all sales that are at least 20% above cost.

I cannot figure out a formula to help me do this.

I have product cost in cell a3 and retail price in b3. So i first need to figure the percentage above cost, which will be in cell d3. Then if the percentage above cost is 20% or more i need to calculate a 6% commission which will be in cell f3. I am so frustrated.


I have a pricing spreadsheet from out vendors. They list the current cost and I have figured our retail according to are selected margin. But I need the retails to round up or down to the nearest .99.
Ex. 23.37 down to 22.99
5.61 up to 5.99
Can anyone help with a formula?


hello gentlemen!

I've a sticky problem at hand...
I need to calculate the average cost per share in a spreadsheet.
The average price works wonders when I only Buy stocks but the moment I sell stocks it changes my average cost of shares.

Basically I need to take the shares I sell out of the calculation...


My current formula is this one:

=SUMPRODUCT($G$10:$G$65000,$L$10:$L$65000,(SUBTOTAL(3,OFFSET($G$10,ROW($G$10:$G$65000)-MIN(ROW($G$10:$G$65000)),,))))/SUBTOTAL(9,$G$10:$G$65000)


I didnt write it myself but ti works when I Buy shares. I tweaked it (I dont understand what the subtotal() does exactly. it uses a counta() method)

For info:

Column G = Quantity (number of shares)
Column L = Price

now I need to be able to customize this formula so that it takes into account only the stocks that are Bought (Column E stores "B" or "S")

Can someone tell me how to tweak the formula so my average cost of share doesnt take into account the quantity and price in the calculation when Column E = "S" please?

Thanks in advance!


I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:

Code:

=SUMPRODUCT(B:B/A:A)/COUNT(B:B)


However, I don't want to include entries where the profit column has a negative value. I tried something like this:

Code:

=SUMPRODUCT((B:B>0)/(A:A))/COUNTIF(B:B,">0")


This doesn't seem to work quite right as it's not ignoring the cost entry when the profit entry is negative. Any advice?