Aged Debtor Report for Sage 50 Receivables


by Hugh Johnson, Posted on 8/9/2018 1:08:29 PM

 Sage 50cloud, Accounts Receivable

Aged Debtor Report for Sage 50 Receivables

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.


Cash is the backbone of any business and; the main source of income in business is sales. But increasing sales does not always guarantee you inflows. Business often operates on a credit basis which allows customers to pay the due amounts within some specified period of time. In the real world, not every customer pays their invoices on time and that’s when the problem arises. Business needs cash to survive. Imagine you have made sales worth €10,000 and the customer didn’t pay you, all the hard-earned money will accumulate as a bad debts. To reduce the possibility of bad debts, businesses have collections teams. Collecting your receivables on time is an important, if not the most effective, way to avoid serious cash flow management issue. Receivables are nothing but unpaid invoices and collections (aka debt collection) is the process of collecting the due money from the customer.


What’s wrong with your Sage 50 Aged Debtors report?

If you are using Sage 50, the most common way to track your debtors is through the Aged Debtors report.  It is neat and tidy and lists out each debtor, how much is owed, a rough indication of how old the debt is and some other useful information such as the customer credit limit.  So what’s wrong with this?

  1. It is long.  The summary report is designed for printing and has 13 customers per page.  So if you have around 200 debtors, then you have a printed report that is about 15 pages long.  The detailed report includes some summary information about each sales invoice and is about twice as long.  There is a drillable option for viewing on screen, but each drill-down takes a long time and you quickly lose context, making it difficult to use.

     2. It can be outof date as soon as you have printed it.

     3. It gives you no sense of the overall company receivables position - is it healthy or not?  Is it                   improving or getting worse?


Accounts Receivable reporting in Power BI

The Suntico Accounts Receivable reporting solution for Sage 50 uses Microsoft Power BI to present much more useful, timely and engaging information. You can see for yourself, the first image is the receivables and collection report from Sage and the second image we have is the Suntico Accounts  Receivables report made using Power BI with the same data.

Microsoft’s Power BI gives you a whole new level of understanding to your receivables and collections reports. So what is so good about this?

  1. It is short - You no longer have to waste time reading lengthy reports that run to 15 or 30 pages. In Power BI you can easily fit the same information, plus a lot of useful contextual analysis all on one screen.

  2. It is up to date - with the Suntico Accounts Receivable solution you can automate your data refresh so that you know it is up to date within about 30 minutes or less.  This, combined with the richness of your information means that you no longer have to print your reports and you can trust them to be up to date.

  3. It gives you context- Not only do you see the individual line items of an overdue invoice, but you also see trends for that customer and for your entire business.

Main visualisations in the Suntico Accounts Receivable reporting solution for Sage 50

  • Classic aged debtors 30-60-90-90+ view
  • Receivables by aged week
  • Tree-map showing distribution of receivables and their age by customer and customer group
  • Actual vs Estimated receipts this month
  • One-line summary for each debtor showing outstanding, within due and overdue amounts
  • Outstanding by week due
  • Outstanding by week expected
  • Outstanding by sales invoice and its’ details
  • Customer transaction and balance history
  • Receivables and debtors by currency
  • Trends - 52-week and 13-week rolling Sales and Days Sales Outstanding
  • Customers with unallocated credits
  • Summary aggregations
    • Outstanding amount
    • Overdue amount
    • Overdue / Outstanding %
    • Days Sales Outstanding (DSO)
    • Weighted average debt age
    • Weighted average due days
    • Weighted average overdue days

Interactive demo

Why not try this with your own Sage 50 data?


Summary

In summary, all the information you need is just one or two clicks away. The visualizations show you the overall scenario in less than a minute. The Suntico Accounts Receivable solution in Power BI is brief, but you can always drill down all the way to each single line item. With very little training, you can even customize your Suntico Accounts Receivable report the way you want. Power BI makes it easier to understand your customers as you can see all of their activity as a group or individually. Trend lines give you a quick way to know how your business is doing. A simple bar diagram will tell you so much about your outstanding and overdue amount. Unallocated credits are another issue with receivables and collections but in Power BI you will know exactly why you have it. And the bonus is every time you update your Sage 50 system, your Power BI can be updated automatically.


Pricing and how to buy

The Suntico Accounts Receivable solution for Sage 50 is a pre-built Microsoft Power BI Template file that works with the Suntico BI OData feed for Sage 50 Accounts.

Element

Price

Basis

Suntico OData feed for Sage 50 Accounts

£828

Annual subscription

Fully documented Accounts Receivable template file for Power BI

£960

One-time charge

Custom reports

£480

Annual subscription per page as a managed service

Custom reports

£960

One-time charge per page as a Power BI template file

 


Prerequisites

The Suntico Accounts Receivable solution works with Sage 50 Accounts version 23 or higher and Sage 50cloud Accounts.


What if I am not using Sage 50?

If you are not using Sage 50, you can still purchase and use the Accounts Receivable template file for Power BI Desktop.  The template needs just the following data from Sage 50:

From the BICustomers table:

  • CustomerID
  • Name
  • Account_Ref
  • Balance
  • AveragePayDays (used to calculate estimated receipt dates)

From the BITransSplits table:

  • AccountRef
  • DueDate
  • GrossAmount
  • Date
  • Type (e.g. Sales Invoice, or Sales Credit)

Optionally from the BICurrencies table (if you want to track receivables by currency):

  • Code (the currency code for the customer account)

If you are able to export the equivalent of this data from your accounting software into a couple of Excel files, then it would be very simple to apply this same template file to your accounting data.


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

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

+353 1 567 4069

[email protected]