Free Microsoft Excel 2013 Quick Reference

Profit Margin Trend

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


Post your answer or comment

comments powered by Disqus
Hi all
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

I'm trying to figure out the formula for a gross profit margin percentage as
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!

I need a formula that will give me the selling price. I have the cost of the
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?

hi all,

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

hi there,

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

ok everyone, after searching the boards i found a thread on here that answered my question...however it was created in 2009 lol so i thought that not to good to ask a question in there. so heres my dea, I need to have a backwards profite margin calc...
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!!!

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'm trying to figure out the formula for a gross profit margin percentage as
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!

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.

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.

Hi, currently at my work place I was tasked with learning excel and to make template that could calculate our profit margins by entering in product codes, our cost, and their price. Now the problem is that I have little experience with excel, I can do a basic worksheet but I don't know how to use things like Vlookup for excel which is a function i expect i will need to use. I was wondering if anyone could help me get off on the right foot with making this template. What are some functions I need to learn how to use? How would i get this all in a template? Are there good programs that can do this already? Thanks in advanced!

-PrivateLucky

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

Hi everyone, Newbie here.....

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

Hi all,

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.

Dear MVPs

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 need a formula to calculate profit magin, can anyone help?

Folks

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

I would like to input a cost price, say in cell A1
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

Can someone please help me!!!
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?

hi all ,

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

Subject Detailed: Universal formula to calculate negative and positve gross
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?"

new to excel-- i would like to set up a formula to calculate the margin
percentage.

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

1.00/1.50 = .667

.667/1.667=40% gross margin

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

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


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