Accounts Receivable

Accounts receivable forecasting method

Accounts Receivable, cash flow

Hugh Johnson @8/24/2018 12:25:03 AM

Accounts receivable forecasting method

Add predictablility to your business with better estimates of when to expect customer receipts. This simple but effective method uses Microsoft Power BI to help with accounts receivable forecasting from your Sage 50 data. Try it free for 30 days.

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.

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.

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.

  • 1-10

Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,

+353 1 567 4069