Penalty / Bonus calculation based on performance

Hi!

Need help in formula to calculate applicable Penalty / Bonus in below scenerio-

Basic Data:
Rate per unit -1000/-
Target production: X
Actual Production: Y

Conditions:
a. If Performance % Band is 95% to 110% - Penalty/Bonus will be Zero
b. If Performance % Band is 95% to 85% - Penalty will be applicable at 10% of basic rate for 95-85% performance only
c. If Performance % Band is 85% to 75% - Penalty will be applicable at 20% of basic rate for 85-75% + amount of b.
d. If Performance % Band is below 75% - Penalty will be applicable at 25% of basic rate for 75% or less + amount of b. + amount of c.
e. If Performance % Band is 110% to 120% - Bonus will be applicable at 10% of basic rate for performance of 110-120% portion
f. If Performance % Band is 120% to 130% - Bonus will be applicable at 10% of basic rate for performance of 110-120% portion + Bonus at 20% for 120-130% portion
g. If Performance % Band is more than 130% - Bonus will be applicable at 10% of basic rate for performance of 110-120% portion + Bonus at 20% for 120-130% portion and 25% for balance portion.


Request your support

Ohh! Sorry for the attachment. Please find attached. I tried V-Lookup but messed up.

I wanted something else. The formula should directly yield the applicable amount of Penalty/Bonus in a designated cell.

I tried to summarise my requirement in added rows in highlighted in Orange, Accent-2