I’m trying to do a spreadsheet that shows prices based on margin and markup. It’s easy to show price as a function of markup, and easy to calculate margin based on retail and wholesale price, but for the life of me I can’t figure out how to do a table that shows price based on margin. For example, if wholesale price is $1 and margin is 10%, what is the retail price? Help.
X is retail price.
Y is wholesale price.
Z is margin expressed in percentage format wherein 10 percent is 10.
X = Y * (1+(Z*.01))
I’m not an accountant, but I think I can help.
First let’s make sure of our terms. Markup is the percentage of the lower price (usually cost or wholesale) which you add to that same lower price to get the higher price (usually selling price or retail).
Margin is the percentage of the higher price that you subtract from that same higher price to get the lower price.
So if something costs you $1.00, and you sell it for $1.50, that’s a 50% markup, and a %30 margin.
To calculate the higher price from the lower using the margin, you can simply divide by the lower price by (1- margin).
So a wholesale price of $1 divided by .9 = a retail price of $1.11. And if you subtract 10% of that (0.11) from itself, you wind up back at $1.
Err, I mean 33.3% margin. I was thinking one third and wrote .3. Sorry about that.
Unless I’m more braindead from the antihistamines than I think I am, this gives you a Z markup, not margin.
Again, take an item that wholesales at $1, and you want to establish a retail price that has a 30% margin over that. Using this formula, you get :
1 * (1+ (30 * .01)) = 1.3.
This is a 30% markup, but only about a 23% margin.
Whoops. My bad.
Thanks, I plugged that into Excel and my table is complete.