The actual of a calculated KPI will always be automatically calculated based on the formula entered within the 'Sub KPI' tab.
STEP 1: Click the 'Target and Actuals' tab.
STEP 2: Select the calculation method.
NOTE: Manual method would allow user to enter Target/Variance/Forecast/Benchmark values as they prefer; however if it's automatic, values will also be calculated from the sub KPIs. Actuals are always calculated from the sub KPIs, there is no such dependency on 'Calculation Method.'
STEP 3: Attend necessary data entry operations and click on 'Save' button.
|
|
• The calculation of the actual and automatic target takes into consideration the aggregation method of the sub KPIs (e.g. Normal, Average or Cumulative) • The calculated KPI aggregation method is always “Normal” • The calculated KPI Time Horizon is always “Period” |
Example:
Formulas using Excel as an example:
Calculated KPI Actual Year to end of Q1 actual:
=E5/((1.51*E6)+(2.13*E7)+(2*E8)+(1.52*E9))
Calculated KPI Actual Year to end of Q2 actual:
=SUM(E5:F5)/((1.51*(AVERAGE(E6:F6))+(2.13*(AVERAGE(E7:F7)))+(2*A
VERAGE(E8:F8))+(1.52*AVERAGE(E9:F9))))
Calculated KPI Actual Year to end of Q3 actual:
=SUM($E$5:$G$5)/((1.51*(AVERAGE($E$6:$G$6))+(2.13*(AVERAGE($E$7:$G$7)))+(2*AVERAGE($E$8:$G$8))+(1.52*AVERAGE($E$9:$G$9))))
Calculated KPI Actual Year to end of Q4 actual:
=SUM(E5:H5)/((1.51*(AVERAGE(E6:H6))+(2.13*(AVERAGE(E7:H7)))+(2*AVERAGE(E8:H8))+(1.52*AVERAGE(E9:H9))))
Calculated KPI Actual Beginning of Q2 to end of Q3:
=SUM($E$5:$G$5)/((1.51*(AVERAGE($F$6:$G$6))+(2.13*(AVERAGE($E$7:$G$7)))+(2*AVERAGE($F$8:$G$8))+(1.52*AVERAGE($E$9:$G$9))))
Definitions of various calculation methods:
|
Cumulative YTD Target |
Sum of all the Targets, YTD |
|
Cumulative YTD Actual |
Sum of all the Actuals, YTD |
|
Cumulative YTD Variance |
Cumulative Actual YTD - Cumulative Target YTD |
|
Cumulative YTD Variance % |
(Cumulative YTD Variance / Cumulative YTD Target) * 100 |
|
Cumulative Period Target |
Sum of the targets of the closed reporting periods within the current period |
|
Cumulative Period Actual |
Sum of the Actuals of the closed reporting periods within the current period |
|
Cumulative Period Variance |
Cumulative Period Actual - cumulative period Target |
|
Cumulative Period Variance % |
(Cumulative Period Variance / Cumulative Period target ) * 100 |
|
Average YTD Target |
Average of all the Targets, YTD |
|
Average YTD Actual |
Average of all the Actuals, YTD |
|
Average YTD Variance |
Average Actual YTD - Average Target YTD |
|
Average YTD Variance % |
(Average Variance YTD / Average Target YTD) * 100 |
|
Average Target Period |
Average of the targets of the closed reporting periods within the current period |
|
Average Actual Period |
Average of the Actuals of the closed reporting periods within the current period |
|
Average Period Variance |
Average Period Actual - Average Period Target |
|
Average Period Variance % |
(Average Period Variance / Average period target) * 100 |
|
Normal Target |
Last closed period Target |
|
Normal Actual |
Last closed period Actual |
|
Normal Variance |
Normal Actual - normal Target |
|
Normal Variance % |
(Normal Variance / Normal Target) * 100 |
|
Cumulative Rolling 'x' Period Actual |
Sum of all the actual within the number of closed periods |
|
Cumulative Rolling 'X' Period Target |
Sum of the actual within the specified number of closed periods |
|
Cumulative Rolling 'x' Periods Variance # |
Cumulative Rolling Actual – Cumulative Rolling Target |
|
Cumulative Rolling 'x' Periods Variance % |
(Cumulative Rolling Variance # / Cumulative Rolling Target) * 100 |
|
Average Rolling 'x' Period Actual |
Average of all actual over the closed periods |
|
Average Rolling 'x' Period Target |
Average of the targets over the closed periods |
|
Average Rolling 'x' Period Variance # |
Average rolling target – Average rolling actual |
|
Average Rolling 'x' Periods Variance % |
(Average rolling Variance # / Average rolling target) * 100 |
Copyright © 2014-2015 CAMMS Online Help. All rights reserved.
Last revised: September 04, 2018
