Reducing DSO using Power BI

[DataNinja777]

Navigating the sea of data

Power Pivot consistently amazes me by effortlessly converting raw data into crucial, real-time information—like overdue receivables or extended payment terms—that would otherwise be buried in a sea of data.

Although standard ERP systems have all the data for accounts receivable by customers, checking customer AR balances through ERP involves a snail-like process. Typically this entails keying in the customer code into a field box and checking the balance and aging for the outstanding amounts one by one. Even though the data is all there in the system, for a company with more than several thousand customers, identifying and properly acting upon overdue accounts receivable on a timely basis tends not to be such a quick exercise when using ERP alone.

Against this backdrop, over the past several years, Power Pivot and Power Query have empowered accountants to do their job better and faster. The real-time identification of issues concerning the company’s accounts receivable collections is one such example.

Credit control with Power Pivot

The primary goal of credit control is to reduce AR non-collection risks. Power Pivot and Power Query have empowered accountants to spot issues like overdue receivables in real time, improving AR management.

Business need for real-time information

To improve collections, a traditional monthly aging report is not enough. By the time it’s reviewed, the balance will likely have changed. As transactions get recorded and receivables cleared, balances fluctuate constantly. Businesses need real-time information, not outdated data from the previous week or even yesterday, to take timely action.

A prior way of sharing real-time information

Several years ago in my world, this real-time information was shared with internal customers (like individual sales reps) over the phone whenever they needed it. It might sound like something from the Stone Age to high-tech folks, but before we implemented the Power Pivot AR monitoring system, I was constantly getting calls from salespeople with questions like:

• “What is the current AR balance in SAP and how much is the remaining credit limit for customer X?”
• “Has the customer paid today to avoid reaching the approaching credit limit?”
• “When will the customer pay next and how much will they pay?”
• “How much insurance coverage does the customer have?”
• “Will the current credit limit level last until the next collection due date or do they (the salesperson) need to apply for an additional credit limit to make more sales to the same customer?”

Many of these questions required an accountant to share real-time information with individual sales reps by checking the current ERP information and insurance database with the insurer and in cases where the collections were not yet recorded in ERP, by checking the online banking system. However, as you can imagine, answering numerous questions from sales reps is an inefficient way of sharing information especially when there are often several dozen salespeople for each accountant in a firm.

From the Stone Age 🪨🦕 to the Smart Age🧠: The Data Revolution

As an automation enthusiast who hates repetitive tasks, revamping the outdated AR telephone service became my top priority. I created a daily report that pulled AR data from the ERP and other sources, including key details like current AR, credit limits, payment terms, overdue days, and more—easily filtered by sales rep

To free myself from constant calls, I built a daily AR monitoring system using SAP tables and data from other sources, like credit assessment scores and insurance credit limits. Power Pivot replaced manual vlookups and error-prone spreadsheets with an automated, streamlined report that refreshed instantly, saving days of work.

Tables used

For this example of a daily AR dashboard, I’ve created a fictitious database in the format of the following standard SAP tables:

Three fact tables:

1. An accounts receivable open balance list (SAP’s FBL5N closing balance with today’s date)
2. AR and Bank GL transaction list for a selected period (SAP’s FBL3N or Line Item Display in SAP S4/HANA)
3. Credit limit list (SAP’s FDK43)

Three dimension tables (the latter two tables are required if using a normalized customer table):

1. A calendar table
2. A customer master table
3. An employee table (to be related to the customer master table’s salesperson key code)
4. A world cities table with latitude and longitude data (to be related to the customer master table’s customer address location ID)

For this model, I created a relationship between AR[Due Date], ‘AR & Bank Transaction’[Posting date] and Calendar[Date]. I also created a relationship between the customer master table and the fact tables (AR, AR & Bank Transaction, Credit limit) using the customer keys.

AR analysis

We’ll start with the AR analysis. This part will show the accounts receivable balance by customer with their credit limits. Salespeople can see the AR approaching its credit limit and take appropriate action (such as applying for an additional credit limit). I’ve stuck up measures for the current accounts receivable and remaining credit limits in the value field of the stacked bar chart.

AR =
IF ( SUM ( AR[Gross amount] ) = 0, BLANK (), SUM ( AR[Gross amount] ) )

Remaining credit limit =
[Credit limit total] – [AR]

Where the measure [Credit limit total] is just the sum of the amount field in the credit limit fact table.

Overdue analysis

I created a calculated column identifying the overdue AR amount:

AR[Overdue] =
IF ( AR[Today] <= AR[Due Date], 0, AR[Gross amount] )

Then totaled that up in a measure below:

Overdue amounts =
IF ( SUM ( AR[Overdue] ) = 0, BLANK (), SUM ( AR[Overdue] ) )

I then added a measure to identify the number of overdue days.

Overdue number of days =
VAR OverdueNumberofDays =
    ROUND (
        DIVIDE (
            SUMX ( 'AR', 'AR'[Overdue] * [Overdue days] ),
            SUMX ( 'AR', 'AR'[Overdue] )
        ),
        0
    ) & " days"
RETURN
    IF ( [Overdue amounts] <> BLANK (), OverdueNumberofDays, BLANK () )

Where

Overdue days =
VALUE (
    IF ( HASONEVALUE ( 'AR'[Today] ), VALUES ( 'AR'[Today] ), BLANK () )
        - AVERAGE ( AR[Due Date] )
)

Ideally, this part should show a nil value, but when the business has thousands of customers, showing a few customers here is quite reasonable. The fewer, the better, however. The business can take appropriate action such as contacting customers to ask for overdue payments.

I’ve also added a measure to identify the number of overdue customers.

Overdue customer distinctcount =
VAR OverdueCustomerDistinctCount =
    COUNTAX ( VALUES ( Customer[Customer Name] ), [Overdue amounts] )
RETURN
    SWITCH (
        OverdueCustomerDistinctCount,
        BLANK (), "No overdue",
        OverdueCustomerDistinctCount & " overdue"
    )

I then created a measure to put in the URL field of the image viewer custom visual to dynamically select a thumbs-up guy image or a thumbs-down guy image depending on the existence or otherwise of overdue customers. (I’ve omitted showing the full http address in the DAX code below.)

URL measure =
IF (
    [Overdue customer distinctcount] <> "No overdue",
    "https://…omitted…/Thumbs-down-guy.png",
    "https://…omitted…/Thumbs-up-guy.png"
)

Past AR collections

We can assess a customer's ability to pay by analyzing their historical cash collections, which is more useful for credit assessment than sales amounts that haven't been converted to cash. For past collection history, I pull data from the AR and Bank journal entries table (FBL3N) or Line Item Display in SAP S4/HANA, where entries typically reflect either sales/AR recording or AR clearing. To get the other side of the bank journal entries, I use a calculated column formula like the one below:

'AR & Bank Transaction'[AR clearing] =
LOOKUPVALUE (
    'AR & Bank Transaction'[Account description],
    'AR & Bank Transaction'[Document Number], 'AR & Bank Transaction'[Document Number],
    'AR & Bank Transaction'[Year], 'AR & Bank Transaction'[Year],
    'AR & Bank Transaction'[Account description], "Bank"
)

Then total up the AR clearing journal entries in a measure.

Past Collections =
- CALCULATE (
    SUM ( 'AR & Bank Transaction'[Amount in local currency] ),
    KEEPFILTERS ( 'AR & Bank Transaction'[Account description] = "AR" ),
    KEEPFILTERS ( 'AR & Bank Transaction'[AR clearing] = "Bank" )
)

Future AR collections

In order to grasp the next collection amounts, I just use the measure [AR] without overdue amounts, as the latter were not paid by their due dates and thus should be excluded from the standard collection schedule by due dates.

Future collections =
[AR] – [Overdue amounts]

To show the past and future collections in the same time-series chart, I used a stacked column chart and put the two measures [Past Collections] and [Future Collections] in the value field. 

Dynamic scenario selections

Then, I used a "harvester measure" to enable different scenarios to be selected via slicers.

Selected measure =
SWITCH ( [Min measure #], 1, [AR], 2, [Overdue amounts], 3, [Past Collections] )

Where

Min measure # =
MIN ( 'Measure'[#] )

The 'Measure' table is a small table of the three scenarios and remains as a disconnected table.

In a report to the salespeople, it is useful to have a search box feature to be able to input customer names (in a real business environment, a customer code is probably better, but I am using the text customer name in this example) and/ or the salesperson’s name. This becomes especially important when there are more than several dozen salespeople and several thousand customers, as we don’t want to be scrolling up and down the visual to look for a particular salesperson, or hover over the mosaic art as in the visual below to look for a particular customer’s name.

For my overdue dashboard, many of the DAX formulas and their logic were quite straightforward. But the business impact of being able to systematically spot AR overdue amounts on a real-time basis has been huge. The timely monitoring of accounts receivable has prevented overdue amounts becoming uncollectable and prevented more goods from being sold to customers who missed payment due dates until the overdue amount is cleared albeit still within their credit limits. All in all, this can have a tremendous benefit to business by ensuring the timely conversion of AR into cash.

Salespeople can now monitor accounts receivable approaching their credit limit well in advance, so that they can take timely proactive measures such as applying for an increase in the credit limit or requesting customers to pay early for additional sales, rather than reacting in a knee-jerk manner in the event of sales being blocked by the system when the credit limit is reached.

Of course, the accuracy of the report depends on the timely and accurate recording and clearance of AR as we receive collections from customers. So sharing the information on a daily basis requires an accountant to maintain the AR transaction table with the most up-to-date information on a real-time basis, allocating collections as soon as they are received (on the same day) from customers, and addressing and clearing any discrepancies promptly.

Dramatic improvement

Thanks to Power Pivot, identifying overdue balances and resolving them in real time is now a breeze for accountants. Sales reps can access the real-time data they need without relying on accountants. Overdue amounts are flagged immediately in the daily dashboard, enabling timely action. Power Pivot has provided real-time business insights, reducing DSO and increasing the company’s cash balance.

Even if there are more than several thousand customers in the master table, with the help of Power Pivot, the number of overdue customers at any one point in time can be reduced to less than the number of fingers on one hand as shown in the interactive dashboard above. Time after time, Power Pivot has proved to be a brainy and reliable ally for accountants!

AR analysis.pbix