Formula/ solution to calcualte volume and sales price.

[Wollama]

Hello all,

This is my first on this forum although I come here quite often to seek solutions. Now I think I've stumbled upon something that has not been yet touched or maybe has been but I was unable to find it. I have a following problem. I need to got though a list of customers and make a decision if the current price that they are getting makes sense for the company. and I can't figure out the way to do that in a productive way. Let me present the issue with an example - let's say we have three customers which bought a specific quantity of the product for a specific price (table below). Now we can see that Customer B although has bought only 5 pieces of a product has a very high unit price so wouldn't have an issue. On the other hand customer C has bought also not that much but really for a very low price so that raises a question if the price should be so low with that volume. Obviously the first solution for me would be to use an XY chart to present that graphically but the issue would be what if I have more customers then say 20 etc. and what if I have more than one product? So this I guess is a question on the fields not only of excel but also mathematics. And I can't wrap my head around it. Anyone could help with this?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Qty[/TD]
[TD]Price (EUR)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100[/TD]
[TD]1,2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[TD]1,4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]20[/TD]
[TD]0,8[/TD]
[/TR]
</tbody>[/TABLE]

Regards

 

[Rijnsent]

Hi Wollama,

what about the following:
-you'd want a price mechanism that's basically giving customers with a high volume a bigger discount/lower price and a low volume a high price.
-that implies e.g. that you'd want e.g. a 50% minimum profit margin for customers with 1-9 pieces, a 30% profit margin for 11-99 and 20% for 100+ pieces
-So add one column with the profit margin percentage: (price - cost) / cost
-and than have a small lookup table with minimum margin percentages per bracket of sales numbers (1-9, 10-99, etc) and compare your number.

Another option would be to calculate the "profit per customer" and put them in brackets for "gold", "silver" and "bronze".

Hope that gives you some ideas of the directions you could go.

Cheers,

Koen