Markup and Margin Calculation

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
I am trying to setup a spreadsheet that will help me see what my selling price should be based off of my cost, markup percent, and gross margin percent. Here is what I have setup on my spreadsheet now:

Cell Reference:
A1, is my wholesale cost
B1, is my markup percent
C1, selling price (this cell should auto populate)
D1, gross margin in dollars (this cell should auto populate)
E1, gross margin in percent

Basically, I want my Selling Price (in cell C1) and my Gross Margin in Dollars (in cell D1) to dynamically change.

What I want to do is to be able to enter in my cost and enter in assumptions for either my Markup Percent or my Gross Margin Percent. If I enter in an assumption for Gross Margin Percent then I would like to automatically see what my Markup Percent would be and visa versa. By entering in these two pieces of information (my cost and either my Markup Percent or my Gross Margin Percent) I would like to see what my selling price should be and what my gross margin would be in dollars.

I found exactly what I am trying to do on the following web site but it does not show me the formulas so that I can do this on my excel spreadsheet. The web site is: (note, you will need Java to view this)

http://www.dinkytown.net/java/ProfitMargin.html

If there is anyone out there that would be willing to help me, it would be so much appreciated and I thank you in advance!

Andy
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can't do that with formulas in Excel. A cell must either contain a constant or a formula. If you enter your wholesale price and markup %, then:

C1 =A1*(1+B1)
D1 =C1-A1
E1 =D1/C1

If you enter your wholesale price and gross margin %, then:

B1 =((A1*(1/(1-E1)))/A1)-1
C1 =A1*(1+B1)
D1 =C1-A1
 

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
Thank You!

Andrew, thank you very much for your help on this!! What you provided me was exactly what I needed and my spreadsheet works now thanks to you!!

Andy
 

jwhite70

New Member
Joined
Aug 2, 2011
Messages
3
I keep getting an error message for the B1 formula when I enter it into the cell??


You can't do that with formulas in Excel. A cell must either contain a constant or a formula. If you enter your wholesale price and markup %, then:

C1 =A1*(1+B1)
D1 =C1-A1
E1 =D1/C1

If you enter your wholesale price and gross margin %, then:

B1 =((A1*(1/(1-E1)))/A1)-1
C1 =A1*(1+B1)
D1 =C1-A1
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,150
Members
410,543
Latest member
ExcelGlenn
Top