Complex Excel Problem involving Step Costs
 

Let's assume we sell 4 products at the following costs: A - $1, B - $3, C -
$6, D - $10

If a customer spends over a certain gross level, the customer is entitled to
differing discounts. The amount of discount varies depending on the item.

For example, if the customer's total before discounts is between $1 - $20,
he/she is entitled to Discount Level 1: 25% off product A, 10% off product B,
0% off product C and 33% off product D.

Then, if a customer spends between $21-$50, he/she is entitled ON THE AMOUNT
BETWEEN THIS LEVEL to Discount Level 2: %35 off product A, %20 off product B,
%50 off product C and %5 off product D.

I am okay to this point. I currently keep a list of customer purchases and
have one column that offers a running total and one column that identifies
whether this running total is sufficient to apply Discount Level 1 or
Discount Level 2. As well, I use the Count and Sum functions with array
formulas to calculate product totals by discount level. This allows me to
apply the correct discounts at the correct levels.

The big dilemma I run into is when a customer purchas crosses a threshhold
level. For example, if a customer has spent $18 and then purchases Product D
for $10.

How can I set up a formula to recognize that a portion of the purchase of
Product D (i.e. $2) should be applied to one discount level, and the
remainder (i.e. $8) should be applied to the second discount level.

I know this was a long explanation, but I figured it best to be clear and
thorough.

Thanks.

Complex Excel Problem involving Step Costs
 

Check http://www.mcgimpsey.com/excel/variablerate.html

"jcc" wrote:

Let's assume we sell 4 products at the following costs: A - $1, B - $3, C -
$6, D - $10

If a customer spends over a certain gross level, the customer is entitled to
differing discounts. The amount of discount varies depending on the item.

For example, if the customer's total before discounts is between $1 - $20,
he/she is entitled to Discount Level 1: 25% off product A, 10% off product B,
0% off product C and 33% off product D.

Then, if a customer spends between $21-$50, he/she is entitled ON THE AMOUNT
BETWEEN THIS LEVEL to Discount Level 2: %35 off product A, %20 off product B,
%50 off product C and %5 off product D.

I am okay to this point. I currently keep a list of customer purchases and
have one column that offers a running total and one column that identifies
whether this running total is sufficient to apply Discount Level 1 or
Discount Level 2. As well, I use the Count and Sum functions with array
formulas to calculate product totals by discount level. This allows me to
apply the correct discounts at the correct levels.

The big dilemma I run into is when a customer purchas crosses a threshhold
level. For example, if a customer has spent $18 and then purchases Product D
for $10.

How can I set up a formula to recognize that a portion of the purchase of
Product D (i.e. $2) should be applied to one discount level, and the
remainder (i.e. $8) should be applied to the second discount level.

I know this was a long explanation, but I figured it best to be clear and
thorough.

Thanks.

Complex Excel Problem involving Step Costs
 

The link is on the right track, but only applies to a simplified case. In
the link, the marginal rates are fixed. This would be the case if I only had
one product. However, because I have multiple products, the rate to be
applied at the threshhold level will not be consistent from customer to
customer.

I need Excel to essentially recognize when a threshhold is crossed, identify
what product caused it to be crossed, and perform a calculation for that
scenario.

Any other suggestions?

"bpeltzer" wrote:

Check http://www.mcgimpsey.com/excel/variablerate.html

"jcc" wrote:

Let's assume we sell 4 products at the following costs: A - $1, B - $3, C -
$6, D - $10

If a customer spends over a certain gross level, the customer is entitled to
differing discounts. The amount of discount varies depending on the item.

For example, if the customer's total before discounts is between $1 - $20,
he/she is entitled to Discount Level 1: 25% off product A, 10% off product B,
0% off product C and 33% off product D.

Then, if a customer spends between $21-$50, he/she is entitled ON THE AMOUNT
BETWEEN THIS LEVEL to Discount Level 2: %35 off product A, %20 off product B,
%50 off product C and %5 off product D.

I am okay to this point. I currently keep a list of customer purchases and
have one column that offers a running total and one column that identifies
whether this running total is sufficient to apply Discount Level 1 or
Discount Level 2. As well, I use the Count and Sum functions with array
formulas to calculate product totals by discount level. This allows me to
apply the correct discounts at the correct levels.

The big dilemma I run into is when a customer purchas crosses a threshhold
level. For example, if a customer has spent $18 and then purchases Product D
for $10.

How can I set up a formula to recognize that a portion of the purchase of
Product D (i.e. $2) should be applied to one discount level, and the
remainder (i.e. $8) should be applied to the second discount level.

I know this was a long explanation, but I figured it best to be clear and
thorough.

Thanks.

Complex Excel Problem involving Step Costs
 

What happens when a custom who has spent $18 then buys Product A for $1
and Product B for $9?

1. Product A ($1@Lv1 discount), Product B ($1@Lv1 & $8@Lv2)
2. Product A ($1@Lv2), Product B ($2@Lv1 & $7@Lv2)
3. Product A ($0.1@Lv1 & $0.9@Lv2), Product B ($0.81@Lv1, $8.19@Lv2)


jcc Wrote:
The link is on the right track, but only applies to a simplified case.
In
the link, the marginal rates are fixed. This would be the case if I
only had
one product. However, because I have multiple products, the rate to
be
applied at the threshhold level will not be consistent from customer
to
customer.

I need Excel to essentially recognize when a threshhold is crossed,
identify
what product caused it to be crossed, and perform a calculation for
that
scenario.

Any other suggestions?

"bpeltzer" wrote:

Check http://www.mcgimpsey.com/excel/variablerate.html

"jcc" wrote:

Let's assume we sell 4 products at the following costs: A - $1, B -
$3, C -
$6, D - $10

If a customer spends over a certain gross level, the customer is
entitled to
differing discounts. The amount of discount varies depending on
the item.

For example, if the customer's total before discounts is between $1
- $20,
he/she is entitled to Discount Level 1: 25% off product A, 10% off
product B,
0% off product C and 33% off product D.

Then, if a customer spends between $21-$50, he/she is entitled ON
THE AMOUNT
BETWEEN THIS LEVEL to Discount Level 2: %35 off product A, %20 off
product B,
%50 off product C and %5 off product D.

I am okay to this point. I currently keep a list of customer
purchases and
have one column that offers a running total and one column that
identifies
whether this running total is sufficient to apply Discount Level 1
or
Discount Level 2. As well, I use the Count and Sum functions with
array
formulas to calculate product totals by discount level. This
allows me to
apply the correct discounts at the correct levels.

The big dilemma I run into is when a customer purchas crosses a
threshhold
level. For example, if a customer has spent $18 and then purchases
Product D
for $10.

How can I set up a formula to recognize that a portion of the
purchase of
Product D (i.e. $2) should be applied to one discount level, and
the
remainder (i.e. $8) should be applied to the second discount
level.

I know this was a long explanation, but I figured it best to be
clear and
thorough.

Thanks.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=522746