How to calculate margin on products ?

[Dull71]

Lets say i want a high margin on low prices and a lower margin on high prices ?

Example.
up to 10 i want 3.2
up to 30 i want 3.0
up to 60 i want 2.8
up to 100 i want 2.6
up to 150 i want 2.5
up to 200 i want 2.4
up to 300 i want 2.0
up to 500 i want 1.8
above 500 i want 1.5

How should this formula be made, and how can i avoid that a price of 59 will cost more then a price 61 when i call for the margin ?

Im totally new to excel, but was told this can be done.

 

[Matty]

Welcome to the Board!

If I follow correctly, you want a set up like this:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 61px;"><col style="width: 62px;"><col style="width: 19px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 93px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Price</td><td>Margin %</td><td>
</td><td>Product</td><td>Price</td><td>Price + Margin</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">£0.01</td><td style="text-align: right;">3.2</td><td>
</td><td>Pan Set</td><td style="text-align: right;">£59.00</td><td style="text-align: right;">£60.65</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">£10.01</td><td style="text-align: right;">3</td><td>
</td><td>Toaster</td><td style="text-align: right;">£61.00</td><td style="text-align: right;">£62.59</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">£30.01</td><td style="text-align: right;">2.8</td><td>
</td><td>TV</td><td style="text-align: right;">£500.00</td><td style="text-align: right;">£509.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">£60.01</td><td style="text-align: right;">2.6</td><td>
</td><td>Laptop</td><td style="text-align: right;">£501.00</td><td style="text-align: right;">£508.52</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">£100.01</td><td style="text-align: right;">2.5</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">£150.01</td><td style="text-align: right;">2.4</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: right;">£200.01</td><td style="text-align: right;">2</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: right;">£300.01</td><td style="text-align: right;">1.8</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: right;">£500.01</td><td style="text-align: right;">1.5</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
The formula in F2 is:

=E2*INDEX(B$2:B$10,MATCH(E2,A$2:A$10,1))/100+E2

Note that you will always have an issue with this where the higher price is just over a margin threshold, e.g. the Laptop being cheaper than the TV, even though the price without the margin added was cheaper.

Does this help?

Matty

 

[Dull71]

Thanks Matty,

Indeed this is close to what im after.
Is there no way to make the margin "floating" between the thresholds, so the prices go between 3.2 on a low price and closer to 3 at the new breakpoint, just to avoid the pricing problem at breakpoint.

I´m greatful for the help I can get.

 

[shg]

You could use an exponential pricing model.

       --A-- --B-- -----C----- -------------------D-------------------
   1         First         3.3 C1: Input                              
   2          Last         1.7 C2: Input                              
   3             K 0.005151455 C3: Input                              
   4                                                                  
   5   Input Yours   Formula                                          
   6      10  3.2        3.23  C6: =C$2 + (C$1-C$2) * (1-C$3) ^ (A6-1)
   7      30  3.0        3.08                                         
   8      60  2.8        2.88                                         
   9     100  2.6        2.66                                         
  10     150  2.5        2.44                                         
  11     200  2.4        2.27                                         
  12     300  2.0        2.04                                         
  13     500  1.8        1.82

First is the price (or markup) you'd charge for a single unit.

Last is the price (or markup) if you sold a zillion.

k is a parameter that controls how fast the price decreases.