Formula to calculate percentage based on achievement gates

spr

New Member
Joined
Jul 16, 2010
Messages
5
Hi team. I consider myself a fairly competent user but am baffled by this problem.

I work in an industry where we are compensated by suppliers on volume business (i.e. a rebate) based on achievement.
There are different gates that need to be achieved to work out how much we are payed in rebate for a particular period.

i.e. we have a target of 100.
if we achieve 80% we are payed a 1% rebate
if we achieve 90% we are payed a 2% rebate
if we achieve 100% we are payed a 3% rebate
and so on - to a cap of 120% where we are payed 5% rebate.

I am trying to calculate the payout figure based on the banding and just stuck with this one! any help appreciated.

:eek:
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi & Welcome,


Something like this as a base model?

You can use he solution in F2 to multiply it against the rebate about...


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">MIN</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">MAX</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">REBATE</TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">RESULT</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">REBATE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">0%</TD><TD style="TEXT-ALIGN: center">80%</TD><TD style="TEXT-ALIGN: center">1%</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">98%</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">3%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">80%</TD><TD style="TEXT-ALIGN: center">90%</TD><TD style="TEXT-ALIGN: center">2%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">90%</TD><TD style="TEXT-ALIGN: center">100%</TD><TD style="TEXT-ALIGN: center">3%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">100%</TD><TD style="TEXT-ALIGN: center">120%</TD><TD style="TEXT-ALIGN: center">4%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">120%</TD><TD> </TD><TD style="TEXT-ALIGN: center">5%</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=INDEX(C2:C6,MATCH(E2,A2:A6,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Thanks for that - however the complexity grows.

the payment is tiered - i.e. we are paid on the 0-80% + the 80-90 + 90-100 + 100-120 as individual payments.

so in a simple model.

90-100% = 5% rebate
100%+ = 10% rebate

Target of 100. Achieve 120

so 100/120 is paid @ 5% = $5
20 is paid @ 10% = $2

there are 5 steps in the model I currently have. :confused:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top