Rebate incentive - Multiple tier payback - Confusing if Statement

G

Guest

I need to design a model to calculate rebate refunds and in doing so I have
run into some problems. Basically here is what I am trying to do...

For 100mg - $5
For 100mg + 200mg - $15
For 100mg + 200mg + 300mg - $40

The incentive only applies when you order all products together. Cannot
order 100mg + 300mg and get money back. Or 200mg+300mg. Must be 100mg,
100mg+200mg, or 100mg+200mg+300mg for incentive to apply.

First example is easy:
A B C D E
1] Item | Item Qty | Eligible Units | Rebate $ |Total Return
2] 100mg | 218 | 4 | $ 5 | $ 20
3] 200mg | 214 | 1 | $ 15 | $ 15
4] 300mg | 213 | 213 | $40 | $ 8520

In cell "C2" =IF(B2-B3<0,0,B2-B3)
In cell "C3" =IF(B3-B4<0,0,B3-B4)
In cell "C4" =B4

So here you get $40 * 213 units, $15* 1 unit, $5 * 4 units. Easy to do with
some very basic if statements. Everything works out fine in this example.

The problem I am having is when I get to a problem like below
Second example:
A B C D
E
Item | Item Qty | Eligible Units | Rebate $ |Total Return
5] 100mg | 100 | ? | $ 5 | ?
6] 200mg | 105 | ? | $ 15 | ?
7] 300mg | 94 | 94 | $40 | $3760

Here the values should be $40 *94 units = $3760, $15*6 units = $90, $5*0
units = $0. These are the values I need to find, but am having trouble with
if statements. I remember in one of classes back in college we had a problem
like this, just can't remember how it was done.

I need a model that will be able to generate the calculations based on the
incentive criteria. In the second example the initial if statements will not
apply because you only get the rebate refund if you have one of each dosage
ordered. Can anyone figure out an if statement that will be able to figure
out my calculated values like in example 2? Remember you can only receive $40
if 100mg+200mg+300mg are ordered, $15 if 100mg + 200mg ordered, and $5 if
just 100mg ordered. Please someone help me out here. I am going crazy.
 
M

Mikeopolo

In the second example you have shown, I would have thought that the $5
would apply to 6 units (100-94), and that the $15 would apply to 5
units (105-100).

Am I missing something - further examples might help!

Regards
Mike
 
M

Morrigan

Is this what you want? See attachment.

I need to design a model to calculate rebate refunds and in doing so I
have
run into some problems. Basically here is what I am trying to do...

For 100mg - $5
For 100mg + 200mg - $15
For 100mg + 200mg + 300mg - $40

The incentive only applies when you order all products together.
Cannot
order 100mg + 300mg and get money back. Or 200mg+300mg. Must be 100mg,
100mg+200mg, or 100mg+200mg+300mg for incentive to apply.

First example is easy:
A B C D E
1] Item | Item Qty | Eligible Units | Rebate $ |Total Return
2] 100mg | 218 | 4 | $ 5 | $
20
3] 200mg | 214 | 1 | $ 15 | $
15
4] 300mg | 213 | 213 | $40 | $ 8520

In cell "C2" =IF(B2-B3<0,0,B2-B3)
In cell "C3" =IF(B3-B4<0,0,B3-B4)
In cell "C4" =B4

So here you get $40 * 213 units, $15* 1 unit, $5 * 4 units. Easy to do
with
some very basic if statements. Everything works out fine in this
example.

The problem I am having is when I get to a problem like below
Second example:
A B C D
E
Item | Item Qty | Eligible Units | Rebate $ |Total Return
5] 100mg | 100 | ? | $ 5 |
?
6] 200mg | 105 | ? | $ 15 |
?
7] 300mg | 94 | 94 | $40 |
$3760

Here the values should be $40 *94 units = $3760, $15*6 units = $90,
$5*0
units = $0. These are the values I need to find, but am having trouble
with
if statements. I remember in one of classes back in college we had a
problem
like this, just can't remember how it was done.

I need a model that will be able to generate the calculations based on
the
incentive criteria. In the second example the initial if statements
will not
apply because you only get the rebate refund if you have one of each
dosage
ordered. Can anyone figure out an if statement that will be able to
figure
out my calculated values like in example 2? Remember you can only
receive $40
if 100mg+200mg+300mg are ordered, $15 if 100mg + 200mg ordered, and $5
if
just 100mg ordered. Please someone help me out here. I am going crazy.
Click to expand...


+-------------------------------------------------------------------+
|Filename: incentive.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4536 |
+-------------------------------------------------------------------+
 
Top