Structuring Quantity Discounts with Multiple Products

[pbatarseh]

Dear Dr. Excel IS fun!

So, I’ve hit a road block in selling my wife’s CDs!

To resolve my conflict a friend suggested using “Goal Seek” or “Scenario Manager,” I am still lost.

Can you help me figure out how to set up Quantity Pricing “Bands” ???

THE PRODUCTS
We have THREE CDs that we sell individually, OR in a Set of three.

THE GOAL
We want to offer quantity discounts based on the Total Quantity of CDs each order.

THE DILEMNA
If I follow the current pricing bands I run into a problem in the higher pricing bands/brackets:

A customer could actually have a total purchase price for 15 sets that is CHEAPER than 13 sets!
Or for individual CDs ordered the problem is similar E.G. 30 CDs ($11/each) for 330.00, still LESS than, 28 CDs ($12/each) for 336.00.

I have a detailed Excel spreadsheet that I’ve started, but I’m a little more than a novice.

Here’s some more detail.

1. Our current pricing structure is broad:
a. 1-9 units per order = 15.00/each
b. 10-29 = 12.00/each
c. 30+ = 9.00/each
i. The challenge with this is that you can get
1. 30 CDs ($9/each) for 270.00, paying LESS then,
2. 23 CDs ($12/each) for 276.00

2. In the CONSIDERATION Column I tried to resolve this:
a. I injected two more pricing bands which
i. Slows down the discount rate
ii. In a way, almost resolves the problem just mentioned above
b. If we incorporate this pricing model it makes a bit more sense all along, but
i. Still it has an overlap, but not so drastic, where you can get
1. 30 CDs ($11/each) for 330.00, still LESS than,
2. 28 CDs ($12/each) for 336.00.
c. Is that as close as we’ll be able to get it? Or am I missing an obvious solution?

3. Along the same lines, I have a challenge with the Complete 3 CD Set (Vol 1, 2, 3) pricing options:
a. Of course if we keep a 3 CD Set at $40 all the way down, it would be cheaper to buy them all individually.
b. If I followed the current individual pricing model of $15, $12, and $9 and bundle them in sets of three, it would cost $45/per set, then down to $36/per set, and finally $27/per set
i. 13 Sets (39 total CDs) would cost 351.00 at $27/each set
c. If I incorporate a slightly different model as shown in the far right column called CONSIDERATION for the 3 CD Set
i. The tiered rates would be $40, 36, 32, and finally 27.
ii. But this gives me a problem where buying 15 sets at this rate is cheaper than buying 13 sets, which falls in the price band above.

 

[Giordano Bruno]

Why not apply the discount progressively. the first 1-9 are $15 then next 10 are sold at $12 each and so on. Then it would never be cheaper to buy more.

 

[Michael M]

Hi and Welcome to the Board.
I'm at work at the moment so, I'm just passing through the Forum.
I don't think there is anything wrong with your math, based on the unit prices, if you buy more you save money....however, maybe you need to change the price structure itself.
Iwould have thought $15, $13.5, and $12 respectively would have been closer to what you want.

 

[Giordano Bruno]

Hi Michael,

Second one should perhaps be $13.75 as 11 * 13.5 = $148.50, so less than 10 @ $15.

If you want to go down this path, the rule is to find the cost of the maximum of the previous quantity and divide it by the previous quantity + 1. That gives you the minimum cost for the next group.

 

[shg]

Further to Giordano's suggestion, here's a function I use:

       --B-- ----C----- ----D---- ---E----
   2   First   $ 15.00                    
   3   Last     $ 7.50                    
   4     k        0.17                    
   5    Qty  Unit Price   Total     Avg   
   6      1    $ 15.00   $ 15.00  $ 15.00 
   7      2    $ 13.73   $ 28.73  $ 14.37 
   8      3    $ 12.67   $ 41.40  $ 13.80 
   9      4    $ 11.79   $ 53.19  $ 13.30 
  10      5    $ 11.06   $ 64.25  $ 12.85 
  11      6    $ 10.45   $ 74.70  $ 12.45 
  12      7     $ 9.95   $ 84.65  $ 12.09 
  13      8     $ 9.54   $ 94.19  $ 11.77 
  14      9     $ 9.19  $ 103.38  $ 11.49 
  15     10     $ 8.90  $ 112.28  $ 11.23 
  16     11     $ 8.66  $ 120.94  $ 10.99 
  17     12     $ 8.47  $ 129.41  $ 10.78 
  18     13     $ 8.30  $ 137.71  $ 10.59 
  19     14     $ 8.17  $ 145.88  $ 10.42 
  20     15     $ 8.05  $ 153.93  $ 10.26 
  21     16     $ 7.96  $ 161.89  $ 10.12 
  22     17     $ 7.88  $ 169.77   $ 9.99 
  23     18     $ 7.82  $ 177.59   $ 9.87 
  24     19     $ 7.76  $ 185.35   $ 9.76 
  25     20     $ 7.72  $ 193.07   $ 9.65 
  26     21     $ 7.68  $ 200.75   $ 9.56 
  27     22     $ 7.65  $ 208.40   $ 9.47 
  28     23     $ 7.62  $ 216.02   $ 9.39 
  29     24     $ 7.60  $ 223.62   $ 9.32 
  30     25     $ 7.59  $ 231.21   $ 9.25 
  31     26     $ 7.57  $ 238.78   $ 9.18 
  32     27     $ 7.56  $ 246.34   $ 9.12 
  33     28     $ 7.55  $ 253.89   $ 9.07 
  34     29     $ 7.54  $ 261.43   $ 9.01 
  35     30     $ 7.53  $ 268.96   $ 8.97

First is the single-unit price.

Last is the price you'd charge for the zillionth item.

k [0..1) controls how fast the price goes down; larger is faster.

In C6 and down

=ROUND(Last + (First - Last) * (1 - k) ^ (B6 - 1), 2)

Larger values of k cause the price to decrease faster.

 

[pbatarseh]

You folks are brilliant! Thanks for the time invested and the effort.

As a marketer, I had honestly paid more attention to a pricing grid that might be easier to explain. Also, I'm not using a sophisticated shopping cart where I can apply a discount rule. I'm limited right now to creating my own pay pal buttons, that link to a certain product number and hard coded product price.

Looking just at the individual price structure now; setting the "per set" price aside for now:

We have set up pricing bands as in this following examples per cd sold.
We honestly never gave it a thought to scale down price at each unit; but rather 1-9, 10-19, etc.

As you'll see below, it's cheaper to buy 30 than 28 units.

Is there a similar rule you can apply to groupings such as these which will resolve the problem? We do have many customers who buy in larger bulk orders on a regular basis; so we want to do right by them.

Wow! Thanks again for the communal wisdom!
Peter


1-9 Units = $15

1 $15.00 $15.00
2 $15.00 $30.00
3 $15.00 $45.00
4 $15.00 $60.00
5 $15.00 $75.00
6 $15.00 $90.00
7 $15.00 $105.00
8 $15.00 $120.00
9 $15.00 $135.00


10-19 Units = $13

10 $13.00 $130.00
11 $13.00 $143.00
12 $13.00 $156.00
13 $13.00 $169.00
14 $13.00 $182.00
15 $13.00 $195.00
16 $13.00 $208.00
17 $13.00 $221.00
18 $13.00 $234.00
19 $13.00 $247.00


20-29 Units = $12

20 $12.00 $240.00
21 $12.00 $252.00
22 $12.00 $264.00
23 $12.00 $276.00
24 $12.00 $288.00
25 $12.00 $300.00
26 $12.00 $312.00
27 $12.00 $324.00
28 $12.00 $336.00
29 $12.00 $348.00


30-39 Units = $11

30 $11.00 $330.00
31 $11.00 $341.00
32 $11.00 $352.00
33 $11.00 $363.00
34 $11.00 $374.00
35 $11.00 $385.00
36 $11.00 $396.00
37 $11.00 $407.00
38 $11.00 $418.00
39 $11.00 $429.00


40+ Units = $9

40 $9.00 $360.00
41 $9.00 $369.00
42 $9.00 $378.00
43 $9.00 $387.00
44 $9.00 $396.00
45 $9.00 $405.00
46 $9.00 $414.00
47 $9.00 $423.00
48 $9.00 $432.00
49 $9.00 $441.00
50 $9.00 $450.00
51 $9.00 $459.00
52 $9.00 $468.00
53 $9.00 $477.00
54 $9.00 $486.00
55 $9.00 $495.00
56 $9.00 $504.00
57 $9.00 $513.00
58 $9.00 $522.00
59 $9.00 $531.00
60 $9.00 $540.00

 

[shg]

Make smaller breakpoints:

       -E- ---F---- ------------G-------------
   2   Qty  Price                             
   3     1  $15.00  F3: Input                 
   4     6  $12.50  F4 and down: =(E4-1)/E4*F3
   5    11  $11.36                            
   6    16  $10.65                            
   7    21  $10.15                            
   8    26   $9.76                            
   9    31   $9.44                            
  10    36   $9.18                            
  11    41   $8.96