Power BI: Get a clear view on your receivables

Finance director
iStock_Boonchok_AW
Share this content
Tags

When I used to supervise the finance department in a small business, I constantly struggled with getting useful information about my receivables from my accounting software. The data was there, no problem. I just couldn’t answer basic questions like:

  • How effective are our collections activities?

  • Are we getting better or worse?

  • Which customers should we be chasing?

  • When should we target additional effort around collections?

  • When should I be expecting to get cash into the business?

As I say, the data is there. It is just a small matter of presenting it in the right way to help answer these questions. This is where Power BI comes into its own. I have been experimenting with this recently and in this article I will share some of what I have found.  

The Sage 50 data that you need

There are two tables in Sage 50 that you will want to analyse receivables.  

Transactions

The most important table in your Sage 50 dataset for analysing receivables is the Transactions table. Most companies would be able to derive all the essential receivables information just from this table. This is where you will find the outstanding amount on any sales invoice as well as customer credits and payments on account.

Customers

Although you can do the core number crunching just from the Transactions table, I also use the Customers table to enrich my analysis. In the Customers table you will find the following data that could be useful:

  • The customer name rather than just an Account Ref

  • Address (should you need physically to go and collect)

  • Currency

  • Payment terms (relevant if you have different terms for different customers)

  • Balance (in theory not necessary, but useful)

  • Average Days to Pay (if you can trust this information)

  • Credit limit

  • Any other fields that you might use for segmentation - such as the Analysis fields, or calculated segmentations that you have done in Power BI.

I also use the Sales Invoices table in my Days Sales Outstanding (DSO) calculations to calculate gross sales over the last 52 weeks. I could equally use the Transactions table for this.

What can Power BI do with this data?

Once you have this data you can use Power BI for three things:

  • Creating new KPI’s to track your receivables in a nice simple way.

  • Sharing information across the management team.

  • Supporting more easy, dynamic drill-down or slicing to investigate your receivables

What I have come up with (so far)

Days Sales Outstanding (DSO)

One of the problems with classic aged debtors analysis is figuring out the position of a customer with a mix of invoices of different ages; some paid and some not paid; with unreconciled payments-on-account and so on.  

DSO is one way around this, delivering a nice single number for each customer no matter how complex their receivables situation. It can be used as an estimate for how long it takes to get paid, but how accurate this is will depend on the constancy of your business. If your sales are constant and unfluctuating then it is a good representation of this. A sudden spike in sales for though will drive up your DSO figure but it may not necessarily represent any slowdown in collections.

As I say, the data is there. It is just a small matter of presenting it in the right way to help answer these questions.

DSO is the total balance outstanding divided by the average daily sales. The average can be taken over a month, quarter, year; whatever is appropriate for the cycles of a business.

I think it is a good measure of the current/impending workload for the collections team. It can be calculated easily in Power BI as a number overall and also for individual customers or segments.

I am calculating it over a rolling 52-week period and the maths for this are:

DSO = (Balance x 364)/(Gross Sales last 52 weeks).

In DAX I have chosen to write it like like:

DSO = CALCULATE ( SUMX( 'Customers',  DIVIDE ( [Balance * 364,
SUMX ( 'Sales Invoices', [Gross Sales last 52 Weeks] ),  0 )))

Where [Gross Sales last 52 Weeks] is another Measure created by summing [Gross Sales] in the Sales Invoices table and applying as a filter the analysis period [Last 52 Weeks] from my Dates table.

Gross Sales last 52 Weeks =

CALCULATE ( SUMX ( 'Sales Invoices', [Gross Sales] ), Dates[Last 52 Weeks] )

I could also have calculated this from my Transactions table, but using the Sales Invoices table saves having to filter my transactions on sales invoices.

This chart shows Customer Balances in descending order along with a DSO number calculated for each customer.  You can click on the chart to explore an interactive illustration of how this works in Power BI.  

 

It is a two-page report. The first page is my DSO calculations by customer and the second page is a “Sales Slicer” that enables me to analyse the sales of an individual customer more closely.

What I like about this is that I can immediately see that my top eight customers by balance are the main ones to focus on, that the largest customer “105 Auto Stop” is not a major concern, but the other seven are.

Without analysing the sales more deeply for these customers I cannot tell if it is just a case that they have all had recent spikes in sales, but it does tell me that their balances as a proportion of annual sales are very high. Concentrating collections activity on these seven customers is likely to be the highest return activity my collections team can do at the moment.

Aged Debt by week

A classic textual Aged Debt report can be hard to interpret, but presented graphically it makes much more sense.

In my Dates table I have defined an analysis period called “Aged Weeks”. In my transactions table, I have summed all “Outstanding” balances on all my sales transactions (in Sage 50 this is Transaction Types “SI”, “SC”, “SD” and “SA”). Take care because you will need to filter out Deleted or Cancelled transactions.

Dragging the two together into one chart shows me a pipeline of aged debt. I can see a big spike that is three weeks old and that should probably be my focus of attention over the next couple of weeks. I think that for the first 8 weeks of a debt it is very useful to see this by week instead of the classic 30/60/90 view.

Now if I put a list of customers alongside and click on week 3, I automatically get a list of customers with outstanding sales transactions from that week.

I could also include a visual to show me the details of these sales transactions, so as I click each customer I can see the these details.

Within Due and Overdue

In my Customers table I have the credit terms for each customer, so for each sales transaction I can determine if it is “Within Due” or “Overdue”.  I think that is forms a nice complement to the DSO analysis.  You can see straight away if a customer has a high DSO number because of a recent spike in sales or because the debt is getting old.

This is sorted in descending order by Overdue amount.  I think it gives a nice sense of where we are at with each customer.  

If you want to just look at my Overdue customers, then a Tree-map is great.  In this example I have just plotted Overdue amount by customer:

In about two seconds I can see that just five customers account for half my overdue receivables.

Overdue as a % of Outstanding

I really like looking at the ratio of Overdue as a percentage of Outstanding.  I really like this figure as an indicator of how well I am doing with collections.  Like all numbers though, it is not a panacea.  If the performance of my collections team is constant, a sudden spike in new sales will drive this number down, giving the impression that I am doing better with my collections than I really am.  The opposite is true for a sudden fall in sales.

So again, rather like DSO, it is an indicator of the work that needs to be done more than a true measure of the effectiveness of my collections team.

Like DSO, it can be presented as a single number for the company:

It can be well be presented by Department (or any other customer segment) or Customer.  

I think it works well also as a Tree-map where the size of the rectangle represents the total outstanding, and the colour represents the % Overdue.

Here we can see that about half of all our outstanding receivables are from 17 customers and that three out of the top four are, give or take, completely overdue.

Expected Receipts - “When am I going to get paid?”

Something that is top of the mind for many business owners is “When am I going to get paid?”. Of course, no amount of data analysis in Power BI will guarantee a correct answer to this question, but consistent objective analysis will help you to make a judgement.

Perhaps rather like in the world of physics, we have one model for things that are close and another for things that are far away.

For me, this is work in progress and there are three techniques that I am looking at. Again it depends on what the question is and I think it comes down to how far ahead are you wanting to predict cash receipts? Perhaps rather like in the world of physics, we have one model for things that are close and another for things that are far away. We can define the “near term” as when we will get paid against current sales invoices that we know about, and “medium-term” as future receipts projections from sales that are yet to be made.

Near-term

For the near-term I have experimented with taking the “Average Days to Pay” figure for each customer from Sage, applying that to the current receivables for each customer to calculate forward receipts.

In this example, I have calculated the expected receipt date for every receivable for every customer. I have based this on the “Average time to pay” value from Sage for each customer.

What I like about this is that it gives a “best guess” based on historic patterns, that it changes dynamically as time progresses and as the receivables profile changes in Sage. Therefore every day you are looking at the current “best guess”, that you can also drill down to the individual customer level and make it more actionable.

So although I am sure that most Sage 50 users do not edit this field, there is always that uncertainty about what it really represents.

What I don’t like about this approach is that it relies on a rather obscure field in Sage called “Average Days to Pay” for each customer.  It is not clear how this field is calculated (okay you might say), but within Sage you can edit it,  Manually!

So although I am sure that most Sage 50 users do not edit this field, there is always that uncertainty about what it really represents.

My plan (not yet done) is to calculate my own “Average Days to Pay” for each customer in Power BI and so get around this uncertainty.

Medium Term Receipts Forecasting

You cannot use current receivables to forecast medium-term receipts.  This is simply because you need to make assumptions on receivables that don’t exist yet.

What I would propose here is the same method that I apply to Sales Forecasting, but instead of applying the model to Sales Invoices, I apply this to Sales Receipts.  This is work in progress, but if you can accept the sales forecast model, you should also be able to accept the sales receipts model.

The beauty of using Power BI to do this is that once you have built the model then it updates automatically every day as you present it with the latest data that you have from Sage 50.

Summary and to find out more

Using Power BI it is possible to drive a much deeper and more actionable understanding of your receivables from your Sage 50 data. You can more easily see your underlying trends as well as very precise and actionable data relating to individual customers.  With sufficient volume of data, you should also be able to calculate a prediction of future receipts by customer.

Perhaps the most important thing of all is that you can present your receivables information in a way that is meaningful and digestible by the entire management team and that can be updated automatically and daily.

Useful links

About Hugh Johnson

Hugh Johnson

I am 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.