How to create a Cumulative Key Indicator

Guide to creating a Cumulative KPI, i.e. a key indicator that allows you to sum up a historical series of values within Agicap

What is a Cumulative KPI used for?

They are KPIs with which each month you can add up one or more values in categories and get a cumulative total.

You can use them to:

1. General Cumulative Start KPI

A) First of all, to create a cumulative KPI you need to create a Manual KPI that you can call 'Period Start'.

We will need this to give the cumulative KPIs we create a starting point for cumulation.

B) Once this very simple KPI has been created (from 'Advanced Settings' > Key Indicators), we will need to return to the treasury plan and then enter the number '1' in the month from which we want the cumulative calculation to begin.We will need this to give the cumulative KPIs we create a starting point for cumulation.

B) Once this very simple KPI has been created (from 'Advanced Settings' > Key Indicators), we will need to return to the treasury plan and then enter the number '1' in the month from which we want the cumulative calculation to begin.

In the case of annual cumulatives, you will therefore need to put '1' on the months of January (to be repeated for each year concerned)

C) Finally, you can hide this KPI, which we are not interested in seeing in the table, as shown in the image below (again from 'Advanced Settings' > Key Indicators):In the case of annual cumulatives, you will therefore need to put '1' on the months of January (to be repeated for each year concerned)

C) Finally, you can hide this KPI, which we are not interested in seeing in the table, as shown in the image below (again from 'Advanced Settings' > Key Indicators):

2. KPI Cumulative Calculation of Individual Output Categories

A) Let us now move on to the creation of the actual cumulative calculation KPI.
​

This is a KPI generated by the formula.
​

After creating it, the first step is to add a dummy value or formula to it, and then close it to confirm its creation.

For example, you could write 'value([start of period],0)' as a formula and then click 'Confirm'. This step is necessary in order to add this new KPI 'in memory', since afterwards we will have to call it up in its own formula to make the calculationolo.

B) Once you have clicked on 'Confirm' select the KPI in question again and click on '(x) Modify Formula' to resume updating it.The exact formula to be added is as follows

Let us see the logic of the formula:

First of all, let me remind you that the 'beginning of period' indicator, as set up before, is equal to 1 in January and empty for the other months. The formula works like a normal Excel 'IF' formula and thus reads: If it is January (start of period = to 1), take the value of exits of the category whose cumulative is to be calculated (in this case 'Rentals') for this same month.

We are in January, cumulative start, so we just take the value of this month's exits and mark it in the KPI 'Cumulative Rentals'. If, on the other hand, we are not in January (i.e. the beginning of the period does not equate to 1) we are told to still take the value of the exits of the category we want to calculate the cumulative ('Rentals') for this same month and add it to the value of the KPI 'Cumulative Rentals' of the previous month.

This way, from month to month, the new exits will be added to the previously calculated cumulative C).

3. Update calculation

From the Treasury Plan, click on 'Update calculation' in the top left-hand corner to display the newly created KPI values.

Important: In order to update the calculation correctly, it is necessary to set the displayed period to at least the whole year. KPIs are in fact only updated in the displayed period. If it did not include the whole year, the update would be incomplete and the calculation of the displayed KPI would not be correct. You can change the period displayed by selecting it at the top left of the treasury plan.