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

Aug

30

3

September

20

4

October

10

3

November

20

20

 

Period

Target

Variance

Actual

Rolling 5 months Actual

1

100

10

91

91

2

100

10

61

152

3

110

10

92

244

4

111

10

100

344

5

120

10

150

494

6

100

10

120

523

7

180

10

60

522

8

 50

10

70

500

9

90

10

90

490

10

100

10

100

440

11

100

10

87

407

12

100

10

95

442

13

 98

10

92

464

14

 97

10

95

469

15

 96

10

97

466

Â