Power BI and cash flow: Delivering useful information to the collections team

Messenger delivering information
istock_erhui1979_aw
Share this content

How can you support your collections team better with the data you already have?

One of the things that tools like Power BI can do really well is present the data you already have but in a different context. Just doing this can transform your understanding of what needs attention and what needs to get done. Efficiently collecting your customer receivables can be one of the most valuable activities in small businesses, yet it is so often starved of good information.

In this article, I am sharing some of the work I have been doing with Power BI to support collections activities for small businesses. Although a simple start  - and I have a lot more in mind particularly regarding KPIs and trend analysis - this is a significantly updated version of the model described in a previous article.

The work done so far comes from an iterative cycle of feedback and in that vein, I would welcome any thoughts or suggestions. What information would you like to put in front of your collections team?

The story so far

I have built a two-page report so far in Power BI that just uses Sage 50 two tables (customers and transactions), and nine data elements from these tables, making it very achievable for anyone who wants to build this at home.

I have put up an interactive demonstration here where you can try it out. The demo dataset is pretty good but if you look really closely please forgive a few strange things like the odd customer balance that is greater than the sum of outstanding receivables (theoretically possible I suppose if I am in the business of issuing credit notes and refunds to customers in advance of sales).

Receivables page

The receivables page has six interactive visualisations:

  • Overdue / within due by aged weeks - designed to give a near-term view of the aged debtors (last eight weeks) and the mix between within due and overdue

  • Overdue / within due by aged period - designed to give a more traditional longer-term view of the receivables

  • Outstanding KPIs

  • Receipts against target for the week (the target element here is work in progress)

  • A tree map of customers by balance, coloured by % outstanding. The customer list is broken down into “top 10” and a drill-down into “others”

  • Balance by customer - showing customer balances and amount outstanding that is within due and overdue

image one


Collections page

The collections page focuses more at invoices and timelines with five interactive visualisations:

  • Balance by customer - showing customer balances and amount outstanding that is within due and overdue

  • Unallocated credits by customer - showing all customers carrying credits (receipts or credit notes) that have not been allocated against any sales invoices

  • Outstanding by sales invoice - a list of all sales invoices with an outstanding balance and whether or not they are within due or overdue

  • Outstanding amounts by week due (relative to this week)

  • Outstanding amounts by estimated receipt week (relative to this week)

image two


Unallocated credits

Clicking on a customer will filter the rest of the page by that customer to show the list of outstanding invoices, their status if there are any unallocated credits for that customer and when various amounts were due or expected relative to this week. The example below is for the customer “3a Church Street”.

image three

This shows that the customer has many overdue invoices totalling £28k which were estimated to be paid last week (but were not), however there are unallocated credits of £14k, suggesting a balance of £14k to be targeted for immediate collection.

Estimating when individual receivables will be collected

Or clicking on due week “0” (receivables that fall due this week) you can see:

  • £52k of invoices that become overdue this week

  • £10k of which is already overdue (earlier this week)

  • £26k of which are estimated to collect this week with £11k next week, £5k the week after and so on.

image four


Why focus on collections?

If it is not already obvious, a smarter and more systematic approach to collections is probably the single most sustainable and achievable way to improve your cash flow without changing your business model. It also requires relatively little data that you already have in your business. You can start making that change today and it doesn’t require a huge upfront preparation effort.

The time problem for a collections team

I have already mentioned several times the idea of a collections “team”. In reality, in many small businesses there is no such thing. So often, collections is an ad-hoc activity given to a member of the accounts team to do when he or she can spare the time. Especially when it comes to calling around a few debtors. The trouble is, that in a small business with a turnover of say £1m to £10m, the number of debtors at any one time can easily run into a few hundred. If you have an hour to spend calling these, you may only reach 10 of them. In this situation, you don’t want to spend the first 15 minutes figuring out who to call.  

What can and can’t Power BI do to help?

What Power BI (or similar analysis tool) can do to help is to reorganise and present your debtor data into a form that is more suitable to decide the best strategies for which customers and when.

What it won’t do for you is to capture the “CRM” element of notes, correspondence and follow-up that you will also need to pay attention to.

If you are using Sage 50, it has a diary and notes function, or you can use a more friendly CRM system specifically designed to help with customer contact and follow-up. If you are already using Sage 50cloud why not take a look at the Office 365 integration that is already available for you? It comes with a free seat of Power BI, so you are all set to go :)

The accounting data “context” for collections

The following is what I see as the key pieces of accounting information to support your collections process:

  • Customer data

    • Company name / reference

    • Balance

    • Credit limit

    • Average time to pay

    • Payment terms

  • Invoice data

    • Date of the invoice

    • Due date

    • What the invoice was for

    • Invoice amount

    • Outstanding balance of the invoice

    • Whether it is within-due or overdue

    • A realistic “expected date” for the invoice to be paid
       

  • Time data

    • What recently became overdue

    • What will become overdue this week, next week

    • What receipts can we expect this week, next week, this month?

    • What receipts are already later than expected?

The time context for collections activities is very important. It is useful to split the focus into current and longer-term. A current focus will look at three or four weeks either side of this week: what should have happened recently but didn’t, and what we expect or want to happen very soon. A longer-term focus will look at the older debt or more recent debt that unfortunately you expect to be old before it is paid.

Normal aged debtor reports tend to be fine to get the big picture but lack the micro view - either from a time, customer or invoice perspective. What Power BI and other data analysis tools are really good at is presenting the macro and micro views together, enabling you to see the big picture but also drill down to what needs to be done this week.

This is what I have attempted to do, armed with a pretty regular Sage 50 dataset and Power BI.

My starting point - Sage 50 data

My starting point for this work was just nine data elements from Sage 50:

  • Customers - Name, Account_Ref, Balance, AveragePayDays

  • Transactions - Account Ref, DueDate, GrossAmount, Date, Type

I have used the OData feed for Sage 50 from Suntico, but you don’t have to. There are only two underlying Sage 50 ODBC tables and nine columns used here you can certainly use other tools or methods to extract these files. If you are not using Sage 50, but another accounts package, I am sure that you can find this or similar data in your system. I used the following Sage 50 transaction types:

  • Receivables

    • SI - Sales Invoice

    • SC - Sales Credit

    • SD - Sales Discount

  • Receipts

    • SR - Sales Receipt

    • SA - Sales Payment on Account

    • SP - Sales Payment (refund) to a customer

The receipts information is not critical to examining your receivables, but it is great to track progress over time or against a collections target or estimates.

Some quick data checks

Is a customer balance less than the sum of its outstanding invoice amounts?

You may think that the sum of all your outstanding customer invoice amounts should equal the sum of your customer balances. This may not always be the case.

Let’s say that you have received a payment of £1,000 from a customer that had a balance of £1,500 for many small invoices. The new customer balance is now £500, but if the payment was not allocated against individual invoices then the total outstanding on all the invoices will still be £1,500. If they remain unallocated then they will show up as overdue. You don’t want to be chasing this customer for payment of £1,500 for overdue invoices. It makes the collections task much easier if all allocations are done, but if they are not then at least you need to know about the effect of any payments on account.

A similar situation will arise if you issue a credit note to a customer that is not linked in Sage 50 to any specific invoice(s).

So the data check here is to identify customers where their balance is less than the total outstanding amount on their invoices.

Is a customer balance greater than the sum of its outstanding invoice amounts?

Assuming that your accounting system is all good, this is a sign that you are missing some sales invoice data in your analysis. The most likely cause is that you are only analysing recent invoices and maybe there are some really old ones that you have missed in your dataset. Anyway, I would suggest that you figure this out before you proceed.

My current Power BI model enhancements to the Sage 50 data

The main enrichments that I have added to the raw Sage 50 data are as follows:

Measures

Balance

Sum of all selected non-zero customer balances

DSO (Days’ Sales Outstanding)

Sum of the outstanding value of receivables * 364, divided by total sales invoices (and credits) in the last 364 days for the selected customers

Overdue

Sum of the outstanding value of all selected sales invoices where the due date is passed

Unallocated Credits

Sum of all customer balances minus the sum of all outstanding sales invoice amounts

WithinDue

Sum of the outstanding value of all sales invoices where the due date has not passed

 
Calculated / Enriched Columns
 

Dates Table

AgedWeeks

Number of whole weeks ago, counting back from today.  Future dates and last 7 days are "Aged Weeks = 0”

AgedPeriods

Aged Periods counting back from today <30, 30-59, 60-89, 90+

ThisWeek

Date is in this week

Last52Weeks

Date is in last 52 Weeks - used for DSO calculation and other things

Customers Table

Top10AndOthersByBalance

Customer name is the customer is in the top 10 by balance, otherwise "Other" - used to make the Tree Map less cluttered

Transactions Table

EstimatedReceiptDate

If the transaction is a Receivable, then it is the transaction date plus the customer's average time to pay

IsReceipt

TRUE if this transaction is a customer receipt - i.e. transaction type "SR", "SA" or "SP"

IsReceivable

TRUE if the transaction is a customer receivable - i.e. "SI", "SC", or "SD"

RelativeWeeksDue

# of weeks away for the DueDate of a Receivable transaction, relative to this week

 

RelativeWeeksEstimated

# of weeks away for the Estimated Receipt Date for a Receivable transaction, relative to this week

 

What next?

There are some things that I feel are still missing in this report, which I am looking into at the moment. These are mainly around measuring performance and tracking progress. I am currently testing how to predict receipts this month and next with some kind of confidence level, more useful variations on traditional KPI’s like DSO, trend analysis and auto-suggested weekly collections targets with the invoices that make them up.

Any of your thoughts and suggestions would be very welcome, or if any of you would be interested in collaboration I would love to hear from you.

Useful links

About Hugh Johnson

Hugh Johnson

I am a blogger on Power BI and your accounting data and the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50.  Please feel free to try it out in connected to your own Sage 50 system on the web or with Power BI Desktop.

I am a senior vice president of Suntico, an online financial data platform that works with popular small business accounting software.  I am responsible for the company’s customer division. This covers products, marketing, sales, customer on-boarding, customer service and renewals. I am also in charge of our product strategy and road map.

I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year). 

My personal passions are high performance Fireball dinghy racing and food.

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.