Complex Excel Problem involving Step Costs

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?

 

[Morrigan]

What happens when a custom who has spent $18 then buys Product A for $
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)