Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

Working capital optimization

Applies to: CELONIS 4.7

Description

This example shows how to calculate the ratio of cases that were paid early for working capital optimization.

Working capital is defined as the difference between a company's current assets and its current liabilities essential for the smooth operation of a business, and is a key figure for measuring a company's liquidity and its short-term financial health. Working capital management aims to optimize liquidity while ensuring sustained operations in the long term. Typical ways to optimize the working capital are inventory reduction, faster collection of receivables and lengthening of the payable cycle. Activities for optimization within these areas are manifold. One example for lengthening the payable cycle is on-time payment of invoices by avoiding both early and late payments. Eradicating early payments can improve working capital by keeping assets until the day they are due. Preventing late payments can stop late payment penalties and allows to take advantage of cash discounts.

Challenge

Each invoice (i.e. case) is issued by a vendor. For each vendor, calculate the ratio of invoices that were paid early.

Solution

The following example shows a PQL statement for the calculation of the early payment ratio per vendor. Using this query, the user is able to discover the vendors which have the highest ratio of invoices paid more than three days early.

[1] The distinction whether an invoice was paid more than three days before the due date is made within the CASE WHEN statement by calculating the throughput time with the CALC_THROUGHPUT function. The CALC_THROUGHPUT operator takes the timestamp of the first occurrence of activity 'Clear Invoice' and the timestamp of the first occurrence of activity 'Due Date passed' and calculates the difference. The second parameter, given as REMAP_TIMESTAMPS operator, counts time units in the specified interval DAYS based on the timestamps in the activity table to enable the calculation of the throughput time. As the CALC_THROUGHPUT operator returns NULL if the end date is before the start date, the result of the calculation is wrapped in the COALESCE operator to return 0 in these cases. The result of the COALESCE operator is then compared to the specified three days. If the result is greater than 3, the CASE WHEN statement returns 1; otherwise 0.

The whole CASE WHEN statement is wrapped in the AVG operator, allowing to calculate the ratio of invoices paid more than three days early. By specifying the vendor name ("Invoice"."VendorName") as a dimension, the ratio is calculated per vendor. The two FILTER statements at the beginning of the query use PROCESS EQUALS to ensure that only cases with an already paid invoice and a specified due date are considered within the calculation.

For the example data, the query returns an early payment ratio of 0.5 for 'Vendor A'. Invoice '1' was not paid early because it was cleared three days before due date, while invoice '2' was paid early as it was cleared five days before due date. For 'Vendor B' the calculated early payment ratio is 1.0 because the only invoice related to this vendor, invoice '3', was paid early (four days before due date):

Query

Filter

FILTER PROCESS EQUALS 'Clear Invoice';

Filter

FILTER PROCESS EQUALS 'Due Date passed';

Column1

"Invoice"."VendorName"

Column2

AVG (
    CASE
        WHEN
            COALESCE (
                CALC_THROUGHPUT (
                    FIRST_OCCURRENCE [ 'Clear Invoice' ]
                    TO
                    FIRST_OCCURRENCE [ 'Due Date passed' ] ,
                    REMAP_TIMESTAMPS ( "Activities"."Timestamp" , DAYS )
                ) ,
                0
            )
            >
            3
        THEN
            1
        ELSE
            0
    END
)

Input

Output

Activities

CaseID : INT

CaseID : INT

Timestamp : DATE

1

'Receive Invoice'

Wed Jan 01 2020 15:00:00.000

1

'Clear Invoice'

Thu Jan 02 2020 15:00:00.000

1

'Due Date passed'

Sun Jan 05 2020 15:00:00.000

2

'Receive Invoice'

Wed Jan 01 2020 15:00:00.000

2

'Clear Invoice'

Wed Jan 01 2020 15:00:00.000

2

'Due Date passed'

Wed Jan 08 2020 15:00:00.000

3

'Receive Invoice'

Wed Jan 01 2020 15:00:00.000

3

'Clear Invoice'

Thu Jan 02 2020 15:00:00.000

3

'Due Date passed'

Mon Jan 06 2020 15:00:00.000

Invoice

CaseID : INT

CaseID : INT

1

'Vendor A'

2

'Vendor A'

3

'Vendor B'

Result

Column1 : STRING

Column2 : FLOAT

'Vendor A'

0.5

'Vendor B'

1.0

Foreign Keys

Activities.CaseID

Invoice.CaseID