Bulk Discount Formula
 

I am looking for a business formula: Basically, I am trying to figure out the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

Bulk Discount Formula
 

If you want to make the relationship between selling price and quantity a
linear relationship, then you could put your quantities of 10 and 100 units
in A2 and A3, and the corresponding unit prices of $10 and $5 in B2 and B3.
Then if your quantity of 59 is in A4, the price per unit would be
=B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2)
and the total for A4 units at that price would be
=A4*(B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2))

Another way of getting the same result is =FORECAST(A4,B$2:B$3,A$2:A$3) for
the unit price or =A4*FORECAST(A4,B$2:B$3,A$2:A$3) for the total.

One thing you will notice is that with that method of calculating your
discount the number for 90 units would be $500, which is the same as for 100
units, and numbers between 90 and 100 would cost slightly more than for 100.
Another danger is that if you tried to continue to use the same formula
beyond 100 units, you'd get to a zero selling price at 190 units, and you'd
be paying the customer to take the product away for 190 units.
--
David Biddulph

"dgold82" wrote in message
...
I am looking for a business formula: Basically, I am trying to figure out
the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would
sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding
scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

Bulk Discount Formula
 

Use a VLOOKUP() against a table you create elsewhere on your sheet. List the
values in ascending order for each price.

1......$10
30.....$8
60.....$7
80.....$6
100...$5

Press F1 and readup on VLOOKUP(), you'll see how it solves your problem
readily. If you entered QTY in A1 and had your table in columns M and N, the
formula would look like:

=VLOOKUP(A1, $M$1:$N$20, 2, 0)

Does that help?
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"dgold82" wrote:

I am looking for a business formula: Basically, I am trying to figure out the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

Bulk Discount Formula
 

How about this
A B
1 10 10
2 100 5
3 59 7.277777778

Formula in B3 will be =PERCENTILE(B1:B2,1-(A3-A1)/(A2-A1))
i tried 10 abd 100 in A3 and it does give the correct answer.

"dgold82" wrote:

I am looking for a business formula: Basically, I am trying to figure out the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

Bulk Discount Formula
 

On Monday, November 23, 2009 at 7:24:39 AM UTC+3, David Biddulph wrote:
If you want to make the relationship between selling price and quantity a
linear relationship, then you could put your quantities of 10 and 100 units
in A2 and A3, and the corresponding unit prices of $10 and $5 in B2 and B3.
Then if your quantity of 59 is in A4, the price per unit would be
=B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2)
and the total for A4 units at that price would be
=A4*(B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2))

Another way of getting the same result is =FORECAST(A4,B$2:B$3,A$2:A$3) for
the unit price or =A4*FORECAST(A4,B$2:B$3,A$2:A$3) for the total.

One thing you will notice is that with that method of calculating your
discount the number for 90 units would be $500, which is the same as for 100
units, and numbers between 90 and 100 would cost slightly more than for 100.
Another danger is that if you tried to continue to use the same formula
beyond 100 units, you'd get to a zero selling price at 190 units, and you'd
be paying the customer to take the product away for 190 units.
--
David Biddulph

"dgold82" wrote in message
...
I am looking for a business formula: Basically, I am trying to figure out
the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would
sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding
scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

How i wish you also posted the actual formula without excel!