Relatively simple question for one of you hotshots

  • Thread starter Thread starter Thomas Flynn
  • Start date Start date
T

Thomas Flynn

Hey there,

This should be somewhat easy for you guys that deal with stuff everyday.

If you can help me out with this problem I would be very greatful.

I want to come p with a worksheet for a rebate program that my company
offers to our customers.

The situation is as follows:


Each half year (april through September and october through march) my
company offers a rebate incentive based on previous years sales vs. current
year sales.

We offer tiered rebate levels - this is what is screwing me up.

I would like a formula that took TARGET and calculated the rebate based on
our tiered rebate payouts. The levels are as follows:


Meet target earn 2%
Exceed target by 10% earn 2.5%
Exceed target by 15% earn 3%
Exceed target by 20% earn 3.5%

Does anyone out there have a formula that can help?


Thanks much,

Tom
 
Relatively simple question for one of you hotshots
Assume cell F1 is named: Target

Set up a vlookup table in D1:E5
as follows:

=Target*0 0
=Target*1 0.02
=Target*1.1 0.025
=Target*1.15 0.03
=Target*1.2 0.035

Name D1:E5 as say: TieredR

(The above table assumes criteria "Exceeds" is amended to mean "Meets or
Exceeds ..")

Assuming sales figs are in col A,
and with a target value specified in F1

Put in B1: =VLOOKUP(A1,TieredR,2,TRUE)
copy down col B, format col B as percentage

Col B will return the tiered rebate percentages

PS: Pl post in plain text


Hey there,

This should be somewhat easy for you guys that deal with stuff everyday.

If you can help me out with this problem I would be very greatful.

I want to come p with a worksheet for a rebate program that my company
offers to our customers.

The situation is as follows:


Each half year (april through September and october through march) my
company offers a rebate incentive based on previous years sales vs. current
year sales.

We offer tiered rebate levels - this is what is screwing me up.

I would like a formula that took TARGET and calculated the rebate based on
our tiered rebate payouts. The levels are as follows:


Meet target earn 2%
Exceed target by 10% earn 2.5%
Exceed target by 15% earn 3%
Exceed target by 20% earn 3.5%

Does anyone out there have a formula that can help?


Thanks much,

Tom
 
Back
Top