Calculate KPI penalty

Hello ExcelForum,

I have the following KPI targets:

A) 99,99 % covering a service target.

B) If the KPI is breached <99,98 % there will be a penalty fee of 2% of the service cost (1200$).

C) For every additional 0,5% decrease in the achiedved KPI there will be an additional 1% penalty fee for the service cost (still 1200$)

How to calculate this in Excel easily and automatically if I have ex. a KPI score of 96,58%

Thanks a lot!!

Regards,
Anders

example.xlsx

I've tried in the attached - Let me know if that isn't correct

Cheers

Assuming once KPI breached 2% is standard penalty, and further 1% additional for every 0.5%
please check attachment if that solves your query




Vorabha

Hello friend,

Plz look the attached file.. let me know if this helps...?

Hi Pavan,

This is exactly the end result I need.

I have several different KPI's with different targets. Instead of making a list for each KPI, is there a formula that can do this for me?

Thanks in advance.

yes please check my attachment

i.e. in A2 as servicecost and B2 as KPI having C2 as actual achived
D2 would be

Please Login or Register  to view this content.

Vorabha

Thanks Vorabha - It is a step close, but not quite there. The result actually needs to be like this using 99,99% target with same methodology as mentioned above.

99,98% 2%
99,48% 3%
98,98% 4%
98,48% 5%
97,98% 6%
97,48% 7%
96,98% 8%
96,48% 9%
95,98% 10%

i thought you would like to have result in value of penalty fee instead of %

kindly use

Please Login or Register  to view this content.

vorabha