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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top