Power BI with your accounting data

Accounts receivable forecasting method

Posted by Narayani Ambar on 24-Aug-2018 00:25:03


The need for predictability

In business, it is important to understand how much cash is expected to be collected in the coming weeks from your Accounts Receivable. Having this information handy helps for better planning and cash flow management.

In an ideal world, all customers would pay all of their invoices on or before the due date. This is not the world we live in though and having realistic estimates of when you are actually going to get paid is really useful. 

In our Accounts Receivable for Sage 50 solution we have added a measure in the Power BI model to estimate which sales invoices will be paid this month, based on the payment history of each customer.

By putting this onto a Gauge Chart, along with receipts received so far this month, we can in an instant see what customer cash inflows we can expect this month and how we are progressing against this estimate.

gauge meter

We have used the gauge meter to display this KPI in our Accounts Receivable solution. It shows the difference between what we expected to receive vs how much we have actually received. Every month the value starts at zero and rises when any collection is made. The meter shows that in this month receipts amounting to 422.26K are expected and that so far we have collected 182.65K, 45% of the total expected amount.

The two measures that are feeding this gauge are within the “My Receipts Measures” table and are there for you to use in other visualisations as you wish.

estimated receipts this moths

Taking into account your actual customer behaviour

Your Sage 50 Aged Debtor reports may tell you how old your receivables are, but they will not tell you when your customers are likely to pay you. Sage 50 does, however track the average time that it takes for each of your customers to pay you. In our Accounts Receivable solution we take advantage of this. For each sales invoice, we calculate an estimated receipt date simply by taking the invoice date and adding the average time it takes for that customer to pay. The Estimated Receipts This Month are calculated by taking the sum of gross invoice values where the due date and the customer’s average days to pay fall into this month.

Let's say you have five customers with invoices that are due in this month. Now as per your past experience with these customers two of them take on average 45 days to pay an invoice, one 60 days and one 30. If you simply relied on taking your Invoices Due figure as your estimate for cash inflows you are going to be very wrong.

How accurate is this estimate for receipts this month?

It is difficult to be precise about how accurate this estimate will be for you though without doubt for most companies most of the time it will represent an improvement and introduce some consistency around cash-flow forecasting.

Very simply, if your customer payment patterns are consistent (e.g. if a customer always pays around 14 days late) then this model should give you good results.

Why not try it?

Why not use our Accounts Receivable solution free for 30 days, and see for yourself if this gives you a better way to forecast your cash inflows from your customers?

Use free for 30 days

Topics: Accounts Receivable, cash flow

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.


  • 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