Nested tables

Calculation Method

Description

Formula

Less is Better

Where it is desirable to get the lowest possible value for the KPI Actual

Where a result lower in value to the target will result in a green light  

Example: Interest ratio (interest on borrowings/total expenditure)
Target 10% | Actual 8%

If (Target = 0)

          If (Actual <= 0) then 100

         Else 0

If ( Target > 0

          1 – (Actual/Target) + 1

Else

          Actual/Target

More is Better

Where it is desirable to get the highest value possible for the KPI Actual

Where a result higher in value to the target will result in a green light

Example: Customer Satisfaction
Target 85% | Actual 95

If (Target = 0)

          If (Actual >= 0) then 100

         Else 0

Else

         Actual/Target

Goal Post

Where it is desirable to get the KPI Actual around the Target value within the Variance

Where achievement of the target results in a green light

Example: Achievement of Budget
Target $3,000,000 ± 5% | Actual $2,950,000

If Actual < LB then: (Actual/LB)

If Actual > UP then: LB-(Actual – UB)/LB

UB (Upper Boundary) = Target +(Target*Variance/100)

LB (Lower Boundary) = Target - (Target*Variance/100)

Calculation Method

Green

Amber

Red

Less is Better

Performance >= 100

(100 - Variance) >= Performance < 100

Performance < (100 - Variance)

More is Better

Performance >= 100

(100 - Variance) >= Performance < 100

Performance < (100 - Variance)

Goal Post

Performance = 100

N/A

Performance <> 100

Month

Target

Actual

July

10

5

August

30

3

September

20

4

October

10

3

November

20

20

 

Month

Target

Actual

July

10

5