Power BI with your accounting data

How to use Power BI to estimate each invoice receipt date

Posted by Narayani Ambar on 17-Sep-2018 13:56:21

How to use a clustered column chart in Power BI to display estimated vs due cash inflows from your accounts receivable trade debtors.  Reveal what customers are paying more slowly than expected and against which invoices.

In a previous article we discussed how to forecast inbound cash flow based on your debtors' payment histories.  One thing is calculating the numbers.  Another thing is how best to display this information in Power BI to best support your receivables collection activities.

In our Accounts Receivable Solution for Sage 50 we display this using a clustered column charts to show the estimated and due receivables by week.  We have chosen do display this by week, since collections activities often run around a weekly cycle.   This is shown below.

Due and Expected Customer Receipts by Week-1

The x axes on these charts show the week relative to this week, where this week is "0", last week is "-1" and next week is "1" etc..  Having this perspective shows very quickly what cash inflows might be estimated in the coming weeks and also what activities might be needed to chase or remind debtors that payment is due, or about to become due.

Having these charts on the same page of your collections report enables you easily to see for example what receipts we were estimating last week and didn't receive, or what invoices fall due next week.  In the example below, I have selected the Estimated Week = "-1" to see the list of invoices that I was estimating to get paid for last week but didn't.

Outstanding sales invoices where payment was estimated for last week

The list of outstanding sales invoices that make up the £82k that was estimated for collection last week is shown in the bottom right table, and the customers in the bottom left table.  In the "Due Week" clustered column chart you can see when these invoices fell (or will fall) due.

You can try this for yourself in page 2 of this interactive demo.

Note about the use of Clustered Column charts here

A clustered column chart is designed to be able to show more than one data series in vertical columns.  We often use them to compare sales by month this year versus last year for example.  In this case we are just using one data series in each clustered column chart quite simply because there is no direct correlation or comparison to be made for the Estimated Receipts and the Due Receipts for a given week.

Topics: Accounts Receivable

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts