Price list markup %

[PEACHYDAR]

HI

I have a price list I use at work, and I always need to know the mark up for billing purposes. Is there a more accurate way of calculating this? I have to use a -1 to display the % correctly. I would have enclosed a snap shot of my screen I had at work today so show you the formula, but I can't find an icon to do that! Any advice is appreciated!

dar

 

[Peter_SSs]

Welcome to MrExcel!

More accurate than what? Exactly how are you doing it now? Formula?

My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[PEACHYDAR]

thanks for the reply! I could not figure out how to inset a .jpg so I put it in a small video on youtube.....

http://www.youtube.com/watch?v=8PqzKhcvd-o

anyway, I did a formula that divides the list price by our cost and then had to subtract 1 to get the correct % to be displayed.

I just wanted to know if I was doing this correctly, and if there was a better way to do it!

dar

 

[Peter_SSs]

That formula looks correct to me (though you don't need the '+' sign in the formula).

 

[gnrboyd]

Here is another option that gives you the same result. (top example).. see below for details on second example.

Book1
	B	C	D	E	F
5	MarkupPercentage		Retail	Cost	MUP
6	RD-20HighEfficiency		3,595.00	3,055.75	17.65%
7	TestItem		100.00	50.00	100.00%
8
9
10	GrossProfitMarginPercentage		Retail	Cost	GPM
11	RD-20HighEfficiency		3,595.00	3,055.75	15.00%
12	TestItem		100.00	50.00	50.00%
Sheet1

Your method gives you a markup percentage. I took the liberty of adding another percentage option for you to consider. The second option is the gross profit margin percentage. The basic difference is a markup is a percentage of cost and the gross profit margin is a percentage of the sale price. One advantage to using gross profit margin is when offering discounts. Using my example item that retails for 100.00 and costs 50.00 you will see that the markup is 100%. Your item cost 50.00 and you marked it up 100% which gives you 100.00 retail. That same item has a 50% gross profit margin meaning that 50% of the sale price is your gross profit margin. Now, it you were to close out the item and only wanted to get your cost out of it, what discount percentage would you give? 50% right? If you use the GPM, you will know exactly the percentage you can give.

You may know all of this and if so, I apologize. This just brought some memories back when I used to work in retail 20 years ago.

Hope this helps.

 

[CWatts]

If your concern was only appearances, you could use a custom function, but your way is the best way IMO:

=MyPCT(A1,B1)

Function MyPCT(Retail As Long, Wholesale As Long)
    MyPCT = Retail / Wholesale - 1
End Function

 

[PEACHYDAR]

thanks so much for all the replies! I will certainly review my price list and I just might add the GPM column! Thanks so much!

dar