Power BI with your accounting data

Narayani Ambar

Recent Posts

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.

Read More

Topics: Accounts Receivable

Power BI scatterchart showing overdue invoices by customer

Posted by Narayani Ambar on 14-Sep-2018 15:50:19

 

Read More

Topics: Accounts Receivable

Weighted Average Receivables Demo

Posted by Narayani Ambar on 28-Aug-2018 13:50:54

In a previous blog I introduced the idea of weighted averages to measure accounts receivable and how they can be used to give a more useful measure of your collections performance. In our Accounts Receivable solution for Sage 50 we have included a multi row card with three weighted average calculations to help you get a better picture of your receivables portfolio.  

Read More

Topics: Accounts Receivable

Weighted accounts receivable days - the true cost of customer credit?

Posted by Narayani Ambar on 27-Aug-2018 17:48:44

Accounts receivable KPIs are often quite crude - with the advantages and disadvantages that go with that.

Read More

Topics: Accounts Receivable

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.




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.

Read More

Topics: Accounts Receivable, cash flow

Days sales outstanding (DSO) - A useful or misused KPI?

Posted by Narayani Ambar on 22-Aug-2018 11:07:50
Read More

Topics: Sage 50, Power BI, Accounts Receivable, Days Sales Outstanding

Track your outstanding receivables with Power BI

Posted by Narayani Ambar on 20-Aug-2018 01:52:21

 

A number speaks for itself. Like, you get a basic scenario of your performance by just looking at the number. Hence, in our Accounts Receivable solution we have added a multi-row card with few important numbers. This card has an aggregate of the outstanding amount, overdue amount, the percentage of overdue to outstanding and days sales outstanding. A detailed analysis is good when you want to plan and evaluate your cash flow needs but it is good to know the numbers as you will get understanding of your receivables performance by just taking a glimpse at these figures.

Read More

Topics: Accounts Receivable, outstanding, overdue

Aged Debtor Report for Sage 50 Receivables

Posted by Narayani Ambar on 15-Aug-2018 10:53:17

Do justice to your Sage 50 receivables & collections reports with Power BI

Accounts Receivable for Sage 50 is a business intelligence solution from Suntico for companies that want to drive down debtor days and improve cash flow.  Good accounts receivable reporting can be tricky to do but this solution makes it easy for you.

Unlike your current aged debtor report from Sage 50 that may run to 10 or 20 pages and tell you very little that is digestible, the Suntico Accounts Receivable solution delivers the complete picture from overview and trends down to an individual sales invoice line item.

Accounts Receivable for Sage 50 uses Microsoft Power BI to deliver automated analysis that can be as fresh as the last 30 minutes, with key insights delivered to your smartphone.

“Since Hugh and his team (Suntico) have come on board with us, we have been able to create multiple reports in Power BI including a new debtors report which has significantly improved cash-flow and reduced our debtor days.”  John Glasgow, Financial Director, The Berkley Partnership.


Read More

Topics: Sage 50cloud, 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