HI

I need to work out the projected future profit margin based on the trend of the previous period of days in the month.

For instance

Day 1 - 25.60%

Day 2 - 24.50%

Day 3 - 26.90%

Day 4 - 23.20%

Day 5 - 21.00%

What would be the trend for the rest of the month based on the above. Please advise the best function/formule to calculate this.

Thanks

VC

I need to work out the projected future profit margin based on the trend of the previous period of days in the month.

For instance

Day 1 - 25.60%

Day 2 - 24.50%

Day 3 - 26.90%

Day 4 - 23.20%

Day 5 - 21.00%

What would be the trend for the rest of the month based on the above. Please advise the best function/formule to calculate this.

Thanks

VC

- Profit margin formula returns incorrect result
- Gross profit margin formula
- Profit Margin
- Pivotable show profit margin in %
- Help: Profit margin
- How to create backwards profite margins
- Need Backwards Profit Margin Formula
- Gross profit margin formula
- How do i write formula to show a profit margin
- Pivot Tables - Posting Profit Margin as %
- Making excel template for profit margins
- Excel Newbie needs help designing a Profit Margin Sheet
- Calculate daily takings needed based on daily spend & profit margin
- Dynamic Chart To enable comperative charting of service line % margins
- Formulas: Profit Margin
- Data Validation to Calculate Profit Margin
- Excel formula for markup and profit margin
- Calculating Gross Profit Margin on 'bonus stock'
- How to get profit in % using pivotable
- Gross profit
- How do i set up a profit margin formula
- Percentage Margin Formula
- Formula to decrease a margin, in connection with increasing basic value
- Margin/price markup as %

Trying to calculate profit margin in excel 2007

Margin = (Sell - Cost) / Sell.

Have the following formula in excel

=SUM(O3-N3)/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

follows:

In Col A2, I will have a cost (i.e. $15.00)

In Col B2, I would like the result to be the total markup of $15.00 based on

a 40% gross profit margin, and then I need the resulting number to be rounded

up or down to the nearest $0.05, ending in a "5."

The resulting number should be $24.95, but I can't get it to come out that

way.

Any suggestions would be appreciated!

product and I want a formula I can plug in a profit margin to get the selling

price. Ex:

My cost is $3.98. I want a 40% GPM. The selling price should be $6.63

I have tried =((1-I5)+1)*E9, where I5 is the plug in GP percentage and E9 is

the product cost, but this gives me $6.37, which is a 38% GPM. Is this the

best I can expect - close but not exact?

i need to convert the raw data from excel into excel pivotable table and show profit margin in % by company.

from the sample data ( see section A macro) , i hope that some one can help me to generate a pivotable, sales by company by profit margin in % .

the problem is when i try to produce the pirvotable i get the profit margin in % as 100%. ( this is error ).

from the raw data , i can manual cal and confirm that the profit margin equal 50% only. ( as i only hv 2 cases of data , and each case profit margin equal 50%. so overall cal of profit margin should be 50% )

hope some one can help.

paul

(section A )

following is the macro for generate the sample data ( you can cut and paste into your excel macro and run ) :-

Sub Macro_07_1_enter__sample_data_()

'

Range("A1").Select

ActiveCell.FormulaR1C1 = "company"

Range("B1").Select

ActiveCell.FormulaR1C1 = "month"

Range("C1").Select

ActiveCell.FormulaR1C1 = "year"

Range("D1").Select

ActiveCell.FormulaR1C1 = "sales amt"

Range("E1").Select

ActiveCell.FormulaR1C1 = "cost"

Range("F1").Select

ActiveCell.FormulaR1C1 = "brand"

Range("G1").Select

'End Sub

'Sub Macro_08_1_enter_value()

'

' Macro_08_2_enter_value Macro

'

Range("A2").Select

ActiveCell.FormulaR1C1 = "company A"

Range("B2").Select

ActiveCell.FormulaR1C1 = "Jan"

Range("C2").Select

ActiveCell.FormulaR1C1 = "yr_2006"

Range("D2").Select

ActiveCell.FormulaR1C1 = "100"

Range("E2").Select

ActiveCell.FormulaR1C1 = "50"

Range("F2").Select

ActiveCell.FormulaR1C1 = "Hakko"

Range("F3").Select

'End Sub

'Sub Macro_07_copy()

'

' Macro_07_copy Macro

'

'

Range("A2:F2").Select

Range("F2").Activate

Selection.AutoFill Destination:=Range("A2:F3"), Type:=xlFillDefault

Range("A2:F3").Select

'Sub Macro_07_margin_in_per_cent()

'

' Macro_07_margin_in_per_cent Macro

'

Range("G1").Select

ActiveCell.FormulaR1C1 = "margin_%"

Range("G2").Select

ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])/RC[-3]"

Range("G2").Select

Selection.Style = "Percent"

Selection.AutoFill Destination:=Range("G2:G3"), Type:=xlFillDefault

Range("G2:G3").Select

End Sub

I would like to know what's the easy method for working out profi

margin especially for 17,000 products with variation price

all the variation price is at the column C:

but it would be nice to add variation profit margin for each price eg.

lower price (1.00 to 1000) = higher profit margin

higher price = (1001 to 60,000) lower profit margin

thank for ur hel

--

Message posted from http://www.ExcelForum.com

what they were saying in the other thread was

a1 (selling price)

a2 (profite margin)

a3= a1-(a1*a2/100))

which looks great and should also work great...however i dont know how to activate that formula so make it work so i can just punch numbers into boxes and get different profite margins and such

any help would be greatly appreciated!!!

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~

follows:

In Col A2, I will have a cost (i.e. $15.00)

In Col B2, I would like the result to be the total markup of $15.00 based on

a 40% gross profit margin, and then I need the resulting number to be rounded

up or down to the nearest $0.05, ending in a "5."

The resulting number should be $24.95, but I can't get it to come out that

way.

Any suggestions would be appreciated!

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.

Is there any way to list a total profit margin percentage on a pivot table? Any help on this matter would be greatly appreciated.

-PrivateLucky

edit: edit the title b/c it was not specific enough

OK so I'm sure you expert and even somewhat novice users will be rolling your eyes after reading my question, but although I would classify myself as a 'Word' Expert.... Believe it or not this is the First time I’ve tried to use ANY version of Excel.... So what would prob be a pretty basic command to a regular user is completly foreign to me!

So I have Excel 2010 and I need to create a sheet for my online sales business that shows Cost to buy, Price Sold, Profit made and Profit Margin.....For the profit margin I want to use SELLING PRICE profit margin Not COST profit margin. (R=P/B) Where R is "Rate", or aka Selling Price Profit margin.... P is "Mark-Up", or aka Profit made ...... and B is "Base", or aka Selling price that I sold the item for. So I want to use that formula for the Profit Margin portion of the sheet but I still want the simple function of 'Cost' Minus 'Sell price' to see 'Profit made' then flow right into the profit margin colum.

I have tried searching Google for info on how to create this sheet but I must be missing something . I've tried to enter the formulas in the cells....but don't think I'm entering them in the right cells or maybe not entering them as a formula....When people say to 'Enter formula into cell C2' Is that like litterally entering it into each cell can I make it apply to the whole column somehow? (I'd like to keep adding new items as I sell them, so it would be like a master sheet) And I noticed the formulas I found (besides not working) had quotation marks... Am I supposed to leave those in the formula.....Hell I don't know, that’s why I'm here!

Thanks Again for any help

DJ

I have always had to calculate this figure manually by trail and error and am sure there must be a simple way to solve it automatically in a formula so here I am . .

The basic idea is to do with calculating total takings needed as a target - I have a budget which gives me a daily profit needed to replace stock and pay all the overheads etc. I know my margins, taxes etc on the different aspects of the business but for now I have to enter a total daily takings figure to see if the profit is enough and if it is higher or lower I have to keep changing the total takings figure until the result matches the profit needed. I am sure there must be a simple bit of maths and or formula I am missing so if anyone can suggest a solution it would be greatly appreciated.

Many thanks.

I have looked endless through all threads on dynamic charting and cannot get the solution to chart 2011-2012 month by month margins as part of performance management of the service lines.

I have provided the data file sample and would be grateful if a chart model is suggested that allow me to be able to view the Service Line Margin trends in one graph. So there would be a cell where I enter the Service Line(say A), and the graph changes to data fron 2011-2012 margins% for service line A. I have over 350 service lines. I have tried everything and can't seem to get a template that works!

Thank you in advance, MVPs

I think what I need is fairly simple, I just can't think of the right way to go about constructing a formula (or even what type of formula I would use!).

Let's say I have a price sheet for my store in Excel. I want to make the spreadsheet easy for my sales people to use to calculate differing profit levels. I'd like to embed a drop down box at the top (I can do that part ) that they can select, say, 10%...20%...30% markup. I know how to do that basic formula. I don't understand how to link that formula to a number selected in a drop down box. I don't know how to make the prices change based on the value selected in the drop down box. I can't do vlookup because it's about 1500 lines long. Also, it doesn't have to be drop down box based--that's just the idea in my head. I tried just making a macro that would run when they hit the button, but when the macro runs it switches the focus back to the top, very annoying if you were looking at prices on cell D811. Any ideas??

then

B2 a 5% markup, B3 then would show the sell price, B4 would show the margin

C2 a 10% markup, C3 the sell price, C4 margin

etc all the way to 100% in 5% increments

In short i'm looking for an easy way to calculate a realistic sell price

based on a margin

I am trying to work out the formula in excel to determine bonus stock gross profit.

bottle units per ctnunit sell pricectn costctn sell priceGPDEALS250ml

24 $ 4.00 $ 20.85

$ 96.00

78%Buy 5 ctns get 1 ctn free500ml

24 $ 4.00 $ 26.13

$ 96.00

73%Buy 8 ctns get 1 ctn free1,000ml

12 $ 6.00 $ 20.85

$ 72.00

71%Buy 8 ctns get 1 ctn free

There are 2 deals:

Buy 5 ctns get 1 free

Buy 8 ctns get 1 free

is there a simple formula/ algebraic equation to translate this into excel and calculate the true profit?

Which is the better deal? one deal you need to buy more bottles to get the better GP.... how do you work out based on eg 1,000 bottles?

recently i have convert my SPSS data analysis to excel using macro , i manage to solve most of the problem , except i hv one problem not able to solve , as following :-

i have excel table which can display following data :-

sales amount

profit amount

cost amount

profit margin in %

when i convert the above 4 variable into pivotable, i get result all correct , except , i encounter problem in display profit in % , it add up the % (i try to change the all other format ), and give the wrong result.

can some one tell me , how to handle % , so that i still can show profit in % at pivot table ?

Hope some one can help.

paul yeo

profit margin

What is the correct financial reporting protocol for reporting negative

gross profit margin (i.e., GP%) on a line item basis in spreadsheets?

In seeking a universal formula to address postive and negative gross profit

margin, I considered the following, which would apply to both positive and

negative revenue.

Formula

=ABS(Revenue - COGS)/Revenue

Example A

Revenue = $100.00

COGS = $90.00

Gross Profit = $10.00

Rev - COGS Absolute Value = $10.00

Gross Profit Margin = 10.0%

Example B

Revenue = ($100.00)

COGS = $90.00

Gross Profit = ($190.00)

Rev - COGS Absolute Value = $190.00

Gross Profit Margin = -190.0%

Example C

Revenue = ($1,000.00)

COGS = $90.00

Gross Profit = ($1,090.00)

Rev - COGS Absolute Value = $1,090.00

Gross Profit Margin = -109.0%

The lower gross profit margin percent on a greater disparity between revenue

(i.e., negative revenue and COGS) may be misleading. However, I confirmed

that my HP 12C calculator generates the same results.

If the calculated gross profit margin is misleading and not in the best

interest to show, what is would be the best acronym/nomenclature to

incorporate into a logic statement indicating that the calculated value is

"not meaningful?"

percentage.

ex -- cost divided by retail= markup, markup divided by 1. the markup

1.00/1.50 = .667

.667/1.667=40% gross margin

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'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B).

Example:

Basic value is $50, my price is $75 (50% margin)

Basic value is $100, my price is $130 (30% margin)

Basic value is $150, my price is $172,5 (15% margin)

And so on...

I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:

Unfortunately, this method has some serious cons, e.g.:

1. It doesn't look good.

2. If the basic price of 1 product is $49,9 and for the second one - $51,1 - in result you got absolutely different end values for them. I tried to put more "if" operators, but Excel didn't let me do it.

I assume that the problem is quite simple and I should find it on the web, but I didn't, probably because my poor technical English.

Please, help.

W.

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

No luck finding an answer? You could always try Google.