Calculate deadlines and costs

Hi,

Apologies for the long post. I hope someone can help with this as this is way beyond my skill level.

I am trying to create Excel formulas to track a Key Performance Indicator (KPI) for a Service Provider. This KPI measures the performance in fixing incidents of different severity levels within a specified maximum time. The severity levels range from 1 to 5, each with its own maximum time to fix:

Severity 1: 4 hours
Severity 2: 24 hours
Severity 3: 5 business days
Severity 4: 20 business days
Severity 5: Time is agreed with Customer (If it is not possible to include this, it can be left out and only severity 1 to 4 will be fine)

An unresolved Incident which is a Fix Time Failure shall be escalated to the next Incident Severity Level up at the end of the Maximum Time To Fix for that Incident Severity Level. The revised Maximum Time To Fix shall become that for the new Incident Severity Level. Further escalation shall occur based on the new Maximum Time To Fix.

Severity Level 1 Incidents that remain unresolved within the Maximum Time to Fix window will be a Fix Time Failure and a new Maximum Time to Fix window will commence after 4 hours, continuing on the same basis until the Service Incident is Fixed.

Each failure to Fix an Incident at the Maximum Time To Fix then applicable to the Incident Severity Level for the Incident shall be counted as a separate Fix Time Failure.

The KPI is reported at the end of every month which is the Service Period.

Direct Service Credits:
£2,500 for the first Fix Time Failure for an individual Incident Severity Level 1 Incident and then £500 for each subsequent Fix Time Failure for the same Incident Severity Level 1 Incident.
£1,500 for each Fix Time Failure for Incident Severity Level 2 Incidents.
£1,000 for each Fix Time Failure for Incident Severity Level 3 Incidents.
£500 for each Fix Time Failure for Incident Severity Level 4 Incidents.
£250 for each Fix Time Failure for Incident Severity Level 5 Incidents.

You are free to modify fields, add columns and/or add tables if you think it will present the data better (maybe have a column for each Severity levels?). The Excel sheet attached contains a new and current tab. New tab is to be worked on. Current shows what I am currently using to manually calculate the KPI.

Here are two examples:

Example 1: If there are 4 Fixed Time Failures in a Service Period and they consist of one Incident Severity Level 1, two Incident Severity Level 2 and one Incident Severity Level 3, the total Direct Service Credits shall be £6,500 (i.e. 1 x £2,500 + 2 x £1,500 + 1 x £1,000).

Example 2: If an Incident Severity Level 1 Incident is fixed after 9 hours, the total Direct Service Credit for that Incident will be £3,000 (i.e. 1 x £2,500 + 1 x £500).

If what I'm asking for is not possible, a severity/direct service credits calculator will be much appreciated. I can then use this to manually calculate if the tickets were on time and how much per ticket. Thank you in advance!

Welcome to the forum.

I don't know about KPI calculation, but I have put together a table to determine severity with a LOOKUP formula in column H and to calculate service credits.
I hope this will get you started.

Good luck!